Automatisierte Bestandsbewertung in Excel: Die Verbrauchsfolgeverfahren FIFO und LIFO

Geht es im Rechnungswesen, im Controlling oder bei der integrierten Finanzplanung um die Bewertung von Vorratsvermögen unter Berücksichtigung eines der zulässigen Verbrauchsfolgeverfahren, stellt dies viele Akteure, trotz guter Excel-Kenntnisse, immer wieder vor Herausforderungen. Dies gilt umso mehr, wenn unterjährig zahlreiche Zu- und Abgänge zu unterschiedlichen Preisen bzw. Kosten vorliegen. Dieser Artikel erläutert am Beispiel des FIFO-Verfahrens einen VBA-freien Ansatz, mit dem sich derartige Bestandsbewertungen in Excel einfach modellieren lassen.

Hintergrund: Verbrauchsfolgeverfahren

Da der Hauptfokus dieses Beitrages auf der Excel-technischen Modellierung liegen soll, hier nur einige kurze Erläuterungen zum betriebswirtschaftlichen Hintergrund. Verbrauchsfolgeverfahren dienen im Rechnungswesen und im Steuerrecht zur einfachen Ermittlung von Anschaffungs- und Herstellungskosten gleichartiger Vermögensgegenstände des Umlaufvermögens, wobei jeweils eine bestimmte Reihenfolge unterstellt wird, in der diese Vorräte gelagert bzw. verbraucht oder verkauft werden. Insbesondere bei stark schwankenden Marktpreisen (auf Einkaufs- und Verkaufsseite) können im Rahmen der sog. Bewertungsvereinfachungsverfahren für gleichartige Gegenstände des Vorratsvermögens fiktive Annahmen gemacht werden, in welcher Reihenfolge die Gegenstände verbraucht bzw. veräußert werden (= Verbrauchsfolgefiktion).

Während bei der FIFO-Methode unterstellt wird, dass die zuerst angeschafften oder hergestellten Vermögensgegenstände zuerst verbraucht oder veräußert worden sind (first in - first out), unterstellt die LIFO-Methode, dass die zuletzt angeschafften oder hergestellten Vermögensgegenstände des Vorratsvermögens zuerst verbraucht oder veräußert werden (last in – first out). Je nach verwendetem Verfahren ergeben sich unterschiedliche wertmäßige Endbestände und auch verschiedene Werte für die Kosten der verkauften Waren (Herstellungskosten bzw. COGS = costs of goods sold).

Bei der Anwendung der Bewertungsvereinfachungsverfahren kann darüber hinaus noch unterschieden werden zwischen periodischer und permanenter mengen- und wertmäßiger Erfassung der Zu- und Abgänge. Während das (einfachere) periodische Verfahren nur am Periodenende zur Bewertung des Endbestands angewendet wird (unterjährige Abgänge werden nicht bewertet), wird beim permanenten Verfahren jeder Abgang individuell bewertet.

Auf diese Weise erhält man unterjährig erheblich bessere Informationen bezüglich der jeweils aktuellen Bestandsbewertung bzw. bezüglich des zutreffenden Aufwands. Seit der Änderung des Bilanzmodernisierungsgesetzes (BilMoG) sind nach § 256 HGB nur noch das LIFO- und FIFO-Verfahren handelsrechtlich zulässig. Der Vollständigkeit halber sei nochmals auf das Niederstwertprinzip hingewiesen (vgl. § 253 Abs. 3 bis 5 HGB). Der in der Bilanz zu erfassende Buchwert der Lagerbestände kann bzw. muss in diesen Fällen ggf. geringer ausfallen als mittels der Bewertungsvereinfachungsverfahren ermittelt.


Mythen und Realität bezüglich der Modellierung in Excel

Nutzt man seine favorisierte Suchmaschine, um Lösungsansätze zur Berechnung der genannten Verbrauchsfolgeverfahren mittels Excel zu finden, so stößt man u.a. auf undurchsichtige benutzerdefinierte Funktionen (sog. User Defined Functions (UDFs)), umfangreiche Rasterberechnungen, ähnlich denen für Abschreibungen, oder speziell entwickelte VBA-Makros. Erst kürzlich ist mir ein weiterer, relativ komplexer Ansatz mittels einer geschachtelten Lambda-Funktion (neu ab Excel 365) untergekommen.

Vielfach wird von den Autoren einfach behauptet, dass nur unter Verwendung der genannten Techniken eine Kalkulation umsetzbar sei, weil eine automatisierte Berechnung mittels "normaler" Formeln in Excel nicht möglich wäre. Dass die Realität anders aussieht, möchte ich Ihnen im folgenden Beispiel zeigen. Dabei zeigt der vorgestellte Modellierungsansatz exemplarisch für die FIFO-Methode, dass sehr wohl eine automatisierte Berechnung in Excel mittels klassischer Formeln möglich ist. Die Berechnung erfolgt dabei sogar auf permanenter Basis, berücksichtigt also jeden Zu- bzw. Abgang individuell.

Die Ausgangslage

In unserem fiktiven Beispiel soll es um den Kauf und Verkauf von Mikroprozessoren eines bestimmten Typs gehen. Sowohl die Einkaufs- als auch die Verkaufspreise schwanken unterjährig und sind jeden Monat unterschiedlich. Die zugehörige Excel-Datei steht hier kostenlos zum Download zur Verfügung und hilft ggf. beim Nachvollziehen der hier erläuterten Formeln.

Ein Hinweis in eigener Sache:

