FIFO stock valuation

[fanboy]

Hi everybody,

I am trying to work out a way to calculate the value of inventory assuming the earliest inventory received is issued out first. I have pasted in a sample dataset below as a guide.

Per the below, the fuel issues on November 1st and 2nd would be costed at $0.90, and the issue on November 3rd at a combination of $0.90, and $0.70 from the fuel delivery on November 2nd. The next issue on November 3rd would be priced at $0.70.

I have tried and failed to figure out how to do this myself. Is anyone able to offer any help or advice?

Many thanks

Fuel out		Fuel in
Date		Amount		Cumulative		Price		Date		Amount		Cumulative		Price
01/11/2017		69		69		?		31/10/2017		250		250		0.9
01/11/2017		21		90		?		02/11/2017		300		550		0.7
01/11/2017		14		104		?		08/11/2017		250		800		0.8
01/11/2017		35		139		?		13/11/2017		300		1100		0.6
02/11/2017		78		217		?
03/11/2017		40		257		?
05/11/2017		94		351		?
05/11/2017		64		415		?
07/11/2017		10		425		?
08/11/2017		35		460		?
08/11/2017		31		491		?
08/11/2017		20		511		?
09/11/2017		90		601		?
12/11/2017		60		661		?
12/11/2017		50		711		?
13/11/2017		28		739		?
13/11/2017		32		771		?
14/11/2017		12		783		?
14/11/2017		54		837		?

<tbody>
</tbody>

 

[Gerald Higgins]

It might possibly be helpful to maintain a running balance of stocks, together with the average price.

Then any issues are at that average price.

 

[MickG]

It appears your data stretches from column "A" to "O" in alternate columns.
This means the "Fuel Out" (cumulative) is column "E" and the "Fuel In" (cumulative) is column "M".
Actual Data starts row 3.
On that basis , Try this for results in column "G".
Nb:- This seems to work for your data but may have problems if more complexed.

[COLOR=navy]Sub[/COLOR] MG31Jan13
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, cRng [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] oP [COLOR=navy]As[/COLOR] Double, nP [COLOR=navy]As[/COLOR] Double, cost1 [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] temp [COLOR=navy]As[/COLOR] Range, cost2 [COLOR=navy]As[/COLOR] Double, Tcost [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Set[/COLOR] Rng = Range("e3", Range("e" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] cRng = Range("m3", Range("m" & Rows.Count).End(xlUp))
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
  [COLOR=navy]If[/COLOR] Dn.Offset(, -4).Value >= cRng(c).Offset(, -1).Value [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] Dn <= cRng(c) [COLOR=navy]Then[/COLOR]
        Dn.Offset(, 2).Value = cRng(c).Offset(, 2).Value
    [COLOR=navy]ElseIf[/COLOR] Dn.Value > cRng(c).Value [COLOR=navy]Then[/COLOR]
        nP = Dn.Value - cRng(c).Value
        oP = cRng(c).Value - temp.Value
        cost1 = oP * cRng(c).Offset(, 2).Value
        c = c + 1
            [COLOR=navy]If[/COLOR] c <= cRng.Count [COLOR=navy]Then[/COLOR]
                cost2 = nP * cRng(c).Offset(, 2).Value
                Tcost = (cost1 + cost2) / Dn.Offset(, -2).Value
                Dn.Offset(, 2).Value = Format(Tcost, "0.00")
                nP = 0: oP = 0: cost1 = 0: cost2 = 0: Tcost = 0
            [COLOR=navy]End[/COLOR] If
      [COLOR=navy]End[/COLOR] If
 [COLOR=navy]End[/COLOR] If
[COLOR=navy]Set[/COLOR] temp = Dn
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[fanboy]

Hey Mick,

Thanks so much for your help. That works perfectly for the dataset I posted up. Could I ask you one more favour? I've jigged the format of the information around slightly so that now

• Fuel out cumulative is show in column K
• Fuel out date issued is shown in column F
• Fuel in cumulative is shown in column P
• Fuel purchase price is shown in column Q
• Fuel in date received is shown in column M

• Fuel price should be pasted to column H
• The dataset still starts from row C downward

I thought I could manipulate the code to make it work for the changes, but I'm getting errors. Do you have the time to update it for the changes above? Sorry to be a pain.

Thanks again

 

[MickG]

Try this on new data layout:-
Results column "H"

[COLOR=navy]Sub[/COLOR] MG01Feb51
[COLOR=navy]Dim[/COLOR] rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, cRng [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] oP [COLOR=navy]As[/COLOR] Double, nP [COLOR=navy]As[/COLOR] Double, cost1 [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] temp [COLOR=navy]As[/COLOR] Range, cost2 [COLOR=navy]As[/COLOR] Double, Tcost [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Set[/COLOR] rng = Range("K3", Range("K" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] cRng = Range("P3", Range("P" & Rows.Count).End(xlUp))
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng
    [COLOR=navy]If[/COLOR] Dn <= cRng(c) [COLOR=navy]Then[/COLOR]
        Dn.Offset(, -3).Value = cRng(c).Offset(, 1).Value
    [COLOR=navy]ElseIf[/COLOR] Dn.Value > cRng(c).Value [COLOR=navy]Then[/COLOR]
        nP = Dn.Value - cRng(c).Value
        oP = cRng(c).Value - temp.Value
        cost1 = oP * cRng(c).Offset(, 1).Value
        c = c + 1
            [COLOR=navy]If[/COLOR] c <= cRng.Count [COLOR=navy]Then[/COLOR]
                cost2 = nP * cRng(c).Offset(, 1).Value
                Tcost = (cost1 + cost2) / (Dn.Value - Dn.Offset(-1).Value) 
                Dn.Offset(, -3).Value = Format(Tcost, "0.00")
                nP = 0: oP = 0: cost1 = 0: cost2 = 0: Tcost = 0
            [COLOR=navy]End[/COLOR] If
      [COLOR=navy]End[/COLOR] If
[COLOR=navy]Set[/COLOR] temp = Dn
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[fanboy]

This is brilliant Mick. You are a kind, kind man. Thank you so much

 

[MickG]

You're very welcome