FIFO stock valuation

F

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

[TABLE="width: 794"]
<tbody>[TR]
[TD="colspan: 7"]Fuel out[/TD]
[TD][/TD]
[TD="colspan: 7"]Fuel in[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Cumulative[/TD]
[TD][/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Cumulative[/TD]
[TD][/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]01/11/2017[/TD]
[TD][/TD]
[TD]69[/TD]
[TD][/TD]
[TD]69[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD="align: right"]31/10/2017[/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]01/11/2017[/TD]
[TD][/TD]
[TD]21[/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD="align: right"]02/11/2017[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[TD]550[/TD]
[TD][/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]01/11/2017[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD]104[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD="align: right"]08/11/2017[/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]01/11/2017[/TD]
[TD][/TD]
[TD]35[/TD]
[TD][/TD]
[TD]139[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD="align: right"]13/11/2017[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[TD]1100[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]02/11/2017[/TD]
[TD][/TD]
[TD]78[/TD]
[TD][/TD]
[TD]217[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/11/2017[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]257[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/11/2017[/TD]
[TD][/TD]
[TD]94[/TD]
[TD][/TD]
[TD]351[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/11/2017[/TD]
[TD][/TD]
[TD]64[/TD]
[TD][/TD]
[TD]415[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/11/2017[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]425[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/11/2017[/TD]
[TD][/TD]
[TD]35[/TD]
[TD][/TD]
[TD]460[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/11/2017[/TD]
[TD][/TD]
[TD]31[/TD]
[TD][/TD]
[TD]491[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/11/2017[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]511[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/11/2017[/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[TD]601[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/11/2017[/TD]
[TD][/TD]
[TD]60[/TD]
[TD][/TD]
[TD]661[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/11/2017[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]711[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/11/2017[/TD]
[TD][/TD]
[TD]28[/TD]
[TD][/TD]
[TD]739[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/11/2017[/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD]771[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/11/2017[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]783[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/11/2017[/TD]
[TD][/TD]
[TD]54[/TD]
[TD][/TD]
[TD]837[/TD]
[TD][/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
151
Tetra201
Tetra201
M
  • Solved
Formula Help.
Replies
3
Views
444
Fluff
Fluff
M
  • Question Question
Arranging XL dynamic Data (fetched through query)
Replies
0
Views
56
M Rizwan Khan
M

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top