Office: Berechnung von FIFO & Durchschnittskaufpreis bei Kauf und Verkaufsliste

Hallo zusammen,

ich habe eine Liste mit Käufen und Verkäufen unterschiedlicher Artikel. Da die Kauf- und Verkaufspreise immer wieder variieren möchte ich gerne den jeweiligen Durchschnittskaufpreis per Formel kalkulieren. An der Stelle komme ich aber leider nicht weiter und bitte um einen Rat von euch als Experten.

Eine kurze Erklärung meiner Tabelle (bzw. der Spalten):
A - Kauf oder Verkauf
B - Artikel
C - Anzahl Artikel (Kauf positive, Verkauf negative Zahl)
D - jeweiliger Kaufs/Verkaufspreis
E - jeweilige Kaufs-/Verkaufssumme
F - Warenbestand
G - FIFO (First In First Out) Kauf-Preis, welche beim Verkauf von Artikel als Bezugsgröße verwendet wird
H - Durchschnittskaufpreis (jeweiliger Durchschnittspreis der vorherigen Käufe)
I - Absoluter Wert des Bestandes (Kaufsummen - Verkaufsummen)
J - Durchschnittspreis auf den Absolut-Wert (Absolut Wert / Bestand)

Die Stelle an der ich mich mit der Formel schwer tue ist eine Formel zu finden, die beim Verkauf den Durchschnittswert aller vorherigen Käufe und Verkäufe ansetzt. Ihr seht das in Spalte H.

Dabei ist wichtig, dass die Einträge in der realen Tabelle nicht alle so untereinander stehen, sondern nach Datum sortiert sind. Es können also auch andere Artikel - wie weiter unten mit der "Birne" verdeutlicht - dazwischen auftauchen.

Ich hoffe ihr könnt mir einen guten Denkanstoß geben, bin da jetzt schon etwas länger am tüfteln...

Datei und Bild davon habe ich angehängt.

Danke Euch vorab & beste Grüße
Christopher

:)

 

Hallo Christopher,

für das, was du vorhast, ist deine Tabelle suboptimal aufgebaut.
Um zu Ergebnissen zu kommen, müsste noch das Einkaufsdatum vorhanden sein.
Dann müssten die Artikel gruppiert werden und trotzdem müsstest du laufend die Bezüge in den Formeln ändern.

Mache dich mal mit der Pivot-Tabelle vertraut.
Hört sich schlimm an, ist aber mit einiger Übung leicht zu handhaben.

Eventuell bietet die Funktion Teilergebnis auch eine Möglichkeit zu Lösung.

Gruß
Rainer

 

Hallo Rainer,

danke für die Rückmeldung. Ein paar Infos um die Situation zu klären:

Die Datei ist nur ein Beispiel um die Komplexität nicht zu groß werden zu lassen. In der Originaldatei pflege ich Kauf, Verkauf und FIFO Preis. Ich würde aber gerne immer den jeweiligen Durchschnittspreis automatisch kalkuliert bekommen und im anderen Tabellenblatt die Gesamtsicht daraus sehen.

D.h. das was ich dort händisch in die Zelle als Formel eingetragen habe, möchte ich ja mit einer komplexeren Formel automatisch kalkulieren. Nur da komme ich gerade nicht mehr weiter und bin über Unterstützung sehr dankbar.

Ich habe die Beispieldatei nochmal erweitert. Ist das Anliegen nun etwas verständlicher?

Pivot-Tabellen nutze ich regelmäßig an der Arbeit, das ist nicht das Problem. Ich will das ja bewusst mit Formeln hinbekommen.

Danke vorab & beste Grüße
Christopher

 

Hallo Christopher,

mit dieser Tabelle und den vorhandenen Formeln wirst du dein Ziel nicht erreichen.
Du wirst für jeden Artikel eine eigene Berechnung durchführen müssen.

Deine kaufmännischen Kenntnisse sind mir unbekannt,
aber sieh mal hier zur Auffrischung: b) Fifo-Verfahren:
http://www.rechnungswesen-portal.de/...nstaende-.html
Und hier:
https://www.wiwiweb.de/kostenrechnun...ung/fifo1.html

Meiner Meinung nach (unerheblich) bist du mit einer Pivottabelle gut bedient.

Vieleicht hat jemand anderes einen Vorschlag.

Gruß
Rainer

 

Hallo Christopher,

meinst Du so?

LG Ingo

 

Moin Ingo,

das ist exakt das was ich gesucht habe!! *Smilie

Respekt zu der Formel. Kannst Du mir vielleicht ganz grob die Logik erklären?

Ich habe versucht auf meine Tabelle zu überführen und hab da eben noch eine Spalte drin, die eine Bedingung setzt ob die Zeile berücksichtigt wird oder nicht. Das habe ich versucht mit in Deine Formel mit einzubauen, aber bisher noch nicht erfolgreich geschafft.

Vielleicht kannst Du mir einen kleinen Tipp geben bzw. Deine Logik beim Aufbau Deiner Matrixformel erläutern.

Danke Dir vorab & LG
Christopher

 

Hallo Christopher,

ich kann mich erst wieder am Montag darum kümmern.

LG Ingo

 

Danke Dir schon mal vorab!

Schönes Wochenende!

 

Hallo Christopher,

Deine neue Bedingung formeltechnisch zu berücksichtigen ist nicht so das Problem, aber den eigentlichen Sinn verstehe ich nicht.

Meine Formel macht Folgendes:
Wenn der Artikel aus der jeweiligen Zeile zum ersten Mal erscheint, dann nimm den Preis aus Spalte E,
ansonsten suche ich mir den zuletzt gültigen Durchschnittspreis dieses Artikels
und multipliziere ihn mit dazugehörigem Bestand und addiere die neu dazugekaufte Menge mal dem neuen Preis dazu, wenn es nicht ein Abverkauf ist
und teile dieses Gesamtergebnis dann wiederum durch den zuletzt gefundenen Bestand zuzüglich der dazugekauften (=positiven) Menge.
Und das ganze immer unter der neuen Voraussetzung, dass in Spalte H eine 1 oder eine 2 steht: *(($H$2:$H2=1)+($H$2:$H2=2))

LG Ingo

 

Hallo Ingo,

nochmal vielen herzlichen Dank für Deine Formel! Die hat mir echt geholfen, auch wenn es schwer für mich ist, diese für mein Scenario weiterzuentwickeln.

Ich habe es aber mal gemacht und bin dabei noch auf eine Herausforderung gestoßen.

Es gibt in der Tabelle zum Teil auch virtuelle Einträge. Genau aus dem Grund habe ich auch nach der Bedingungsspalte gefragt.

Das Problem was ich jetzt habe ist, wenn der Bestand eines Artikels auf 0 geht und danach ein virtueller Eintrag eingetragen ist (noch nicht realisierter Kauf), dann will die Formel durch 0 teilen und ich bekomme einen Fehler.

Ich würde gerne erreichen, dass er diese Einträge dann einfach ignoriert und nichts (Leerzeichen bzw. 0) ausgibt.

Vermutlich ist das relativ einfach, ich probiere jetzt schon einige Zeit rum und habe noch nicht das gewünschte Ergebnis.

Das wäre super, wenn Du mir da nochmal einen kleinen Tipp geben könntest.

Vielen Dank nochmal an Dich & LG
Christopher

 

Hallo Christopher,

ja das ist einfach. Ich habe hier einfach ein WENNFEHLER(........;0) umbaut, dann klappt es.

LG Ingo

 

Besten Dank Ingo, das hat mir wirklich sehr geholfen! *Smilie

Die Frage mit dem Durchschnittspreis ist für mich damit geklärt.

Ich hab nur noch eine abschließende Frage zur Berechnung des FIFO Preises. Das mach ich alles mit Hand.

Siehst Du da generell eine Möglichkeit das mit Formeln zu berechnen und hast Du ggf. einen Tipp wo ich dazu ein bisschen recherchieren kann?

Nochmals vielen lieben Dank & LG
Christopher

 

Hallo Christopher,

dann erkläre mir mal, wie Du das errechnest.
FIFO sagt mir nichts.

LG Ingo

 

Hallo Ingo,

FIFO bedeutet First In First Out, also die Käufe, die ich zuerst getätigt habe, gehen auch wieder als erstes raus..

Also folgendes Szenario:
Kauf: 10 x 10€
Kauf: 10 x 6€

Verkauf: 15 x 9€
-> Der Durchschnittspreis ist 8€
-> FIFO: (10x10€ + 5x6€)/15 = 8,67€

Das ganze dynamisch mit Formeln ist aber anspruchsvoll *wink.gif*

Danke Dir vorab & LG
Christopher

 

Hallo Christopher,

oh ja, das wird anspruchsvoll und sicher fehleranfällig.
Wenn ich mal viel Zeit habe, denke ich da mal drüber nach.

LG Ingo