Wir erstellen sämtliche Excel-Modelle/-Berechnungen unter Anwendung von etablierten Financial Modelling Standards (vgl. z.B. www.fast-standard.org). Aus diesem Grunde arbeiten wir horizontal. Dadurch können die Formeln einfach nach rechts durchkopiert und der Zeithorizont mit wenigen Klicks beliebig verlängert werden. Alle Eingabezellen sind einheitlich und klar formatiert (Einheiten für jede Zeile angegeben). Außerdem werden negative Zahlen gemäß internationalen Standards in Klammern (nicht mit Minuszeichen) dargestellt. In Spalte G finden sich, wo sinnvoll, sogenannte Zeilensummen. Es kann nicht oft genug betont werden, dass die Verwendung solcher Standards für eine hohe Flexibilität, Nachvollziehbarkeit und einfache Bedienbarkeit von Finanzmodellen (auch durch externe Dritte) sorgt.

Artikel_DG_22_Abb_01.png

img20px.jpg  Abb. 1 (Für größere Ansicht bitte anklicken) >>

Wie in Abb. 1 zu sehen, nutze ich für das Beispiel eine monatliche Periodizität. Für die hier erläuterten Formeln spielt das aber keine Rolle, theoretisch können die Berechnungen auch auf Tagesbasis (z.B. bei Aktientransaktionen) oder auch quartalsweise erfolgen.
Im oberen Teil benötigen wir zunächst unsere Annahmen für die Käufe bzw. Verkäufe. Dabei sind jeweils die Stückzahl sowie die zugehörigen Preise anzugeben.

Daraus kann dann zunächst der Lagerbestand zu jeder Periode (Anzahl Mikroprozessoren) ermittelt werden. Der jeweilige Bestand ergibt sich als Differenz der kumulierten Käufe und der kumulierten Verkäufe. Da wir nicht mehr verkaufen können, als auf Lager liegt, ergänzen wir eine Kontrollzeile die immer dann eine Fehlermeldung ausgibt, wenn der Bestand in einer Periode negativ werden sollte.

Exkurs Fehlerkontrolle: Die Kontrollzellen enthalten entweder eine "0" wenn alles in Ordnung ist, oder eine "1", wenn eine bestimmte Bedingung nicht erfüllt ist (hier größer oder gleich 0). Dies realisieren wir über eine einfache WENN-Funktion, z. B.

 =WENN(Ihre_Bedingung_falsch;1;0)

also beispielsweise in Zelle I20

=WENN(I18<0;1;0)

Damit die Zelle optisch gut sichtbar wird, definieren Sie eine auffällige bedingte Formatierung für den Fall, dass der Zellwert ungleich "0" ist (z.B. Rotfärbung) und nutzen ein benutzerdefiniertes Zahlenformat, damit nicht 0 oder 1, sondern aussagekräftige Informationen angezeigt werden (z. B. "OK" und "Fehler").


Schließlich können wir das zugehörige Bilanzkonto für die Vorräte (diesmal wertmäßig in EUR) entwickeln. Die Eröffnungsbilanz entspricht immer der Schlussbilanz der Vorperiode. Die Schlussbilanz einer Periode ergibt sich aus der Eröffnungsbilanz und den Änderungen innerhalb der betrachteten Periode. Alle Käufe werden addiert, alle Verkäufe subtrahiert (bzw. da negativ ausgewiesen, ebenfalls addiert). Außerdem muss die Bestandsbewertung als dritte Komponente etwaige Gewinne, die sich aus Preisänderungen ergeben, positiv, etwaige Verluste negativ berücksichtigen (Hinweis: Im vorliegenden Fall ist es daher sinnvoll, auch in den Monaten Verkaufspreise in Zeile 13 einzutragen, wenn weder Käufe noch Verkäufe stattfinden, weil sonst der komplette Bestand auf null abgewertet wird.)

Die Berechnungsformeln dazu sind relativ trivial. Kauf- bzw. Verkaufswerte ergeben sich jeweils als Produkt aus den zugehörigen Annahmen für Mengen und Preise. Ein Gewinn bzw. Verlust wird ermittelt, indem zunächst der Anfangsbestand (Stücke) mit dem jeweiligen Verkaufspreis bewertet wird und davon dann der Eröffnungsbilanzwert sowie die Käufe der Periode abgezogen werden (Beispiel in K25):

=(K12+K18)*K$13-SUMME(K$23:K$24))


Sofern alle Mikroprozessoren verkauft sind (z.B. im November), muss der Schlussbilanzwert natürlich wieder null sein, so dass wir hier auch eine Art Kontrolle haben. Wir haben nun also bereits (in Zeile 24) die Informationen, was die Käufe der jeweiligen Periode kosten (= Materialkosten) und auch die Umsatzerlöse aus den Verkäufen (Zeile 26, natürlich mit positivem Vorzeichen).Damit haben wir eine gute Basis, die spannende Frage aber lautet: Wie hoch sind die Kosten der verkauften Mikroprozessoren (= COGS) auf FIFO-Basis?

Das Problem "zu Fuß" gelöst
  • Der erste Verkauf im Februar gestaltet sich noch relativ einfach. Da dieser komplett aus der im Januar für 20 EUR/Stück eingekauften Charge bestritten werden kann, also insgesamt 100 Stücke x 20 EUR = 2.000 EUR.
  • Beim zweiten Verkauf im April wird es schon komplizierter. Wir haben ja noch 100 Stücke zu 20 EUR aus dem ersten Kauf auf Lager und müssen weitere 50 Stücke aus dem zweiten Kauf berücksichtigen, also: 100 x 20 EUR + 50 x 25 EUR = 3.250 EUR (bzw. 21,67 pro Stück).
  • Analog gilt für den 3. Verkauf im Juni: 50 x 25 EUR (aus Kauf 2) + 20 x 30 EUR (aus Kauf 3) = 1.850 EUR (bzw. 26,43 pro Stück)
  • Für den vierten und letzten Verkauf im November gilt: 30 x 30 EUR (aus Kauf 3) + 100 x 20 EUR (aus Kauf 4) + 200 x 25 EUR (aus Kauf 5) = 7.900 EUR (bzw. 23,94 pro Stück).

