Kapitalflussrechnung umgesetzt mit Excel

am Beispiel des größten Energieversorgungskonzern Deutschlands

• Kapitalflussrechung mit Globalgrößen
• Kapitalflussrechung mit Einzelgrößen
• Bewegung des Anlagevermögens
• Frühwarnindikatoren durch Tageszahlen
• Fondsrechnung

Arbeitsschritte:

Nach dem laden von Excel wird das neu erstellte Arbeitsblatt „KFR“ genannt (Die Endung „XLS“ bzw. „XLSX“ wird automatisch vom Programm vergeben). Dafür wird im Hauptmenü „Datei“ und „Speichern unter…“ gewählt.

Es folgt das Anlegen eines Bilanzschemas, das der Abbildung 1-3 entspricht.

Im folgenden Schritt soll das Arbeitsblatt „Tabele1“ in „Bilanz“ umbenannt werden. Dafür reicht ein Doppelklick auf den Namen des Arbeitsblattes oder durch ausführen folgender Schritte im Hauptmenü „Format“, „Blatt“ und „Umbenennen“.

In der Zelle A1 kommt:

Aktivseite (in Mio. €),

in B1:

Berichtsjahr

und bestätigen jeweils mit der Eingabetaste.

Die Spalte A wird benutzt zur Bezeichnung der Bilanzpositionen. Dafür sind gemäß Abbildung 1-1 folgende Bezeichnungen zu übernehmen.

Abbildung 1-1 als PDF

Schalten Sie nun auf Formelanzeige mit: „Optionen“, Bildschirmanzeige“, und klicken Sie im Dialogfenster das Kästchen vor „Formeln“ an. Das Fenster schließen Sie mit „OK“.

Die Zelle B2 dient der Aufnahme einer Zwischensumme.

Wir aktivieren sie Zelle B2 und tippen folgende Formel ein:
=SUMME(B3:B5)

In die Zelle B7 tippen wir ein:
=SUMME(B8:B11)

in B14:
=B2+B7+B13

in B17:
=SUMME(B18:B21)

in B25:
=SUMME(B26:B28)

in B30:
=SUMME(B31:B33)

und in B36:
=B17+B25+B30+B35+B23

Bedingungsfunktionen
Um jeweils schnell überprüfen zu können, ob die Summen der Aktiv- und Passivseite der Bilanz übereinstimmen, setzen wir in die Zelle B37 eine Formel. Diese bewirkt, dass die Zellen der Bilanzsummen (B14 und B36) miteinander verglichen werden. Bei Erfüllung der Bedingung „B14=B36“ soll in der Zelle B37 das Wort „summengleich“ stehen, im anderen Fall der Hinweis „Fehler“. Dies erreichen wir, wenn wir eintippen (ohne Leerzeichen):

=WENN(B14=B36;"summengleich";"Fehler") und Eingabe.

Kopieren von Spalten
Die eingetragenen Formeln sollen außer für Spalte B auch für Spalte C gelten. Da wir in Spalte B außer den Formeln noch keine Eintragungen vorgenommen haben, können wir diese Formeln global übernehmen. Dazu setzen wie den Mauszeiger auf das „B“ in der Spaltenkopfleiste und fahren bei gedrückter Taste nach C.
Beide Spalten sind nun aktiviert (geschwärzt). Nun wählen wir „Bearbeiten“, „Ausfüllen“ und „Rechts“ aus dem Menü. Ihre Tabelle sollte nun der Abbildung 1-2 entsprechen.

Abbildung 1-2 als PDF

Entnehmen Sie bitte die Eingabewerte der Abbildung 1-3. in den Formelzellen erhalten Sie jeweils die Werte der Abbildung.

Nach eintippen von „4,7“ in B13 sollte in der Zelle B14 der Wert „20043,3“ stehen und nach dem Ausfüllen von Zelle B35 wird die Anzeige in B37 „summengleich“ sein.

Die Spalte C mit den Zahlen für das Vorjahr füllen wir analog zur Spalte B aus. Wiederum werden die Summenwerte in C2, C7, C14, C17, C25, C30 und C36 und das Vergleichsergebnis in C37 automatisch aktualisiert.

Abbildung 1-3 als PDF

Spalte „Veränderung“

In die Zelle D1 tippen wir:

Veränderung und in D2:

=B2-C2 als Formel zur Berechnung der Differenz der Werte des Berichts- und des Vorjahrs. Wir erhalten in D2 die ursprüngliche Eintragung „-303,7“ als Ergebnis.
Anstatt nun die restlichen Formeln der Spalte D einzeln einzugeben, kopieren wir die Eintragungen von D2 nach unten. Wir markieren hierfür die Zellen D2 bis D36, wählen „Bearbeiten“, „Ausfüllen“, sowie „Unten“.

