Question

Design a spreadsheet that can be used to evaluate aggregate plans. The spreadsheet should enable the evaluation of:

1.pure strategy based on leveling production,

2.pure strategy based on minimizing inventory by laying off/hiring as needed,

3.a mix of the above two.

The spreadsheet should have the following headings:

1	2	3	4	5	6	7	8	9	10	11
		Sales in		Production
Month	Sales	labor-hour	Working	labor-hour	Number of	Workers	Workers	Beginning	Ending	Backorder
	(Million)	(000s)	days	(000s)	Workers	Hired	Fired	Inventory	Inventory

All but columns 1, 2, and 4 should be computed using cell formulas. Column 5 should allow for using cell formulas in the case of level production or direct input for mixed plans. The cost of any plan, in total and for each cost component, should be computed without additional effort. Assume an 8-month horizon and use the following data to test your spreadsheet:

Sales per direct labor hour	$30
Hiring cost	$ 300.0
Firing cost	$ 500.0
Regular labor cost per hour	$ 15.00
Overtime premium cost per hour	$ 7.50
Backorder cost per month	2%
Inventory carrying cost per month	1%
Beginning inventory	$ 400000
Desired ending inventory in September	$ 200000
Number of Employees as of end of December	1900

Month	Sales	Working
	(Million)	days
January	8.0	20
February	7.4	21
March	11.2	23
April	11.0	20
May	9.8	22
June	8.0	22
July	8.0	10
August	10.6	23
September	9.4	20

I have solved some of it but I got Stuck in Ending Inventory. Thank you for your help.

J4 10 Workers Workers Beginning Endin order Balance ($ 000s) Sales in Production Month Sales (million) Working Days labor-hour (000s) Inventory Inventory ($ 000s) ($000s) labor-hour (000s) Hired 3 December 4 January 5 February 6 March 7 April 8 May 9 June 10 July 11 August 12 September 13 14 Total 15 Unit Cost Workers 1900 1916 1916 1916 1916 1916 1916 1916 1916 1916 400.0 266.67 246.67 373.33 366.67 326.67 266.67 266.67 353.33 313.33 20 21 23 20 8.0 306.56 321.888 352.544 306.56 337.216 337.216 153.28 352.544 306.56 16 0 0 1596.80 0 2256.64 0-623.68 11.2 11.0 9.8 8.0 8.0 10.6 9.4 0 0 316.48 0 2116.48 10 23 20 0 023.68 0 200000 83.4 2780 181 2774.368 17244 16 400200000 15 0.3 0.5 0.01 2.0% Total Cost

Answer 1

The ending inventory is returned by the Excel Solver using the following spreadsheet models.

1. Pure strategy based on level production

Formulas:

2. Pure strategy based on minimizing inventory by laying off/hiring as needed

In this model, we have changed the objective from minimizing total cost to minimizing total ending inventory. Resulting solution is following:

3. Mix strategy