Something similar to FIFO method

[Bogdan]

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

 

[Joel]

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]

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

 

[Phillip]

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 instanceif
I have:

 Type     Date    Number       Acquisition          Lot
                                   ofunits      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

 

[Joel]

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