Mengen- und Preiszuordnung nach dem FIFO-Verfahren

[schauan]

Hallo ws-53,

also, im Eröffnungsthread #1 steht zumindest, dass er die 70 sehen will und wo
Darauf aufbauend habe ich dann auch gleich geschlussfolgert und seinen Wunsch anscheinend richtig interpretiert, wie man dann auf dem Bild in #7 und meinem Ansatz sieht

[ws-53]

Hallo André,

#1 -  Aber in 2024 sind noch 10 Einheiten von der zweiten Beschaffung übrig, die anderen 10 kommen von der dritten Beschaffung. Der Preis beträgt also 70.

Ja, das steht da. Aber wenn der Absatz von 20 im Jahr 2024 durch 10 aus der 2. Beschaffung (30 für 80€) und 10 aus der 3. Beschaffung (20 für 60€) gedeckt wird, dann ergibt sich somit ein Beschaffungswert von (80 / 30 * 10) + (60 / 20 * 10) = 56,67.

Da hat sich der TE somit entweder verrechnet, oder er verwendet einen Berechnungsansatz, der sich mir nicht erschließt.

[schauan]

Hallöchen Axel,

also, ich habe jetzt mal meinen Ansatz optimiert, LET und LAMBDA ist da nicht nötig, bei den Matrizenrechnungen habe ich mich etwas sehr umständlich angestellt und Formeln Ändern ist nicht erforderlich.

Du kannst jetzt alle 3 Tabellen erweitern. Die linke und die mittlere Tabelle durch entsprechende Eingaben nach unten und die rechte Tabelle nach rechts und unten. In der rechten Tabelle hab ich schon ein paar Spalten mehr, die hatte ich nur mal zum Test. Du brauchst da nur so viele Spalten wie Du auch Beschaffungszeilen hast und für jeden Absatz eine Zeile. Allerdings erweitert die sich nicht von selbst. Die Formeln kommen beim Erweitern aber mit, Du brauchst da keine zu ändern, auch in der Absatztabelle nicht.
Die erste Zeile mit den Nullen darfst Du nicht entfernen.
Diese Hilfstabelle schlüsselt Dir auch auf, aus welcher Beschaffung wie viel zu entnehmen ist.

@ws-53,

also erst mal zur Korrektur, Deine Lösung dürfte vom Rechenweg her auch passen.

Ich weiß nicht, wie ich es noch erklären soll. Nimm die 100 oder 80 nicht als Gesamtpreis für die 20 oder 30, sondern als Einzelpreis.
Wenn Du in Spalte C in C7 statt der 100 dann 2000 einträgst, in C8 2400, in C9 1200 usw, und in Spalte G die Werte vom TE lässt, also in G8 die 20 und nicht die 30 aus der linken Tabelle, dann kommst Du mit Deiner Lösung in Spalte L auch auf die 70 und 55 usw wie auf dem Bild

[Axel4711]

Vielen Dank schauan und alle, die sich hier beteiligt haben!!!

[schauan]

... eine Korrektur hab ich noch. Bitte in K9 diese Formel eingeben:
=SUMME($G$8:INDEX(Absatz[Menge];MIN(ANZAHL(Absatz[Menge]);ZEILE(A1))))
und runter ziehen. Beim Erweitern dann z.B. die Zelle in K markieren und nach unten ziehen.

Ich muss nur mal schauen, beim ziehen bekomme ich 1x einen Zirkelbezug. Kopiere ich die letzte Zeile und füge sie ein, bekomme ich das als Ergebniszeile und keinen Zirkelbezug. Kopiere ich nur K14 und füge es ein, bekomme ich auch eine Ergebniszeile, aber rechts daneben #BEZUG in die Formeln.

Ich muss aber jetzt erst mal zum Beckenbodentraining

[schauan]

Hallöchen,

Anbei nun die korrigierte Version.
Zur Beseitigung des Zirkelbezuges reichte es, L9:S9 zu markieren und nach unten bis ans Ende dieser Tabelle zu ziehen. Ab Zeile 14 war hie und da was falsches drin Spalte K ist auch korrigiert. Weiterhin habe ich noch die Formeln in Spalte H flexibel auf den Tabellenbereich in C angepasst.

Dazu habe ich noch in der rechten Tabelle den Bezug von G nach K gelenkt. Ansonsten, wenn man in der mittleren Tabelle eine Zeile löscht, kommt in der rechten dann irgendwo #BEZUG.

In Spalte G müsste vorsorglich noch die Spalte S... z.B. in XX geändert werden oder je nachdem, wo man denkt, dass es reicht
Also z.B. in G8 dann
=SUMMENPRODUKT((L9:INDEX(L9:XX9;;ANZAHL2(Beschaffung[Preis]))*MTRANS(Beschaffung[Preis]))/SUMME((L9:INDEX(L9:XX9;;ANZAHL2(Beschaffung[Preis])))))
Ist jetzt auch drin

[ws-53]

Da mir die, durch den Copilot erstellte, LET-Formel viel zu kompliziert war, um so vollständig nachvollziehen zu können, habe ich nun eine eigene LET-Formel erstellt, die, zumindest für mich, viel verständlicher ist.

[rate mit]

Hallo,

auch wenn es kaufmännischer Unsinn ist... hier noch einmal ein Vorschlag nach deinen Wünschen....

Bei 1 Million Zeilen und etlichen Spalten machen ich mir wirklich keine Sorgen, das Excel damit an seine Grenze kommt...

Und bei deinen paar Daten ist die Rechenzeit sicherlich auch nicht besonders groß...

[schauan]

@rate_mit,

deine zweite Lösung aus #10 wäre dann aber auch kaufmännischer Unsinn, da komme ich mit etwas gutem Willen auch auf die 70 usw...
Also, ich trage dort, wie ich auch schon bei ws-53 beschrieben habe, statt dem Einzelpreis 100 den Gesamtpreis 2000 ein, statt der 80 dann 2400 usw.
Dann bekomme ich in der zweiten Tabelle auch die Gesamtbeschaffungspreise und wenn ich die 1400 durch 20 teile, bin ich bei 70.

Eigentlich sind "nur" die Spaltenüberschriften fragwürdig. Man könnte links statt Preis dann Beschaffungspreis nehmen und rechts bei Absatz dann vielleicht Angebotspreis.

Und entweder man nimmt dort bei beiden entweder Stückpreise oder die Gesamtpreise.

[ws-53]

@rate mit - Du konntest jetzt schon mehrfach sehen, dass auch dein Ansatz ohne volatile Funktionen realisiert werden kann. Aber diesbezüglich habe ich den Eindruck, dass du beratungsresident bist.

Und wenn ich in meiner dynamischen LET dafür sorge, dass 1 Mio Zeilen erzeugt werden, dann dauert das 25 Sekunden.