Question

Solve in excell by using:
1) Chase strategy (zero inventory)
2) Level strategy (constant work force)

********** PLEASE PROVIDE ALL FORMULAS USED IN EXCELL ******************

Densepack is to plan workforce and production levels for the six-month period January to June. The firm produces a line of disk drives for mainframe computers that are plug compatible with several computers produced by major manufacturers. Forecast demands over the next six months for a particular line of drives produced in the Milpitas, California, plant are 1,280, 640, 900, 1,200, 2,000, and 1.400. There are currently ſend of December) 300 workers employed in the Milpitas plant. Ending inventory in December is expected to be 500 units, and the firm would like to have 600 units on hand at the end of June.

Answer 1

1	Chase Strategy (Zero Inventory)
		January	Feb	March	April	May	June	Total
	Opening Inventory	0	0	0	0	0	0	0
	No of units produced	780	640	900	1200	2000	1400	6920
	Net Predicted demand	780	640	900	1200	2000	1400	6920
	Closing Inventory	0	0	0	0	0	0	0
	No of units produce and sale	780	640	900	1200	2000	1400	6920
	Working Days per Month	20	24	18	26	22	15	125
	No of units produced per one day per worker	0.14653	0.14653	0.14653	0.14653	0.14653	0.14653
	No of units can produced per Month per worker (No of units can produced per one day *Working days per Month)	2.9306	3.51672	2.63754	3.80978	3.22366	2.19795
	No of workers required (No of units produced /No of units produced per month per worker)	266.1571009	181.9877613	341.2270525	314.9788177	620.4128227	636.9571646
	No of workers required	267	182	342	314	621	637
	Hiring Cost (No of worker*$500)	$133500	$91000	$171000	$157000	$310500	$318500
	Holding Cost(Closing Inventory*$80)	0	0	0	0	0	0
	Total Cost (Hiring cost+Holding cost)	$133500	$91000	$171000	$157000	$310500	$318500	$ 11,81,500
	As per Zero Inventory syatem Opening Inventory will not considered in each month and Closing Inventory should be 0)
	whatever demand is there that extent only we have to produce.But In Jan month production is 1280 units demand is 780 units so excess produced 500,have to produce in jan only 780 units
	In June Month production is 1400 units demand is 2000 units so we can sell only 1400 units
2	Leval Strategy (Constant work force)
		January	Feb	March	April	May	June	Total
	Opening Inventory	500	1000	1000	1000	1000	1000	5500
	No of units produced	1280	640	900	1200	2000	1400	7420
	Net Predicted demand	780	640	900	1200	2000	1800	7320
	Closing Inventory	1000	1000	1000	1000	1000	600	5600
	No of units produced	1280	640	900	1200	2000	1400	7420
	Working Days per Month	20	24	18	26	22	15	125
	No of units produced per one day per worker	0.14653	0.14653	0.14653	0.14653	0.14653	0.14653
	No of units can produced per Month per worker (No of units can produced per one day *Working days per Month)	2.9306	3.51672	2.63754	3.80978	3.22366	2.19795
	No of workers required (No of units produced /No of units produced per month per worker)	436.7706272	181.9877613	341.2270525	314.9788177	620.4128227	636.9571646
	No of workers required	437	182	342	315	621	637
	Hiring Cost (No of worker*$500)	$218500	$91000	$171000	$157000	$310500	$318500
	Holding Cost(Closing Inventory*$80)	$80000	$80000	$80000	$80000	$80000	$48000
	Total Cost (Hiring cost+Holding cost)	$     2,98,500	$     1,71,000	$     2,51,000	$ 2,37,000.00	$     3,90,500	$     3,66,500	$ 17,14,500
	Closing Inventory = Opening Inventory+No of units produced -Demand
	Opening Inventory of January= Closing Inventory of December
	Closing Inventory of January= Opening Inventory of Febraury like that remaing months
	As per given probleam firm would like to have 600 units on hand at the end of june but as per above caluculation closing inventory at the end of the month is 400,so we can sell 1800 units in the month of june or we can produce 1600 units ,so we can maintain 600 units Closing Inventory in the month june