Microsoft Excel 16.0 Answer Report

Worksheet: [Book2.xlsx]Sheet2

Report Created: 6/17/2021 2:41:58 AM

Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine

Engine: Simplex LP

Solution Time: 0.016 Seconds.

Iterations: 2 Subproblems: 0

Solver Options

Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)

Cell Name Original Value Final Value

$E$22 Profit 0 242.5

Variable Cells

Cell Name Original Value Final Value Integer

$C$14 X1 0 300 Contin

$D$14 X2 0 125 Contin

Constraints

Cell Name Cell Value Formula Status Slack

$C$16 Labour Constraint 6 $C$16<=$E$16 Binding 0

$C$17 Sausage Constraint 30 $C$17<=$E$17 Binding 0

$C$18 Ham Constraint 18.75 $C$18<=$E$18 Not Binding 11.25

Microsoft Excel 16.0 Sensitivity Report

Worksheet: [Book2.xlsx]Sheet2

Report Created: 6/17/2021 3:59:52 AM

Variable Cells

Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $C$14 X1 300 0 0.6 1E+30 0.1 $D$14 X2 100 0 0.5 0.1 0.5

Constraints

Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $C$16 Labour Constraint 5.4 0 6 1E+30 0.6 $C$17 Sausage Constraint 30 1 30 10 4.285714286 $C$18 Ham Constraint 15 0 30 1E+30 15 $C$19 Flour Constraint 16 12.5 16 1 4

A linear programming model was developed and solved in excel solver. Use the Answer report and Sensitivity report to answer the questions below.

a)The LP model was to maximize profit. Using the sensitivity report, write out the objectivefunction.

b)What is the optimal solution?

c)There are four Basic Variables, what are they?

d)How much extra sausage and ham is left over at the optimal solution point? Is there any idle labour time?

e)What is the sensitivity range for the objective coefficient of X2?

f)What would the solution be if the profit for a unit of X2 was increased from $0.5 to

$0.6? g)What would be the effect on the optimal solution if two more pounds of flour were

available?