Question

Maman-Paz is a distributor of meal kits. Their meal kit is a package that contains all necessary food items for customers cook it at home. They source ingredients from manufacturers and distribute to retailers such as Walmart and Target. To date they have not been reaching the Boston area and had no customers from there. However, with new budget approved they are ready to enter this market.

Maman-Paz has selected 6 target cities they want to serve with their famous pasta kit, PastaFusion, in the first year of their operations. To serve these cities they need to purchase warehouses from a number of candidate locations and plan inbound deliveries from suppliers to warehouses as well as outbound deliveries from warehouses to the target cities.

The table below shows the target cities and their forecasted demand for PastaFusion the coming year.

Cambridge: 10 Tons/year
Medford: 7 Tons/year
Boston: 12 Tons/year
Quincy: 3 Tons/year
Winthrope: 9 Tons/year
Dorchester: 4 Tons/year

Four candidate warehouse locations have been identified in the following areas with their estimated capacity.

Allston: 35 Tons/year
Somerville: 60 Tons/year
Chelsea: 20 Tons/year
Malden: 40 Tons/year

Maman-Paz potential suppliers are located in the following locations with the following capacities:

Arlington: 35 Tons/year
Brookline: 40 Tons/year
Melrose: 25 Tons/year

Below are the distance matrices, in miles, from suppliers to warehouses and from warehouses to target cities. Shipping cost from suppliers to warehouses is $1.21 per Ton per mile. Shipping cost from warehouses to target cities is $1.8 per Ton per mile.

Question 1:The optimal transportancation cost is 5744

Question2

The top management is interested in further analyzing the capacity of warehouses. Since they will have to open some warehouses for serving this region, it is important for them to know how much capacity in these open warehouses is actually being used. They do not want to open warehouses that have too much unused capacity.

They ask you to calculate the fraction of total capacity that is used. To do this you need to compare the total used warehouse capacity with the total available warehouse capacity. (Note that total available capacity is obtained by adding up the capacities of all open warehouses. You should not include warehouses that are not opened yet).

Based on the optimal solution obtained in Question 1, answer the following question. For those warehouses that are open, what fraction of the total available warehouse capacity is being used?

Question 3Question 4

Once you present your solutions and receive feedback, you discover that maintaining warehouses also has a cost and this cost must be considered in the model. This cost is proportional to the warehouse capacity. For every Ton per year of capacity, there is $10000 maintenance cost. For example, if a warehouse’s capacity is 40 Tons/year, the company incurs $400000 maintenance cost per year.

Assuming the original warehouse capacities, add the new maintenance cost to the model and solve the problem again.

For those warehouses that are open, what percentage of the total available warehouse capacity is being used?

helsea Malden omerville lston 150 100 110 130 rlington 60 Brookline 100 130 130 120 elrose ambridge Medford Boston Quincy Winthrope Dorchester 50 100 50 lston 0 110 15 0 Somerville 20 100 50 0 helsea 50 0 50 Malden A warehouse is a bottleneck when its capacity is fully used. Which of the following are true for the optimal solution you found in Question 1? Select all correct answers. If the capacity of Chelsea is increased by 10%, the total cost is reduced by 300 dollars. Somerville warehouse is a bottleneck. O If the capacity of Malden is reduced by 20%, the total cost will not decrease. If the capacity of Somerville warehouse is increased by 10%, the total cost is reduced by 700 dollars Around 80% of the capacity in Chelsea warehouse is being used None of the above

Answer 1

1

The excel model is shown below

14 x12+13 Allston Somerville Chelsea Malden Capacities Arlington 130 110 Plant WH 0 Brookline WH-City 70 Total cost[ Melrose 25 。 10 Cambridge 10 Medford 12 15 Boston Quincy 110 65 Winthrope Dorchester 10 40 11 Demand/Capacities35 12 Allston SomervilleChelseMalden 13 Arlington Brookline 15 Melrose 16 Cambridge 17 Medfor 18 19 Boston 20 Quincy Winthrope Dorchester 24 Received at WH 25 Sent from WH 26 27 4 Sheet1174 Sheet1175 Sheet1176 Sheet1177Sheet1178 Sheet1179 Sheet1180 Sheet 囲回凹 100%

The formulas are shown below

12+13 Allston Somerville Chelsea Malden Copocities Cost Arlington Plant - WH -SUMPR 82:E4,B14 E16 WH-City Brookline B5:E10,B17.E22) 1.8 -SUMP Melrose Total cost 2413 5 10 7 12 110 Winthrope 10 4 Demand/Capacities Allston Somerville Chelse Malden Arlington SUM B14 E14) Brookline -SUM(B16 E16) Melrose 17-SUM(B17:E17 18 SUM B18 E13 19 -SUMB19 E19) 20-SUM(B20:E20) Winthrope 21 SUMB21:E21) 22 SUM B22 E22) Dorchester Received at WH -SUMIB14:816) SUM B17:822) -SUMIC14:C16 SUMID14:D16 -SUMIE14:E16 SUM C17:C22) SUM E17 E22) Sent from WH -SUM( D17 D22) Sheet1174 Sheet1175 Sheet1176 Sheet1177 Sheet1178 Sheet1179 Sheet1180 Sheet 囲回凹--+- + 85%

The solver parameters are shown below

