Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to program FIFO inventory in excel ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to program FIFO inventory in excel ?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to program FIFO inventory in excel ?
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. wrote: 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. wrote: "Amount" is the volume purchased or sold? wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to program FIFO inventory in excel ?
hi,
it is not possible to oversell the inventory. many thanks wrote: 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. wrote: 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. wrote: "Amount" is the volume purchased or sold? wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to program FIFO inventory in excel ?
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 wrote: hi, it is not possible to oversell the inventory. many thanks wrote: 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. wrote: 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. wrote: "Amount" is the volume purchased or sold? wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can we run FIFO Inventory in excel? | Excel Worksheet Functions | |||
Is there a way to unload the loaded XLL file in Excel? Hi all, I amdebugging XLL link library using Visual C++. Everytime I rebuild the XLL, Ihave to close the whole Excel program and relaunch the Excel program again,and then load in the newly gene | Excel Discussion (Misc queries) | |||
Program for inventory | Excel Discussion (Misc queries) | |||
How to prepare Multi Stock Valuation on Fifo Basis in Excel | Excel Worksheet Functions | |||
FIFO Inventory tracking | Excel Programming |