Lab Assessment 3 (15%) Location Decision model
Managing a Merger at Lightning Networks
After receiving regulatory approval from the European Union, Lightning Networks, a major
wireless ca
ier, and SatTV, the largest satellite TV provider in Europe, completed their 50
illion euro merger in 2016. After initial scepticism when the deal was first announced, analysts
had warmed to the idea of synergies in the merger. Lightning expected to benefit from the large
customer base of SatTV and the company announced that it expected significant annual cost
savings within three years of the merger. Simone Durand, senior VP of supply chain at Lightning,
was charged with identifying some cost reduction opportunities. She decided to focus her initial
attention on the distribution networks the two companies used to fulfill demand for installation
and repair products. The merger offered an opportunity to combine the two distribution
networks.
The Cu
ent Distribution Network
Any new installation or repair by Lightning or SatTV required a set of products for the technician
to complete the job. Rather than ca
ying these products with technicians, both companies had
decided to centralize product inventories in a few locations. Annual product demand for the
two companies across six regions in Europe was as shown in Table 5-16.
TABLE 5-16 Annual Demand in Europe for Lightning Networks (wireless) and SatTV (satellite)
Zone
Wireless
Demand
Satellite
Demand
Zone
Wireless
Demand
Satellite
Demand
Northwest 200,000 120,000 Middle South 120,000 120,000
Southwest 100,000 100,000 Northeast 150,000 110,000
Middle North 220,000 100,000 Southeast 90,000 100,000
Lightning had served its product needs from three warehouses located in Madrid, Spain;
Rotterdam, Nether lands; and Krakow, Poland. SatTV had served its product needs from three
warehouses located in Toulouse, France; Munich, Germany; and Budapest, Hungary. Each facility
was specialized to handle either wireless or satellite prod ucts because of the historical focus of
the company it belonged to. The specialization, capacity, and annual fixed cost for each facility
were as shown in Table 5-17. The capacity of each warehouse is given in terms of how much
annual demand it can handle. From Table 5-17, observe that the Madrid warehouse can serve
demand of up to 370,000 units.
TABLE 5-17 Warehouse Specialization, Capacities, and Fixed Costs
Location Specialization Capacity Fixed Cost (euro/year)
Madrid Wireless 370,000 600,000
Rotterdam Wireless 420,000 650,000
Krakow Wireless 310,000 520,000
Toulouse Satellite 280,000 475,000
Munich Satellite 290,000 488,000
Budapest Satellite 250,000 425,000
The variable cost of shipping one unit (either wireless or satellite) from each warehouse location to
each market is shown in Table 5-18.
TABLE 5-18 Variable Distribution Cost per Unit in Euro
Northwest
Southwest
Middle
North
Middle
South
Northeast
Southeast
Madrid XXXXXXXXXX XXXXXXXXXX
Rotterdam XXXXXXXXXX XXXXXXXXXX
Krakow XXXXXXXXXX XXXXXXXXXX
Toulouse XXXXXXXXXX XXXXXXXXXX
Munich XXXXXXXXXX XXXXXXXXXX
Budapest XXXXXXXXXX XXXXXXXXXX
The Network Options
Simone had a short term and a longÂterm decision to make. In the short term, she had to decide
whether to make all the warehouses flexible or not. Making all warehouses flexible required an
investment equivalent to an additional annual cost of 200,000 euro. Flexible warehouses, however, could
e used to serve demand for both wireless and satellite products.
In the longer term, Simone had to decide whether to restructure the distribution network. She could
choose to close some warehouses, leave others open as they were, or double the capacity of some
warehouses. Dou bling the capacity of a warehouse would increase its annual fixed cost by 80
percent. Thus, if the capacity of the Madrid warehouse was doubled, its annual fixed cost would be
900,000 euro.
Closing a warehouse would also incur some cost, thus reducing the annual fixed cost that could be
saved. Simone's team estimated that closing a warehouse would save 80 percent of the annual fixed
cost. Thus, closing the Madrid warehouse would still result in an annual cost of 100,000 euro
ecause only 80 percent of the fixed cost is saved.
Study Questions
1. What is the annual cost if Lightning uses the cu
ent network (with warehouses specialisations
as in Table 5Â17) optimally to meet European demand?
2. Should Simone make all warehouses flexible given the additional cost of 200,000 euro per
year?
3. What supply chain network configuration do you recomÂmend for the long term if demand is
as in Table 5Â16? Should any warehouses be closed? Should any warehouses see their capacity
doubled?
4. What supply chain network configuration do you recommend for the long term if the
Northeast and Southeast demand is expected to increase by 30 percent while all other
demands remain as in Table 5Â16? Should any warehouses be closed? Should any warehouses
see their capacity doubled?
Managing a Merger at Lightning Networks
The Cu
ent Distribution Network
The Network Options
_EVSCRATCH
ev_HiddenInfo
Optimize ERROR:#REF! MACROS FORMAT 3 RISKOPT/obj
FindThe 1 0 Start FALSE L.FORMULA MaxIte
Stop Trials FALSE 1000 BeforeCalc FALSE SmartStop
Stop Minutes FALSE 5 AfterCalc FALSE SameSeed
Stop Change FALSE 100 1 TRUE EndTrial FALSE SampleType
Stop Formula FALSE Finish FALSE MacroBeforeSim
Pop. Size 50 MacroAfterSim
UNUSED UNUSED Seed TRUE 1
Up. Display 3
PauseOnE
FALSE
Gen.Log FALSE
Graph FALSE
#Chrom. 2 #Const. 2
Meth+OtherOps Mut.+Op Cross+Op Descr. TimeBlocks Const #Ranges Range Min Max Flags ROFUNCEVAL RISKOPT DEVEVAL EVAL Type Entry M. Form. Description LeftVal LeftOp Ref. RightOp RightVal PenaltyFct ROevaltime ROfunc ROparam
RECIPE 0.1 0.5 0 1 ERROR:#REF! 0 100 False,False,False 2 1 0 2 ERROR:#REF! 2 20
RECIPE 0.1 0.5 0 1 ERROR:#REF! 0 1 True,False,False 2 1 0 6 ERROR:#REF! 5 0
optimal cu
ent
Warehouse Capacity & Fixed Cost
Location Capacity Annual Fixed Cost
Madrid 370000 600000
Rotterdam 420000 650000
Krakow 310000 520000
Toulouse 280000 475000
Munich 290000 488000
Budapest 250000 425000
Regional Demand By Product
Northwest Southwest Middle North Middle South Northeast Southeast
Wireless 200000 100000 220000 120000 150000 90000
Satellite 120000 100000 100000 120000 110000 100000
Distribution Cost
Northwest Southwest Middle North Middle South Northeast Southeast
Madrid 2.50 1.50 3.00 2.75 4.00 4.50
Rotterdam 1.75 3.00 1.50 3.00 2.50 3.50
Krakow 3.25 4.00 2.50 3.00 2.00 2.50
Toulouse 2.00 2.00 2.75 2.50 3.75 4.00
Munich 2.25 3.00 2.25 2.50 2.75 3.00
Budapest 3.50 3.75 2.50 2.50 2.50 2.00
Distribution Quantity Variables Wireless Distribution Quantity Variables Satellite
Northwest Southwest Middle North Middle South Northeast Southeast Capacity Constraint Northwest Southwest Middle North Middle South Northeast Southeast Capacity Constraint
Madrid 0 100000 0 120000 0 0 220000 Madrid
Rotterdam 200000 0 220000 0 0 0 420000 Rotterdam
Krakow 0 0 0 0 150000 90000 240000 Krakow
Toulouse Toulouse 120000 100000 0 60000 0 0 280000
Munich Munich 0 0 100000 60000 0 0 160000
Budapest Budapest 0 0 0 0 110000 100000 210000
Demand Constraint 200000 100000 220000 120000 150000 90000 Demand Constraint 120000 100000 100000 120000 110000 100000
Plant Fixed Cost (Wireless) 1770000
Plant Fixed Cost (Satellite) 1388000
Distribution Wireless 1685000
Distribution Satellite 1440000
Total Cost 6283000
optimal flexible
Warehouse Capacity & Fixed Cost
Location Capacity Annual Fixed Cost
Madrid 370000 600000
Rotterdam 420000 650000
Krakow 310000 520000
Toulouse 280000 475000
Munich 290000 488000
Budapest 250000 425000
Regional Demand By Product
Northwest Southwest Middle North Middle South Northeast Southeast
DemandWireless 200000 100000 220000 120000 150000 90000
DemandSatellite 120000 100000 100000 120000 110000 100000
Distribution Cost
Northwest Southwest Middle North Middle South Northeast Southeast
Madrid 2.50 1.50 3.00 2.75 4.00 4.50
Rotterdam 1.75 3.00 1.50 3.00 2.50 3.50
Krakow 3.25 4.00 2.50 3.00 2.00 2.50
Toulouse 2.00 2.00 2.75 2.50 3.75 4.00
Munich 2.25 3.00 2.25 2.50 2.75 3.00
Budapest 3.50 3.75 2.50 2.50 2.50 2.00
Distribution Quantity Variables Wireless Distribution Quantity Variables Satellite
Northwest Southwest Middle North Middle South Northeast Southeast Northwest Southwest Middle North Middle South Northeast Southeast Capacity Constraint
Madrid 0 100000 0 0 0 0 Madrid 0 100000 0 0 0 0 200000
Rotterdam 0 0 220000 0 0 0 Rotterdam 100000 0 100000 0 0 0 420000
Krakow 0 0 0 0 150000 0 Krakow 0 0 0 0 110000 0 260000
Toulouse 200000 0 0 60000 0 0 Toulouse 20000 0 0 0 0 0 280000
Munich 0 0 0 60000 0 0 Munich 0 0 0 60000 0 0 120000
Budapest 0 0 0 0 0 90000 Budapest 0 0 0 60000 0 100000 250000
Wireless Demand Constraint 200000 100000 220000 120000 150000 90000 Sat Demand Constraint 120000 100000 100000 120000 110000 100000
Plant Fixed Cost 3158000
Distribution Wireless 1660000
Distribution Satellite 1235000
Total Cost 6053000
Cost Savings 230000
Annual Fixed Cost 200000
Annual Savings 30000
Total Savings 90000
Sensitivity Report 1
Microsoft Excel 16.0 Sensitivity Report
Worksheet: Lab Case 3
Report Created: 23/05/2022 3:46:17 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$26 Madrid Northwest 0 0.5 2.5 1E+30 0.5
$C$26 Madrid Southwest 100000 0 1.5 0.5 1.5
$D$26 Madrid Middle North 0 1.25 3 1E+30 1.25
$E$26 Madrid Middle South 0 0.25 2.75 1E+30 0.25
$F$26 Madrid Northeast 0 2 4 1E+30 2
$G$26 Madrid Southeast 0 2.5 4.5 1E+30 2.5
$B$27 Rotterdam Northwest 0 0 1.75 1E+30 0
$C$27 Rotterdam Southwest 0 1.75 3 1E+30 1.75
$D$27 Rotterdam Middle North 220000 0 1.5 0.5 1.75
$E$27 Rotterdam Middle South 0 0.75 3 1E+30 0.75
$F$27 Rotterdam Northeast 0 0.75 2.5 1E+30 0.75
$G$27 Rotterdam Southeast 0 1.75 3.5 1E+30 1.75
$B$28 Krakow Northwest 0 1.25 3.25 1E+30 1.25
$C$28 Krakow Southwest 0 2.5 4 1E+30 2.5
$D$28 Krakow Middle North 0 0.75 2.5 1E+30 0.75
$E$28 Krakow Middle South 0 0.5 3 1E+30 0.5
$F$28 Krakow Northeast 150000 0 2 0.5 2
$G$28 Krakow Southeast 0 0.5 2.5 1E+30 0.5
$B$29 Toulouse Northwest 200000 0 2 0 2
$C$29 Toulouse Southwest 0 0.5 2 1E+30 0.5
$D$29 Toulouse Middle North 0 1 2.75 1E+30 1
$E$29 Toulouse Middle South 60000 0 2.5 0 0.25
$F$29 Toulouse Northeast 0 1.75 3.75 1E+30 1.75
$G$29 Toulouse Southeast 0 2 4 1E+30 2
$B$30 Munich Northwest 0 0.25 2.25 1E+30 0.25
$C$30 Munich Southwest 0 1.5 3 1E+30 1.5
$D$30 Munich Middle North 0 0.5 2.25 1E+30 0.5
$E$30 Munich Middle South 60000 0 2.5 0 0
$F$30 Munich Northeast 0 0.75 2.75 1E+30 0.75
$G$30 Munich Southeast 0 1 3 1E+30 1
$B$31 Budapest Northwest 0 1.5 3.5 1E+30 1.5
$C$31 Budapest Southwest 0 2.25 3.75 1E+30 2.25
$D$31 Budapest Middle North 0 0.75 2.5 1E+30 0.75
$E$31 Budapest Middle South 0 0 2.5 1E+30 0
$F$31 Budapest Northeast 0 0.5 2.5 1E+30 0.5
$G$31 Budapest Southeast 90000 0 2 0.5 2
$K$26 Madrid Northwest 0 0.5 2.5 1E+30 0.5
$L$26 Madrid Southwest 100000 0 1.5 0.5 1.5
$M$26 Madrid Middle North 0 1.25 3 1E+30 1.25
$N$26 Madrid Middle South 0 0.25 2.75 1E+30 0.25
$O$26 Madrid Northeast 0 2 4 1E+30 2
$P$26 Madrid Southeast 0 2.5 4.5 1E+30 2.5
$K$27 Rotterdam Northwest 100000 0 1.75 0 0.5
$L$27 Rotterdam Southwest 0 1.75 3 1E+30 1.75
$M$27 Rotterdam Middle North 100000 0 1.5 0.5 1.75
$N$27 Rotterdam Middle South 0 0.75 3 1E+30 0.75
$O$27 Rotterdam Northeast 0 0.75 2.5 1E+30 0.75
$P$27 Rotterdam Southeast 0 1.75 3.5 1E+30 1.75
$K$28 Krakow Northwest 0 1.25 3.25 1E+30 1.25
$L$28 Krakow Southwest 0 2.5 4 1E+30 2.5
$M$28 Krakow Middle North 0 0.75 2.5 1E+30 0.75
$N$28 Krakow Middle South 0 0.5 3 1E+30 0.5
$O$28 Krakow Northeast 110000 0 2 0.5 2
$P$28 Krakow Southeast 0 0.5 2.5 1E+30 0.5
$K$29 Toulouse Northwest 20000 0 2 0.25 0
$L$29 Toulouse Southwest 0 0.5 2 1E+30 0.5
$M$29 Toulouse Middle North 0 1 2.75 1E+30 1
$N$29 Toulouse Middle South 0 0 2.5 1E+30 0
$O$29 Toulouse Northeast 0 1.75 3.75 1E+30 1.75
$P$29 Toulouse Southeast 0 2 4 1E+30 2
$K$30 Munich Northwest 0 0.25 2.25 1E+30 0.25
$L$30 Munich Southwest 0 1.5 3 1E+30 1.5
$M$30 Munich Middle North 0 0.5 2.25 1E+30 0.5
$N$30 Munich Middle South 60000 0 2.5 0 0
$O$30 Munich Northeast 0 0.75 2.75 1E+30 0.75
$P$30 Munich Southeast 0 1 3 1E+30 1
$K$31 Budapest Northwest 0 1.5 3.5 1E+30 1.5
$L$31 Budapest Southwest 0 2.25 3.75 1E+30 2.25
$M$31 Budapest Middle North 0 0.75 2.5 1E+30 0.75
$N$31 Budapest Middle South 60000 0 2.5 0 0.5
$O$31 Budapest Northeast 0 0.5 2.5 1E+30 0.5
$P$31 Budapest Southeast 100000 0 2 0.5 2
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$32 Wireless Demand Constraint Northwest 200000 2 200000 60000 60000
$C$32 Wireless Demand Constraint Southwest 100000 1.5 100000 170000 100000
$D$32 Wireless Demand Constraint Middle North 220000 1.75 220000 60000 20000
$E$32 Wireless Demand Constraint Middle South 120000 2.5 120000 170000 60000
$F$32 Wireless Demand Constraint Northeast 150000 2 150000 50000 150000
$G$32 Wireless Demand Constraint Southeast 90000 2 90000 60000 60000
$K$32 Sat Demand Constraint Northwest 120000 2 120000 60000 20000
$L$32 Sat Demand Constraint Southwest 100000 1.5 100000 170000 100000
$M$32 Sat Demand Constraint Middle North 100000 1.75 100000 60000 20000
$N$32 Sat Demand Constraint Middle South 120000 2.5 120000 170000 60000
$O$32 Sat Demand Constraint Northeast 110000 2 110000 50000 110000
$P$32 Sat Demand Constraint Southeast 100000 2 100000 60000 60000
$R$26 Madrid Capacity Constraint 200000 0 370000 1E+30 170000
$R$27 Rotterdam Capacity Constraint 420000 -0.25 420000 20000 60000
$R$28 Krakow Capacity Constraint 260000 0 310000 1E+30 50000
$R$29 Toulouse Capacity Constraint 280000 0 280000 60000 60000
$R$30 Munich Capacity Constraint 120000 0 290000 1E+30 170000
$R$31 Budapest Capacity Constraint 250000 0 250000 60000 60000
1
2
3
4
5
6
7
8
9
10
A
Warehouse Capacity & Fixed Cost
Location
Madrid
Rotterdam
Krakow
Toulouse
Munich
Budapest
Regional Demand By Product