Einkaufspreis aus Einkaufshistorie ziehen

[SemmyW]

Hallo zusammen,

ich brauche eure Hilfe bei einer Tabelle die ich baue. Es geht darum, zu den Bestellzeilen den EK aus der Bestellhistorie zu bekommen, die zum Zeitpunkt der Bestellung gültig sind. 

Der EK selbst ist nicht direkt vom Datum abhängig, da man Bestand ja auch Lagernd hat. Also wenn ich gestern 5 Stück eines Artikels zum Preis von je 2€ gekauft habe und heute die nächsten 5 Stück zu je 3€, würden die ersten 5 verkauften Artikel einen EK von 2 € zugewiesen bekommen und zweiten 5 verkauften Artikel mit einem EK von 3€. Eine Mischkalkulation um den durchschnittlichen EK für alle 10 Stück zu erhalten ist bewusst nicht gewählt worden.

Ich bekomme keine Formel hin, die eine gleichzeitige Zählung der verkauften Artikel in Bezug auf die eingekauften Artikel macht, um den richtigen EK anzugeigen.

Wenn Ihr prinzipiell Anregungen habt, wie man die Bestellhistorie anders aufbauen müsste oder sonst Ideen die in Zukunft hilfreich sind, gebt gerne Bescheid.


Vielen Dank schon mal!

[Fennek]

Hallo,

zuerst habe ich nach "Excel FIFO" gegooglt, aber die Ergebnisse waren ernüchternd.

Vorschlag:

In einer Collection wird für jedes Element ein Datenatz beim Eingang angelegt und beim Ausgang wieder entfernt:

Col.Add "123|5€

Damit sollte es möglich sein.

Wäre ein VBA-Ansatz möglich?

mfg

[Fennek]

Code für den Einkauf:

Code:

Sub FIFO()
Dim Col As Collection
Set Col = New Collection

For i = 3 To Cells(Rows.Count, 2).End(xlUp).Row
    For m = 1 To Cells(i, 7)
        Col.Add Cells(i, 2) & "|" & Cells(i, 9)
    Next m
Next i

For i = 1 To Col.Count
    Debug.Print Col.Item(i)
Next i
End Sub

[SemmyW]

Hallo,

zuerst habe ich nach "Excel FIFO" gegooglt, aber die Ergebnisse waren ernüchternd.

Vorschlag:

In einer Collection wird für jedes Element ein Datenatz beim Eingang angelegt und beim Ausgang wieder entfernt:

Col.Add "123|5€

Damit sollte es möglich sein.

Wäre ein VBA-Ansatz möglich?

mfg

Hallo Fennek,

Danke dir für deine Nachricht. Ich habe da leider auch keinen Erfolg bis jetzt gehabt und mit Collection kenne ich mich auch nicht aus 

VBA wäre auch möglich, aber ich weiß nicht wie man mit VBA es so machen würde, dass nicht immer wieder alle Zeilen erneut durchgerechnet werden, oder wäre dies egal?
Ich lasse mich hier gerne überraschen, Ihr könnt diese Themen eh deutlich besser als ich.

[Fennek]

Hallo,

Foren können nicht mehr als ein "PoC" liefern, den Beispielcode für reale Daten umzusetzen, ist dann die eigentliche Herausforderung:

Nach meinem Verständnis der Spaltenbeschreibung:

Code:

Sub FIFO()
Dim Col As Collection
Set Col = New Collection

With Sheets("Einkaufshistorie")
    For i = 3 To .Cells(Rows.Count, 2).End(xlUp).Row
        For m = 1 To .Cells(i, 7)
            Col.Add .Cells(i, 2) & "|" & .Cells(i, 9)
        Next m
    Next i
End With

With Sheets("Auswertung")
    For i = 3 To .Cells(Rows.Count, 4).End(xlUp).Row
        For c = 1 To Col.Count
            If CStr(.Cells(i, 4)) = Split(Col.Item(c), "|")(0) Then
                .Cells(i, "L") = Split(Col.Item(c), "|")(1)
                Col.Remove c
                Exit For
            End If
        Next c
    Next i
