Question

1. Incorporate this model into a spreadsheet using the picture below as a guide for the Excel spreadsheet you develop: (the unit profit cells have been filled in for you to give you a start). Hint: There are SUMPRODUCT functions in the two “Resource Used” cells, and another SUMPRODUCT function in the “Total Profit” cell.

X2 Unit Profit 3 2 Resource Resource Resource Usage Used Available esource 1 esource 2 Xi Total Profit Decision

Hint: to answer questions parts c, d, and e, substitute each X1 and X2 values in parts c, d, and e below into the constraints on resources 1 and 2 given above.

(c ) Is (X1, X2) = (2,1) a feasible solution?

(d) Is (X1, X2) = (2,3) a feasible solution?

(e) Is (X1,X2) = (0, 5) a feasible solution?

(f) Use Solver to solve this model (to get the yellow decision cells and the orange total profit cell) by creating your Excel linear programming model with the information above and running Excel’s Solver (Data tab > Solver)

Please show me on excel for part F:

a.
Objective Function	Z=3X1+2X2
Functional Contraints	3X1+X2<=9
	X1+2X2<=8
Nonnegativity Contraints	X1>=0, X2>=0
b.
	X1	X2
Unit Profit	3	2
			Resource		Resource
	Resource Usage		Used		Available
Resource 1	3	1	9	<=	9
Resource 2	1	2	8	<=	8
	X1	X2			Total Profit
Decision	2	3			12
c.	Yes	(X1,X2)=(2,1)	where 2>= 0 and 1>= 0		also, 3x2+1=7 <= 9
					2+2x1=4<=8	so (2,1) is feasible
d.	Yes	(X1,X2)=(2,3)	2>=0 and3>=0	is the optimal solution, so feasible
e.	No	(X1,X2)=(0,5)	0+2x5=10	10>8	so feasible
f.		(

Answer 1

Solver model

13 2 Unit Profit 3 2 Resource Resource Resourcce usage Used Available 4 :SUMPRODUCT($B$9:$C$9,B5:C5) <=9 SUMPRODUCT(SB$9:$C$9,B6:C6) 8 5 Resource 1 3 6 Resource 2 1 2 Total Profit X1 X2 9 Decision SUMPRODUCT(B2:C2,B9:C9)

Set Objective SF$9 Value Of: By Changing Variable Cells: $B$9:$C$9 Subject to the Constraints: Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method Simplex LP Options 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. Help Solve Close

Solution

	X1	X2
Unit Profit	3	2
			Resource		Resource
	Resource usage		Used		Available
Resource 1	3	1	9	<=	9
Resource 2	1	2	8	<=	8
	X1	X2			Total Profit
Decision	2	3			12