Paul, The example is fabricated to illustrate the WOS function I'm looking
for. Typically we are given a sales forecast 26 weeks out (next 6 months)
and the production numbers are entered manually based on keeping inventory in
a 4 to 6 WOS range.

"paul" wrote:

a few questions first.Is this example a real time example or is it
historical?How far in advance do you forecast out,how is the production
number forecast/input?.
--
paul
remove nospam for email addy!



"Langrbj" wrote:

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"