Die Eintragungen einiger Zellen löschen wir. Markiert wird hierzu D6, die Leertaste betätigt und mit der Eingabetaste abgeschlossen. Analog dazu verfahren wir auch mit D12, D15, D16, D22, D24, D29, D34 und D37.

In der Zelle D16 steht „#WERT!“. Die eingetragene Formel „=B16-C16“ konnte natürlich mit den Texteintragungen der Zellen B16 und C16 zu keinem sinnvollen Ergebnis führen. Wir übertippen diese mit:

Veränderung (Beschriftung der Passivseite)

Ausrichtung der Kapitalflusswerte

Die Eintragungen der Zellen D2 bis D16, die ein Minuszeichen enthalten, stellen Aktivminderungen dar. Der Wert des Berichtsjahres ist gegenüber dem Vorjahr niedriger. Dies bedeutet den Ausweis einer „Mittelherkunft“.

Globale Größen stehen in D2, D7 und D13. Wir wollen diese auf die linke Seite der Zelle setzen, d.h. sie linksbündig ausrichten.

In D3, D4, D9 und D11 befinden sich werte für Aktivmehrung. Sie sind in nachfolgenden Schritten der „Mittelverwendung“ zuzuordnen.

Es folge die Ausrichtung der Kapitalflusswerte für die Passivseite. Hier stellen die positiven Zahlen eine Mittelherkunft dar. Die Zellen, deren Inhalte ein negatives Vorzeichen tragen, enthalten Positionen der Mittelverwendung. Vergleichen Sie bitte Ihren Bildschirm mit der Abbildung 1-4.

Abbildung 1-4 als PDF

Bewegungsbilanz

Nun sollen die Werte der Spalte Veränderung D auf zwei Spalten verteilt werden. Als Beschriftung setzen wir in E1 und E16:

Mittelherkunft sowie in F1 und F16:
Mittelverwendung.

Um eine Doppelerfassung von Werten zu vermeiden, sind Einzelpositionen und Summen getrennt voneinander zu verarbeiten oder wir benutzen eine Wenn-Funktion. Dafür wird in E2 folgende Formel eingegeben:

=WENN(D2<0;-D2;" ") und in F2
=WENN(D2>0;D2;" ")

Anstatt nun die restlichen Formeln der Spalte E einzeln einzugeben, kopieren wir die Eintragungen von E2 nach unten. Wir markieren hierfür die Zellen E2 bis E14, wählen „Bearbeiten“, „Ausfüllen“, sowie „Unten“. Analog verfahren wir mit der Spalte F.

Für die Passivseite schreiben wir in E17:

=WENN(D17>0;D17;" ") und in F17
=WENN(D17<0;-D17;" ")

Nun verfahren wir wie zuvor und markieren die Zellen E17 bis E36 und wählen „Bearbeiten“, „Ausfüllen“, sowie „Unten“. Analog verfahren wir mit der Spalte F. Wenn Sie dies nachvollziehen, sollte Ihr Bildschirm der Abbildung 1-5 entsprechen.

Abbildung 1-5 als PDF

Erfolgsrechung

Für den nächsten Arbeitsschritt benötigen wir ein neues Tabellenblatt. Dafür betätigen wir im Menü „Einfügen“ und „Tabellenblatt“ welches durch folgende Schritte „Format“, „Blatt“ und „Umbenennen“ als „GUV“ benannt werden soll.

Markieren Sie Zelle A1 und tippen Sie:

Erfolgsrechnung (in Mio. €) und weiter mit
Umsatzerlöse
Zinsergebnis
Sonstige betriebliche Erträge
Personalaufwand
Abschreibungen des AV
Abschreibungen des UV
Sonstige betriebliche Aufwendungen

In Zelle A9 tippen Sie

Ergebnis der gewöhnlichen Geschäftstätigkeit
Außerordentliche Aufwendungen
Steuern
Jahresüberschuss
Gewinnvortrag
Einstellungen in die Gewinnrücklagen
Bilanzgewinn

Jeden Eintrag schließen Sie mit der Eingabetaste ab; damit wechseln Sie in die nächste Zeile. Mit dem Eintrag „Bilanzgewinn“ sollten Sie in Zeile 15 angekommen sein (Abbildung 1-6, linke Spalte). Wenn Sie den unteren Fensterrand erreichen, wird das Fenster automatische weitergeschoben, so dass Sie ohne Unterbrechung arbeiten können.

