B
Bruce
I am setting up a spreadsheet to determine the weeks of supply. The column on
the worksheet are months.
The rows are Sales, Production and Inventory where Inventory is calculated
as Closing inventory of last month + Production - Sales.
To work out weeks of supply I am currently assuming next months (demand /
Colsing inventroy) * 4.3. The flaw in this is it assumes that the demand is
constant over time.
Given the Data below where months to weeks is a constant = 4.3
Month: Jun July Aug Sep
Sales: n/a 200 400 100
Production: n/a 500 500 500
Inventory: 500 800 900 1300
WeeksSupply: n/a 10.7 8.6 38.7
The flaw is in the demand part of the calculation. Basically is should sum
the Sales Row until it is > than the Inventory.
How do I determine this range needs to be for the SUM? I can tthink of how
to do it with formula. Maybe it could be done with a user function..
Any ideas?
Bruce?
the worksheet are months.
The rows are Sales, Production and Inventory where Inventory is calculated
as Closing inventory of last month + Production - Sales.
To work out weeks of supply I am currently assuming next months (demand /
Colsing inventroy) * 4.3. The flaw in this is it assumes that the demand is
constant over time.
Given the Data below where months to weeks is a constant = 4.3
Month: Jun July Aug Sep
Sales: n/a 200 400 100
Production: n/a 500 500 500
Inventory: 500 800 900 1300
WeeksSupply: n/a 10.7 8.6 38.7
The flaw is in the demand part of the calculation. Basically is should sum
the Sales Row until it is > than the Inventory.
How do I determine this range needs to be for the SUM? I can tthink of how
to do it with formula. Maybe it could be done with a user function..
Any ideas?
Bruce?