How to program FIFO inventory in excel ?

[galih.chandra]

dear all,

I have 1 sheet which contains product name, buy or sell, price, amount,
trade date, counterparty.
for example :
book A - buy - $50 - 10 - 1 jan 06 - G
book B - buy - $100 - 20 - 1 jan 06 - G
book A - buy - $60 - 15 - 2 jan 06 - H
book A - sell - $65 - 15 - 3 jan 06 - I
book B - sell - $110 - 15 - 3 jan 06 - J

I would like to have a new sheet which shows FIFO for those books
so it would shows table with something like

book A - sell - $65 - 15 - 3 jan 06 - I consist of : book A - buy -
$50 - 10 - 1 jan 06 - G
:
book A - buy - $60 - 5 - 2 jan 06 - H

(book A sold at 3 jan 06 to I at price $65 is using 10 books bought at
1 jan 06 at price $50 from G & 5 books bought at 2 jan 06 at price $60
from H (in table form))

how can I do that ?

thanks

 

[acampbell012]

"Amount" is the volume purchased or sold?

 

[galih.chandra]

hi, amount is for both...
if the bond is sold, then the number shows amount sold
if the bond is purchased, then the number shows amount bought

tha.

 

[acampbell012]

Sorry, I have one more question for you. Is it possible to oversell
your inventory? I am nearing a solution for you and do not currently
have solution for reducing inventory below 0.

 

[galih.chandra]

hi,

it is not possible to oversell the inventory.

many thanks

 

[acampbell012]

Try the following code in a worksheet module and let me know how it
works:

Assumptions:
1. Buy/Sell transaction are chronilogical. Routine reduces inventory
from top to bottom
2. No overselling of inventory occurs.

I setup your test data in columns A to F, no headers. To preserve
your original values, I copy all buy transactions to columns g
(starting_inventory). The "Update_Inventory" routine cycles through
the data, finds each "sell" transaction by book type. It then works
down the list reducing the inventory (buy items) in column G until the
value of the sales quantity has been met. Then, it moves on to the next
sale. The "Do" loop and "ErrorHandler" get around a problem I was
having with continuous looping and crashing on the last "findnext"
operation.

Post back and let me now how it works.

Alan

Option Explicit
Sub Update_Inventory()
Starting_Inventory
Dim endrow As Long 'last row in range
Dim Title As String 'book title
Dim Mycell As Range 'Cell with sold book title
'Dim MyCell2 As Range
Dim qtySell As Long 'Sell volume in current row
Dim i As Long 'row counter
Dim buysell As Range
Dim MyRow As Long
Dim RemInv As String
Dim invred As Long
Dim Lastrow As Long 'row above sell transaction
endrow = Range("A" & Rows.Count).End(xlUp).Row
Set buysell = Range("B1:B" & endrow).Find("Sell", LookIn:=xlValues)
If buysell Is Nothing Then
Exit Sub
End If
For i = Range(buysell.Address).Row To endrow Step 1
Title = Range(buysell.Address).Offset(0, -1).Value
qtySell = buysell.Offset(0, 2).Value
Lastrow = buysell.Row - 1
For Each Mycell In Range("A1:A" & Lastrow)
Do While MyRow < buysell.Row
If Mycell.Value <> Title Then
GoTo nextmycell:
ElseIf Mycell.Offset(0, 1).Value = "buy" Then
RemInv = Range(Mycell.Address).Offset(0, 6).Address
If Range(RemInv).Value = 0 Then
GoTo nextmycell:
ElseIf qtySell <= Range(RemInv).Value Then
Range(RemInv).Value = Range(RemInv).Value -
qtySell
qtySell = 0
MyRow = buysell.Row
GoTo NextSale:
ElseIf Range(RemInv).Value < qtySell Then
invred = Range(RemInv).Value
Range(RemInv).Value = 0
qtySell = qtySell - invred
GoTo nextmycell:
End If
End If
Loop
nextmycell:
Next Mycell

NextSale:
On Error GoTo ErrorHandler:
Set buysell = Range("B" & i & ":B" & endrow). _
FindNext(buysell)
Next
ErrorHandler:
End Sub

Sub Starting_Inventory()
Dim endrow As Long
Dim Mycell As Range
endrow = Range("A" & Rows.Count).End(xlUp).Row
For Each Mycell In Range("A1:A" & endrow)
If Mycell.Offset(0, 1).Value = "buy" Then
Mycell.Offset(0, 6).Value = Mycell.Offset(0, 3).Value
End If
Next Mycell
End Sub