Question

Question 1

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 $0.98 per Ton per mile. Shipping cost from warehouses to target cities is $1.57 per Ton per mile.

	Allston	Somerville	Chelsea	Malden
Arlington	130	110	100	150
Brookline	70	80	90	60
Melrose	120	130	130	100

	Cambridge	Medford	Boston	Quincy	Winthrope	Dorchester
Allston	10	30	30	50	100	50
Somerville	20	40	15	40	110	40
Chelsea	50	40	5	50	100	20
Malden	10	5	50	90	65	40

They ask you to solve the network design problem. Management also notifies you that it is not necessary to use all of the potential suppliers.

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?

Enter a value between 0 and 1 with TWO decimal places.

Answer 1

1)

Optimal solution is determined using Solver as follows:

xSUMPRODUCT(B4:E6,J4:M6)*0.98+SUMPRODUCT(B10:G13,J10:013) 1.57 U18 Tables of optimal distribution plan Parameters Tables Allston Somerville |Chelsea Malden Row Total Allston Somerville | Chelsea Malden Capaci 35 Arlington 150 100 110 130 4 Arlington Brookline 70 5 Brookline Melrose 25 100 130 120 130 6 Melrose 16 26 Column Total 20 7 Capacity 35 idge Medford Boston Quincy Winthrope Dorchester Row Total Cambri Winthrope Dorchester Cambridge Medford Boston Qui Allston 100 10 10 Allston Somerville 40 40 110 15 16 11 Somerville 12 Chelsea 20 100 12 Chelsea 26 10 Malden 40 50 65 10 13 Malden 12 10 Column Total 10 14 Demand 15 16 -1 17 Total Cost 4,763 18 19 20 21

EXCEL FORMULAS:

Tables of optimal distribution plan
	Allston	Somerville	Chelsea	Malden	Row Total
Arlington	0	0	5	0	=SUM(J4:M4)
Brookline	0	3	11	26	=SUM(J5:M5)
Melrose	0	0	0	0	=SUM(J6:M6)
Column Total	=SUM(J4:J6)	=SUM(K4:K6)	=SUM(L4:L6)	=SUM(M4:M6)
	Cambridge	Medford	Boston	Quincy	Winthrope	Dorchester	Row Total
Allston	0	0	0	0	0	0	=SUM(J10:O10)
Somerville	0	0	0	3	0	0	=SUM(J11:O11)
Chelsea	0	0	12	0	0	4	=SUM(J12:O12)
Malden	10	7	0	0	9	0	=SUM(J13:O13)
Column Total	=SUM(J10:J13)	=SUM(K10:K13)	=SUM(L10:L13)	=SUM(M10:M13)	=SUM(N10:N13)	=SUM(O10:O13)

Total cost =SUMPRODUCT(B4:E6,J4:M6)*0.98+SUMPRODUCT(B10:G13,J10:O13)*1.57

Solver parameters:

SSolver Parameters SJS18 Set Objective O Max Min O Value Of: o Of: 0 By Changing aiable Cells: SJS4:SMS6,SJS10:SOS13 Subject to the Constraints: Add SNS4:SNS6SFS4:SFS6 SJS14:SOS14 SBS14:SGS14 Change SPS10:SPS13 SJS7:SMS7 Delete Reset All Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. Solve Close

2)

The modified model and solution is following:

e fx SUMPRODUCT(B4:E6,J4:M6) *0.98+SUMPRODUCT(B11:G14,J11:014) 1.57+SUM(B8:E8) 10000 J19 Parameters Tables Tables of optimal distribution plan Allston Somerville Chelsea Malden Row Total AllstonSomerville Chelsea Malden Capaci 4 Arlington Arlington 130 110 100 150 35 5 Brookline 70 Brookline 29 120 25 Melrose 6 Melrose 130 130 100 7 Capacity 16 35 60 Column Total Logical Warehouse 35 kept open-1 10 Cambridge Medford Boston QuincyWinthro Dorchester Cambridge Medford Boston Quincy Winthro Dorchester Row Total 11 Allston Allston 10 100 10 29 12 Somerville 40 Somerville 15 110 40 Chelsea 13 Chelsea 100 12 16 Malden 14 Malden 10 15 Demand 10 Column Total 10 16 17 18 Total Cost- 555,805 19 21

EXCEL FORMULAS:

Parameters Tables
	Allston	Somerville	Chelsea	Malden	Capacity
Arlington	130	110	100	150	35
Brookline	70	80	90	60	40
Melrose	120	130	130	100	25
Capacity	35	60	20	40
Logical Capacity	=B7*J8	=C7*K8	=D7*L8	=E7*M8
	Cambridge	Medford	Boston	Quincy	Winthrope	Dorchester
Allston	10	30	30	50	100	50
Somerville	20	40	15	40	110	40
Chelsea	50	40	5	50	100	20
Malden	10	5	50	90	65	40
Demand	10	7	12	3	9	4

Total cost (Cell J19)  =SUMPRODUCT(B4:E6,J4:M6)*0.98+SUMPRODUCT(B11:G14,J11:O14)*1.57+SUM(B8:E8)*10000

Solver parameters:

SSolver Parameters SIS19 Set Objective O Max ⓔMin O Value Of: 0 By Changing aiable Cells: SJS4:SMS6,SJS11:SOS14,SJS8:SMS8 Subject to the Constraints: Add SJS15:SOS15 SBS15:SGS15 Change SPS11 :SPS14 = SJ57:SMS7 SJS8:SMS8binary Delete Reset All Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. Solve Close

Allston and Chelsea warehouses should be kept open

Percentage of total available warehouse capacity used of Allston = 29/35 = 83 %

Percentage of total available warehouse capacity used of Chelsea = 16/20 = 80 %

Total cost = $ 555,805