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" wrote in message
...
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