LP: Certain Sensitivity Analysis Questions must be answered based on the Excel Output below.

The sandwich company, Alamo Lone Star (ALS) prepares sandwiches for sale in

vending machines in various locations of an urban area. The four types of sandwich that

are now provided are those that have been found to sell well. The following information

is available on each type:

Type of sandwich	Decision variables	Minimum number of units sold	Preparation time per unit (minutes)	Profit per unit ($)
Tuna mayonnaise Ham and cheese Cheese and salad Spicy vegetable	T H C S	200 200 200 200	0.40 0.50 0.48 0.55	0.42 0.44 0.35 0.46

The most popular sandwich is the cheese and salad so ALS ensure that at least half of all sandwiches supplied are cheese and salad. All four types of sandwich are prepared each evening and then distributed to the vending machines the next morning. Sandwich preparation is carried out by five part-time workers. Four of these workers each work 3.5 hours each evening and one works for only two hours. ALS has 50 identical sandwich vending machines each with a capacity of 40 units.

The manager of ALS needs to know how many of each type of sandwich they should produce each evening to maximize profit, subject to unit profit figures and constraints mentioned above. This problem was formulated as a linear programming model and then solved using Excel Solver. The Excel Solver output is provided below.

Please note that to keep the model as simple as possible, it was assumed that ALS can sell all sandwiches they produced.

APPENDIX

Below is the Excel Solver output. Note that the cell entry 1E+30 means an unlimited amount.

Objective Cell (Max)

Cell Name

$F$4 Max Profit

Variable Cells

Original Value

0

Original Value

0 0 0 0

Cell Value

Final Value

790

Final Value

Cell

$B$2

$C$2

$D$2

$E$2

Constraints

Cell

$F$10

$F$11

$F$12

$F$13

$F$7

$F$8

$F$9

Variable

Cell

$B$2

$C$2

$D$2

$E$2

Name

Values T Values H Values C Values S

Name

Min. supply (S) Proportion (C) Prep. Time (min.) Vending space Min. supply (T) Min. supply (H) Min. supply (C)

Cells

 400 0 960 2000 400 200 1000 

Final Value

400

200 1000 400

400

200

1000

400

Formula

$F$10>=$H$10

$F$11>=$H$11

$F$12<=$H$12

$F$13<=$H$13

$F$7>=$H$7

$F$8>=$H$8

$F$9>=$H$9

Reduced Objective Cost Coefficient

Status

Not Binding

Binding

Binding

Binding

Not Binding

Binding

Not Binding

Allowable Increase

Slack

200

0

0

0

200

0

800

Allowable Decrease 0.0200 1E+30 0.5353 0.0100

Allowable Decrease 1E+30 800 30 58.2524 1E+30 200 1E+30

Name

Values T

Values H

Values C

Values S

0 0.42 0.0400

0 0.44 0.0067

0 0.35 0.0913

0 0.46 0.0913

Constraints

Cell Name

10. $F$10 Min. supply (S)

11. $F$11 Proportion (C)

12. $F$12 Prep. Time (min.)

13. $F$13 Vending space

7. $F$7 Min. supply (T)

8. $F$8 Min. supply (H)

9. $F$9 Min. supply (C)

Value

 400 0 960 2000 400 200 1000 

Price

0

-0.0913

0.2667

0.2677

0

-0.0067

0

R.H. Side

200

0

960

2000

200

200

200

Increase

200

375

30

68.1818

200

300

800

Final Shadow Constraint Allowable

ALS is now worried about losing half an hour of preparation time. Joe, the person who currently works only two hours each evening, is aware of this and offered to do an extra hour if Janet reduces her time to 3 hours. How will this impact the optimal solution and the total daily profit, if any?

		This change is not allowed. Thus, the optimal solution and the total daily profit will change. The model has to be rerun.
		This change is allowed. The optimal solution will remain the same but the optimal daily profit will decrease by $8.001.
		This change is allowed. The optimal solution will remain the same but the optimal daily profit will increase by $8.001.
		This change is not allowed. Thus, the optimal solution will remain the same. The model has to be rerun to find out the new optimal daily profit.
		This change is allowed. The optimal solution and the total daily profit will not change