First-In, First-Out Inventory Database

[rommelq]

Hi,

I'm trying to create a simple first-in, first-out inventory database using excel but without the vbasic codes. Below is a sample of my database. I would like to seek your help in coming up with formulas to replace the "?" Or you may suggest some other way to achieve the same results.

My objectives are to breakdown the ending inventory under the FIFO method using simple formulas and to determine the total consumed tanks and corresponding costs for the month.

By the way, i'm using only Excel 2002 under Windows ME.

Thank you.


First In, First Out
Date Type Tanks Unit Price Amount
01/01/04 Delivery 10 1,210.00 12,100.00
01/05/04 Delivery 5 1,220.00 6,100.00
01/06/04 Consumed -6 ? ?
01/10/04 Delivery 6 1,230.00 7,380.00
01/12/04 Consumed -8 ?
01/15/04 Delivery 20 1,240.00 24,800.00
01/17/04 Consumed -12 ? ?
01/20/04 Delivery 5 1,250.00 6,250.00

Balance 20

Breakdown:
?
?

 

[SIXTH SENSE]

Hi!

Here is the UDF I was talking about.


<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Fifo2(DataTable <SPAN style="color:#00007F">As</SPAN> Range)
Application.Volatile <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ConQty1()
<SPAN style="color:#00007F">Dim</SPAN> DElQty1()
<SPAN style="color:#00007F">Dim</SPAN> Price1()
<SPAN style="color:#00007F">Dim</SPAN> TMP <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">ReDim</SPAN> ConQty1(0)
<SPAN style="color:#00007F">ReDim</SPAN> DElQty1(0)
<SPAN style="color:#00007F">ReDim</SPAN> Price1(0)
<SPAN style="color:#007F00">'MsgBox ""</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> DataTable.Rows.Count
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(DataTable(i, 1))
    <SPAN style="color:#00007F">Case</SPAN> UCase("Consumed")
        ConQty1(UBound(ConQty1)) = Abs((DataTable(i, 2)))
        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> ConQty1(UBound(ConQty1) + 1)
    Case UCase("Delivery")
        DElQty1(UBound(DElQty1)) = (DataTable(i, 2))
        Price1(UBound(Price1)) = (DataTable(i, 3))
        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> DElQty1(UBound(DElQty1) + 1)
        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> Price1(UBound(Price1) + 1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i

costx = 0
i = 1
<SPAN style="color:#00007F">For</SPAN> k = <SPAN style="color:#00007F">LBound</SPAN>(ConQty1) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ConQty1) - 1
  
    <SPAN style="color:#00007F">For</SPAN> l = <SPAN style="color:#00007F">LBound</SPAN>(DElQty1) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(DElQty1) - 1
        p = Price1(l)
        <SPAN style="color:#00007F">If</SPAN> DElQty1(l) > ConQty1(k) <SPAN style="color:#00007F">Then</SPAN>
        costx = costx + p * (ConQty1(k))
        DElQty1(l) = DElQty1(l) - ConQty1(k)
        ConQty1(k) = 0
        <SPAN style="color:#00007F">GoTo</SPAN> NXTCON:

        <SPAN style="color:#00007F">ElseIf</SPAN> ConQty1(k) > DElQty1(l) <SPAN style="color:#00007F">Then</SPAN>
        costx = costx + p * DElQty1(l)
        ConQty1(k) = ConQty1(k) - DElQty1(l)
        DElQty1(l) = 0
        Price1(l) = 0
        
        <SPAN style="color:#00007F">GoTo</SPAN> NXTDEL:
        <SPAN style="color:#00007F">Else</SPAN>
        costx = costx + p * ConQty1(k)
        ConQty1(k) = 0
        DElQty1(l) = 0
        Price1(k) = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

NXTDEL:
    <SPAN style="color:#00007F">Next</SPAN> l
NXTCON:
i = i + 1
<SPAN style="color:#00007F">Next</SPAN> k
Fifo2 = costx
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

click on the price column of the last sheet I post with NA as the unit price, the function there is

=-Fifo2($B$2:$D$4)

Which uses the above code.