Hat man das Konzept einmal nachvollzogen, ist die Berechnung auch für Nicht-Betriebswirte einfach nachvollziehbar. Dass die Werte korrekt sind, zeigt schnell eine Summierung aller vier Verkäufe. Die Summe in Höhe von 15.000 EUR entspricht genau der Summe aller Käufe im Bilanzkonto (Zelle G24 in Abb. 1).

Wie aber lässt sich diese Berechnungslogik modellieren bzw. automatisieren?
Mit Hilfe von nur drei Zeilen lassen sich alle Werte (ohne VBA, UDFs etc.) automatisch berechnen. Wie das in der Excel-Datei aussieht, zeigt Abb. 2 (rote Markierung).

Artikel_DG_22_Abb_02.png

img20px.jpg Abb. 2 (Für größere Ansicht bitte anklicken) >>


Schritt 1:

Zunächst ermitteln wir (in Zeile 31) die Periode (hier also den Monat), für den wir Käufe berücksichtigen müssen, um die gewünschte Anzahl an Mikroprozessoren verkaufen zu können. Für den ersten Verkauf ist das einfach Periode 1 (Jan 22), weil nur 100 der 200 dort erworbenen Mikroprozessoren benötigt werden.

Beim zweiten Verkauf wird Periode 3 (Mär 22) zurückgegeben. Dies liegt daran, dass die noch verbleibenden Mikroprozessoren aus Kauf 1 nicht ausreichen und teilweise auch Stücke aus Kauf 2 verwendet werden müssen.

Analog wird für den dritten Verkauf Periode 5 (Mai 22) zurückgegeben. Auch hier müssen teilweise Stücke aus dem letzten Kauf davor (Kauf 3) verwendet werden.

Der letzte Verkauf ergibt Periode 10, da ja alle Prozessoren verkauft werden, somit auch die aus dem letzten Kauf im Oktober 2022. Alle anderen Perioden weisen Nullwerte („-“) aus, da ja keine Verkäufe getätigt werden. Wir finden die erste Periode, in der die kumulierten Käufe (Zeile 16) größer oder mindestens gleich der Verkaufsmenge ist, über die Formel in Zelle I31:

=VERGLEICH(WAHR;$I16:I16>=I$17;0)

Diese Formel können wir nach rechts durchkopieren, müssen aber vorher bei $I16 durch Einfügen eines $-Zeichens aus dem Spaltenbuchstaben einen absoluten Bezug machen.
Das dritte Argument der VERGLEICH-Funktion muss hier 0 lauten, so dass Excel den ersten Wert, der mit dem Wert für das Suchkriterium genau übereinstimmt, zurückgibt. Beispielsweise funktioniert in Zelle N31 die Formel

=VERGLEICH(WAHR;$I16:N16>=N$17;0)


dann folgendermaßen:
{I16>=N$17, J16>=N$17, K16>=N$17, L16>=N$17, M16>=N$17, N16>=N$17}
bzw.
={FALSCH, FALSCH, FALSCH, FALSCH, WAHR, WAHR}

Zum ersten Mal ist die Bedingung demnach an Position 5 erfüllt, weswegen dieser Wert ausgegeben wird. Jetzt ist noch etwas Feintuning nötig, damit die Formel in allen Perioden funktioniert und problemlos durchkopiert werden kann.

Da in der ersten Periode mit $I16:I16>=I$17 noch kein Array existiert und die VERGLEICH-Funktion in diesem Fall einen Fehler (#NV) ausgibt, fangen wir das Ganze über ein WENN-Funktion ab:

=WENN(I$5=1;(I$16>=I$17)*1;VERGLEICH(WAHR;$I16:I16>=I$17;0))

Zeile 5 enthält dabei in unserem Beispiel die laufende Periode. Eine weitere #NV-Meldung taucht dann auf, wenn die Verkäufe mangels Bestand nicht alle abgewickelt werden können (z.B. wenn unsere Kontrollzellen in Zeile 20 anschlagen, weil versucht wird, mehr zu verkaufen als auf Lager ist). Im Fall dieses Fehlers, soll einfach ein Nullwert ausgewiesen werden:

=WENNNV(WENN(I$5=1;(I$16>=I$17)*1;VERGLEICH(WAHR;$I16:I16>=I$17;0));)

Schließlich benötigen wir den Wert nur in den Perioden, in denen überhaupt Verkäufe stattfinden, so dass wir zur finalen (durchkopierbaren) Formel für Zelle I31 gelangen:

=WENN(I12;WENNNV(WENN(I$5=1;I$16>=I$17)*1;VERGLEICH(WAHR;$I16:I16>=I$17;0)););)

Diese letzte Erweiterung prüft, ob I12 ungleich null ist. In diesem Fall wird die danach folgende (geschachtelte) Formel angewendet, andernfalls einfach ein Nullwert ausgegeben.
Sicherlich fragen Sie sich jetzt, wofür benötigen wir das alles überhaupt? Die Antwort lautet, da wir im Folgenden die gesamten Kosten aller Verkäufe bis zum jeweiligen Zeitpunkt ermitteln wollen.
  • Der erste Verkauf im Februar wird aus den Käufen im Januar bestritten.
  • Der Verkauf im April wird bestritten aus Käufen im Januar und März.
  • Der Verkauf im Juni wird bestritten aus Käufen im März und Mai.
  • Der letzte Verkauf im November wird bestritten aus Käufen im Mai, September und Oktober.

Betrachten wir die Verkäufe noch einmal andersherum
:
  • Die Verkäufe im Februar und April verwenden alle im Januar gekauften Mikroprozessoren sowie einen Teil der in März bezogenen.
  • Die Verkäufe im Februar, April und Juni verwenden alle im Januar und im März gekauften Mikroprozessoren sowie einen Teil der in Mai bezogenen.
  • Die Verkäufe bis einschließlich November verwenden die Prozessoren aller Einkäufe bis September sowie einen Teil der der im Oktober eingekauften Chips (im Beispiel zufällig 100 %, da mit 330 Stücken der gesamte restliche Lagerbestand veräußert wird)

