- [20 marks] The accompany Excel sensitivity report presents the result of a linear program to determine the most economical combination of sandwiches for a catering firm to provide for a golf pro The pro shop has asked the catering firm to provide 70 sandwiches in a variety to include tuna, tuna and cheese, ham, ham and cheese and cheese. They have specified a minimum of 10 each of tuna and ham, and 12 each of tuna/cheese and ham/cheese.
The catering firm makes the sandwiches using the following resources: bread, tuna, ham, cheese, mayonnaise, mustard, lettuce, tomato, packaging material and labor hours.
The unit costs for the sandwiches are shown in the following table:
Sandwich
Tuna
Tuna / Cheese
Ham
Ham /Cheese
Cheese
Unit Cost
$2.42
$2.12
$3.35
$3.02
$2.36
Adjustable Cells
Cell
Name
Final
Value
Reduced
Cost
Objective
Coefficient
Allowable
Increase
Allowable
Decrease
$C$5
Tuna
10
0
2.42
1E+30
0.38
$D$5
Tuna/Ch
30
0
2.12
0.24
1E+30
$E$5
Ham
10
0
3.35
1E+30
0.99
$F$5
Ham/Ch
12
0
3.02
1E+30
0.66
$G$5
Cheese
8
0
2.36
0.66
0.24
Constraints
Cell Name
Final
Value
Shadow
Price
Constraint
R.H. Side
Allowable
Increase
Allowable
Decrease
$H$8 Bread (slices)
140
0
140
1E+30
0
$H$9 Tuna (oz.)
130
-0.08
130
24
6
$H$10 Ham (oz.)
76
0
100
1E+30
24
$H$11 Cheese (oz.)
74
0
80
1E+30
6
$H$12 Mayo (oz.)
54
0
72
1E+30
18
$H$13 Mustard (oz.)
4.4
0
8
1E+30
3.6
$H$14 Lettuce (oz.)
17.5
0
20
1E+30
2.5
$H$15 Tomato (oz.)
35
0
40
1E+30
5
$H$16 Package (unit)
70
0
72
1E+30
2
$H$17 Labor (hrs)
5.6
0
8
1E+30
2.4
$H$18 Minimum Quantity
70
2.36
70
0
8
$C$19 Min Tuna
10
0
10
13.5
10
$C$20 Min Tuna/Cheese
30
0
12
18
1E+30
$C$21 Min Ham
10
1
10
6
1.5
$C$22 Min Ham/Cheese
12
1
12
8
2
Use the given sensitivity report to answer the following questions:
- What is the optimum production plan, i.e. how much of each sandwich that the catering firm should provide to the golf pro shop? [3 marks]
- What is the total cost of the sandwich production plan? Show [3 marks]
- What is the range over which the quantity of tuna could vary without changing the combination of binding constraints? [3 marks]
- After the sandwiches are made, how many labour hours remain? [2 marks]
- What would be the impact on the production plan and the total cost if the unit cost for ham sandwiches increased to $3.75? [5 marks]
- Which constraints are binding? [2 marks]
- Tuna sandwiches use 4 ounces of tuna. Tuna with cheese sandwiches use 3 ounces of tuna. What would happen to the cost and production plan if the cost per ounce of tuna decreased by $0.10 per ounce? [2 marks]
- [20 marks] The accompany Excel sensitivity report presents the result of a linear program to determine the most economical combination of sandwiches for a catering firm to provide for a golf pro The pro shop has asked the catering firm to provide 70 sandwiches in a variety to include tuna, tuna and cheese, ham, ham and cheese and cheese. They have specified a minimum of 10 each of tuna and ham, and 12 each of tuna/cheese and ham/cheese.
The catering firm makes the sandwiches using the following resources: bread, tuna, ham, cheese, mayonnaise, mustard, lettuce, tomato, packaging material and labor hours.
The unit costs for the sandwiches are shown in the following table:
Sandwich
Tuna
Tuna / Cheese
Ham
Ham /Cheese
Cheese
Unit Cost
$2.42
$2.12
$3.35
$3.02
$2.36
Adjustable Cells
Cell
Name
Final
Value
Reduced
Cost
Objective
Coefficient
Allowable
Increase
Allowable
Decrease
$C$5
Tuna
10
0
2.42
1E+30
0.38
$D$5
Tuna/Ch
30
0
2.12
0.24
1E+30
$E$5
Ham
10
0
3.35
1E+30
0.99
$F$5
Ham/Ch
12
0
3.02
1E+30
0.66
$G$5
Cheese
8
0
2.36
0.66
0.24
Constraints
Cell Name
Final
Value
Shadow
Price
Constraint
R.H. Side
Allowable
Increase
Allowable
Decrease
$H$8 Bread (slices)
140
0
140
1E+30
0
$H$9 Tuna (oz.)
130
-0.08
130
24
6
$H$10 Ham (oz.)
76
0
100
1E+30
24
$H$11 Cheese (oz.)
74
0
80
1E+30
6
$H$12 Mayo (oz.)
54
0
72
1E+30
18
$H$13 Mustard (oz.)
4.4
0
8
1E+30
3.6
$H$14 Lettuce (oz.)
17.5
0
20
1E+30
2.5
$H$15 Tomato (oz.)
35
0
40
1E+30
5
$H$16 Package (unit)
70
0
72
1E+30
2
$H$17 Labor (hrs)
5.6
0
8
1E+30
2.4
$H$18 Minimum Quantity
70
2.36
70
0
8
$C$19 Min Tuna
10
0
10
13.5
10
$C$20 Min Tuna/Cheese
30
0
12
18
1E+30
$C$21 Min Ham
10
1
10
6
1.5
$C$22 Min Ham/Cheese
12
1
12
8
2
Use the given sensitivity report to answer the following questions:
- What is the optimum production plan, i.e. how much of each sandwich that the catering firm should provide to the golf pro shop? [3 marks]
- What is the total cost of the sandwich production plan? Show [3 marks]
- What is the range over which the quantity of tuna could vary without changing the combination of binding constraints? [3 marks]
- After the sandwiches are made, how many labour hours remain? [2 marks]
- What would be the impact on the production plan and the total cost if the unit cost for ham sandwiches increased to $3.75? [5 marks]
- Which constraints are binding? [2 marks]
- Tuna sandwiches use 4 ounces of tuna. Tuna with cheese sandwiches use 3 ounces of tuna. What would happen to the cost and production plan if the cost per ounce of tuna decreased by $0.10 per ounce? [2 marks]
