Sleekfon and Sturdyfon are two major cell phone manufacturers that have recently merged. Their current market sizes are as shown in Table 5-9. All demand is in millions of units.

Sleekfon has three production facilities in Europe (EU), North America, and South America. Sturdyfon also has three production facilities in Europe (EU), North America, and the rest of Asia/Australia. The capacity (in millions of units), annual fixed cost (in millions of $), and variable production costs ($ per unit) for each plant are as shown in Table 5-10.

Transportation costs between regions are as shown in Table 5-11. All transportation costs are shown in dollars per unit. Duties are applied on each unit based on the fixed cost per unit capacity, variable cost per unit, and transportation cost. Thus, a unit currently shipped from North America to Africa has a fixed cost per unit of capacity of $5.00, a variable production cost of $5.50, and a transportation cost of $2.20. The 25 percent import duty is thus applied on $12.70 (5.00 + 5.50 + 2.20) to give a total cost on import of $15.88. For the questions that follow, assume that market demand is as in Table 5-9.

The merged company has estimated that scaling back a 20-million-unit plant to 10 million units saves 30 percent in fixed costs. Variable costs at a scaled-back plant are unaffected. Shutting a plant down (either 10 million or 20 million units) saves 80 percent in fixed costs. Fixed costs are only partially recovered because of severance and other costs associated with a shutdown.

FOR THIS REVIEW QUESTION I NEED TO FIGURE OUT 1-5.

a. What is the lowest cost achievable for the production and distribution network prior to the merger? Which plants serve which markets?

b. What is the lowest cost achievable for the production and distribution network after the merger if none of the plants is shut down? Which plants serve which markets?

c. What is the lowest cost achievable for the production and distribution network after the merger if plants can be scaled back or shut down in batches of 10 million units of capacity? Which plants serve which markets?

d. How is the optimal network configuration affected if all duties are reduced to 0?

e. How should the merged network be configured?

Required- All of the sections in question should be answered in an excel file , but different worksheets ( Screenshot of excel sheet should be fine if you can't share excel file here

Note- I tried to solve and below is the answer please let me know this is okay and I am stuck at d and e. please solve and e and correct the other one if I'm wrong. complete d and e in excel sheet and share the excel sheet/ screenshot of excel sheet.

a)

Starting from the basic models in (a), we will build more advanced models in the subsequent parts of this question. Prior to merger, Sleekfon and Sturdyfon operate independently, and so we need to build separate models for each of them.

Optimization model for Sleekfon:

n = 3: Sleekfon production facilities.

m = 7: number of regional markets.

D_j = Annual market size of regional market j

K_i = maximum possible capacity of production facility i

c_ij = Variable cost of producing, transporting and duty from facility i to market j

f_i = Annual fixed cost of facility i

x_ij = Number of units from facility i to regional market j.

It should be integral and non-negative.

Please note that we need to calculate the variable cost c_ijbefore we plug it into the optimization model. Variable cost c_ij is calculated as follows:

c_ij = production cost per unit at facility i + transportation cost per unit from facility i to market j + duty*( production cost per unit at facility i + transportation cost per unit from facility i to market j + fixed cost per unit of capacity)

SYMBOL	INPUT	CELL
Dj	Annual market size of regional market j	B4:H4
Ki	maximum possible capacity of production facility i	C12:C14
cij	Variable cost of producing, transporting and duty from facility i to market j	B22:H28
fi	Annual fixed cost of facility i	D12:D17
xij	Number of units from facility i to regional market j.	C43:I45
obj.	objective function	D48
5.1	demand constraints	J43:J45
5.2	capacity constraints	C46:I46

(Sheet sleekfon in workbook problem5.4)

The above model gives optimal result as in following table:

And we use the same model but with data from Sturdyfon to get following optimal production and distribution plan for Sturdyfon:

(b)

Under conditions of no plant shutdowns, the previous model is still applicable. However, we need to increase the number of facilities to 6, i.e., 3 from Sleekfon and 3 from Sturdyfon. And the market demand at a region needs revised by combining the demands from the two companies. Decision maker has more facilities and greater market share in each region, and hence has more choices for production and distribution plans. The optimal result is summarized in the following table.

(c)

This model is more advanced since it allows facilities to be scaled down or shutdown. Accordingly we need more variables to reflect this new complexity.

Optimization model for Sleekfon:

n = 6: Sleekfon and Sturdyfon production facilities.

m = 7: number of regional markets.

D_j = Annual market size of regional market j, sum of the Sleekfon and Sturdyfon market share.

K_i =capacity of production facility i

L_i =capacity of production facility if it is scaled back

c_ij = Variable cost of producing, transporting and duty from facility i to market j

f_i = Annual fixed cost of facility i

g_i = Annual fixed cost of facility i if it is scaled back

h_i = Shutdown cost of facility i

x_ij = Number of units from facility i to regional market j.

It should be integral and non-negative.

y_i = Binary variable indicating whether to scale back facility i. y_i = 1 means to scale it back, 0 otherwise.

Since two facilities, Sleekfon S America and Sturdyfon Rest of Asia, can not be scaled back, the index i

doesnt include these two facilities.

z_i = Binary variable indicating whether to shutdown facility i. z_i =1 means to shutdown it, 0 otherwise.

(1-y_i z_i) would be the binary variable indicating whether the facility is unaffected.

Please note that we need to calculate the variable cost c_ijbefore we plug it into the optimization model. Variable cost c_ij is calculated as following:

c_ij = production cost per unit at facility i + transportation cost per unit from facility i to market j + duty*( production cost per unit at facility i + transportation cost per unit from facility i to market j + fixed cost per unit of capacity)

And we also need to prepare fixed cost data for the two new scenarios: shutdown and scale back. As explained in the problem description, fixed cost for a scaled back facility is 70% of the original one; and it costs 20% of the original annual fixed cost to shutdown it.

Above model gives optimal solution as summarized in the following table. The lowest cost possible in this model is $988.93, much lower than the result we got in (b) $1066.82. As shown in the result, the Sleekfon N.America facility is shutdown, and the market is mainly served by Sturdyfon N.America facility. The N.America market share is 22, and there are 40 in terms of production capacity, hence it is wise to shutdown one facility whichever is more expensive.

For questions (d) and (e), we need to change the duty to zero and run the optimization model again to get the result. We can achieve this by resetting B7:H7 to zeros in sheet merger (shutdown) in workbook problem5.4.xls.

i solve this , please solve d and e like this please