Damit haben wir einen Schlachtplan zur Ermittlung der gesamten Kosten für die jeweiligen Verkäufe:
  1. Bis zur Vorperiode der bei „Lfd. Periode letzter Kauf“ ausgewiesenen Periode werden alle Kosten summiert.
  2. Für die bei „Lfd. Periode letzter Kauf“ ausgewiesene Periode ist der prozentuale Anteil der Stücke zu kalkulieren, die aus dieser Periode zusätzlich verwendet werden müssen.
Die bei „Lfd. Periode letzter Kauf“ ausgewiesene Periode kann im Grunde auch als jeweilige „Break-even-Periode“ gesehen werden, weil die bis dort getätigten Käufe ausreichen, um alle Verkäufe durchführen zu können.

Schritt 2:

Wir werden nun also berechnen, wieviel Prozent aus der jeweiligen Break-Even-Periode zu verwenden sind, um exakt auf die gewünschte Verkaufsmenge zu kommen. Dazu nutzen wir die beiden Excel-Funktionen REST und BEREICH.VERSCHIEBEN.

Funktionsweise der REST-Funktion

Die REST-Funktion (Syntax: REST(Zahl;Divisor) gibt den Rest einer Division zurück. Das Ergebnis hat dasselbe Vorzeichen wie der Divisor. Beispielsweise gibt

  =REST(30;7)

den Wert 2 zurück. Im Grunde ermittelt Excel dabei, wie oft der Divisor vollständig in die Zahl passt und zieht das Produkt aus dieser Zahl und dem Divisor von der Zahl ab.

Im Beispiel 4*7 = 28, damit ergibt sich der Rest als 30-28=2.

Für unsere Berechnungen verwenden wir einen kleinen Trick, in dem wir als Divisor die Zahl 1 verwenden. Auf diese Weise erhalten wir den nicht ganzzahligen Rest der Division, also immer Werte größer (oder gleich) Null und kleiner als 1. Werden also bspw. noch 60 von 100 Stücken benötigt, ergibt

den Wert 0,6. Von der 100 Stück-Charge müssten also 60 % verwendet werden. Die in diesem Beispiel verwendeten absoluten Zahlen (60 und 100) werden wir natürlich gleich noch durch Formeln dynamisieren.

Funktionsweise der BEREICH.VERSCHIEBEN-Funktion

Allgemein gibt die BEREICH.VERSCHIEBEN-Funktion einen Bezug zurück, der gegenüber einem angegebenen Ausgangsbezug versetzt ist. Der zurückgegebene Bezug kann eine einzelne Zelle oder ein Zellbereich sein. Die Anzahl der zurückzugebenden Zeilen und Spalten können vorgegeben werden.

Die Syntax lautet:

BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])

Dabei bedeuten die Argumente Folgendes:
  • Bezug: Ausgangspunkt des Verschiebevorgangs
  • Zeilen: Anzahl der Zeilen, um die die obere linke Eckzelle des Bereichs nach oben (= negative Werte) oder nach unten (= positive Werte) verschoben wird
  • Spalten: Anzahl der Spalten, um die die obere linke Eckzelle des Bereichs nach links (= negative Werte) oder nach rechts (= positive Werte) verschoben wird.
  • Höhe: Höhe des neuen Bezugs in Zeilen (positiv nach unten und negativ nach oben)
  • Breite: Breite des neuen Bezugs in Spalten (positiv nach rechts u. negativ nach links)

Die beiden letzten Argumente Höhe und Breite sind lediglich optional. Die Funktion wird häufig (auch bei uns in Schritt 3 weiter unten) zusammen mit der SUMME-Funktion eingesetzt, um die Werte der Zellen des neuen Bezugs zu addieren.

FIFO – prozentualen Anteil der letzten Periode ermitteln

Lassen Sie uns die kombinierte Formel bzw. deren Funktionsweise ansehen und analysieren. Ausgangspunkt soll dabei die Zelle I32 sein:

=REST((I$17-BEREICH.VERSCHIEBEN($H$16;;I$31))/BEREICH.VERSCHIEBEN($H$8;;I$31);1)

Über den Teil

BEREICH.VERSCHIEBEN($H$16;;I$31)

wird die Zahl der kumulierten Käufe in der Break-Even-Periode ermittelt. Deswegen ist der Ausgangspunkt $H$16. Bewegen wir uns eine Spalte nach rechts, erhalten wir die kumulierten Käufe für Januar, zwei Spalten nach rechts die für Februar etc. Um wie viele Spalten es nach rechts gehen soll, bestimmt die weiter oben ermittelte Zahl in Zeile 31 (= "Lfd. Periode letzter Kauf" bzw. "Break-Even -Periode").

I$17-BEREICH.VERSCHIEBEN($H$16;;I$31)

zieht von den kumulierten Verkäufen der aktuellen Periode die kumulierten Käufe der Break-Even-Periode ab. Dabei ergibt sich immer ein negativer Wert, was aber zur Berechnung des Anteils beabsichtigt ist. Der Wert muss immer kleiner als (oder gleich) der kumulierten Anzahl in der Break-Even-Periode sein. Diese haben wir ja genau so definiert.

Der Teil

BEREICH.VERSCHIEBEN($H$8;;I$31)

im Nenner der REST-Funktion (beim Argument ZAHL) ist die Anzahl der eingekauften Mikroprozessoren in der Break-Even-Periode.

