First-In, First-Out Inventory Database

rommelq

New Member
Joined
Aug 26, 2002
Messages
47
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. :-D


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:
?
?
 
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
Code:
=-Fifo2($B$2:$D$4)

Which uses the above code.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

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