Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something similar to FIFO method
Hi everyone,
I would like to create a script similar with FIFO (first-in first out) method, but instead of taking out the oldest item from stock, it should take out the stock with the smallest (minimum) acquisition price. For instance if I have: Type Date Number Acquisition Lot of units price per unit IN 29-Oct-08 3,000 7.8500 1 IN 30-Oct-08 75,000 7.9000 2 IN 06-Nov-08 7,500 7.8000 3 OUT 25-Nov-08 12,500 8.1027 On 25th of November the quantity of 12,500 units should be taken out in the following order: - first 7,500 units from Lot 3, as this lot has the smallest acquisition price - the following 3,000 units from Lot 1, as this lot has the minimum available acquisition price - the difference of 2,000 unit from lot 2 I hope I was pretty concise in my explanantions. Can anyone help me? Many thanks in advance, Bogdan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something similar to FIFO method
I t is hard to wriote a script the way you posted your request. I assume the
stock items and the orders are on seperate worksheets and what you posted is the results of the scripts. I would need to know the sheet names and the location of the stock items and the where the orders are posted. the algorithm is simple if you sort the stock items by "Acquisition of units". QuantyRequired = 12500 Rowcount = 1 Do while QuantyRequired 0 Number = Range("C" & Rowcount) if Number QuantyRequired then QuantyRequired = 0 Else QuantyRequired = QuantyRequired - Number end if Rowcount = Rowcount + 1 loop "Bogdan" wrote: Hi everyone, I would like to create a script similar with FIFO (first-in first out) method, but instead of taking out the oldest item from stock, it should take out the stock with the smallest (minimum) acquisition price. For instance if I have: Type Date Number Acquisition Lot of units price per unit IN 29-Oct-08 3,000 7.8500 1 IN 30-Oct-08 75,000 7.9000 2 IN 06-Nov-08 7,500 7.8000 3 OUT 25-Nov-08 12,500 8.1027 On 25th of November the quantity of 12,500 units should be taken out in the following order: - first 7,500 units from Lot 3, as this lot has the smallest acquisition price - the following 3,000 units from Lot 1, as this lot has the minimum available acquisition price - the difference of 2,000 unit from lot 2 I hope I was pretty concise in my explanantions. Can anyone help me? Many thanks in advance, Bogdan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something similar to FIFO method
Hi Joel,
Many thanks for your answer. It is helpful, I started to build a code around it, but I'm again stuck :) because the code you sent works fine only in case of one quantity out. Using the same example, let's assume that beside 12,500 units I'll have another 30,000 units out on Nov 30. Of course, this quantity should be taken out from the BALANCE of Lot 2 (which is now 73,000 units). What should be added to your initial code in order to be able to deduct these 30,000 units from 73,000? Many thank in advance again, Bogdan "Joel" wrote: I t is hard to wriote a script the way you posted your request. I assume the stock items and the orders are on seperate worksheets and what you posted is the results of the scripts. I would need to know the sheet names and the location of the stock items and the where the orders are posted. the algorithm is simple if you sort the stock items by "Acquisition of units". QuantyRequired = 12500 Rowcount = 1 Do while QuantyRequired 0 Number = Range("C" & Rowcount) if Number QuantyRequired then QuantyRequired = 0 Else QuantyRequired = QuantyRequired - Number end if Rowcount = Rowcount + 1 loop "Bogdan" wrote: Hi everyone, I would like to create a script similar with FIFO (first-in first out) method, but instead of taking out the oldest item from stock, it should take out the stock with the smallest (minimum) acquisition price. For instance if I have: Type Date Number Acquisition Lot of units price per unit IN 29-Oct-08 3,000 7.8500 1 IN 30-Oct-08 75,000 7.9000 2 IN 06-Nov-08 7,500 7.8000 3 OUT 25-Nov-08 12,500 8.1027 On 25th of November the quantity of 12,500 units should be taken out in the following order: - first 7,500 units from Lot 3, as this lot has the smallest acquisition price - the following 3,000 units from Lot 1, as this lot has the minimum available acquisition price - the difference of 2,000 unit from lot 2 I hope I was pretty concise in my explanantions. Can anyone help me? Many thanks in advance, Bogdan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something similar to FIFO method
On 14 Dec, 10:35, Bogdan wrote:
Hi everyone, I would like to create a script similar with FIFO (first-in first out) method, but instead of taking out the oldest item from stock, it should take out the stock with the smallest (minimum) acquisition price. For instance if I have: *Type * * Date * *Number * * * Acquisition * * * * *Lot * * * * * * * * * * * * * * * * * *of units * * *price per unit IN * * *29-Oct-08 * * * * * * 3,000 * * * * * *7.8500 * * * * * * * 1 IN * * *30-Oct-08 * * * * * 75,000 * * * * * * 7.9000 * * * * * * * 2 IN * * *06-Nov-08 * * * * * * 7,500 * * * * * *7.8000 * * * * * * * 3 OUT * * 25-Nov-08 * * * * * 12,500 * * * * * * 8.1027 On 25th of November the quantity of 12,500 units should be taken out in the following order: - first 7,500 units from Lot 3, as this lot has the smallest acquisition price - the following 3,000 units from Lot 1, as this lot has the minimum available acquisition price - the difference of 2,000 unit from lot 2 I hope I was pretty concise in my explanantions. Can anyone help me? Many thanks in advance, Bogdan Phillip London UK Assumptions Nothing in colum F An empty row between your OUT rows and your IN roews Your IN data starts in A1 This works for me Sub test() Dim instock As Long Dim rngAcq As Range Dim rngStock As Range Dim rngDB As Range Dim OutAmount As Long Dim StockAccum As Long Dim x As Long Set rngDB = ActiveSheet.Range("a1").CurrentRegion.Cells Set rngLot = rngDB.Columns(5).Cells Set rngAcq = rngDB.Columns(4).Cells Set rngStock = rngDB.Columns(3).Cells instock = WorksheetFunction.Sum(rngStock) OutAmount = Application.InputBox("Enter OUT amount", "Stock out", , , , , 1) rngDB.Sort key1:=rngAcq.Cells(1), header:=xlGuess StockAccum = 0 x = 2 Do While StockAccum < OutAmount StockAccum = rngStock.Cells(x).Value + StockAccum If StockAccum < OutAmount Then rngStock.Cells(x).Value = 0 x = x + 1 ElseIf StockAccum = OutAmount Then rngStock.Cells(x).Value = 0 ElseIf StockAccum OutAmount Then rngStock.Cells(x).Value = StockAccum - OutAmount End If Loop rngDB.Sort key1:=rngLot.Cells(1), header:=xlGuess End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something similar to FIFO method
the new code will change the quantity on the spreadsheet to remove the items
needed QuantyRequired = 12500 Rowcount = 1 Do while QuantyRequired 0 Number = Range("C" & Rowcount) if Number QuantyRequired then Range("C" & Rowcount) = Range("C" & Rowcount) - QuantyRequired QuantyRequired = 0 Else QuantyRequired = QuantyRequired - Number Range("C" & Rowcount) = 0 end if Rowcount = Rowcount + 1 loop "Bogdan" wrote: Hi Joel, Many thanks for your answer. It is helpful, I started to build a code around it, but I'm again stuck :) because the code you sent works fine only in case of one quantity out. Using the same example, let's assume that beside 12,500 units I'll have another 30,000 units out on Nov 30. Of course, this quantity should be taken out from the BALANCE of Lot 2 (which is now 73,000 units). What should be added to your initial code in order to be able to deduct these 30,000 units from 73,000? Many thank in advance again, Bogdan "Joel" wrote: I t is hard to wriote a script the way you posted your request. I assume the stock items and the orders are on seperate worksheets and what you posted is the results of the scripts. I would need to know the sheet names and the location of the stock items and the where the orders are posted. the algorithm is simple if you sort the stock items by "Acquisition of units". QuantyRequired = 12500 Rowcount = 1 Do while QuantyRequired 0 Number = Range("C" & Rowcount) if Number QuantyRequired then QuantyRequired = 0 Else QuantyRequired = QuantyRequired - Number end if Rowcount = Rowcount + 1 loop "Bogdan" wrote: Hi everyone, I would like to create a script similar with FIFO (first-in first out) method, but instead of taking out the oldest item from stock, it should take out the stock with the smallest (minimum) acquisition price. For instance if I have: Type Date Number Acquisition Lot of units price per unit IN 29-Oct-08 3,000 7.8500 1 IN 30-Oct-08 75,000 7.9000 2 IN 06-Nov-08 7,500 7.8000 3 OUT 25-Nov-08 12,500 8.1027 On 25th of November the quantity of 12,500 units should be taken out in the following order: - first 7,500 units from Lot 3, as this lot has the smallest acquisition price - the following 3,000 units from Lot 1, as this lot has the minimum available acquisition price - the difference of 2,000 unit from lot 2 I hope I was pretty concise in my explanantions. Can anyone help me? Many thanks in advance, Bogdan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can we run FIFO Inventory in excel? | Excel Worksheet Functions | |||
MATCH AMOUNT FIFO METHOD & INSERT ROWS WHERE EVER REQUIRED. | Excel Programming | |||
FIFO: Share calculation in VBA? | Excel Programming | |||
FIFO method calculations | Excel Programming | |||
FIFO Inventory tracking | Excel Programming |