Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF weeks of inventory calculation - lost on how to create this lo
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weeks of Supply Calculation | Excel Worksheet Functions | |||
Create Calculation to return number of weeks and days | Excel Worksheet Functions | |||
Impact of negative sales on inventory weeks on hand | Excel Discussion (Misc queries) | |||
return calculation every four weeks | Excel Discussion (Misc queries) | |||
Weeks of Supply Calculation | Excel Discussion (Misc queries) |