# FIFO stock valuation

#### fanboy

##### Board Regular
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

##### Well-known Member
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

##### MrExcel MVP
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.

Code:
``````[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

Last edited:

#### fanboy

##### Board Regular
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

##### MrExcel MVP
Try this on new data layout:-
Results column "H"
Code:
``````[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

Last edited:
• hiker95 and fanboy

#### fanboy

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

#### MickG

##### MrExcel MVP
You're very welcome