FIFO calculations

[MTamiesA]

I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO.

What I need is a total cost (what I have paid) for what is sitting in my freezer.

Column A is START (5)
Column B is RECEIVED (6)
Column C is USED (2)
Column D is END (9) or (A1+B1-C1)
Column E is OLD COST ($12.20) cost per unit of those 5
Column F is NEW COST ($13.50) cost per unit of the 6 i got in

So I need in Column G a FIFO formula for total cost of what I have in the fridge.

ANY help woud be greatly appreciated!!!

 

[bertie]

Hi and welcome to the board.

I don't know anything about first in first out inventories, but from your description is this just a product of:

(start * old cost) + (received * new cost)

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 56px"><COL style="WIDTH: 55px"><COL style="WIDTH: 71px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Start</TD><TD style="TEXT-ALIGN: center">Received</TD><TD style="TEXT-ALIGN: center">Used</TD><TD style="TEXT-ALIGN: center">End</TD><TD style="TEXT-ALIGN: center">Old Cost</TD><TD style="TEXT-ALIGN: center">New Cost</TD><TD style="TEXT-ALIGN: center">Total Cost</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">$12.20</TD><TD style="TEXT-ALIGN: center">$13.50</TD><TD style="TEXT-ALIGN: center">$142.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=+A2+B2-C2</TD></TR><TR><TD>G2</TD><TD>=(A2*E2)+(B2*F2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[Robert Mika]

In FIFO (FIRST IN FIRST OUT) the product which is longer in stock is going to be sold first so I will go with:

(A2-C2)*E2+(B2*F2) in G2

This way you will have:

Still 3 item from old stock 3*12.2= 26.6
and 6 item from new delivery 6*13.5=81

giving total 117.6

 

[Andrew Fergus]

@Robert : slight typo with 26.60 (should be 36.60) but IMO that is the correct application of FIFO for a total value of 117.60.

@ MTamiesA : welcome to MrExcel! An issue you will face is if you have up to 3 (or more) differing prices in stock. For example, starting with your current data......if on the next line you purchase 5 more at $13.60 each and use only 1, then what value do you want to see?

Andrew

 

[MTamiesA]

Thanks everyone!

@ Robert, thanks for the welcome.

If there does come a time where there are three of the same items with different prices then PERHAPS I have a chance at winning my argument that we should just adjust the average price each time items come in.

Until then...it's most likely that there will only be 2 items at 2 different prices since its something that gets updated once a week and we don't order that much.

Thanks for that formula....i just knew i was over complicating it.

Ill be back if something doesn't work! THANKS AGAIN EVERYONE!!

 

[rambhagwat]

FIFO method is generally applied in Inventory value calculations or to calculate capital gains in case of share transactions. It is slightly involved to solve with just a formula in Excel. Any inventory item or shares are bought or sold in different lots and at different prices. When we use the FIFO method, when a lot is sold, the sold quantity may come from one or more different lots which were bought at different prices. That is the complication. To solve the problem systematically, we need a list of ALL buy and sell transactions in order or sequence (that is, the date order). The information needed is date of transaction, inventory item name, quantity bought or sold and the price of buy or sell. Using this data we need to create an output list. For each sell transaction, we need to find out which bought lots the sold quantity came from. The output will have typically following information:

Inventory name,Sold quantity, Selling price, price at which this quantity was bought. Once this is done, any question can be answered. I developed a sample Excel sheet using VBA. I am unable to attach the file here. However, if someone is interested please mail me at {email address removed by Moderator} and I will send it.

Thanks
Ram Bhagwat

 

[Peter_SSs]

rambhagwat

Welcome to the MrExcel board!

Please refer to point 19 of the Posting Rules regarding openly posted email addresses.

If you have a workbook that you would like to share through MrExcel, you may also like to submit it for consideration for inclusion here: http://www.mrexcel.com/coolspreadsheets.html

 

[rambhagwat]

Thanks Peter,

Although I did not go thru the posting rules,i was a little hesitant to put my own email address in the message. Thanks for pointing me to the Cool Spreadsheet link. I will upload it there and inform the forum my another message.

Regards
Ram Bhagwat

 

[Excelanil]

FIFO method is generally applied in Inventory value calculations or to calculate capital gains in case of share transactions. It is slightly involved to solve with just a formula in Excel. Any inventory item or shares are bought or sold in different lots and at different prices. When we use the FIFO method, when a lot is sold, the sold quantity may come from one or more different lots which were bought at different prices. That is the complication. To solve the problem systematically, we need a list of ALL buy and sell transactions in order or sequence (that is, the date order). The information needed is date of transaction, inventory item name, quantity bought or sold and the price of buy or sell. Using this data we need to create an output list. For each sell transaction, we need to find out which bought lots the sold quantity came from. The output will have typically following information:

Inventory name,Sold quantity, Selling price, price at which this quantity was bought. Once this is done, any question can be answered. I developed a sample Excel sheet using VBA. I am unable to attach the file here. However, if someone is interested please mail me at {email address removed by Moderator} and I will send it.

Thanks
Ram Bhagwat

Hi Ram:

How do I get a copy of your Excel Sheet with the VBA code?

Thanks in advance.

Cheers!

 

[mfahadpk]

Hi Ram

Id really appreciate if you can provide the Excel file for the FIFO calculations to me. Can you kindly upload it to the link given by the moderator above so I could download it or let me know any other option. If I provide my email here, it would be against the rules.

cheers

Fahad