Question

A manufacturer of integrated circuits is planning production for the next four months. The forecast demand for the circuits is shown in the following table.

Circuit	September	October	November	December
IC341	650	875	790	1100
IC256	900	350	1200	1300

At the beginning of September, the warehouse is expected to be completely empty. There is room for no more than 1,800 integrated circuits to be stored. Holding costs for both types is $0.05 per unit per month. Because workers are given time off during the holidays, the manufacturer wants to have at least 800 IC341s and 850 IC256s already in the warehouse at the beginning of January.

Production costs are $1.25 per unit for IC341 and $1.35 per unit for IC256. Because demand for raw materials is rising, production costs are expected to rise by $0.05 per month through the end of the year. Labor to make model IC341 is 0.45 hours per unit; making model IC256 takes 0.52 hours of labor. Management has agreed to schedule at least 1,000 hours per month of labor. As many as 200 extra hours per month are available to management at the same cost, except during the month of December, when only 100 extra hours are possible. What should be the production schedule for IC341 and IC256 for the four months? What recommendations would you give the manufacturer based on your solution?

-Caluclated on Excel with formulas, Show step by step,

Answer 1

The boxes in green, are decision variables
The boxes in blue are constrains
The boxes in orange is the objective function

The formulae used:

	Demand Circuit	SEPT.	OCT.	NOV.	DEC.
	IC341	650	875	790	1100
	IC256	900	350	1200	1300	All inv to be positive
	Opening Inv 341	0	=C15	=D15	=E15	>=	0
	Opening Inv 256	0	=C16	=D16	=E16	>=	0
		=SUM(C4:C5)	=SUM(D4:D5)	=SUM(E4:E5)	=SUM(F4:F5)	Warehouse constraint
		<=	<=	<=	<=
		1800	1800	1800	1800
	Production cost 341	1.25	=C9+0.05	=D9+0.05	=E9+0.05
	Production cost 256	1.35	=C10+0.05	=D10+0.05	=E10+0.05
	Labor hours 341	0.45	0.45	0.45	0.45
	Labor hours 256	0.52	0.52	0.52	0.52
Decision var	Production units 341	1521	826	768	1100
	Production units 256	1249	1	2200	1150	Closing inv constraint
	Closing inventory 341	=C13+C4-C2	=D13+D4-D2	=E13+E4-E2	=F13+F4-F2	>=	800
	Closing inventory 256	=C14+C5-C3	=D14+D5-D3	=E14+E5-E3	=F14+F5-F3	>=	850
	Prod time	=SUMPRODUCT(C11:C12,C13:C14)	=SUMPRODUCT(D11:D12,D13:D14)	=SUMPRODUCT(E11:E12,E13:E14)	=SUMPRODUCT(F11:F12,F13:F14)	Prod time constraint
		<=	<=	<=	<=
		1200	1200	1200	1100
0.05	Holding cost	=SUM(C15:C16)*$A$20	=SUM(D15:D16)*$A$20	=SUM(E15:E16)*$A$20	=SUM(F15:F16)*$A$20
	Production cost	=SUMPRODUCT(C9:C10,C13:C14)	=SUMPRODUCT(D9:D10,D13:D14)	=SUMPRODUCT(E9:E10,E13:E14)	=SUMPRODUCT(F9:F10,F13:F14)
	Total cost	=SUM(C20:F21)
	Obj func	Min

On solving:

The solution:

	Demand Circuit	SEPT.	OCT.	NOV.	DEC.
	IC341	650	875	790	1100
	IC256	900	350	1200	1300	All inv to be positive
	Opening Inv 341	0	871	822	800	>=	0
	Opening Inv 256	0	349	0	1000	>=	0
		0	1220	822	1800	Warehouse constraint
		<=	<=	<=	<=
		1800	1800	1800	1800
	Production cost 341	1.25	1.3	1.35	1.4
	Production cost 256	1.35	1.4	1.45	1.5
	Labor hours 341	0.45	0.45	0.45	0.45
	Labor hours 256	0.52	0.52	0.52	0.52
Decision var	Production units 341	1521	826	768	1100
	Production units 256	1249	1	2200	1150	Closing inv constraint
	Closing inventory 341	871	822	800	800	>=	800
	Closing inventory 256	349	0	1000	850	>=	850
	Prod time	1333.93	372.22	1489.6	1093	Prod time constraint
		<=	<=	<=	<=
		1200	1200	1200	1100
0.05	Holding cost	61	41.1	90	82.5
	Production cost	3587.4	1075.2	4226.8	3265
	Total cost	12429
	Obj func	Min

Thank you for asking. Please rate my answer...!!