Weeks of Supply Calculation

[Langrbj]

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"

 

[paul]

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?.

 

[widman]

I have one I've used for the last 14 years (10 in the current company) where
I track about 280 sales items. It contains a running history of 6 weeks of
sales per item, turns them into average for the 6 weeks, gives me an override
if I think it will increase in the coming weeks, allows for an overall %
increase and an overall number of days of safety stock, tells me how many
days I have in stock if my projection is correct, then tells me the day each
product will run out at that rate, then whether it is in "critical" status or
not, based on shipping times.
It then goes on to proyect the coming 8 weeks ending inventories, and a
column that tells me how many days I will have of each at that time.
Then it give me a suggested order to balance it all out so that I would, in
a perfect world, run out of everything on the same day, based on the number
of weeks I insert at the top, showing me what the weight of the total order
would be. Since I have to order in 45,000 lb lots (or 90,000 or 135,000,
etc. I then play with the weeks and round manually to configure the load and
type in the products arriving on the week I can get them there (usually 7 to
8 weeks lead time).
(there is actually a summary page where 5 warehouses are combined, with
totals, total on order, value of inventory, etc)
Each week I press the macro button that drops off the oldest week and moves
everything over one week. then i use a vlookup to bring in the current
inventory, copy, paste values of the difference from the previous week and
insert into the current week.

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!

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"

 

[Langrbj]

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.

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!

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"

 

[Langrbj]

widman, Sounds like an impressive sheet, but a bit more than I'm looking for.
All I'm looking for is a true WOS calculation without averaging. I'd like
to think that somebody somewhere has written a custom VBA function to
calculate a true WOS.

I have one I've used for the last 14 years (10 in the current company) where
I track about 280 sales items. It contains a running history of 6 weeks of
sales per item, turns them into average for the 6 weeks, gives me an override
if I think it will increase in the coming weeks, allows for an overall %
increase and an overall number of days of safety stock, tells me how many
days I have in stock if my projection is correct, then tells me the day each
product will run out at that rate, then whether it is in "critical" status or
not, based on shipping times.
It then goes on to proyect the coming 8 weeks ending inventories, and a
column that tells me how many days I will have of each at that time.
Then it give me a suggested order to balance it all out so that I would, in
a perfect world, run out of everything on the same day, based on the number
of weeks I insert at the top, showing me what the weight of the total order
would be. Since I have to order in 45,000 lb lots (or 90,000 or 135,000,
etc. I then play with the weeks and round manually to configure the load and
type in the products arriving on the week I can get them there (usually 7 to
8 weeks lead time).
(there is actually a summary page where 5 warehouses are combined, with
totals, total on order, value of inventory, etc)
Each week I press the macro button that drops off the oldest week and moves
everything over one week. then i use a vlookup to bring in the current
inventory, copy, paste values of the difference from the previous week and
insert into the current week.

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!

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"

 

[Langrbj]

Well, I came up with the answer myself! I'm sure a professional programmer
can come up with more elegant code, but this function works. Ic can be
copied into a module of any spreadshee using the VBA editor. I'll leave this
post in case anyone else searching for a WOS function can make use of this.

Function WOS(inv, fships)
'Calculates True WOS with inventory and future ships as arguments
'inv is a single cell representing the current week's ending inventory
'fships is the range of future ships listed in 1 row and two or
' more columns
'if the sum of future ships won't deplete the current week's ending
' inventory the future ships are used to calculate an average
' demand and a WOS is estimated
'Data should be structured like this:
' Wk1 Wk2 Wk3 Wk4 Wk5
'Production 10 5 3 2 1
'Shipments 0 5 5 3 2
'End Inv 10 10 8 7 6
'WOS 2.0 3.0 3.2 3.5
tot = Application.Sum(fships)
n = fships.Columns.Count
For i = 1 To n - 1
tot1 = Application.Sum(fships.Resize(1, i))
tot2 = Application.Sum(fships.Resize(1, i + 1))
If inv <= tot1 Then
WOS1 = i - 1 + (inv / tot1)
ElseIf inv > tot1 And inv <= tot2 Then
WOS1 = i + ((inv - tot1) / (tot2 - tot1))
Exit For
End If
Next
If WOS1 = 0 Then
WOS = inv / (tot / n)
Else
WOS = WOS1
End If
End Function