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 Decision Minimum number Preparation time Profit
sandwich variables of units sold per unit (minutes) per unit ($)
Tuna mayonnaise T 200 0.40 0.42
Ham and cheese H 200 0.50 0.44
Cheese and salad C 200 0.48 0.35
Spicy vegetable S 200 0.55 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 Original Value Final Value
$F$4 Max Profit 0 790
Variable Cells
Cell Name Original Value Final Value
$B$2 Values T 0 400
$C$2 Values H 0 200
$D$2 Values C 0 1000
SE$2 Values S 0 400
Constraints
Cell Name Cell value Formula Status Slack
SF$10 Min supply 400 $F$10>=$H$10 Not Binding 200
(S) $F$11 Proportion 0 $F$11>=$H$11 Binding 0
(C) $F$12 Prep. Time (min.) 960 $F$12<=SH$12 Binding 0
$F$13 Vending space 2000 $F$13<=$H$13 Binding 0
$F$7 Min. supply (T) 400 $F$7>=$H$7 Not Binding 200
$F$8 Min. supply (H) 200 $F$8>=$H$8 Binding 0
SF$9 Min. supply (C) 1000 $F$9>=$H$9 Not Binding 800
Variable Cells
Cell Name Final Reduced Obective Allowable Allowable
Value Cost Coefficient Increase Decrease
$B$2 Values T 400 0 0.42 0.0400 0.0200
SC$2 Values H 200 0 0.44 0.0067 1E+30
SD$2 Values C 1000 0 0.35 0.0913 0.5353
$E$2 Values S 400 0 0.46 0.0913 0.0100
Refer to above Excel output
ALS is now considering making simultaneous (multiple) changes. The unit price of cheese and salad sandwich were to be increased so that the new profit per unit becomes 50.38. Also, the unit price of spicy vegetable sandwich were to be increased so that the new profit per unit becomes $0.53. Which of the following correctly explain whether these multiple changes would have any effect on the daily optimal production schedule and any effect on the total daily profit?
a. These changes are not allowed. Thus, the optimal production schedule will change. The optimal daily profit will be $848
b. These changes are not allowed. Thus, the optimal production schedule will remain the same. The model has to be rerun to find out the new optimal daily profit.
c. These changes are allowed. The optimal solution will not change but the optimal daily profit will be $848
d. These changes are allowed. The optimal daily profit will increase but the optimal daily production will change
e. These changes are not allowed. Thus, the optimal production schedule will change. These changes must be entered into the model and the model has to be rerun