Auf diese Weise wird mit der Gesamtformel der erforderliche prozentuale Anteil berechnet. Da wir diese Berechnung nur in den Monaten benötigen, in denen tatsächlich Verkäufe stattgefunden haben und in denen tatsächlich eine Break-Even-Periode in Zeile 31 berechnet wurde, schachteln wir die Formel in eine entsprechende WENN-Funktion und erhalten beispielhaft für Zelle I32:

=WENN(I$12*I$31;REST((I$17-BEREICH.VERSCHIEBEN($H$16;;I$31))/
BEREICH.VERSCHIEBEN($H$8;;I$31);1);)

Eine kleine Falle lauert hier noch: Sofern es keinen Rest gibt, also der Wert der REST-Funktion gleich Null ist, ergibt auch die Gesamtformel den Wert Null. In diesem Fall muss aber der Anteil der Break-Even-Periode 100 % betragen, das heißt, dass alle Prozessoren dieses Monats zu berücksichtigen sind (nicht gar keine). Im Excel-Beispiel ist dies bspw. beim letzten Verkauf im November der Fall. Wir müssen deshalb in der nun anstehenden Kostenermittlung diesen Spezialfall noch berücksichtigen.

Schritt 3:

Wenn ich Sie bis hier noch nicht verloren habe, dann holen Sie sich am besten einen Kaffee, denn jetzt kommt der interessanteste Teil. Um die Kostenbasis aller Verkäufe (in Zeile 33) zu ermitteln, sind verschiedene Komponenten zu berücksichtigen:
A) Zunächst benötigen wir die Kosten aller Käufe bis zu dem Monat, der vor der Break-Even-Periode liegt (wie weiter oben erläutert). Dazu nutzen wir die Formel:

=SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31)

Diese Kosten erhöhen sich aber, sofern 100 % der Kosten aus der Break-Even-Periode verwendet werden müssen (der nächste Monat muss also ebenfalls berücksichtigt werden => unser Spezialfall, den ich am Ende von Schritt 2 erwähnt hatte). Wir erweitern daher die Formel für diesen Fall:

=SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31+(I$32=0)*1))

B) Jetzt müssen wir für die Break-Even-Periode den prozentualen Anteil der verwendeten Kosten ermitteln. Dies gelingt über:

=SUMME(BEREICH.VERSCHIEBEN($I$24;;;;I$31))-SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31))

Wir ermitteln also im ersten Teil einfach die Kaufkosten aller Monate (bis einschließlich der Break-Even-Periode) und ziehen davon (im 2. Teil) die Kaufkosten aller Monate vor der Break-Even-Periode ab. Damit bleiben nur die Kosten für die Break-Even-Periode übrig. Davon ist aber noch der „ungenutzte“ (also nicht benötigte) Anteil der Break-Even-Periode abzuziehen:

=BEREICH.VERSCHIEBEN ($H$24;;I$31)*(1-I$32)

Setzen wir diese Teile bis hierher zusammen, so erhalten wir:

=SUMME(BEREICH.VERSCHIEBEN($I$24;;;;I$31))-SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31))-BEREICH.VERSCHIEBEN($H$24;;I$31)*(1-I$32)+SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31+(I$32=0)*1))

C) Da wir nur die Kosten für die Verkäufe des jeweiligen Monats ermitteln müssen, ziehen wir von der soeben berechneten Kostensumme für alle Monate (inkl. des aktuellen Monats) einfach die Summe aus allen Vormonaten ab.

=SUMME(BEREICH.VERSCHIEBEN($I$24;;;;I$31))-SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31))-BEREICH.VERSCHIEBEN($H$24;;I$31)*(1-I$32)+SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31+(I$32=0)*1))-SUMME($H$33:H$33)

Achten Sie (bei allen hier erläuterten Formeln) auf die korrekten absoluten Bezüge ($-Zeichen), damit wir die Formeln später nach rechts durchkopieren können.
D) Auch hier benötigen wir die Berechnung wieder nur in den Monaten, in denen tatsächlich Verkäufe stattgefunden haben und in denen tatsächlich eine Break-Even-Periode in Zeile 31 berechnet wurde. Wir schachteln also wieder und erhalten die finale Formel (für Zelle I33):

=WENN(I$12*I$31;SUMME(BEREICH.VERSCHIEBEN($I$24;;;;I$31))-SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31))-BEREICH.VERSCHIEBEN($H$24;;I$31)*(1-I$32)+SUMME(BEREICH.VERSCHIEBEN($H$24;;;;I$31+(I$32=0)*1))-SUMME($H$33:H$33);)

Die Mathematiker und Experten unter den Lesern werden bemerken, dass die obige Formel komplexer ist, als sie sein müsste. Das habe ich aber aus didaktischen Gründen bewusst so gemacht. Die Formel lässt sich durch Umstellungen auf etwa die Hälfte der Länge kürzen. Dies würde den Beitrag sprengen, ist aber in der Excel-Datei (in Zeile 34) enthalten.

Fazit

Nach all den formeltechnischen Mühen ist es nun auch einfach möglich, für alle Verkäufe unter Anwendung des FIFO-Verfahrens die Gewinne bzw. Verluste zu ermitteln (gesamt und pro Stück, vgl. Abb. 3).

Artikel_DG_22_Abb_03.png

img20px.jpg  Abb. 3 (Für größere Ansicht bitte anklicken) >>

Verkaufserlös und Kostenbasis haben wir weiter oben berechnet. Der jeweilige Gewinn bzw. Verlust ergibt sich aus dem Delta und kann z.B. im Fall von Aktien (nicht wie hier Mikroprozessoren) zur Steuerberechnung herangezogen werden.

Damit habe ich Ihnen einen (hoffentlich nachvollziehbaren) makro- bzw. UDF-freien Berechnungsansatz für die FIFO-Methode gezeigt. Zugegeben habe ich schon weniger komplexe Sachverhalte modelliert, aber es ist, wie es ist, die Lösung ermittelt zuverlässig die jeweilige FIFO-Kostenbasis für die Verkäufe und basiert ausschließlich auf der Verwendung von Standardformeln ohne VBA-Unterstützung. Haben Sie eine elegantere Lösung, dann lassen Sie es mich wissen!