End With
End Sub

In der Beispieldatei sind die Ergebnisse eingetragen.

mfg

[LCohen]

clever-excel-forum.de/Thread-Aktiendepot-Berechnungen-erstellen?highlight=LCohen+fifo

[SemmyW]

Hallo,

Foren können nicht mehr als ein "PoC" liefern, den Beispielcode für reale Daten umzusetzen, ist dann die eigentliche Herausforderung:

Nach meinem Verständnis der Spaltenbeschreibung:

Code:

Sub FIFO()
Dim Col As Collection
Set Col = New Collection

With Sheets("Einkaufshistorie")
    For i = 3 To .Cells(Rows.Count, 2).End(xlUp).Row
        For m = 1 To .Cells(i, 7)
            Col.Add .Cells(i, 2) & "|" & .Cells(i, 9)
        Next m
    Next i
End With

With Sheets("Auswertung")
    For i = 3 To .Cells(Rows.Count, 4).End(xlUp).Row
        For c = 1 To Col.Count
            If CStr(.Cells(i, 4)) = Split(Col.Item(c), "|")(0) Then
                .Cells(i, "L") = Split(Col.Item(c), "|")(1)
                Col.Remove c
                Exit For
            End If
        Next c
    Next i
End With
End Sub

In der Beispieldatei sind die Ergebnisse eingetragen.

mfg

Vielen Dank für deinen Code,

Leider scheint da noch etwas nicht zu stimmen. Ich glaube du berücksichtigst nicht die verkaufte Menge aus Spalte "I" (Lineitem quantity), dort wird angegeben wie viel Stück in der Bestellzeile stehen.
So wie ich es sehe gehst du von einem verkauf pro Zeile aus. oder? 


Wenn man die Tabelle nach dem Produkt 123 Filtert müssten die Zeilen 3, 8 und 10 mit einem EK von 3 € angegeben werden, da 1+2+2 = 5 Stück verkauft wurden.
In Zeile 12 wurden dann weitere 3 Stück verkauft die mit 3,25 € angegeben werden müssten und in Zeile 17 wurden wieder 3 Stück verkauft, davon wären 2 Stück mit 3,25 und 1 Stück 3,05 € im EK gewesen. ( hier könnte man dies wohl zusammen mischen also (2x3,25 + 3,05)/3= 3,18€.
In Zeile 19 wurden dann wieder 2 Stück verkauft und diese sind mit dem EK von 3,05€ zu tragen.

Sorry wenn dies für Verwirrung sorge! 

Und magst du mir sagen, wieso einige der Werte nicht richtig als Zahlen formatiert sind sobald das Makro fertig ist?

Vielen Dank schonmal!

---

clever-excel-forum.de/Thread-Aktiendepot-Berechnungen-erstellen?highlight=LCohen+fifo

Hallo LCohen!

Danke für deinen Beitrag. Ich hab mir deinen Thread dazu mal durchgesehen und bin noch dabei die Tabelle zu verstehen. 
Magst du mir evtl helfen und sagen, wo in deinem VBA Code der entsprechende Teil dazu steht? Ich habe mit VBA bisher nur mit der "Marko aufzeichnen" Funktion gearbeitet und verstehe vieles von deinem Code nicht auf anhieb 


Besten Dank!

[SemmyW]

Hallo,

Ich habe jetzt mal meine Ursprüngliche Liste um eine Spalte ergänzt in der die Soll EK-Preise zu sehen sind. Dies ist natürlich händisch zugewiesen, und dient nur der Richtungsweisung, damit man Missverständnisse vermeiden kann.

Danke sehr!

[LCohen]

Eigentlich nicht nötig. Das Einzige wäre, dass Du das FIFO für alle Haltedauern gleich haben möchtest. Bei mir gilt FIFO nur für "älter als 1 Jahr", bei den anderen wird proportional abgeschöpft.