Question

Glassware is a high-end glass manufacturer. The company is planning to expand its sales operation to the European market. A supply chain analyst within the company picked five locations for potential warehouse sites. These locations are Barcelona Spain, Budapest-Hungary, Helsinki-Finland, Lyon-France, and Munich-Germany. A marketing analyst divided Europe into four general regions; East, West, South, and North. In the accompanying spreadsheet, the fixed cost of opening warehouses, the capacity of the warehouses, transportation cost from warehouses to regions, and demand of regions are provided. You can download the template from here. To increase brand royalty, Glassware wants to match demand for all regions. Also, the company has the following strategical plan. Glassware wants to open at least two warehouses in Europe. Glassware wants at most one warehouse in Lyon and Munich. If the company opens Budapest warehouse, it must also open Barcelona warehouse as well. What should Glassware do? Instruction to complete the problem. . Click here to open the excel template file • Build the spreadsheet without populating the solver screen. • Once finished press the yellow button "CLICK HERE TO SAVE YOUR WORK" and the past the resulting URL in the answer field below. You can paste the link by using CTRL + VIMAC Users use Command+V). PS: Since there is no solver add-in in the Lockdown Browser, you cannot find the optimal solution. However, you can still build the model.

Answer 1

Demand of each region is not specified.

Therefore, for sake of understanding, a hypothetical demand of 10,000 units is considered for each region.If actual demand is different, same spreadsheet model can be used by changing the actual demand of each region.

Spreadsheet model and solution using Excel solver is following
B19 @ fx =SUMPRODUCT(B12:F16,B3:F7) А с E G H 1 K M N 0 Р Q R S Solver Parameters X Set Objective: SB519 To: O Max Min value of: 0 By Changing Variable Cells: SB$12:$F$16 $: Subject to the Constraints: SF512:$F$16 - binary SB$17:$E$17 = $B$8:SE8 SG$12:$G$16 >= 0 SI$12:$I$13 > SK$12:$K$13 SIS14 <- SK$14 Add Change Additional constraints Delete 1 Parameter Table 2 East West South North Fixed Cost Capacity 3 Barcelona 55 43 46 60 70,000 22,000 4 Budapest 40 50 45 55 50,000 18,000 5 Helsinki 45 60 50 30 80,000 15,000 6 Lyon 57 40 37 49 95,000 13,000 7 Munich 43 43 52 37 75,000 24,000 8 Demand 10,000 10,000 10,000 10,000 9 10 Optimal distribution plan Open w/h = Spare 11 East West South North 1, otherwise Capacity 12 Barcelona 0 10,000 2,000 0 1 10,000 13 Budapest 10,000 0 8,000 0 1 0 14 Helsinki 0 0 0 10,000 1 5,000 o o o 0 0 0 16 Munich 0 0 0 0 0 0 17 Column 10,000 10,000 10,000 10,000 18 19 Total Cost = 1,782,000 Reset All LHS RHS 2 Make Unconstrained variables Non-Negative 3 0 0 Select a Solving Method Simplex LP Options 0 1 15 Lyon 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 20

SUM Xfx =SUMPRODUCT(B12:F16,B3:F7) А B с D E F G H JK East West South North Capacity 46 60 43 50 Fixed cost 70000 50000 22000 18000 45 50 60 1 Parameter Tal 2 3 Barcelona 55 4 Budapest 40 5 Helsinki 45 6 Lyon 57 7 Munich 143 8 Demand 10000 9 10 Optimal distril 55 30 49 15000 80000 95000 40 37 13000 124000 75000 43 10000 52 10000 37 10000 Additional const 11 =B2 -D2 -E2 =C2 10000 Open w/h = 1, otherwise = 0 1 10 12 =A3 13 A4 LHS RHS =SUM(F12:F16) >= 2 =F12-F13 >= 0 F1S+F16 <= 1 10000 0 1 Spare Capacity =G3*F12-SUM(B12:E12) =G4*F13-SUM(B13:E13) =G5*F14-SUM(B14:E14) =G6*F15-SUM(B15:E15) |=G7*F16-SUM(B16:E16) 1 2000 0 8000 0 0 10000 0 0 lo 0 =SUM(D12:016) =SUM(E12:E16) 0 10 14 =AS 10 0 15 =A6 0 0 0 0 17 Column Total=SUM(B12:B16) =SUM(C12:016) 18 19 =SUMPRODUCT( B12:F16 , B3:F7) 20 21 16 A7