Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
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 "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
[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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
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. "seed" wrote: [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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
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 "FSt1" wrote: 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 "Steve" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Months of supply
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 "FSt1" wrote: 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 "Steve" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inventory Months Supply Question | Excel Worksheet Functions | |||
Supply and Demand Charting | Charts and Charting in Excel | |||
Autofilter - Supply Criteria | Excel Discussion (Misc queries) | |||
I need a template for my supply inventory | Excel Worksheet Functions | |||
supply requisition | Excel Discussion (Misc queries) |