Z

#### zeroscout

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