AutoSave on 12 - Excel File Home Insert Draw Page Layout Formulas Data Review N36 fi E A C D E F G 18 2 12 1 1.6 13 25 Non 26 Japan L. IS 13 18 1 Rest of 2 201 17 16 1.8 Asia 27 Australis 28 Africa 14 29 30 Variable Production Costs, Transportation costs and Duties From Plants to Markets N S Europe America America EU Africa Japan Europe LIE 32 Rest of Asia Australia Boge 888 33 LED 7 1024 7.00 9.00 8.31 1049 10.50 Sleekfon N 34 America 6 50 940 7.40 915 760 10.25 1080 S 35 America 7.18 6301 930 773 10 47 10.80 Europe 30 LEUS 7.18 1024 700 9.00 10:49 10.50 Stumdyfon N 37 Ames 6 50 9:40 749 915 750 10 25 10.88 Rest of AGE 7:36 954 7:17 334 6.65 6.00 9.75 10 Quantity Shipped 41 IN IS Propie Japan Rest of Africa Capacity America America (EU) AA (EU) 20.00 0.00 0.00 0.00 000 Sleeldon N Anna 10.00 0.00 000 3.00 200 200 000 3.00 S 45 America 000 400 000 000 0.00 100 5.00 45 Demand 000 0.00 000 0.00 0.00 000 0.00 47 Total cost for Sleekin $56430 40 sa data sleekfon sturdyfon merger (no shutdown) merger shutdown) Ready . 000 000 09 14 Type here to search o GEN De A 10 -1 Sport D Punt Carties and cost C Vw C 11 10 3 HE ENTE Son E 17 TH 10. Tra IN ADS A 2 DOLL 1 11 1 1 WA 20A 30 Vantare travers 1070 LE 10 EN E + 100 ST E WIDE 03 ADO 1 0 bon daleko E Market Demands and Duties ON Am Am stof Africa Seo 11 Stando Dend 6 Til Trport D 34 11 Prant Capacities and out 90 Ford C Vale Cest E LT Then N 10 50 100 100 15 SN A Festa 17 10 10 Transportation Con IN A) Resto AWA N DOLL 1 3 Am M 3 1 TE 2 20 00 Varia Proton Casa Torten els ane Duero Pons to Mar AD A 1034 be T3 TO 3 60 101 10 IN 1020 700 10 A 4. SAU AT 410 D DE Careers and Co 3 LE 12 SN 20 10 1 1 30 18 30 10 10 17 HI 19 Transportation Cours 20 AN N. 31 N 1 1 1 1 1 1 18 1 2 22 Tipe T 24 Top 11 1 26 NI stof 1 A TA A 20 30 Variable Productions Transport Costa Dumann Mart 1 SE EROV 12 YO 70 N 54 09 TE TE 30 1024 LE 19 SIN 37 00 30 HE SOS th 11 10 Quantity Shop N 300 200 10 TE 00 400 00 0.00 15 One SD 003 NO 001 000 One 000 Go 000 MN Am 11 00 COM 000 SE 40 De 14 Starter Paran Cost 03 V Scal F C Tanco TI 12 30 20 39 SIN 15 10 2 JE SO 14 oot 10 SIN 16 10 10 10 Red Now 15 11 1 22 SI 22 23 17 10 10 Transportation Costa 211 IN A) 21 N 1 15 22 A 3 A 10 24 11 25 N 25 1 23 A 27 Audi Anice V CE 1 10 1 TE TI - 1 12 3 Variable Production costs. Transportation costs and 31 5 Fas 12 - Am V 7 CE 700 TE 100 SonN 3E A 09 OS BEO 5 7 154 10 A Tape EU SN JA 7.88 1020 700 900 831 1049 OSOL 9 7.69 10.89 AL 136 5.00 SO 40 Quantity Shipped 41 JA Call Free Am Ant Red A Shadow Pant added 2 000 50 1100 2.00 000 Obo 1.00 03 SledN 2000 000 000 DO 000 000 000 0.00 tool 15 100 0 000 000 200 000 3 000 15 000 000 0.00 TO 000 1900 100 000 000 StarN Amma 0.00 000 1100 000 0.00 000 000 000 m P 000 000 00 500 000 900 500 000 48 49 A Dand 000 003 000 0.00 0.00 000 000 000 09 51 Total cost forged two TABLE 5-9 Global Demand and Duties for Sleekfon and Sturdyfon Europe Europe Market N. America S. America (EU) (Non-EU) Sleekfon demand 10 4 20 3 Sturdyfon demand 12 1 8 Import duties (%) 3 20 4 15 Rest of Asia/ Australia 2. Africa 1 Japan 2 7 4 4 3 22 1 25 TABLE 5-10 Plant Capacities and Costs for Sleekfon and Sturdyfon Capacity Fixed Cost/Year Variable Cost/Unit Sleekfon Europe (EU) 20 100 6.0 N. America 20 100 5.5 S. America 10 60 5.3 Sturdyfon Europe (EU) 20 100 6.0 N. America 20 100 5.5 Rest of Asia 10 50 5.0 DELL 1.50 Japan 1.70 1.90 TABLE 5-11 Transportation Costs Between Regions ($ per Unit) Europe Europe N. America S. America (EU) (Non-EU) N. America 1.00 1.50 1.80 S. America 1.50 1.00 1.70 2.00 Europe (EU) 1.50 1.70 1.00 1.20 Europe (Non-EU) 1.80 2.00 1.20 1.00 Japan 1.70 1.90 1.80 1.80 Rest of Asia Australia 2.00 2.20 1.70 1.60 Africa 2.20 2.20 1.40 1.50 1.80 Rest of Asia/ Australia 2.00 2.20 1.70 1.60 1.20 1.00 1.80 Africa 2.20 2.20 1.40 1.50 1.90 1.80 1.00 1.80 1.00 1.20 1.90