In dieser SQL-Kochstunde zaubern wir aus den Zutaten Window Functions und Bilanzdaten eine ausgewogene Cash-Flow-Rechnung. Wie aus den unzähligen Kochshows im Fernsehen gewohnt, haben wir auch schon mal etwas vorbereitet und stellen Ihnen die Zutaten, quasi ohne lästiges Einkaufen, digital bereit.
Erster Zubereitungsschritt: Man nehme das folgende Skript und führe es in einer Ihnen liebgewonnenen SQL-Server-Umgebung auf einer Testdatenbank aus.
--Tabelle erstellen
CREATE TABLE [dbo].[Bilanzdaten](
[Periode] [int] NULL,
[Konto] [varchar](50) NULL,
[Betrag] [float] NULL
) ON [PRIMARY]
GO
--Tabelle befüllen
INSERT INTO Bilanzdaten VALUES (2015,'Sachanlagen', 500)
INSERT INTO Bilanzdaten VALUES (2015,'Finanzanlagen', 200)
INSERT INTO Bilanzdaten VALUES (2016,'Finanzanlagen', 90)
INSERT INTO Bilanzdaten VALUES (2016,'Sachanlagen', 620)
INSERT INTO Bilanzdaten VALUES (2015,'Vorräte', 300)
INSERT INTO Bilanzdaten VALUES (2016,'Vorräte', 240)
INSERT INTO Bilanzdaten VALUES (2015,'Forderungen aus L.L.', 100)
INSERT INTO Bilanzdaten VALUES (2016,'Flüssige Mittel', 525)
INSERT INTO Bilanzdaten VALUES (2016,'Forderungen aus L.L.', 150)
INSERT INTO Bilanzdaten VALUES (2015,'Flüssige Mittel', 500)
INSERT INTO Bilanzdaten VALUES (2015,'Gezeichnetes Kapital', 100)
INSERT INTO Bilanzdaten VALUES (2015,'Kapitalrücklage', 400)
INSERT INTO Bilanzdaten VALUES (2016,'Gezeichnetes Kapital', 115)
INSERT INTO Bilanzdaten VALUES (2016,'Kapitalrücklage', 365)
INSERT INTO Bilanzdaten VALUES (2015,'Gewinnrücklage', 100)
INSERT INTO Bilanzdaten VALUES (2016,'Gewinnrücklage', 245)
INSERT INTO Bilanzdaten VALUES (2015,'Jahresüberschuss', 200)
INSERT INTO Bilanzdaten VALUES (2016,'Jahresüberschuss', 235)
INSERT INTO Bilanzdaten VALUES (2015,'Rückstellungen', 200)
INSERT INTO Bilanzdaten VALUES (2016,'Rückstellungen', 130)
INSERT INTO Bilanzdaten VALUES (2015,'Verbindlichkeiten aus L.L.', 200)
INSERT INTO Bilanzdaten VALUES (2016,'Verbindlichkeiten geg. Kreditinstituten', 260)
INSERT INTO Bilanzdaten VALUES (2016,'Verbindlichkeiten aus L.L.', 275)
INSERT INTO Bilanzdaten VALUES (2015,'Verbindlichkeiten geg. Kreditinstituten', 300)Und so einfach haben wir schon unsere erste Zutat: die Bilanzdaten in Tabellenform. Ganz ohne weitere Zutat kommen wir dann aber doch nicht aus. Mit dem zweiten Skript erstellen wir noch zusätzlich die Kontendimension.
-- Tabelle erstellen
CREATE TABLE [dbo].[T_IMPORT_DIM_Konten](
[Konto] [varchar](50) NULL,
[Kontenkategorie] [varchar](50) NULL,
[Kontenklasse] [varchar](50) NULL
) ON [PRIMARY]
GO
-- Tabelle befüllen
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Sachanlagen','Anlagevermögen', 'Aktiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Finanzanlagen', 'Anlagevermögen','Aktiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Vorräte','Umlaufvermögen','Aktiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Forderungen aus L.L.', 'Umlaufvermögen','Aktiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Flüssige Mittel','Umlaufvermögen','Aktiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Gezeichnetes Kapital','Eigenkapital','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Kapitalrücklage', 'Eigenkapital','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Gewinnrücklage', 'Eigenkapital','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Jahresüberschuss', 'Eigenkapital','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Rückstellungen', 'Rückstellungen','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Verbindlichkeiten aus L.L.', 'Fremdkapital','Passiva')
INSERT INTO T_IMPORT_DIM_Konten VALUES ('Verbindlichkeiten geg. Kreditinstituten', 'Fremdkapi-tal','Passiva')Damit haben wir aber schon unsere Grundzutaten zusammen. Jetzt müssen wir diese nur noch mit unserem heiß geliebten Universal-Küchengerät, dem DeltaMaster Modeler, so lange verrühren bis eine ho-mogene Bilanz-Masse herauskommt. Das Ergebnis kann sich schon mal sehen lassen:
Abbildung 1 Bilanzbericht in DeltaMaster
Jetzt könnte schon Schluss sein, wenn wir uns mit dem Bilanz-Gericht zufrieden geben.
Bewegungsbilanz erstellen mit Window Functions
Aber wir sind heute voller Tatendrang und wollen dem Gericht die besondere DeltaMaster-Note verpassen. Dem Bilanz-Gericht wollen wir mit noch relativ neuen SQL-Gewürzen LAG und LEAD aus der Window-Functions-Gattung ein weiteres Geschmackserlebnis namens Bewegungsbilanz bereiten.
Mit LAG und LEAD lassen sich nämlich sehr spannende Berechnungen durchführen, die wir normaler-weise nur aus unserer OLAP-Welt kennen. Mit LAG bzw. LEAD ist es möglich, den Vorgänger- bzw. Nachfolgerdatensatz relational nach den angegebenen Kriterien zu bestimmen. Mit der nachfolgenden Syntax lassen wir uns für unsere Kennzahl „Betrag“ den Vorgänger, per ORDER BY geordnet nach der Spalte „Konto“, bestimmen. Unser Ziel ist es, für jedes Konto den Vorjahreswert zu bestimmen, um dar-aus schlussendlich das Delta zwischen dem heutigen Saldo und dem Vorjahressaldo zu ermitteln. Bei unserem ersten Versuch scheint das jedoch nicht richtig zu funktionieren. Bei den ersten drei Datensätzen funktioniert es vermeintlich einwandfrei, jedoch hat dies weniger mit unserem Können als mit der Tatsache zu tun, dass die Periode zufällig für die ersten Datensätze auch richtig sortiert ist. Es fehlt uns also noch ein bisschen an der richtigen Gewürzmischung.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag) OVER (ORDER BY Konto) AS Vorjahreswert
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 2 Ergebnis des 1. SQL-Statements
Bei unserem nächsten Anlauf wollen wir den Fehler wettmachen und geben noch etwas mehr Sortierung hinzu. Wenn wir uns diesmal das Ergebnis anschauen, wird mit der Periodenspalte in der ORDER-BY-Klausel die Periode richtig sortiert, aber das Ergebnis ist immer noch nicht richtig. Es wird zwar immer der Vorgänger ermittelt, jedoch wird dabei nicht unterschieden, um welches Konto es sich dabei han-delt. Damit wird kurzerhand dem 2015er Betrag für die Forderungen aus L.L. der 2016er Betrag des Kontos „Flüssige Mittel“ als Vorjahreswert zugeordnet. Also immer noch nicht richtig abgestimmt.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag) OVER (ORDER BY Konto, Periode) AS Vorjahreswert
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 3 Ergebnis des 2. SQL-Statements
Was uns jetzt helfen könnte, wäre eine Möglichkeit, die Konten separat zu betrachten und hierzu den Vorgänger bzw. den Nachfolger zu ermitteln. Und siehe da, das hat die Syntax auch vorgesehen. Mit der PARTITION BY-Klausel können wir unsere Daten mundgerecht portionieren. Wenn wir dies nun für die Konten machen, kommen wir endlich auch zum gewünschten Ergebnis. Der Vorjahreswert wird nun richtig ermittelt – und zwar für jedes einzelne Konto.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Vorjahreswert
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 4 Ergebnis des 3. SQL-Statements
Die LAG und LEAD Syntax hält jedoch noch weitere Parameter bereit. Bis jetzt haben wir immer den direkten Nachfolger bzw. Vorgänger bestimmt. Die LAG bzw. LEAD Formel ist aber auch dahingehend flexibel, mit welchem Abstand sie den Vorgänger oder Nachfolger ermitteln soll. In nachfolgendem Statement wird mit der 2 in der LAG Formel z.B. der Vorvorgänger ermittelt. Die 2 gibt also damit den genauen Versatz an. Wenn man diesen Parameter nicht einstellt, so wie es bei den vorherigen Beispielen der Fall war, wirkt dieser so, als wäre eine 1 eingetragen.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag,2) OVER (PARTITION BY Konto ORDER BY Periode) AS Vorjahreswert
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 5 Ergebnis des 4. SQL-Statements
Neben dem Offset-Parameter gibt es einen weiteren Parameter in der LAG/LEAD-Syntax, welcher den Default-Wert bestimmt. Wie wir in den vorherigen Beispielen sehen konnten, wurde uns für die Datensätze, für die es keinen Vorgänger gab, eine NULL ausgegeben. Mit dem Default-Parameter lässt sich dieser Wert wiederum anpassen. Da wir die Bewegungsdaten zwischen dem Vorjahres- und dem Jahreswert ermitteln wollen, empfiehlt es sich in unserem Beispiel als Default die Betrags-Spalte heranzuziehen. Damit erreichen wir, dass es bei der Subtraktion des Vorjahreswertes zu einem Delta von 0 kommt, falls es für den Datensatz keinen Vorjahreswert gibt.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag,1,Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Vorjahreswert
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 6 Ergebnis des 5. SQL-Statements
Im nächsten Schritt ermitteln wir nun noch die Differenz zwischen Jahres- und Vorjahreswert, indem wir die Betragsspalte von dem mittels LAG-Formel ermittelten Vorjahreswert abziehen.
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag,1,Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Vorjahreswert
,Betrag - LAG(Betrag,1,Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Differenzbetrag
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten]
Abbildung 7 Ergebnis des 6. SQL-Statements
Nachdem wir den Differenzbetrag dank der Window Functions relational ermittelt haben, fehlt uns für die Bewegungsbilanz nur noch ein weiterer Schritt, um die Konten nach ihrer Flussbewegung zu kategorisieren und zu klassifizieren. Hierfür nutzen wir zu allererst einmal unsere Grundberechnung, die wir in ein WITH-Statement kleiden, um uns im nächsten Schritt auf die vorberechneten Ergebnisse beziehen zu können. Damit wir nun die Konten schematisch den Kategorien und Klassen der Bewegungsbilanz zu-ordnen können, müssen wir per LEFT JOIN noch ein Attribut aus der Kontendimension heranziehen. Das Attribut Kontenklasse aus der Kontendimension enthält nämlich die Information, ob es sich bei dem Bilanz-Konto um ein Aktiv- oder Passivkonto handelt. Ist das Konto z. B. ein Aktivkonto und gab es im betrachteten Zeitraum eine Bestandsmehrung, so spricht man hier von einer Aktivzunahme. Gleichzeitig ist diese Aktivzunahme als Mittelverwendung klassifiziert. Ebenso sind Passivabnahme (also eine Bestandsminderung eines Passivkontos) der Klasse Mittelverwendung zuzuordnen. Auf der anderen Seite ergibt sich aus der Aktivabnahme und Passivzunahme die Klasse Mittelherkunft. Mit Hilfe der Konten-klassen-Informationen aus der Kontendimension und den vorberechneten Bewegungsdaten lässt sich nun über das CASE-Statement diese schematische Logik abbilden. Damit sind wir unserem Ziel der Cash-Flow-Rechnung einen weiteren Schritt nähergekommen.
ALTER View [dbo].[V_IMPORT_FACT_Bewegungsbilanz] AS
WITH Schritt1 AS (
SELECT [Periode]
,[Konto]
,[Betrag]
,LAG(Betrag,1,Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Vorjahreswert
,Betrag - LAG(Betrag,1,Betrag) OVER (PARTITION BY Konto ORDER BY Periode) AS Differenzber-trag
FROM [Blog_Bewegungsbilanz].[dbo].[Bilanzdaten])
SELECT Schritt1.*
,Case
WHEN k.Kontenklasse = 'Aktiva' AND Differenzbertrag > 0 THEN 'Aktivzunahme'
WHEN k.Kontenklasse = 'Aktiva' AND Differenzbertrag < 0 THEN 'Aktivabnahme'
WHEN k.Kontenklasse = 'Passiva' AND Differenzbertrag > 0 THEN 'Passivzunahme'
WHEN k.Kontenklasse = 'Passiva' AND Differenzbertrag < 0 THEN 'Passivabnahme'
END Kategorie_Bewegungsbilanz
,Case
WHEN k.Kontenklasse = 'Aktiva' AND Differenzbertrag > 0 THEN 'Mittelverwendung'
WHEN k.Kontenklasse = 'Aktiva' AND Differenzbertrag < 0 THEN 'Mittelherkunft'
WHEN k.Kontenklasse = 'Passiva' AND Differenzbertrag > 0 THEN 'Mittelherkunft'
WHEN k.Kontenklasse = 'Passiva' AND Differenzbertrag < 0 THEN 'Mittelverwendung'
END Klasse_Bewegungsbilanz
FROM Schritt1
LEFT JOIN [Blog_Bewegungsbilanz].[dbo].[T_IMPORT_DIM_Konten] k
ON Schritt1.Konto = k.Konto
Abbildung 8 Ergebnis des 7. SQL-Statements
ALTER VIEW [dbo].[V_IMPORT_DIM_Kategorie_Bewegungsbilanz] AS
SELECT DISTINCT
[Kategorie_Bewegungsbilanz]
FROM V_IMPORT_FACT_Bewegungsbilanz
GO
ALTER VIEW [dbo].[V_IMPORT_DIM_Klasse_Bewegungsbilanz] AS
SELECT DISTINCT
[Klasse_Bewegungsbilanz]
FROM V_IMPORT_FACT_BewegungsbilanzGeben wir nun in den Modeler unsere erweiterte Bewegungsdaten-Tabelle und fügen noch die neu hinzugekommenen Dimensionen Kategorie_Bewegungsbilanz und Klasse_Bewegungsbilanz hinzu, so kommen wir endlich zu unserem lang ersehnten Geschmackserlebnis Bewegungsbilanz. Nun sind wir auf unserer kulinarischen Reise nur einen Schritt weit von unserem Endergebnis, der Cash-Flow-Rechnung entfernt.
Abbildung 9 Bewegungsbilanzbericht in DeltaMaster
Über die Bilanz und Bewegungsbilanz hin zur Cash Flow Rechnung
In unserem Beispiel betrachten wir das Unternehmen aus externer Sicht und haben dementsprechend nur die Bestandssalden zur Verfügung, wodurch keine Aussage über die Vorgänge getroffen werden kann, die zu den Salden geführt haben. Um die Vorgänge feiner und genauer den verschiedenen Cash-Flow-Arten hinzurechnen zu können, müssten weitere Informationen wie der Anlagenspiegel und die Erfolgsrechnung vorliegen.
Um unsere Cash-Flow-Rechnung umzusetzen, wechseln wir nun vom relationalen Modell ins Frontend, denn DeltaMaster liefert uns alle Funktionalitäten, um die Cash-Flow-Rechnung direkt hier aufzubauen.
Für die Cash-Flow-Rechnung benötigen wir eine Reihe an selbst erstellten Analysewerten, um das Cash-Flow-Rechnungsschema abzubilden. Folgende Tabelle zeigt das Schema sowie die Berechnungslogik der einzelnen Konten.
Tabelle 1 Berechnungslogiken der Cash-Flow Konten
Nachdem wir alle Analysewerte im Analysewertbrowser erstellt haben, können wir nach dem oben gezeigten Schema unseren Cash-Flow-Bericht aufbauen.
Abbildung 10 Cash-Flow-Bericht in DeltaMaster
Damit sind wir am Ende unseres Kochrezeptes angelangt. Wenn Sie beim Nachkochen von uns unterstützt werden wollen, stehen Ihnen die Berater und Beraterinnen von Bissantz gerne zur Verfügung.
