I have solved first two questions,I need solution to question 3 & 4use solver for excel and give...

Question

I have solved first two questions,I need solution to question 3 & 4use solver for excel and give answers on word document. Use my excel provided in attachement. The important thing is you should know how to build constraints in solver.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 carrier, and SatTV, the largest satellite TV provider in Europe, completed their 50  billion 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 Current 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 carrying 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 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 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  be 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  because only 80 percent of the fixed cost is saved.  Study Questions  1. What is the annual cost if Lightning uses the current 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 Current Distribution Network 	The Network Options   _EVSCRATCH ev_HiddenInfo 	Optimize	ERROR:#REF!				MACROS			FORMAT	3		RISKOPT/obj 	FindThe	1	0			Start	FALSE		L.FORMULA			MaxIter 	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 	PauseOnErr	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 current 	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

Dr Shweta · Accepted Answer

Screenshot of Solver
Ans 3. Here we believed that the warehouses are not flexible as it’s the case of long-term demand and flexibility option is only mentioned in case of short-term demand.

I have solved first two questions, I need solution to question 3 & 4 use solver for excel and give answers on word document. Use my excel provided in attachement. The important thing is you should...

Answer To: I have solved first two questions, I need solution to question 3 & 4 use solver for excel and give...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment