UDF weeks of inventory calculation - lost on how to create this lo

[zeroscout]

Hello all, I am trying to create a function to calculate the weeks of
inventory based on future sales, actuals for historical and forecast for
future.

I can create a row in the excel report that will show actuals through the
latest reported week and forecast, so the function can use the same row of
data for sales.

My goal is to create a true weeks of inventory calculation, not based on the
inventory divided by the average sales over a time period, but how many weeks
it would take to deplete the inventory reported based on actual sales or
forecast.

For the first product, the inventory is in row 21, first weeks reported
starting in column G, and the sales data is in row 17.

If the inventory of the first week to calculate is in G21, then I would want
to begin the weeks of inventory calculation using the sales starting in the
following week, cell H17

My thought of how this would work would be a loop that counts how many weeks
the sum of 100% of those weeks sales is less than the inventory.

Example would be in week of 12/30/07, the inventory reported was 4,573. The
sales in the next week, 1/6/08 = 548, 1/13/08 = 407, 1/20/08 = 503, 1/27/08 =
617, 2/3/08 = 575, 2/10/08 = 444, 2/17/08 = 515, 2/24/08 = 478, 3/2/08 = 483.

There fore, if the loop was functioning as I envision it, the count of the
loop would be 9, and since these weeks only add up to 4,570, there would be a
balance of 3 left to decrement from the inventory.

The next step in the function would take the balance and divide it into the
sales of week 3/9/08, which are 250. This is equal to 0.012.

At the end of this step, the function would add to loop count and the last
step together to calculate the actual weeks of inventory to be 9.012.

Thank you for taking the time to read the narrative above. Here is the
function I have written so far. I am confident that if I was aided in the
first step of the loop, I could complete the function. Any and all help is
greatly appreciated.

Public Function WKSINVsim02(ByVal invST As Single) As Single
Dim invLP As Integer, invINC As Single, posCL As Integer

''invST is starting inventory quantity
''invLP is counter
''invINC is inventory loop quantity
''posCL is pos cell in loop

invLP = 0
invINC = 0
posCL = invST.Offset(-4, 0).Value

Do While invINC < invST
invINC = invINC + posCL
invLP = invLP + 1
posCL = cell.posCL.Offset(0, 1).Value
Loop

WKSINVsim02 = invLP

End Function

 

[Patrick Molloy]

pass into this function just the column of sales (548,407,503,617,575,444
....) and the stock amount (4573)

Function Inventory(table As Range, stock As Long)
Dim cell As Range
Dim weeks As Double
Dim total As Long
For Each cell In table.Cells
If (total + cell.Value) <= stock Then
weeks = weeks + 1
total = total + cell.Value
Else
weeks = weeks + (stock - total) / cell.Value
Exit For
End If
Next
Inventory = weeks
End Function

 

[zeroscout]

Hello Patrick,
Thank you, this works very well! I have tested the function with an offset
formula in the report and it works awesome. This will get me through the
current report.

Now I will see if I am able to engineer in a programmatic solution for the
table variable and I will post back my efforts.

Thank you again, this function is a huge help,
Brian

 

[Patrick Molloy]

thanks - we all appreciate feedback - especially the good ones