|
Excel Count If Rolling Sum
I am trying to set up an equation in Excel to calculate how many weeks of a
given number of inventory will last assuming a forward looking demand. Most people would say to just take the inventory number divided by the average weekly forecast to get the number. But since I need it in a time phased format and the demand can be highly variable from week ot week, this is not a good way of doing it. I've tried Dsum, Dcount, countif, and all sorts of other nested if functions but cannot get it quite right... Can someone who is smarter than me help?!? Below is an example (I wish I could just attach a file!). Week 10/4 10/11 10/18 10/25 11/1 11/8 11/15 11/22 11/29 12/6 12/13 12/20 12/27 Beg. Inv. 100 98 48 48 48 - 497 492 392 242 241 236 228 Supply - - - - - 500 - - - - - - - Demand 2 50 - - 150 3 5 100 150 1 5 8 100 Unfulfilled Demand - - - - (102) - - - - - - - - End. Inv. 98 48 48 48 - 497 492 392 242 241 236 228 128 WOS 2.2 1.1 1.1 1.1 - 11.3 11.1 8.9 5.5 5.5 5.3 5.2 2.9 |
Excel Count If Rolling Sum
Since your demand is so varied, how would you determine future demand?
average of past 3 demands? assume latest? No way to plan for an uncertain future. :-) "Rob Williamson PSU" wrote: I am trying to set up an equation in Excel to calculate how many weeks of a given number of inventory will last assuming a forward looking demand. Most people would say to just take the inventory number divided by the average weekly forecast to get the number. But since I need it in a time phased format and the demand can be highly variable from week ot week, this is not a good way of doing it. I've tried Dsum, Dcount, countif, and all sorts of other nested if functions but cannot get it quite right... Can someone who is smarter than me help?!? Below is an example (I wish I could just attach a file!). Week 10/4 10/11 10/18 10/25 11/1 11/8 11/15 11/22 11/29 12/6 12/13 12/20 12/27 Beg. Inv. 100 98 48 48 48 - 497 492 392 242 241 236 228 Supply - - - - - 500 - - - - - - - Demand 2 50 - - 150 3 5 100 150 1 5 8 100 Unfulfilled Demand - - - - (102) - - - - - - - - End. Inv. 98 48 48 48 - 497 492 392 242 241 236 228 128 WOS 2.2 1.1 1.1 1.1 - 11.3 11.1 8.9 5.5 5.5 5.3 5.2 2.9 |
Excel Count If Rolling Sum
True. And our inventory / service level shows it!
But seriously, we forecast based on seasonality of historical demand as well as recent mean value... Essentially, I just want an equation that will tell me how many columns (weeks) until the assumed "ending inventory" of a fiven column (week) will run out... Can you help? "Sean Timmons" wrote: Since your demand is so varied, how would you determine future demand? average of past 3 demands? assume latest? No way to plan for an uncertain future. :-) "Rob Williamson PSU" wrote: I am trying to set up an equation in Excel to calculate how many weeks of a given number of inventory will last assuming a forward looking demand. Most people would say to just take the inventory number divided by the average weekly forecast to get the number. But since I need it in a time phased format and the demand can be highly variable from week ot week, this is not a good way of doing it. I've tried Dsum, Dcount, countif, and all sorts of other nested if functions but cannot get it quite right... Can someone who is smarter than me help?!? Below is an example (I wish I could just attach a file!). Week 10/4 10/11 10/18 10/25 11/1 11/8 11/15 11/22 11/29 12/6 12/13 12/20 12/27 Beg. Inv. 100 98 48 48 48 - 497 492 392 242 241 236 228 Supply - - - - - 500 - - - - - - - Demand 2 50 - - 150 3 5 100 150 1 5 8 100 Unfulfilled Demand - - - - (102) - - - - - - - - End. Inv. 98 48 48 48 - 497 492 392 242 241 236 228 128 WOS 2.2 1.1 1.1 1.1 - 11.3 11.1 8.9 5.5 5.5 5.3 5.2 2.9 |
| All times are GMT +1. The time now is 11:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com