Geht es um die Planung von Investitionen und insbesondere der zugehörigen Abschreibungen mit Hilfe von Excel, stehen viele Unternehmer vor Herausforderungen. Häufig führen die genutzten Formeln dazu, dass in Summe mehr abgeschrieben wird als ein Vermögensgegenstand wert ist oder es werden für jede einzelne Investition dutzende von Zeilen vorbereitet. In diesem Beitrag zeigen wir ihnen, wie Sie Investitionen und Abschreibungen korrekt und flexibel mit nur 3 Zeilen in Excel planen können.
Hintergrund
Mit Investitionen sind Vermögensgegenstände des Anlagevermögens gemeint, also Wirtschaftsgüter, die länger als ein Jahr im Unternehmen genutzt werden und anders als die Vermögensgegenstände des Umlaufvermögens regelmäßig abgeschrieben werden.
In der Liquiditätsrechnung sind die Auszahlungen für Investitionen voll zu berücksichtigen (ggf. zuzüglich Umsatzsteuer), sobald die zugehörigen Rechnungen beglichen werden. In der Erfolgsrechnung (GuV) werden nur die Abschreibungen berücksichtigt. Mit den Abschreibungen erfasst man dabei planmäßige und außerplanmäßige Wertminderungen der Wirtschaftsgüter/Vermögensgegenstände (AfA = Absetzung für Abnutzung). In der Praxis ist die lineare Verteilung bzw. Abschreibung der häufigste Fall. Dazu werden die (Netto-)Anschaffungskosten gleichmäßig auf die voraussichtliche Nutzungsdauer des Wirtschaftsgutes verteilt.
In der Bilanz werden beim Anlagevermögen i.d.R. 3 Gruppen von Vermögensgegenständen unterschieden und separat ausgewiesen: 1. Immaterielle Vermögensgegenstände, 2. Sachanlagen und 3. Finanzanlagen.
In jeder Gruppe werden wiederum unterschiedliche Vermögensgegenstände einzeln geplant. Ziel ist es, die Investitionsplanung und Abschreibungsberechnungen im Folgenden so universell und flexibel aufzubauen, dass die einzelnen Berechnungsblöcke einfach kopiert werden können, um die Planung schnell auf eine beliebige Anzahl von Vermögensgegenständen erweitern zu können.
Ein solcher universeller Planungs- bzw. Berechnungsblock besteht pro Vermögensgegenstand typischerweise aus 3 Zeilen (siehe Screenshot).
In der ersten Zeile werden die Bezeichnung des Vermögensgegenstandes, ggf. der anzuwendende MwSt.-Satz sowie ein oder mehrere Netto-Investitionsbeträge in den jeweiligen Planperioden (= Monate) eingetragen. Die zweite Zeile ist eine Hilfszeile zur Berechnung der Abschreibungsbasis. In der dritten Zeile werden dann nach Eingabe der Nutzungsdauer (ND) die entsprechenden Abschreibungsbeträge für jeden Monat automatisch berechnet.
Der „Trick“ mit der Abschreibungsbasis
Die Abschreibungsbasis ist der Betrag in einer bestimmten Periode, der multipliziert mit dem monatlichen prozentualen Abschreibungssatz den Abschreibungsbetrag ergibt. Für das hier gewählte Beispiel bedeutet dies für die erste Investition in Periode 2 i.H.v. € 12.000: Bei einer Nutzungsdauer von 3 Monaten wird jeden Monat ein Drittel, also ein Betrag von € 4.000, abgeschrieben.
Der obige Screenshot zeigt, dass sich die Abschreibungsbasis bspw. in Periode 3 (grünes Rechteck) als Summe der drei Werte im roten Rechteck ergibt. Bei einer vom Beispiel abweichenden, längeren Nutzungsdauer, müsste das rote Rechteck entsprechend breiter sein, d.h. mehr Zellen müssten aufsummiert werden.
Um die Abschreibungsbasis für die Periode 4 zu ermitteln, müssen beide Rechtecke eine Spalte weiter nach rechts verschoben werden (Screenshot unten). Da in Periode 4 eine weitere Investition i.H.v. € 6.000 geplant ist, beträgt die Abschreibungsbasis nun € 18.000. Der Abschreibungsbetrag für diese Periode beträgt dementsprechend € 6.000 (1/3 von € 18.000; basierend auf der unterstellten Nutzungsdauer von 3 Monaten).
Mit diesem Ansatz können beliebig viele Investitionen in der gleichen Zeile geplant werden, wobei alle Berechnungen automatisch nur mit Hilfe der 3 Zeilen durchgeführt werden. Es sollte sich natürlich um vergleichbare Vermögensgegenstände handeln, da die Nutzungsdauer innerhalb eines Blockes immer identisch sein muss.
Formeltechnische Umsetzung in Excel
Die Nutzungsdauer (z.B. E11 in Screenshot unten) ist in Monaten einzugeben (falls Sie hier Jahre eingeben wollen, ist der Wert in der folgenden Zelle % p.m. entsprechend zu zwölfteln).
In Zelle F11 steht einfach der Kehrwert von Zelle E11, allerdings müssen wir über eine WENN-Funktion einen möglichen #DIV/0!-Fehler abfangen, und zwar für den Fall, dass der Vermögensgegenstand nicht abgeschrieben werden soll (z.B. bei Grundstücken). Die Formel kann dann bspw. folgendermaßen aussehen:
=WENN(E11=0;0;1/E11)
Die Mehrwertsteuerberechnung ist optional. Zunächst kann ein USt.-Satz über ein Dropdownfeld ausgewählt werden (Zelle E9), der zugehörige Prozentsatz wird dann automatisch in Zelle F9 über eine entsprechende SVERWEIS-Funktion aufgerufen (Hinweis: Die Investitionsbeträge in Zeile 9 ab Spalte J sind jeweils Nettobeträge. Die zugehörige MwSt. wird weiter unten auf dem Blatt berechnet, nicht im Screenshot gezeigt).
Kernstück der Abschreibungsberechnung ist die Formel für die Abschreibungsbasis. Diese wird über eine BEREICH.VERSCHIEBEN-Funktion umgesetzt. Der zentrale Teil sieht für Zelle J10 bspw. folgendermaßen aus:
=SUMME(BEREICH.VERSCHIEBEN(J9;0;0;1;-$E11)
Es sind allerdings zwei Dinge zusätzlich zu berücksichtigen. Zum einen darf natürlich erst dann nach links verschoben bzw. summiert werden (hier 3 Monate), wenn der laufende Planungszeitraum (Zeile 6) mindestens der Abschreibungsdauer in Monaten entspricht. Ansonsten würde bspw. auch die Zeilensumme in Spalte I mit einbezogen oder weitere Zelleninhalte links davon. Zum anderen müssen wir auch hier wieder einen Fehler abfangen, sofern nicht abgeschrieben werden soll, d.h. die Abschreibungsdauer 0 Monate beträgt. Die kopierbare Endformel in Zelle J10 sieht dann bspw. folgendermaßen aus:
=WENN($E11=0;0;WENN(J$6<$E11;SUMME(BEREICH.VERSCHIEBEN(J9;0;0;1;-J$6));SUMME(BEREICH.VERSCHIEBEN(J9;0;0;1;-$E11))))
Im ersten Teil der zweiten WENN-Funktion wird nur um den laufenden Monat verschoben, solange dieser kleiner (= kürzer) ist als die Abschreibungsdauer.
Last but not least muss noch die eigentliche Abschreibung pro Monat berechnet werden (Zeile 11). Dabei handelt es sich um das Produkt aus der prozentualen Abschreibungsrate p.m. (Zelle F11) und der Abschreibungsbasis in der jeweiligen Periode. Für J11 bspw.: =$F11*J10
Achten Sie auf die Verankerung (= $-Zeichen) der Zelle $F11, nur dann können Sie die Formel einfach durch die gesamte Zeile (nach rechts) kopieren.
Fertig ist der flexible Abschreibungsblock, den Sie nun beliebig oft kopieren können um auf diese Weise schnell eine umfangreiche Investitions- und Abschreibungsplanung aufzusetzen. Für eine integrierte Planung ist es noch ratsam, die buchmäßige Entwicklung aller Vermögensgegenstände in einem sogenannten Bilanzkonto zusammenzufassen (siehe folgender Screenshot).
Erhöhungen bzw. Investitionen werden von oben verlinkt (z.B. Eingabezeile 9, falls mehrere Blöcke eingefügt wurden müssen natürlich alle addiert werde), die Abschreibungen (= Verminderungen) stammen z.B. aus Zeile 11 (ggf. ebenfalls weitere Blöcke addieren). Sofern die Abschreibungen hier negativ ausgewiesen werden, ergibt sich der Schlussbilanzwert jeweils als Summe von Eröffnungsbilanzwert, Erhöhungen und Verminderungen. Dabei entspricht der Eröffnungsbilanzwert von Periode 2 natürlich dem Schlussbilanzwert von Periode 1 usw.
Wie üblich haben wir auch zu diesem Tutorial eine entsprechende Excel-Datei vorbereitet, die wir ihnen gerne kostenlos zur Verfügung stellen.
