FIFO functie eindvoorraad per week laten berekenen

[ExelHenk]

Hallo Excelexperts,

ik heb een User Defined Function in VBA gemaakt die voor mij in Excel de waarde van de eindvoorraad berekend op basis van het First-in, First-out-systeem. Het probleem is dat mijn systeem per week de voorraad bijhoud en daar houd de functie geen rekening mee zodat de eindvoorraad niet goed berekent wordt.

Welke knappe kop heeft verstand van UDF's in VBA of excel en kan dit probleem voor mij oplossen? Ikzelf denk dat de functie wat aangepast moet worden, maar wellicht kunnen er ook wat functies in Excel gecombineerd worden in een formule om op het juiste antwoord te komen. Ik ben benieuwd!

In de bijlage heb ik het bestand gepost waarin ik ook heb waargegeven wat er uiteindelijk moet komen te staan. Handmatig is het namelijk best uit te rekenen, maar het moet automatisch gebeuren.

Bedankt bij voorbaat!

Ps1. Omdat het eigenlijk (ook) een VBA vraag is heb ik deze vraag ook in het VBA-forum van helpmij.nl geplaatst: http://www.helpmij.nl/forum/showthr...er-week-laten-berekenen?p=3998445#post3998445

Ps2. Het goede voorbeeldbestand staat in het 2 bericht van mij in deze thread, excuses daarvoor

 

[ExelHenk]

Verkeerd bestand gepost. Dit is het correcte bestand. Excuses

 

[ExelHenk]

Wie kan mij hiermee helpen?

Met vriendelijke groet, ExelHenk

 

[Mark xl]

Ik heb in je bestandje gekeken maar ik kom er met geen mogelijkheid achter wat je bedoelt, en hoe je aan de waarden komt in bijvoorbeeld cel AU3 in BLad 1
"=(250*19)+(300*20)-(250*19)-(50*20)"

kun je uitleggen wat wat is, en wat duidelijker uitleggen wat je precies bedoelt.

Ik kon er in ieder geval (nog) niet veel chocola van maken...

 

[ExelHenk]

Uitleg

Hey Mark, Natuurlijk wil ik het uitleggen!

In het eerste "tabel" in blad 1 wordt ingevuld wanneer welke materialen worden ingekocht. Dit is op datum. In "tabel" 2 in blad 1 wordt per week de voorraad bijgehouden op basis van het First in, First out systeem. Je kan dit automatisch laten uitrekenen met een 'User Defined Function' (UDF) in Visual Basic. Dit heb ik gedaan (vrij standaard uit een boek), maar de FIFO functie die ik gemaakt heb houdt er geen rekening mee dat "tabel" 2 op weekbasis is. Dat is het probleem. Deze functie is gemaakt voor een tabel waar alle materialen 1 keer voorkomen in het voorraadsysteem, terwijl de materialen in tabel 2 elke week opnieuw voorkomen.

In kolom AT (ik neem aan dat je die bedoelt) heb ik handmatig berekent wat de UDF dus uiteindelijk telkens automatisch moet vinden en berekenen. Let wel, elke week kunnen er andere, meer/minder materialen ingekocht/gebruikt worden.

Ik neem AT3 als voorbeeld: In weeknummer 18 zijn er 250 materialen a 19 OMR + 300 materialen a 20 OMR ingekocht voor in totaal 10.750 OMR (Tabel1). Hiervan zijn er 300 verbruikt (tabel 2) Op basis van het FIFO systeem zijn dit dus 250 materialen a 19 OMR en 50 materialen a 20 OMR voor in totaal 5.750 OMR. Wat overblijft voor gebruik in een volgende week is 250 * 20 OMR = 5.000 OMR , oftwel 10750 OMR - 5.750 OMR = 5.000 OMR.
Dit staat dus in AT3

AT10 als voorbeeld nemende zie je dat van die materialen nog voorraad van een vorige week aanwezig is (AM10). Die voorraad had een waarde van 16.000 OMR (AT6). Er moet dus rekening in de UDF gehouden worden dat de beginvoorraad een andere prijs per stuk kent. In de nieuwe bijlage heb ik de eindvoorraad van een vorige week toegevoegd aan de beginvoorraad van de huidige (geselecteerde) week.

(Blad 2 is niet nodig voor het berekenen van de waarde van de eindvoorraad p/w)

Is het zo duidelijk uitgelegd?

Groeten, ExelHenk

 

[ExelHenk]

Hoi iedereen, wie kan mij helpen? Groeten, Exelhenk