Inventory: FIFO, LIFO and Average Cost

[pegbol]

.
.

Hello,

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

Thanks in advance for your help.

regards,
Pedro
.
.

 

[Vlinder_Lentebloem]

I copy/pasted from the posting and put the rows and columns the same (some headings were shifted so I put them back in original position). Not sure whether (and how) I could extract the spreadsheet from the posting. I'm first-time visitor...
With my spreadsheet it only changes values in a few cells and not to reasonable values.

 

[jindon]

The code only changes Col.I (Unit cost) when any cell is changed in col A:E
Other will be calculate by the fomrula embeded (done by Pegpol himself)

Is it not changing?

 

[Vlinder_Lentebloem]

jindon-san,

It is working now !

I assumed the formulas were copied along when I copied from pegbol's posted sheet (I still need to get some more experience using this site).
On top the 'go to next line' character was not accepted
in the version of EXCEL I'm using, so the change_workbook never started any of the macros. When I deleted the 'go to next line' character, it now works perfectly.
Many many thanks ! Doomo arigatoo gozaimasu !
Nancy

 

[jindon]

OK
If any bugs, please let me know.

 

[jcardoch]

Hi, I have the following error message:

compile Error: Sub or Function not defined (FIFO)

Please, can you help me.

Juan.

 

[jcardoch]

The code works Perfect!!!!

Muchas Gracias,
Juan

 

[chacha]

i am asking you to provide the formula which i can use to and to get the desire result but don't try to teach them what is the meaning of LIFO AND FIFO.BASICALLY I AM WORKING IN ONE COMPANY AND MAINTAINING THE INVENTORY IN EXCEL FORMAT BUT MY BOSS TOLD ME TO BUILD FORMULA OF FIFO, BUT I DON'T HAVE A KNOWLEDGE ABOUT ITOPENING RECEIVING COST OF GOOD SOLD CONSUMPTION CLOSING12 BAG 60 BAGS 72 BAG 12 CONSUMED HOW I CALCULATED THE CLOSING FIGURE BECAUSE ALL THE UNIT PRICE ARE VARIED AND EXPIRY ARE ALSO CHANGE.PLEASE ASSIST ME.CHAMCHA

 

[Necaxa]

OK
If any bugs, please let me know.

I've been looking for a good LIFO inventory calculation and finally I found
it. I am using the sheet and Pegbol's position to tie your code but when I type the opening balance I get a number 13 error mesage ("Type mismatch") and the highlighted line is : sumOut = a(i, 3).
My knowledge about macros is limited and I need your help to solve this issue.

Thanks in advance.

 

[POLLYROSENTHAL]

I cannot find formulas for the cells. Where is the hyperlink? Thank you.

 

[esung1234]

.
.

