1) Type your name in Cell A1. Type your section (03) for 12:30 and (04) for 2:00.
2) Type “Cost” in C5; “Dec. Var.” in C6; “Cost per Unit” in C7; “Solutions” in C8. For Cell D5, type “=SUMPRODUCT(D7:F7,D8:F8); you will get an initial “0”.
3) Type “Choc. X1” in D6; “PeaButX2” in E6; and “CaramelX3” in F6.
4) Type “0.18”, “0.12”, and “0.15” in Cells D7 through F7.
5) Type “C1” through “C5” in Cells B10 through B14.
6) Type “30% Choc.” In Cell C10; “Protein” in Cell C11; “Carbohy.” in Cell C12; “Sat. Fats” in Cell C13; and “Weight” in Cell C14.
7) Type the coefficients for each constraint, i.e. “0.7 in Cell D11; “3.5” in Cell E11; and “2” in Cell F11. The exercise gave you constraints C2 through C4. You need to formulate Constraints 1 and 5. [If you cannot figure them out, you may email me. I’ll give you the equations for one point each. This is better than coming up with wrong answers on the rest of the assignment.] In Cells G10:G14, you need to put in the inequality (>= for > and <= for <). For Cells H10:H14, you need to type in the values for the RHS, i.e. “5” for Cell H11; “6” for Cell H12; and “5” for Cell H13. Check your entries; this is a prime place for errors.
8) Type “LHS” in Cell D17; “Relation.” in Cell E17; and “RHS” in Cell F17.
9) Type “C1 through C5” in Cells B18:B22. For Cells c18:c22, type in the information used in Cells C10:C14. For Cells E18:E22, type in the correct inequalities, as you did in Cells G10:G14. For Cells F18:f22, type in the RHS values, as you did in Cells H10:H14. For Cell D18, type SUMPRODUCT(D10:F10,D8:F8). For Cells D19-D22 use the same formula, but change the cells in the first pair; D8:F8 is constant. You will initially get “0” for D18 through D22.
You have now completed Sheet 1. Go to “Data” and then “Solver.” Be sure to check your entries. A typo could
10) For objective function, type “$D$5”.
11) Click on “Min”.
12) Type “$D$8:$F$8 for “By Changing Variable Cells.”
13) For “Subject to the Constraints,” click on “Add”. Put in “D18,” hit the correct relationship, and then “F18”; then click “Add”. Do this for each of the remaining four constraints. When you’ve entered the final constraint, click “Cancel.” Check the constraints.
14) You can click “Make Unconstrained Variables Non-Negative.” For “Select a solving method”, use “GRG Nonlinear.”
15) You need to click on “Answer” and “Sensitivity”. If all goes well, you will get an “Answer Report 1” and “Sensitivity Report 1.” The objective function value should be 0.6066. Note: “E-0x” means a very small number, essentially zero. If “Solver Results” box does not come up immediately, hit solver again and the “Solver Results” box should appear.