Die kostenlose Download-Datei erleichtert Ihnen, das Beispiel nachzuvollziehen und kann als Ausgangsvorlage für eigene Berechnungen genutzt werden.

Excel-Übungsdatei zum Download >>


Download des vollständigen Beitrages: Download


Autor(en): Dirk Gostomski
letzte Änderung ).D.G. am 05.04.2022
Autor(en):  Dirk Gostomski


Autor:in
Herr Dirk Gostomski (fimovi.de)
Dirk Gostomski ist seit mehr als 20 Jahren selbständiger Berater und Trainer im Bereich Financial Modelling mit Excel. Mit www.financial-modelling-videos.de bietet er auch Intensiv-Video-Workshops an, in denen Schritt für Schritt die Erstellung von professionellen Finanzplanungs, Projektfinanzierungs- und Cashflow-Modellen in Excel erläutert wird.
Homepage | weitere Fachbeiträge des Autors | Forenbeiträge
Weitere Fachbeiträge zum Thema

Premium-Stellenanzeigen



Eigenen Fachbeitrag veröffentlichen? 

Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten. Mehr Infos >>

Kommentar zum Fachbeitrag abgeben

Nur registrierte Benutzer können Kommentare posten!

Anzeige

Premium-Mitgliedschaft

Erhalten Sie Zugriff auf Premium-Inhalte von Controlling-Portal.de und Rechnungswesen-Portal.de.

- über 500 Premium-Fachbeiträge und 
- Video-Kurse
- professionelle Excel-Vorlagen
- Aufgaben und Lösungen

Alle Premium-Inhalte im Überblick >>

Der Jahresbeitrag beträgt nur 89,- EUR inkl. MMSt. Jetzt sichern>>

Über 3.000 Artikel und Vorlagen

Community_Home.jpg






Werden Sie Mitglied in einer großen Controller-Community und erhalten Zugriff auf viele Inhalte und diskutieren ihre Fragen im Controlling-Forum.
Anzeige

Eine neue Stelle?

Mit dem Studium fertig, Umzug in eine andere Region, Aufstiegschancen nutzen oder einfach nur ein Tapetenwechsel? Dann finden Sie hier viele aktuell offene Stellen im Controlling.
Zu den Stellenanzeigen >>

Zukunft_Aussicht_Menschen_Fernglas_pm_prometeus_315.jpg

Sie suchen einen Controller? Mit einer Stellenanzeige auf Controlling-Portal.de erreichen Sie viele Fachkräfte. weitere Informationen >>

KLR-Begriffe von A bis Z

A-C   D-F   G-I   J-L   M-R   S-U   V-Z 

Testen Sie ihr Wissen mit unseren Aufgaben und Lösungen im Bereich Kostenrechnung >>

Sie haben eine Frage?

Ratlos_Verwirrt_pm_RainerPlendl_400x275.jpg

Nutzen Sie kostenfrei das Forum für Controller und und diskutieren ihre Fragen zu Controlling-Themen.
Anzeige
Excel-Vorlage: RS Controlling System

Riesen- Ressource

Auf Controlling-Portal.de sind bereits über 1.500 Fachbeiträge veröffentlicht und ständig kommen neue dazu.

Zu den neuesten Fachbeiträgen >>
Zu den derzeit meistgelesenen Fachbeiträgen >>
Zu den Premium-Beiträgen >>

Sie möchten auch einen Fachbeitrag hier veröffentlichen? Dann erhalten Sie hier weitere Informationen >>

Amüsante Glosse

Zweifel_Denken_Frau_Brille_pm_luckyraccoon_400x300.jpg

Erfolgreiche Unternehmer einer Kleinstadt treffen sich regelmäßig im örtlichen Golfclub und diskutieren mit Ihrer Lieblingskellnerin und BWL-Studentin Pauline betriebswirtschaftliche Probleme. Amüsant und mit aktuellem Bezug geschrieben von Prof. Peter Hoberg.

Zur Serie "Neulich im Golfclub" >>
Anzeige

Neu - Controlling-Journal!

Controlling Journal-2022_190px.jpg
Kurzweiliger Mix aus Fachinformationen, Karriere- und Arbeitsmarkt-News, Büroalltag und Softwarevorstellungen.

Probieren Sie jetzt das Print-Magazin Controlling-Journal >>

Sie möchten sich weiterbilden?

mann-treppe-up-karriere_pm_pressmaster_B10716345_400x300.jpg

In unserer Seminar-Rubrik haben wir einige aktuelle Seminar- und Kurs-Angebote für Controller, u.a. auch Kurse zum Controller / IHK zusammengestellt.
Anzeige

Premium-Mitgliedschaft

Erhalten Sie Zugriff auf Premium-Inhalte von Controlling-Portal.de und Rechnungswesen-Portal.de. Aktuelle und ständig erweiterte Fachbeiträge und sämtliche Ausgaben des Controlling-Journals. Das aktuelle Controlling-Journal erhalten Sie zudem als pdf-Ausgabe. Verschaffen Sie sich hier einen Überblick über unsere Premium-Inhalte.

Mitgliedschaft für nur 89,- EUR inkl. MWSt. im Jahr! Studenten und Auszubildende 39,- EUR für zwei Jahre! Weitere Informationen >>
 

Wie zufrieden sind Sie mit uns?

Über Ihre Meinung und auch ihre Anregungen für Veränderungen oder Erweiterungen würden wir uns sehr freuen. Nur 10 kurze Fragen, die schnell beantwortet sind. Vielen Dank im Voraus für Ihre Mithilfe! zur Umfrage >>
 

Fachbeitrag veröffentlichen?

