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

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

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

Can you explain how you've arrived at your results. A few examples
would help.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Rob Williamson PSU <Rob Williamson
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