Given the following demand data use excel solver to design

Question

Sawyer Furniture is one of the few remaining domestic manufacturers of wood furniture. In the current competitive environment, cost containment is the key to its continued survival. Demand for furniture follows a seasonal demand pattern with increased sales in the summer and fall months, culminating with peak demand in November.

The cost of production is $16 per unit for regular production, $24 for overtime, and $33 for subcontracting. Hiring and firing costs are $500 per worker. Inventory holding costs are $20 per unit per month. There is no beginning inventory. Ten workers are currently employed. Each worker can produce 50 pieces of furniture per month. Overtime cannot exceed regular production. Given the following demand data, use Excel Solver to design an aggregate production plan for Sawyer Furniture that will meet demand at the lowest possible cost.

Input:		Beg. Wkrs		10			Regular		$16		Hiring		$500
		Units/wkr		50			Overtime		$24		Firing		$500
		Beg. Inv.		0			Subk		$33		Inventory		$20
Month	Demand		Reg		OT	Subk		Inv		#Wkrs		#Hired		#Fired
Jan	500		500		0	0		0		10		0		0
Feb	500		500		0	0		0		10		0		0
Mar	1000		1,000		0	0		0		20		10		0
Apr	1200		1,000		200	0		0		20		0		0
May	2000		1,000		1,000	0		0		20		0		0
Jun	400		400		0	0		0		8		0		12
Jul	400		400		0	0		0		8		0		0
Aug	1000		1,000		0	0		0		20		12		0
Sep	1000		1,000		0	0		0		20		0		0
Oct	1500		1,500		0	0		0		30		10		0
Nov	7000		3,500		3,500	0		0		70		40		0
Dec	500		500		0	0		0		10		0		60
Total	17,000		12,300		4,700	0		0		246		72		72

Answer 1

By using the demand data information illustrated in the problem and by using the excel sheet, respond to the following case. Sawyer Furniture is one of some remaining domestic manufacturers of the wood furniture. In present competitive atmosphere, cost containment is the key to its continued survival. Demand for furniture follows a seasonal demand pattern by means of increased sales in the summer and fall months, concluding with peak demand in November. The cost of production is $16 per unit for the regular production, $24 for overtime and $33 for sub-contracting. Hiring and firing costs are $500 per worker. The inventory holding costs are $20 per unit per month. There is no starting inventory. 10 workers are presently employed. Each and every worker can make 50 pieces of furniture per month. Overtime can’t go beyond regular production. Provide the given demand data, to design aggregate production plan for the Sawyer Furniture which will meet demand at lowest cost.