Months of supply

[Steve]

I need to calculate months of supply. Supplied data is on-hand inventory and
a use forecast. For example:

Inventory 30
Use: Mo1 Mo2 Mo3 Mo4 ...
10 15 10 10
Answer: 2.5

The forecast may "use" more inventory than currently on hand since it may be
replenshied, but that is not part of this analysis.
Thanks

 

[FSt1]

hi
months supply are usually based on averge usage. where i work, we use averge
days use(ADU) so that we can calculate a weeks supply(times 7), a months
supply(time 30), ect.
based on your data....
4 months usage = 45
45/ 4 months = .375 per day
..375 * 7 days = 2.625 per week
on hand = 30 or 30/2.625 =11.43 weeks supply
..3.75 *30 days = 11.25 per month
on hand = 30 or 30/11.25 =2.66 months supply

months (or weeks) supply are usually calculated to determine re-order
points based on lead times and amount of safty stock you wish to keep on hand.
30 lead time = you ok.
60 lead time = your are approching your re-order window
90 lead time = you're in trouble.

it might be better to base your ADU on past useage rather than forcast
becasue past usage history usually goes back further than forcast goes out
and compare that to forcast.
the ADU should be a moving average based on data going back from now to what
ever time period you feel comfortable with ie 6 moths , a year ect. and
recalculated periodically.
both past usage and forcast may have increasing/decreasing trends so then
you may need to calculate the average monthly increase/decrease so that you
can adjust your ADU.

regards
FSt1
member APICS

 

[seed]

[cell address containing inventory number] / average([range containing use
numbers])

I think that's what you're going for, though the answer would be 2.67

Ryan

 

[Steve]

Thanks, but an average won't work. I need to use a countif (I think) with a
criteria that I cannot quite get my arms around.

[cell address containing inventory number] / average([range containing use
numbers])

I think that's what you're going for, though the answer would be 2.67

Ryan

I need to calculate months of supply. Supplied data is on-hand inventory and
a use forecast. For example:

Inventory 30
Use: Mo1 Mo2 Mo3 Mo4 ...
10 15 10 10
Answer: 2.5

The forecast may "use" more inventory than currently on hand since it may be
replenshied, but that is not part of this analysis.
Thanks

 

[FSt1]

correction.

both past usage and forcast may have increasing/decreasing trends so then
you may need to calculate the average monthly increase/decrease so that you
can adjust your ADU.

should be ...
calculate the average monthly increase/decrease so that you can adjust your
re-order quantity.

regards
FSt1

 

[Steve]

FS,
I need the use-up period based on the forecast; the future is not looking
anything like the past if you know what I mean. Thanks