Question

2. Consider the following linear programming problem. ACME toy company has to decide the amount of dolls, toy trucks, and action figures to make. Each product is subject to the following constraints and p Dolls Toy trucksAction figures Constraint Parts per unit (hrs) Assembly per unit (hs)4 Packaging per unit (hrs) 3 2000 2000 1000 3 3 $5 $6 $4 Profit margin (a) Set up the linear programming proble in Excel in a worksheet named LP setup." (10pts) (b) Perform the linear programming problem and report your results in a new wark- sheet named LP results. (5pts)

Answer 1

2)

x = dolls

y = toy trucks

z = Action figures

Maximize 4x + 6y + 5z

constraints

2x + 5y + 3z <= 2000
4x + 4 y + 3z <= 2000
3x + 3y + 2z <= 1000

Formulas in Excel

x	y	z
0	0	500
2	5	3	=SUMPRODUCT($B$2:$D$2,B4:D4)	2000
4	4	3	=SUMPRODUCT($B$2:$D$2,B5:D5)	2000
3	3	2	=SUMPRODUCT($B$2:$D$2,B6:D6)	1000
			Profit
4	6	5	=SUMPRODUCT($B$2:$D$2,B8:D8)

Using Solver

Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools E8 Solver Parameters Set Objective: 〇Min ○Value Of: To: Max 2000 2000 1000 1500 1500 1000 By Changing Variable Cells: 4 SB$2:SDS2 Profit Subiect to the Constraints: 6 2500 Change 10 Delete Beset All 15 Load/Save Make Unconstrained Variables Non-Negative 17 18 19 20 Select a Solving GRG Nonlinear Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 23 24 25 26 27 28 Help

x = 0, y = 0 ,z = 500

maximum profit = 2500