Excel Count If Rolling Sum

  • Thread starter Rob Williamson PSU
  • Start date
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
 
S

Sean Timmons

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. :)
 
R

Rob Williamson PSU

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top