Last-In, First-Out (LIFO)- Assumes that the last unit making its way into inventory is sold first. The older inventory is therefore left over at the end of the accounting period.

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: #0c266b, colspan: 13"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - inv.xls[/TD]
[TD="align: right"]___Running: 11.0 : OS = Windows XP [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 13"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout[/TD]
[TD="align: center"]<form name="formCb285793"><input *******="window.clipboardData.setData("Text",document.formFb506491.sltNb910359.value);" type="button" value="Copy Formula" name="btCb963142"></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 13"]

<tbody>
<form name="formFb506491"></form>[TD="bgcolor: white"]<select onchange="document.formFb506491.txbFb548867.value = document.formFb506491.sltNb910359.value" name="sltNb910359"><option value="=F7" selected="">H7</option><option value="=F7*E7">J7</option><option value="=G7*I7">K7</option><option value="=J7">L7</option><option value="=H7+F8-G8">H8</option><option value="=F8*E8">J8</option><option value="=G8*I8">K8</option><option value="=L7+J8-K8">L8</option><option value="=H8+F9-G9">H9</option><option value="=F9*E9">J9</option><option value="=G9*I9">K9</option><option value="=L8+J9-K9">L9</option><option value="=H9+F10-G10">H10</option><option value="=E9">I10</option><option value="=F10*E10">J10</option><option value="=G10*I10">K10</option><option value="=L9+J10-K10">L10</option><option value="=H10+F11-G11">H11</option><option value="=((120*E9)+(250*E8))/G11">I11</option><option value="=F11*E11">J11</option><option value="=G11*I11">K11</option><option value="=L10+J11-K11">L11</option><option value="=H11+F12-G12">H12</option><option value="=F12*E12">J12</option><option value="=G12*I12">K12</option><option value="=L11+J12-K12">L12</option><option value="=H12+F13-G13">H13</option><option value="=E12">I13</option><option value="=F13*E13">J13</option><option value="=G13*I13">K13</option><option value="=L12+J13-K13">L13</option><option value="=H13+F14-G14">H14</option><option value="=E12">I14</option><option value="=F14*E14">J14</option><option value="=G14*I14">K14</option><option value="=L13+J14-K14">L14</option><option value="=H14+F15-G15">H15</option><option value="=F15*E15">J15</option><option value="=G15*I15">K15</option><option value="=L14+J15-K15">L15</option><option value="=H15+F16-G16">H16</option><option value="=E15">I16</option><option value="=F16*E16">J16</option><option value="=G16*I16">K16</option><option value="=L15+J16-K16">L16</option></select>[/TD]
[TD="bgcolor: #d4d0c8, align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input size="80" value="=F7" name="txbFb548867">[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"][/TD]
[TD="bgcolor: #d4d0c8"]<center>A</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>B</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>C</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>D</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>E</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>F</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>G</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>H</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>I</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>J</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>K</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>L</center>[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>5</center>[/TD]
[TD="bgcolor: #ffcc00, align: right"] [/TD]
[TD="bgcolor: #ffcc00, align: right"] [/TD]
[TD="bgcolor: #ffcc00, align: right"] [/TD]
[TD="bgcolor: #ffcc00, align: right"] [/TD]
[TD="bgcolor: #99ccff, colspan: 4, align: center"]QUANTITY[/TD]
[TD="bgcolor: #00ccff, colspan: 4, align: center"]COST[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>6</center>[/TD]
[TD="bgcolor: #ffcc00, align: center"]Nº[/TD]
[TD="bgcolor: #ffcc00, align: center"]DATE[/TD]
[TD="bgcolor: #ffcc00, align: center"]DOC.[/TD]
[TD="bgcolor: #ffcc00, align: center"]DESCRIPTION[/TD]
[TD="bgcolor: #99ccff, align: center"]UNIT PRICE[/TD]
[TD="bgcolor: #99ccff, align: center"]IN[/TD]
[TD="bgcolor: #99ccff, align: center"]OUT[/TD]
[TD="bgcolor: #99ccff, align: center"]BALANCE[/TD]
[TD="bgcolor: #00ccff, align: center"]UNIT COST[/TD]
[TD="bgcolor: #00ccff, align: center"]DEBIT[/TD]
[TD="bgcolor: #00ccff, align: center"]CREDIT[/TD]
[TD="bgcolor: #00ccff, align: center"]BALANCE[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>7</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]1[/TD]
[TD="bgcolor: #ffffff, align: center"]01/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"] [/TD]
[TD="bgcolor: #ffffff, align: left"]BEG. BAL.[/TD]
[TD="bgcolor: #ffffff, align: right"]10.00[/TD]
[TD="bgcolor: #ffffff, align: right"]100[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]100[/TD]
[TD="bgcolor: #ffff00, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]1,000.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]1,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>8</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]2[/TD]
[TD="bgcolor: #ffffff, align: center"]12/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]C001[/TD]
[TD="bgcolor: #ffffff, align: left"]IN[/TD]
[TD="bgcolor: #ffffff, align: right"]20.00[/TD]
[TD="bgcolor: #ffffff, align: right"]300[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]400[/TD]
[TD="bgcolor: #ffff00, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]6,000.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]7,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>9</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffffff, align: center"]12/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]C002[/TD]
[TD="bgcolor: #ffffff, align: left"]IN[/TD]
[TD="bgcolor: #ffffff, align: right"]25.00[/TD]
[TD="bgcolor: #ffffff, align: right"]170[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]570[/TD]
[TD="bgcolor: #ffff00, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]4,250.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]11,250.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>10</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]4[/TD]
[TD="bgcolor: #ffffff, align: center"]12/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]V001[/TD]
[TD="bgcolor: #ffffff, align: left"]OUT[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]50[/TD]
[TD="bgcolor: #ffffff, align: right"]520[/TD]
[TD="bgcolor: #ffff00, align: right"]25.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]1,250.00[/TD]
[TD="bgcolor: #ffffff, align: right"]10,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>11</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]5[/TD]
[TD="bgcolor: #ffffff, align: center"]23/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]V002[/TD]
[TD="bgcolor: #ffffff, align: left"]OUT[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]370[/TD]
[TD="bgcolor: #ffffff, align: right"]150[/TD]
[TD="bgcolor: #ffff00, align: right"]21.62[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]8,000.00[/TD]
[TD="bgcolor: #ffffff, align: right"]2,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>12</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]6[/TD]
[TD="bgcolor: #ffffff, align: center"]27/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]C003[/TD]
[TD="bgcolor: #ffffff, align: left"]IN[/TD]
[TD="bgcolor: #ffffff, align: right"]30.00[/TD]
[TD="bgcolor: #ffffff, align: right"]700[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]850[/TD]
[TD="bgcolor: #ffff00, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]21,000.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]23,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>13</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]7[/TD]
[TD="bgcolor: #ffffff, align: center"]29/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]V003[/TD]
[TD="bgcolor: #ffffff, align: left"]OUT[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]450[/TD]
[TD="bgcolor: #ffffff, align: right"]400[/TD]
[TD="bgcolor: #ffff00, align: right"]30.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]13,500.00[/TD]
[TD="bgcolor: #ffffff, align: right"]9,500.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>14</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]8[/TD]
[TD="bgcolor: #ffffff, align: center"]29/09/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]V004[/TD]
[TD="bgcolor: #ffffff, align: left"]OUT[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]250[/TD]
[TD="bgcolor: #ffffff, align: right"]150[/TD]
[TD="bgcolor: #ffff00, align: right"]30.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]7,500.00[/TD]
[TD="bgcolor: #ffffff, align: right"]2,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>15</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]9[/TD]
[TD="bgcolor: #ffffff, align: center"]02/10/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]C004[/TD]
[TD="bgcolor: #ffffff, align: left"]IN[/TD]
[TD="bgcolor: #ffffff, align: right"]25.00[/TD]
[TD="bgcolor: #ffffff, align: right"]320[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]470[/TD]
[TD="bgcolor: #ffff00, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]8,000.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]10,000.00[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>16</center>[/TD]
[TD="bgcolor: #ffffff, align: right"]10[/TD]
[TD="bgcolor: #ffffff, align: center"]03/10/2005[/TD]
[TD="bgcolor: #ffffff, align: center"]V005[/TD]
[TD="bgcolor: #ffffff, align: left"]OUT[/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"] [/TD]
[TD="bgcolor: #ffffff, align: right"]50[/TD]
[TD="bgcolor: #ffffff, align: right"]420[/TD]
[TD="bgcolor: #ffff00, align: right"]25.00[/TD]
[TD="bgcolor: #ffffff, align: right"]
[/TD]
[TD="bgcolor: #ffffff, align: right"]1,250.00[/TD]
[TD="bgcolor: #ffffff, align: right"]8,750.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 13"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: #ffffff, align: left"]LIFO[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

.
.

Can someone help me with the formula for the "Unit Cost" column? I would prefer not to use the VBA code. Is there a formula I can use in the "Unit Cost" column that will calculate the unit cost using LIFO. Any assistance will be greatly appreciated! =)