Sie sind Autor von Fachbeiträgen mit Controlling-bzw. Rechnungswesen-Thema? Gerne veröffentlichen oder vermarkten wir ihren Fachbeitrag. Eine kurze Autorenvorstellung oder Unternehmensdarstellung am Ende des Artikels ist möglich. jetzt Mitmachen >>

Talentpool - Jobwechsel einfach!

HR-Bewerbung-Digital_pm_yupiramos_B123251108_400x300.jpg

Tragen Sie sich kostenfrei im Talentpool auf Controlling-Portal.de ein und erhalten Jobangebote und Unterstützung beim Jobwechsel durch qualifizierte Personalagenturen.
Anzeige

Kennzahlen-Guide

Kennzahlen-Guide-klein.pngÜber 200 Kennzahlen aus Finanzen, Personal, Logistik, Produktion, Einkauf, Vertrieb, eCommerce und IT.
Jede Kennzahl wird in diesem Buch ausführlich erläutert. Neben der Formel wird eine Beispielrechnung aufgeführt. Für viele branchenneutrale Kennzahlen stehen Zielwerte bzw. Orientierungshilfen für eine Bewertung zur Verfügung. Für die genannten Bereiche hat die Redaktion von Controlling-Portal.de jeweils spezialisierte Experten als Autoren gewonnen, die auf dem jeweiligen Gebiet über umfangreiche Praxiserfahrung verfügen. 

Preis: ab 12,90 Euro Brutto mehr Informationen >>
 

Dashboards mit Excel

dashboards_cover.jpgWie erstelle ich ein Tacho- oder Ampel-Diagramm? Wie kann ich Abweichungen in Tabellen ansprechend visualisieren? Das wird Ihnen hier anschaulich erklärt.

Taschenbuch in Farbe für 34,90 EUR
oder E-Book für 24,90 EUR 
mehr Informationen >>

  

Reporting 1x1

reporting1x1-klein.jpgViel ist zum Berichtswesen oder Reporting schon geschrieben worden. Dennoch zeigen Umfragen, dass rund 50 Prozent der Empfänger von Berichten mit dem Reporting nicht zufrieden sind. Jörgen Erichsen erklärt in diesem Buch die Bedeutung und die Handhabung des Berichtswesens speziell für kleinere Betriebe. Mit zahlreichen Beschreibungen, Beispielen und Checklisten.

Taschenbuch in Farbe für 24,90 EUR
oder E-Book für 15,90 EUR 
mehr Informationen >>

Anzeige

Buch-Tipp

Dashboards mit Excel im Controlling

dashboards_cover.jpgWie erstelle ich ein Tacho- oder Ampel-Diagramm? Wie kann ich Abweichungen in Tabellen ansprechend visualisieren? Das wird Ihnen hier anschaulich erklärt.

Taschenbuch in Farbe für 34,90 EUR, Mehr Infos >>


Anzeige
Excel-Vorlage: RS Controlling System
Anzeige
 



aufgefallen ?!


Ihre Werbung hier !






Anzeige

Stellenmarkt

Teamleiter:in Buchhaltung und Accounting/Bilanzbuchhalter:in
Meteor ist seit mehr als 60 Jahren Partner der euro­päischen Automobil- und Zuliefer­industrie, wenn es um komplexe Dich­tungs­systeme bzw. Lö­sungen für technische Spezial­gebiete wie Sonder­fahr­zeugbau, Umwelt­technik sowie Hoch- und Tief­bau geht. Unser Erfolgs­rezept: Hoch­wertige Roh­stoffe... Mehr Infos >>

FINANZBUCHHALTER (M/W/D)
Welches Arbeitsumfeld wünschen Sie sich? Wir sind ein dynamischer, börsennotierter Konzern, der aus seiner Vergangenheit heraus mittelständisch geprägt ist. Dabei kombinieren wir das Beste aus beiden Welten: flache Hierarchien, einen engen Zusammenhalt, ein zielorientiertes Arbeiten – und vor all... Mehr Infos >>

Business Analyst (m/w/d) im operativen Einkaufscontrolling
Wir bewegen Großes und sind selbst ständig in Bewegung. Damit das so bleibt, suchen wir Sie. Gemeinsam machen wir Europas führendes Unternehmen für Waggonvermietung und innovative Logistiklösungen noch erfolgreicher. Mehr Infos >>

Weitere Stellenanzeigen im Stellenmarkt >>
Anzeige

JOB- TIPP

Stellenmarkt.jpg
Sind Sie auf der Suche nach einer neuen Herausforderung? Interessante Stellenangebote für Controller finden Sie in der Controlling-Portal Stellenbörse. Ihr Stellengesuch können Sie kostenfrei über ein einfaches Online-Formular erstellen. Zur Stellenbörse >>
Anzeige

Software-Tipp

idl_controller-portal-290x120_02-19-weiß.jpg

Aktuelle Scores, Whitepaper, Studien, Checklisten und Handbücher rund um Konsolidierung, Berichtswesen und Rechnungslegung. 

Jetzt kostenfrei holen!


Anzeige

Software-Tipps

Software4you.PNG
4PLAN stellt die einfache Planung von Stellen, Mitarbeitern, Ressourcen und Vakanzen sicher. Dabei ist auch die Planung mit Varianten, Versionen und in beliebigen Zeithorizonten möglich. Änderungen von Rahmenbedingungen wie Tarif- und Sozialversicherungsregeln werden ebenso berücksichtigt wie Auswirkungen von Leistungsänderungen, Versetzungen oder Ein- und Austritten. Mehr Informationen >>

LucaNet_simply_intelligent_Logo_RGB.jpg
LucaNet.Planner deckt alle Anforderungen der integrierten Unternehmensplanung, des Reportings und der Analyse ab. Setzen Sie auf eine Software, die Ihnen mit maximaler Transparenz Arbeitsprozesse erleichtert. Mit wenigen Mausklicks passen Sie vorgefertigte Strukturen an Ihre Bedürfnisse an und importieren Ist-Zahlen über fertige Schnittstellen aus Ihrem Vorsystem.  Mehr Informationen >>

