Question

A "product mix" problem can be solved quite easily by hand, as long as there is only one constraint (i.e. one resource in which demand > capacity). However, when there are multiple constraints it becomes quite complex. In the lab, we used Solver to find the product mix that results in the highest profit, subject to capacity constraints for each resource, and the constraint that we would not make more than the demand for any given product.

Set up a model based on what was done in the lab to find the optimal product mix for the problem in the attached spreadsheet. What was the resulting total profit?

*Note - make sure that your "aggregate workload" calculations reference your decision variables (production amounts) rather than demand, or you may get an error when you solve.

		less than $32,000
		between $32,000 and $32,250
		between $32,250 and $32,500
		between $32,500 and $32,750
		between $32,750 and $33,000
		more than $33,000

		Product
		A	B	C	D	E	F
Time required on each workstation (minutes required per unit of production)	Doo-hickey	1.55	2.62	2.9	1.6	0.95	2.4
	Simegma	0.58	1.3	2.65	1.5	0.98	2
	Thing-a-ma-jig	0.82	1.7	2.55	1.99	1.01	0.8
	Whatchamacallit	1.1	2.14	3.85	2.06	1.41	1.2
	Prototon 2000	1.04	1.24	3.62	2.34	1.21	2.2
Profit margin per unit		$   18.00	$         32.00	$   44.00	$   22.00	$   16.00	$     27.00
Forecast demand per week		250	280	280	210	160	110
*assume that each machine has 40 hours/week of capacity (2,400 minutes)
*assume that production of a specific product should not exceed demand for that product
*add a constraint that all production quantities must be integers
*use the "Simplex LP" Solving Method in Solver

Answer 1

Set up Excel model as follows:

с D E F G H J 1 B с D E F 2.62 2.9 1.6 0.95 2.4 <= 2400 A B А 2 3 Doo-hickey 1.55 4 Simegma 0.58 5 Thing-a-ma-jig 0.82 6 Whatchamacallit 1.1 7 Prototon 2000 1.04 8 1.3 1.5 0.98 2 2.65 2.55 3.85 1.01 1.7 2.14 0.8 1.99 2.06 2.34 =SUMPRODUCT(B3:G3,$B$14:$G$14) =SUMPRODUCT(B4:G4,$B$14:$G$14) =SUMPRODUCT(B5:45,$B$14:$G$14) =SUMPRODUCT(B6:36,$B$14:$G$14) =SUMPRODUCT(B7:67,$B$14:$G$14) <= 2400 <= 2400 <= 2400 <= 2400 1.41 1.2 2.2 1.24 3.62 1.21 Total Profit =SUMPRODUCT(B10:G10,$B$14:$G$14) 18 32 44 22 16 27 9 10 Profit ($): 11 12 Demand/week: 13 14 Result: 15 250 280 280 210 160 110 16 17

Set up Solver Parameters as follows:

Solver Parameters X Set Objective: $H$10 1 To: Max O Min O Value of: 0 By Changing Variable Cells: $B$14:$G$14 1 Subject to the Constraints: $B$14:$G$14 <= $B$12:$G$12 $B$14:$G$14 = integer $H$3:$H$7 <= $J$3:$J$7 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

Click Solve to generate the solution

After that, values appear automatically in variable (yellow colored) cells

A F G H - J B А с B D с E D 1 E F 1.55 2.9 0.95 2400 0.58 2.62 1.3 1.7 2400 2.65 2.55 3.85 3.62 0.82 1.1 1.04 1.6 1.5 1.99 2.06 2.34 2.4 2 0.8 1.2 0.98 1.01 1.41 1.21 2399.51 1699.2 1738.11 2399.56 2165.98 2.14 1.24 2400 2400 2400 2.2 2 3 Doo-hickey 4 Simegma 5 Thing-a-ma-jig 6 Whatchamacallit 7 Prototon 2000 8 9 10 Profit ($): 11 12 Demand/week: 13 14 Result: 15 16 17 Total Profit 32094 18 32 44 22 16 27 250 280 280 210 160 110 225 278 280 59 160 110 18

Solver Results х Solver found an integer solution within tolerance. All Constraints are satisfied. Reports Answer Keep Solver Solution O Restore Original Values Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found an integer solution within tolerance. All Constraints are satisfied. It is possible that better integer solutions exist. To make sure Solver finds the very best solution, set the integer tolerance in the options dialog to 0%.

Click OK

---

Result:

Following quantity of each product should be produced

Product	Production Qty
A	225
B	278
C	280
D	59
E	160
F	110

Total profit = $ 32,094