Has anyone come across a formula that can calculate the weeks of supply (WOS)
for a production scheduling application? For the data:

Week 1 2 3 4 5
Production 5 3 1 1 1
Sales 0 2 3 2 1
Inventory 5 6 4 3 3
WOS 2 3 99 99 99

The trick is to develop a formula that takes the week 1 ending inventory of
5 and then subtracts sales of 2 in week 2, then subtracts sales of 3 in week
3, and upon realizing that at week 3 the inventory is <=0 you take [week] 3
minus [week] 1 to get 2 weeks of supply. The same calculation repeats for
week 2. For week three 99 is indicated to show the future sales listed won't
deplete the inventory.

Does anyone know of a way to calculate this without averaging the sales or
setting up an akward cumulative calculation for each week and using the match
and index functions?

This weeks of supply calculation has a number of applications. I suprised
there isn't a standard function in Excel with functionality along the lines
of "this value will reach zero in x periods with the irregular series of
withdrawls in range b1..m1"