R
Rob Williamson PSU
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
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