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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF weeks of inventory calculation - lost on how to create this lo
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF weeks of inventory calculation - lost on how to create this lo
OK, I'm going to talk a bit about how I see what you are trying to do. You have a cell with the actual inventory in it and below that cell (maybe right up against it, maybe with some rows with blanks or text or whatever) you have a series of rows with actual weekly sales of the item. All these numbers (inventory and past sales) are in the same column. Let's plan on there being some rows between the inventory value and the first of the weekly sales because that's the more general case and if you can get this to work with those rows, you can get it to work without those 'extra' rows. This means that your function will have to have the location of the cell with the inventory in it and the cell at the top of the set of weekly sales, so the function will have to be passed these two locations, as ranges. So far the function looks like Public Function WOI(rInv As Range, rWks As Range) As Double End Function where WOI stands for Weeks Of Inventory, rInv is the cell (say J3) that holds the inventory and rWkS is the cell at the top of the Weekly Sales (say J7) and the value that needs to be returned is a single or double - I'll go with a double. You were onto something with the idea of using Offsets, but Offsets need to be from Ranges, not variables like your invST (an integer, I believe). If we're going to be looping, we are probably going to need a counter or index variable. If we are going to be keeping a running total of weekly sales, we probably need something to store that in and we may need something to hold the value of the inventory. Let's Dim a few things in the function and do some initializations Public Function WOI(rInv As Range, rWks As Range) As Double Dim dSum As Double Dim dOldSum As Double Dim dInv As Double Dim lL As Long lL = 0 dInv = rInv.Value dSum = rWks.Offset(lL, 0).Value End Function You'll notice that dInv now holds the value of the cell where the inventory is and dSum holds the first of the weekly sales, the value of the cell at the top of the weekly sales. Yes, the value of dSum could have been determined by dSum = rWks.Value but the way it was defined above is a foreshadowing of how we will be getting weekly sales values once inside the while/wend loop - so let's add that loop now: Public Function WOI(rInv As Range, rWks As Range) As Double Dim dSum As Double Dim dOldSum As Double Dim dInv As Double Dim lL As Long lL = 0 dInv = rInv.Value dSum = rWks.Offset(lL, 0).Value 'You will stay in this loop until dSum includes one week too many, so ' save the old sum in dOldSum While dInv dSum dOldSum = dSum lL = lL + 1 dSum = dSum + rWks.Offset(lL, 0).Value Wend End Function Let's talk through what's going on in the loop. While the sum of all weeks sales is less than the inventory, the code inside the loop will execute. First, the previous value of dSum is cached in dOldSum, then lL is incremented and finally, the next weekly sales value is added to the previous dSum. As the comment says, this loop will run until it has added in the statement dSum = dSum + rWks.Offset(lL, 0).Value one too many of the weekly sales values. Therefore, we need to use dOldSum (the last sum of the weekly sales which was less than the inventory) to calculate what fraction of that last-step-too-far weekly sales value is needed to bring dOldSum just up to dInv. And here's that in the function: Public Function WOI(rInv As Range, rWks As Range) As Double Dim dSum As Double Dim dOldSum As Double Dim dInv As Double Dim lL As Long lL = 0 dInv = rInv.Value dSum = rWks.Offset(lL, 0).Value 'You will stay in this loop until dSum includes one week too many, so ' save the old sum in dOldSum While dInv dSum dOldSum = dSum lL = lL + 1 dSum = dSum + rWks.Offset(lL, 0).Value Wend WOI = CDbl(lL) + (dInv - dOldSum) / rWks.Offset(lL, 0).Value End Function Now there are a few things that need to be tested (I did do a simple test, just to check my logic) and potential problems to be thought through: 1) What does the code do if the first weekly sales happens to be more than the inventory - does the value get calculated correctly? 2) What happens if the loop reaches the bottom of the weekly sales and dSum is still less than dInv? This one is going to take more thought, especially if it's possible that there were no sales during a week and the person who enters the sales decides it's OK to just leave the cell blank for that week. Since you know more about your specific application than I do, the ball is in your court for this, but one possible hint is to check if the value of the weekly inventory is "". 3) Look up Application.Volatile in the help files and decide how you want the function to recalculate and add, if you decide it's appropriate a line Application.Volatile = True after the Dim statements Finally, you will note that in your code, you looked at the loop criterion differently than I did - you were, in effect, checking if dSum was less than dInv. Doing it that way would affect the code in the function above - just for grins, go ahead and modify it to use that inequality test, and think through the ramifications - you may find things get simpler if you do. To use this UDF, go to a cell of your choice, type in =woi( then click on the cell with the inventory in it, say J3, they type a comma =woi(J3, then click on the cell at the top of the weekly sales values, say J7, then close the parentheses =woi(J3,J7) and you should get your answer. Hope this has helped... :Bgr --- Automerged consecutive post before response --- :Bgr Or, you could just use Patrick Molloy's solution - he got in, worked it out and posted it while I was still typing mine... -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110734 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF weeks of inventory calculation - lost on how to create thi
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF weeks of inventory calculation - lost on how to create thi
thanks - we all appreciate feedback - especially the good ones :)
"zeroscout" wrote in message ... 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" wrote: 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 |
Reply |
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) |