Question

A small candy shop is preparing for the holiday season. The owner must decide how many bags of deluxe mix and how many bags of standard mix of Peanut/Raisin Delite to put up. The deluxe mix has .66 pound raisins and .34 pound peanuts, and the standard mix has .48 pound raisins and .52 pound peanuts per bag. The shop has 73 pounds of raisins and 58 pounds of peanuts to work with.
Peanuts cost $.58 per pound and raisins cost $1.46 per pound. The deluxe mix will sell for $2.49 per pound, and the standard mix will sell for $2.32 per pound. The owner estimates that no more than 118 bags of one type can be sold.

a. If the goal is to maximize profits, how many bags of each type should be prepared? Use Excel Solver. (Round intermediate cost calculations to 2 decimal places and round your answers to the nearest whole number.)

Deluxe		bags
Standard		bags

  
b. What is the expected profit? Use Excel Solver. (Enter your answer based on the unrounded (not rounded) decision variable values from Part a. Round your answer to the nearest whole number. Omit the "$" sign in your response.)

Profit           $

Answer 1

a)

Set up Excel model as follows:

А D E וד B Deluxe 1 Standard 0.66 0.48 <= 73 2 3 raisins 4 peanuts 5 deluxe demand 6 standard demand 0.52 <= 58 0.34 1 =SUMPRODUCT(B3:C3,$B$12:$C$12) =SUMPRODUCT(B4:04,$B$12:$C$12) =SUMPRODUCT(B5:05,$B$12:$C$12) =SUMPRODUCT(B6:06,$B$12:$C$12) <= 118 1 <= 118 7 8 Total Profit =2.49-B3*1.46-B4*0.58 =2.32-C3*1.46-C4*0.58 =SUMPRODUCT(B9:09,$B$12:$C$12) 9 Profit: 10 11 12 Result: 13

Set up Solver Parameters as shown below:

Solver Parameters Х Set Objective: $D$9 To: Max Min Value Of: 0 By Changing Variable Cells: $B$12:$C$12 Subject to the Constraints: $D$3:$D$6 <= $F$3:$F$6 > 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.

Values appear automatically in variable cells (B12:C12)

A c E D F — H - K L M B Deluxe 1 Standard 2 † † † † X 3 raisins 0.66 0.48 73 73 4 peanuts 0.34 0.52 58 58 5 deluxe demand 1 56.22 118 6 standard demand 1 74.78 118 7 8 Total Profit 9 Profit 1.3292 1.3176 173.26 10 11 12 Result: 56.22 74.78 13 14 Solver Results 15 16 17 Solver found a solution. All Constraints and optimality 18 conditions are satisfied. Reports 19 20 Answer 21 Keep Solver Solution Sensitivity 22 Limits 23 Restore Original Values 24 25 26 27 Return to Solver Parameters Dialog Outline Reports 28 29 30 31 OK Cancel Save Scenario... 32 33 34 35 Solver found a solution. All constraints and optimality conditions are satisfied. 36 37 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this 38 means Solver has found a global optimal solution. 39 40 41 42

Click OK

Following number of bags of each type should be prepared (rounded-off)

Deluxe		56 bags
Standard		75 bags

---------------------------------------------------------

b)

Expected profit = $ 173 (rounded-off)

(Expected profit appears in cell D9, which is 173.26 )