Note- I tried to solve and below is the answer provided please let me know this is okay or not? and I am stuck at last parts d and e. please solve d and e and modify the answer if need/ if I'm wrong. complete d and e in excel sheet and share the excel sheet/ screenshot of excel sheet.

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?

TABLE 5-9 Global Demand and Duties for Sleekfon and Sturdyfon

MARKET	N.America	S.America	Europe (Eu)	Europe (Non-Eu)	Japan	Rest of Asia /Australia	Africa
Sleekfon demand	10	4	20	3	2	2	1
Sturdyfon Demand	12	1	4	8	7	3	1
Import Duties (%)	3	20	4	15	4	22	25
TABLE 5-9 Global Demand for Sleekfon and Sturdyfon

		Capacity	FixedCost/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
TABLE 5-10 Plant Capacities and Costs for Sleekfon and Sturdyfon

	N. America	S. America	Europe (Eu)	Europe (Non-Eu)	Japan	Rest of Asia/ Australia	Africa
N. America	1.00	1.50	1.50	1.80	1.70	2.00	2.20
S. America	1.50	1.00	1.70	2.00	1.90	2.20	2.20
Europe (EU)	1.50	1.70	1.00	1.20	1.80	1.70	1.40
Europe (non Eu)	1.80	2.00	1.20	1.00	1.80	1.60	1.50
Japan	1.70	1.90	1.80	1.80	1.00	1.20	1.90
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
TABLE 5-11 Transportation costs Between regions ($ per unit)

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 costc_ijbefore we plug it into the optimization model. Variable costc_ijis 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: