FIFO Inventory Valuation in Excel using Data Tables – How To

Hasaan Fazal

Inventory can be valued in number of ways, FIFO, LIFO and AVCO being the most famous. To learn few more inventory valuation methods have a quick look at this: What are different inventory valuation methods?

Entities purchase inventory as and when they feel the need or based on a particular method for example Economic Order Quantity (EOQ). Often the the purchase price is different every time order is placed. Therefore, the price of each purchase lot is different from each other as you can see in the data at left

Table of Contents

First In First Out (FIFO) assumes that every time units are taken from store, they are issued from the oldest available lot first and next lot to be consumed only if needed.

This requirement makes the calculations a little too complex for formulas. May be there is a way to do it via formulas but I am unaware of it yet and would love to know. On the other hand VBA is also an alien knowledge for me so far. So is there a way? Yes there is!

By the end of the tutorial we will be able to get this:

This is where Excel Data Tables comes handy. One of the less known and used tool for sure. But it can make this process super smooth and easy. So lets get to learn it!

Important: Excel Data Tables are different from Excel Tables so please don’t confuse the two

In a hurry?

Skip the tutorial for now and get the file. This fully unlocked no secrets held Excel file is available for you to change and use at your disposal. It is designed to input data easily. You just have to give the units sold and all calculations will be done automatically.

ALSO And any future revisions to this template will be sent to you absolutely free!

Buy Now

Got a question? Contact details are here

FIFO Inventory Valuation in Excel – Step by Step

Step 1: Download this workbook. It has the dummy data that we can use to understand the process.

Step 2: Format the worksheet as follows so that we know exactly what information is where:

Step 3: Suppose we have made a sale of 1000 units. So enter that in cell G1 and name the cell as “sales” by typing in the name box having cell G1 active.

Step 4: Now we want the total units sold to be divided as per lots available deducting from the first available lot. Go to cell F4 and put this formula and drag it down to cell F10.

=MIN(B4,sales-SUM($F$3:F3))

Step 5: Next put the following formulas in the mentioned cells and drag them to row 10:

G4: =F4*C4
H4: =B4-F4
I4: =H4*C4

Step 6: Calculate the totals of each column by simply selecting the 11th row and hitting ALT+=

So we have completed the calculations and if you change the value in sales we can easily update the records of each column showing the correct cost and value of inventory at hand

Though the above process has helped me get the numbers but its not helping much if I want to calculate the cost of sales for different quantities. For example if I want to know the cost of sales of all the quantities by the end of the month, I simply need a report instead of punching each quantity in cell and then record the value somewhere. To solve this we will take few more steps

Step 7: Format the cells as follows. This is where we will have a mini report generator for the units you mention and Excel will automatically calculate the CGS of units mentioned on FIFO basis:

Step 8: In cell L4 put the following formula and drag the fill handle down to cell L10

=SUM($K$4:K4)

Step 9: In cell M3 refer to total of CGS we calculated before in cell G11 with this formula: =G11

Step 10: Select the range L3: M10 and go to Data tab > Forecast group > Click what-if drop down button > click Data table. A pop up will appear. Leave the row input cell empty and mention units sold in column input cell G1. Click OK.

Step 11: Put the following formula in CGS column’s cells:

N4: =M4
N5: =M5-SUM($N$4:N4), drag the fill handle down to cell N10

Step 12: Select cells N4:N10 and hit Ctrl+1. Go to Custom and in the input bar mention the following and click OK:

0;0;

Step 13: Hide the columns L and M. Now test your report by typing the units in “Units” column and it will give you the correct CGS as per FIFO.

So here you have fully working FIFO Calculator and a mini report generator!

FIFO Stock Valuation in Excel - Template

Download the fully working and unlocked Excel file that can serve as a template for your FIFO calculations and also help learning the techniques applied.

And any future revisions to this template will be sent to you absolutely free!

Buy Now

Got a question? Contact details are here

Liked it? Pin it!

49 COMMENTS

Robert Large At

This is nice, Here’s the formula made in Excel 365 making it dynamic.

purchase = sales
avail = units
uNeed = Formula used in F4 downwards
logic = extra formula to remove negative numbers
remain = Formula used in H4 downwards

This doesn’t calculate the CGS or value, but follow the ‘remain’ principal to achieve.

=LET(
purchase,g1,
avail,b4:b10,
uNeed,MAP(avail,SCAN(0,VSTACK(0,avail),LAMBDA(v,a,v+a)),LAMBDA(a,b,MIN(a,purchase-SUM(b)))),
logic,DROP(IF(uNeed<0,0,uNeed),-1),
remain,MAP(avail,logic,LAMBDA(a,b,a-b)),
HSTACK(logic,remain))
coreball At

I followed your lead and created a FIFO share register in the form of a data table. The final COGS calculation is off by a fair amount. If you could help me out, please tell me how to send you my Excel sheet.
speaktoastrologer At

Black magic for love back is a kind of spell that is black magic but it is not for evil purposes. It will help you to get your love back in your life.
bestastrologysolution At

Consult Shastri Ji For Problems Related To Love, Relationships, After Marriage Issues, Parents Against Love Marriage, Breakup With Lover, Husband/Wife Asking For Divorce, Business Disputes, Career Issues, Health Problems, Visa Disapproval, Family Disputes And Other Life Related Issues.
harivanshtours At

Thanks for sharing such a great information.. It really helpful to me..I always search to read the quality content and finally i found this in you post. keep it up!
maharanacab At

This blog is very informative thank you to share this kind of informartion.
novelwebcreation At

articles that are nice and very interesting I like to read the articles you make
novelwebsolution At

Thanks for sharing such a great information.. It really helpful to me..I always search to read the quality content and finally i found this in you post. keep it up!
prontopanels At

Thanks for sharing such a great information.. It really helpful to me..I always search to read the quality content and finally i found this in you post. keep it up!
casawoodz At

This is really great thanks to share this kind of blog.
AMD Dental Clinic At

I bookmarked this site to check out new stuff post. Keep it up, you’re great! This is an awesome motivating article. Keep it up. Thanks
AMD Dental Clinic At

Thank you very much for sharing good content. Useful blog information, wanted to thank you for this excellent read!! Keep it up!
fireboy and watergirl At

We need you to make some adjustments to the formulae so that the sheet uses LIFO for its calculations.
Roger At

Try using this in cell F4 instead if you are having problems:

=MIN(SALES-SUM($F$3:F3),B4)

If you find this helpful please consider sending a small gratitude amount
Boba, BSC, ETH, MATIC
0xA42A2D6a4a08f1dbdcAb9e643dD673c8b9de5de6

#karma

Thanks pakaccountants for helping us!
Sam At

Is there a way to get the cost of goods sold in cell G11 only by using one formula?
ssd At

Great Job,

Works very well for me.

But I am a bit of a geek, I would like to know how the Min function works in this example and how does the DATA Table part work.

If it is not too much, would you reply on my email.

Thanks for the wonderful work that you are doing Hassan Fazal.

Best Regards,
Ssd
- ssd At
  
  By the way forgot to mention in the earlier comment that when the rows for the data input become more than a thousand the excel slows down……..
james j licata At

I need an excel spreadsheet to buy foreign currency then use it to purchase goods in foreign currency and translate back to dollars. since each purchase of currency is at a different rate, I need a FIFO function to account for use of funds at 2 different rates
Maria At

Thanks you solved my problem
jawad At

What if Multiple sales/issuance of inventory in different dates? You are only showing to calculate a specific single item calculation.
MS At

Nice article. It’s informative blog.
Kriszta At

Hi, the formula =MIN(B4,sales-SUM($F$3:F3)) doesn’t works for me, too. Is there any solution?
thx
- Kriszta At
  
  It’s OK! 😀 I have to write SZUM
- AXMADJON SOXIBOYEV At
  
  You wrote a comma (,) after cell B4. In fact, semicolons are written (;)
  
  Here it is
  
  =MIN(B4;sales-SUM($F$3:F3))
Shirley At

What about weight or moving average formula?
Joel At

this is genius work!
Dan At

Will this work with an invoicing template in Excel?
Specifically, using FIFO for COGS associated with each invoice.
- Hasaan Fazal At
  
  Yes, it should work. You have the technique and the template. Try it out. And if you get any questions you can always ask.
Karanbir Singh Soin At

I have made a FIFO share register using a data table as in your example. Somehow the COGS part in the end is not coming right. How can I mail you my excel sheet if you could guide me?
Naveed Rasheed At

Tell me how can we merge previous sheet cell record in next sheet cell e.g
1- Sheet1 In First Semester cell show three exam result with subject code (Fail 5101,5102,5103)
2- Sheet2 In second Semester cell show two exam result (*************Fail 5201,5202)
tell me by which formula 1st semester cell record will show in 2nd semester cell record until he will pass the exam…………….same condition will apply next more 6 semesters e.g 3rd, 4th, 5th, 6th, 7th, 8th so on. I hope that you will give me answer.
softwareguide At

Awesome! and very easy to understand.
- Pavel At
  
  Can you give me the copy that you done
rinda At

Could you make some changes to the formulas and have the sheet calculate using LIFO?
- Hasaan Fazal At
  
  A detailed tutorial is already in process and will be published today. Will update the comment with link.
A At

Hi, thanks, Great template!
is it possible to tune these formulas to have the same template for weighted average?
Kelsey At

Could you make some changes to the formulas and have the sheet calculate using LIFO?
irfan At

Dear,

can uyou please help in establishing Stock card using FIFO with multiple items. I HV around 300 item and want stock card using FIFO
- Irfan At
  
  Nice
- Irfan At
  
  Let me try tomorrow Nice
Igor Paix At

=MIN(B4,sales-SUM($F$3:F3))

This first formula is wrong… could anyone help me please?

Thx
- Hasaan Fazal At
  
  Hey,
  Yes here to help. Ask please
  - Kriszta At
    
    Hi, the formula =MIN(B4,sales-SUM($F$3:F3)) doesn’t works for me, too. Is there any solution?
    thx
  - Manuel Salameh At
    
    I have the same issue and you haven’t answered Kriszta. What is wrong here? Excel sais, thi sis not a formula 😐
    And to be honest, I haven’t really understood, what the formula should do, otherwise I would have done it by my own :-/
  - Manuel Salameh At
    
    Hi Hasaan, any chance that you post the fix to the wrong excel formula?
    - Michael At
      
      click on the box where you typed in 1000. On the far left you will see “G1”. Now click on that box and replace “G1” with sales. renaming the box as “sales” should do the trick
Tanya Morera At

This has been super helpful information, but i can’t get the formula’s to work in my google drive spreadsheet.
- Hasaan Fazal At
  
  For this to work we need data tables like functionality implemented. I will have to check if google spreadsheets allow this method to work or not as I am not using google sheets frequently.
prakash rajendhran At

its very use full guide for ever…………
rukhsana At

Very productive indeed

Comments are closed.

FIFO Inventory Valuation in Excel – Step by Step

More tutorials on Inventory valuation in Excel

Liked it? Pin it!

49 COMMENTS