FIFO stock valuation

fanboy

Board Regular
Joined
Feb 9, 2008
Messages
59
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 outFuel in
DateAmountCumulativePriceDateAmountCumulativePrice
01/11/20176969?31/10/20172502500.9
01/11/20172190?02/11/20173005500.7
01/11/201714104?08/11/20172508000.8
01/11/201735139?13/11/201730011000.6
02/11/201778217?
03/11/201740257?
05/11/201794351?
05/11/201764415?
07/11/201710425?
08/11/201735460?
08/11/201731491?
08/11/201720511?
09/11/201790601?
12/11/201760661?
12/11/201750711?
13/11/201728739?
13/11/201732771?
14/11/201712783?
14/11/201754837?

<tbody>
</tbody>
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Feb 9, 2008
Messages
59
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
Joined
Jan 9, 2008
Messages
14,841
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:

fanboy

Board Regular
Joined
Feb 9, 2008
Messages
59
This is brilliant Mick. You are a kind, kind man. Thank you so much
 

Forum statistics

Threads
1,077,855
Messages
5,336,782
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top