Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks of Supply Calculation
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" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks of Supply Calculation
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" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks of Supply Calculation
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. "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" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks of Supply Calculation
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" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeks of Supply Calculation
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. "widman" wrote: 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. "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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not sure which function and how to use it. Help | Excel Worksheet Functions | |||
Calculate weeks cover | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |