Question

NanuqCatering is planning to produce 'ready-to-go' tray of sandwiches. Each tray will include tuna, tuna/cucumber, ham, ham/cucumber, and cucumber sandwiches. Most popular orders come in the range of 80 to 160 sandwiches per tray. The management has specified a minimum of 15 each of tuna and ham, 12 each of tuna/cucumber and ham/cucumber and 15 cucumber sandwiches to be included in each tray. Due to demand the management also specified that the company should make fewer ham sandwiches than cucumber sandwiches. The company makes trays of sandwiches using the following resources: bread, tuna, ham, cucumber, mayonnaise, lettuce, and labour hours according to the plan shown in Table 1 below. The company has no shortage of bread. For each resource, the availability limit is listed in the last column of Table 1. NanuqCatering is looking for the most economical combination of the tuna X. tuna/cucumber X, ham X,,,ham/cucumber X, and cucumber X, sandwiches to minimise the production cost per tray.

Answer 1

Ans (1)

Decision variables express the decisions that ought to be made. As per the question, you need to find the number of sandwiches that should be made in order to minimise the production cost per tray. Thus, your decision variables are:

1. X11 : Total tuna sandwiches that ought to be made per tray
2. X12 : Total tuna/cucumber sandwiches that ought to be made per tray
3. X21 : Total ham sandwiches that ought to be made per tray
4. X22 : Total ham/cucumber sandwiches that ought to be made per tray
5. X33 : Total cucumber sandwiches that ought to be made per tray

Ans (2)

The costs per sandwich are provided in the given table. Your objective's to minimise the production cost per tray which is-

Production cost per tray = 13.60X11 + 14.80X12 + 14.40X21 +14.60X22 + 9X33

Ans (3)

Constraints are the restrictions that need to be satisfied. As per the question these are-

1. Popular order quantities lie between 80 and 160. Hence,
   • 80 <= X11 + X12 + X21 +X22 + X33 <= 160 .....................(1)
2. Management specifications on the minimum content per tray
   • X11 >= 15 .....................(2)
   • X12 >= 12 .....................(3)
   • X21 >= 15 .....................(4)
   • X22 >= 12 .....................(5)
   • X33 >= 15 .....................(6)
3. Specification of fewer ham sandwiches than cucumber sandwiches
   • X33 > X21 .....................(7)
4. Resource Availability:
   • Tuna: 0.2X11 + 0.1X12 <= 6.5 .....................(8)
   • Ham: 0.15X21 + 0.1X22 <= 7 .....................(9)
   • Cucumber: 0.15X12 + 0.15X22 + 0.4X33 <= 12 .....................(10)
   • Lettuce: 0.2X11 + 0.15X12 + 0.2X21 + 0.15X22 + 0.4X33 <= 20 .....................(11)
   • Mayonnaise: 0.04X11 + 0.05X12 + 0.04X21 + 0.05X22 + 0.06X33 <= 5 .....................(12)
   • Labour: 0.2X11 + 0.25X12 + 0.2X21 + 0.25X22 + 0.25X33 <= 24 .....................(13)

Ans (4)

Follow the steps below:

1. Enter the data into your spreadsheet as shown-
   • 
     Decision Variables Tuna Ham/cucumber Cucumber Tuna/cucumber Ham X12 X21 X11 X22 X33 0 0 0 0 0 9 Cost per sandwich ($) 13.6 14.8 14.4 14.6 Objective Minimise total Cost 0 Constraints Popuplar order quantities 0 0 0 0 0 0 <= 160 80 0 0 0 0 0 0 >= Minimum sandwiches per tray 0 0 >= 15 0 12 0 0 >= 0 >= 0 >= 15 0 12 0 0 >= 15 Fewer ham sandwiches than cucumber sandwiches 0 0 < 0 Resource Availability per sandwich Tuna 0.2 0.1 6.5 0.15 0.1 0 <= 0 <= 0 <= 7 0.15 0.15 0.4 12 Ham Cucumber Lettuce Mayonnaise Labour 0.2 0.2 0.15 0 <= 20 0.04 0.15 0.05 0.25 0.04 0.05 0.4 0.06 0.25 0 <= 5 0.2 0.2 0.25 0 <= 24
     
2. Now, click on DATA tab.
   • In the Analysis group select SOLVER.
   • A parameters dialog box will open.
   • Enter the values as follows and click on Solve.
   • 
     Connections All Edit Links Solver Parameters X La From Text Sources Get External Data Connections D8 ... x Set Objective: SDS8 ERS A B с D To: O Max Min O Value of: 0 1 Decision Variables Tuna By Changing Variable Cells: SD3:5H53 2 X11 ERE 3 4 Cost per sandwich ($) Add 6 Change 7 Objective Minimise total Cost 8 Delete 9 10 Subject to the Constraints: SIS12 <= SK$12 SIS13 >= SK$13 SIS14 >= SK$14 SIS15 > SK$15 SIS16 >= SK$16 SIS17 >= SK$17 SIS18 >= $K$18 SIS19 <= SKS 19 SIS22 <- SKS22 SIS23 <= SK$23 SIS24 <= SK$24 SIS25 <= SK$25 SIS26 <= SKS26 Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Reset All 11 Constraints Load/Save 12 13 Popuplar order quantities Options Minimum sandwiches per tray 14 15 16 17 18 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
     
3. The answer is-
   • 
     Objective Cell (Min) Cell $D$8 Minimise total cost Name Original Value Final Value 1029.8 0 Name Original Value Final Value 0 20 Variable Cells Cell $D$3 X11 $E$3 X12 $F$3 X21 $G$3 X22 SH$3 X33 0 12 0 15 0 12 0 21
     
   • So 20,12,15,12 and 21 tuna, tuna/cucumber, ham, ham/cucumber and cucumber sandwiches should be made and presented per tray respectively to minimise cost. The minimised cost is $1,029.8