idl.png
Die IDL CPM Suite ist eine Software für die Unternehmenssteuerung mit integrierten Applikationen für Konzernkonsolidierung, Finanzplanung, operative Planung, Managementreporting, regulatorisches Berichtswesen und Analyse. Herausragende Usability, hohe Automatisierung und Fachlichkeit zeichnen sie aus. Mehr Informationen >>

Weitere Controlling-Software-Lösungen im Marktplatz >>
Anzeige

Excel-Vorlage SWOT Analyse Dashboard

SWOT-Analyse-mit-Excel.png
In dieser Excel-Vorlage können Sie 4 verschiedene Charts individuell pro Bereich der SWOT Analyse auswählen. Wenn diese zum Einsatz kommt, ist es sehr hilfreich, professionelle Business-Charts zu erstellen, um die jeweilige Situation besser darzustellen.
Mehr Informationen >>

ETF-Rechner

Der ETF-Rechner vergleicht die mögliche Wertenwicklung von ausschüttenden und thesaurierenden Aktien-ETFs zu den eingegebenen Annahmen. Das Excel-Tools funktioniert wie ein Zinseszinsrechner, optimiert auf ETFs!
Mehr Informationen >>

RS Controlling-System für EÜR inkl. Liquiditätsplanung

RS Controlling System.png
Mit dem RS-Controlling-System für Einnahme-Überschuss-Rechnung steuern Sie erfolgreich ihr Unternehmen. Dieses Tool ermöglicht es Ihnen ihr Unternehmen zu planen, zu analysieren und zu steuern. Durch den Soll-/Ist-Vergleich für Ihre Einnahmen-Überschuss-Rechnung können Sie gezielt  Abweichungen analysieren. 
Mehr Informationen >>

Weitere über 400 Excel-Vorlagen finden Sie hier >>
Anzeige

Titel mit Banner Ebook Controlling-Instrumente_Kristoffer-Ditz.jpg

E-Book Controlling-Instrumente

Der erfahrene Controller und Excel-Coach Kristoffer Ditz stellt in seinem E-Book "Controlling-Instrumente" mit ebenso einfachen wie hilfreichen Tools für alle Controller-Lebenslagen vor. Preis 8,90 EUR hier bestellen >>
Anzeige

Kosten- und Leistungsrechnung: Aus der Praxis für die Praxis von Jörgen Erichsen 

kosten-muenzen-geld-pm_Boris_Zerwann_240x180.jpg
Zur KLR gibt es zahllose Veröffentlichungen, die sich vor allem mit Definitionen und Theorien befassen. Im Unterschied dazu ist diese Serie aus 6 Teilen so konzipiert, dass sie sich vor allem mit der Anwendbarkeit im Tagesgeschäft befasst und so gerade für Unternehmer in kleinen Betrieben und Selbstständige einen hohen Nutzwert bieten. Weitere interessante Serien finden Sie hier >> 
Anzeige

Software-Tipp

Liquiditätsplanung_Fimovi.jpgRollierende Liquiditätsplanung auf Wochenbasis. Mit der Excel-Vorlage „Liquiditätstool“ erstellen Sie schnell und einfach ein Bild ihrer operativen Liquiditätslage für die nächsten (bis zu 52) Wochen.. Preis 47,60 EUR Mehr Infos und Download >>
Anzeige

RS Controlling-System

RS-Controlling-System.jpg Das RS- Controlling-System bietet Planung, Ist- Auswertung und Forecasting in einem Excel-System. Monatliche und mehrjährige Planung. Ganz einfach Ist-Zahlen mit Hilfe von Plan/Ist-Vergleichen, Kennzahlen und Kapitalfluss- rechnung analysieren.  Alle Funktionen im Überblick >>
Anzeige

Excel Tool

Anlagenverwaltung in Excel: Das Inventar ist nach Bilanzpositionen untergliedert, Abschreibungen und Rest- Buchwerte ihrer Anlagegüter werden automatisch berechnet. Eine AfA- Tabelle, mit der Sie die Nutzungsdauer ihrer Anlagegüter ermitteln können, ist integriert. mehr Informationen >>
Anzeige

RS-Plan

RS-Plan.jpgRS-Plan - Unternehmens- planung leicht gemacht:
Erstellen Sie mit RS-Plan Ihre Unternehmensplanung professionell. Automatische Plan-GuV, Plan-Bilanz, Plan- Kapitalflussrechnung und Kenn- zahlen. Preis: 119,- EUR mehr Informationen >>
Anzeige

Software-Tipp

Baukostenrechner-150px.jpgDer Excel-Baukostenrechner unterstützt Sie in der Kalkulation der Kosten Ihres Hausbaus mit einer detaillierte Kosten- und Erlösplanung. Zusätzlich bietet Ihnen dieses Excel-Tool einen Plan / IST-Vergleich sowie verschiedene andere Auswertungen.. Preis 30,- EUR Mehr Infos und Download >>
Anzeige

Premium-Mitgliedschaft

Erhalten Sie als StudentIn oder Auszubildene(r) Zugriff auf Premium-Inhalte von Controlling-Portal.de und Rechnungswesen-Portal.de zum Vorzugspreis von 39,- EUR für 2 Jahre. Aktuelle und ständig erweiterte Fachbeiträge, ausgewählte sonst kostenpflichtige Excel-Vorlagen und sämtliche Ausgaben des Controlling-Journals im Archiv. Das aktuelle Controlling-Journal erhalten Sie zudem als pdf-Ausgabe. Verschaffen Sie sich hier einen Überblick über unsere Premium-Inhalte.

Weitere Informationen zum Ausbildungspaket >>