12+13 Solver Parameters Allston Somerville Chel Malden Capacities Cost Set Objective: SI54 Arlington 110 Plant- WH Brookline WH-City O Yalue of Min Melrose Total cost By Changing Variable Cells: Cambridge SBS14:SES22 Medfor Subject to the Constraints Quincy SAS17:SAS22 SASS SAS10 Add 65 Winthrope SB$24:SES24SBS11:SES11 $B $24:SES24 SBS25 SES25 SFS14:SF516-SFS2-SFS4 Dorchester 40 Change 11 Demand/Capacities35 Delete Allston SomervilleChelse Malden Arlington Reset All Brookline Melrose Load/Save Cambridge 17 Medford Sclect a Solving Method: Simplex LP 19 Boston Quincy Solving Method Winthrope 21 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 Sover problems that are non smooth. Dorchester 23 24 Received at WH 25 Sent from WH delp Solve cigse 27 Sheet1175 Sheet1176 Sheet1 177 Sheet1178 Sheet1 179 Sheet1180 Sheet. O

The result is shown below

14 -12+13 AllstonSomerville Chelsea MaldenCapacities Arlington Plant - WH 3835.7 WH - City Brookline 70 1908 Melrose Total cost 5743.7 Cambridge 30 30 15 50 50 Quincy Solver Results 65 Winthrope 110 Dorchester 10 40 Solver found a solution. All Constraints and optimality conditions are satisfied. 11 Demand/Capacities 20 Answer OKeep Solver Solution Allston Somerville Chelsea Malden Sensitivity Limits Arlington ORestore Original Values Brookline 16 Melrose Return to Solver Parameters Dialog Outline Reports Cambridge 17 10 18 Save Scenario. 19 Quincy 9 Winthrope Dorchester Creates the type of report that you specify, and places each report on a separate sheet in the workbook 24 Received at WH 30 Sent from WH 25 Sheet1174 Sheet1175 Sheet1176 Sheet1177 Sheet1178 Sheet1179 Sheet1180 Sheet 囲回凹 100%

The sensitivity analysis is shown below

A7 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 13 SB$15 Brookline Allston 5 18 $C$16 Melrose Somerville 2 Sensitivity Report 77 Sheet1174 Sheet1175 Sheet1176 Sheet1 177 Sheet178 Sheet117!..G +100%

The sensitivity analysis continued

A7 A B 33 $B$20 Allston 5.9 E+30 5.9 34 $C$20 Somerville 5.9 1E+30 35 SD$20 Chelsea 30.1 30.1 E+30 36 SE$20 Malden 37 $B$21 Allston 162 E+30 65.8 75.1 75.1 E+30 38 $C$21 Somerville 39 $D$21 Chelsea 40 SE$21 Maldern 41 $B$22 Allston 105.2 0 105.2 198 E+30 180 99.3 99.3 E+30 75.1 1E+30 30.1 30.1 E+30 42 $C$22 Somerville 43 $D$22 Chelsea 24.2 24.2 E+30 0.3 0.3 E+30 1E+30 44 SE$22 Malden 0.3 46 Constraints 47 Final Shadow Constraint Allowable Allowable 48 Cell Value Price R.H. Side Increase Decrease 49 SA$17 Melrose 10 102.7 1 50 SA$18 Melrose 93.7 51 SA$19 Melrose 52 SA$20 Melrose 3 180.9 53 SA$21 Melrose 9 201.7 54 SA$22 Melrose 4 156.7 $B$24 Received at WH Allston 55 $C$24 Received at WH Somerville 56 $D$24 Received at WH Chelsea 57 58 SES24 Received at WH Maldern $B$24 Received at WH Allston 59 Sensitivity Report 77 Sheet1174Sheet1175 Sheet1176 Sheet1177 Sheet1178 Sheet117 + 100%

The sensitivity analysis continued

A7 0 41 $B$22 Allston 30.1 30.1 42 $C$22 Somerville 24.2 E+30 24.2 43 SD$22 Chelsea 0.3 0.3 E+30 44 SE$22 Malden 0.3 1E+30 46 Constraints 47 Final Shadow Constraint Allowable Allowable 48 Cell Value Price R.H. Side Increase Decrease 49 SA$17 Melrose 10 102.7 7 50 SA$18 Melrose 93.7 51 SA$19 Melrose 52 SA$20 Melrose 3 180.9 53 SA$21 Melrose 9 201.7 54 SA$22 Melrose 4 156.7 $B$24 Received at WH Allston 55 $CS24 Received at WH Somerville 56 E+30 57 $D$24 Received at WH Chelsea 57 E+30 SE$24 Received at WH Malden 58 $B$24 Received at WH Allston 59 SC$24 Received at WH Somerville 60 3 108.9 61 SD$24 Received at WH Chelsea SES24 Received at WH Malden 62 84.7 63 SFS14 Arlington Dorchester 64 SF$15 Brookline Dorchester 65 SF$16 Melrose Dorchester 1E+30 12.1 1E+30 67 Sensitivity Report 77Sheet1174 Sheet1175 Sheet1176 Sheet1177 Sheet178 Sheet117..O +100%

2

There are three warehouses that are open. Their total warehouse capacity is 60 + 20 + 40 = 120. The utilized capacity is 3 + 12 + 30 = 45.

This means the fraction of utilization is 45/120 = 0.375

3

Correct answer: None of the above

Reasons:

• The shadow price for Chelsea is 130. This means for a 10% increase or 2 unit increase, the impact will be 260
• Somerville warehouse is not completely utilized and hence is not a bottleneck
• Malden current capacity is 40. If it becomes 32 (reduction of 20%) it will not impact the optimal solution
• Somerville warehouse capacity is not a binding constraint and thus there will be no impact
• Chelsea warehouse is only using 60% of their capacity