Um zu überprüfen, ob der Bilanzgewinn mit denen aus der Bilanz aufgeht, schreiben wir in Zelle B16:

=WENN(B15=Bilanz!B21;"lt. Bilanz richtig!";"Fehler")

in B9 tragen wir ein: =SUMME(B2:B8)

in B12: =SUMME(B9:B11)

und in B15: =SUMME(B12:B14)

Abbildung 1-6 als PDF

Anlagegitter

Für den nächsten Arbeitsschritt benötigen wir ein neues Tabellenblatt. Dafür betätigen wir im Menü „Einfügen“ und „Tabellenblatt“ welches durch folgende Schritte „Format“, „Blatt“ und „Umbenennen“ als „AG“ benannt werden soll.

Wir tragen die Beschriftungen für Zeilen und Spalten in Anlehnung an Abbildung 1-7 an.

Abbildung 1-7 als PDF

Die Werte des Berichtsjahres (B3, B4, und B5) finden Sie in der nun betrachteten Tabelle von F3 bis F6. Dafür Tragen Sie – sofern Sie sich an die Vorgaben gehalten haben – in F3 folgende Formel:

=Bilanz!B3 und analog dazu auch in F4 und F5

in F6 bilden wir die Summe mit
=SUMME(F3:F5)

Die Werte des Vorjahres werden in den Zellen B3, B4 und B5 übertragen. Dafür tragen Sie in B3 folgende Formel:
=Bilanz!C3 und analog dazu auch in B4 und B5

und auch hier bilden wir die Summe in B6 mit
=SUMME(B3:B5)

In E6 werden die Abschreibungen des Anlagevermögens aus der GUV in Höhe von „-6,3“ durch folgende Formel übernommen:
=GUV!B6*-1

In D6 wird die Differenz der Summen aus den Werten des Berichtsjahres und des Vorjahres gebildet. Dafür tippen wir in D6:
=F6-B6

Der Zugang der Investitionen in C7 bildet sich durch folgende Formel:
=D6+E6

Nun sollte Monitor der folgenden Abbildung entsprechen. Aus der Abbildung 1-8 können Sie auch die Formeln verfolgen.

Abbildung 1-8 als PDF

Für die Kapitalflussrechung zu Fonds 1 benötigen wir den Wert aus C6, da die Bilanz uns hier nicht weiterhelfen würde.

Fondstyp 1

Aus den Werten unserer Bilanz können wir nun den Fonds vom Typ 1 ermitteln. Er bezieht sich auf die liquiden Mittel – also Guthaben bei Kreditinstituten, Wertpapieren und Besitzwechsel. Wir pflegen hier noch eine Bruttobetrachtung, da nur Positionen der Aktivseite unserer Bilanz einbezogen werden.

Aufschlussreich sind die Beträge der Bestände (Spalte B und C), aber auch die Differenzen (Spalte D), die Entwicklungen dokumentieren.

Dafür benötigen wir ein neues Tabellenblatt welches wir im Menü durch „Einfügen“ und „Tabellenblatt“ erstellen und durch „Format“, „Blatt“ und „Umbenennen“ als „KFR1“ benennen.

Wir tragen die Beschriftungen für Zeilen und Spalten sowie den Formeln in Anlehnung an Abbildung 1-9 an.

Abbildung 1-9 als PDF

Die Größe des Fonds ergibt sich aus den Zahlen von Vor- und Berichtsjahr in den Spalten B und C. die Gegenüberstellung derselben liefert Informationen über Zu- oder Abnahme der liquiden Mittel. In unserem Fall ist eine Minderung um „-265,0“ Mio. € zu verzeichnen (vgl. Abbildung 1-9)

Die liquiden Mittel belaufen sich auf 954,8 Mio. € am Ende des Berichtsjahres. Im Vergleich zum Vorjahr sind sie um -265,0 Mio. € gesunken. Die Frage, worauf dies zurückzuführen ist, lässt sich durch Analyse der Gegenposition beantworten.

Zu Erstellung und Analyse der Gegenposition, arbeiten wir weiterhin in dem Tabellenblatt "KFR1". Die Formeln und Werte können Sie entsprechend der Abbildung 1-10 entnehmen.

Abbildung 1-10 als PDF

Des Weiteren können Sie die einzelnen Werte auch der unten aufgeführten Excel-Tabelle entnehmen. Dort wurden auch die Fondstypen 2 und 3 berechnet.

Excel-Tabelle KFR.xml downloaden

drucken