Trendfon and Sharpfon are two major phone manufacturers that have recently merged. Their current market sizes are as shown in Table 1. All demand is in millions of units.

Trendfon has three production facilities in Europe (EU), North America, and South America. Sharpfon also has three production facilities in Europe (EU), North America, and 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 2.

Transportation costs between regions are as shown in Table 3. All transportation costs are shown in $ 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 Europe (EU) to Africa has a fixed cost per unit of capacity of $5.00, a variable production cost of $6.20, and a transportation cost of $2.40. As region changes a 25 percent import duty is applied on $13.60 (5.00+6.20+2.40) to give a total cost on import of $17.00. For the questions below, assume that market demand is as in Table 1.

Table 1: Global Demand and Import Duties

Markets	N. America	S. America	Europe (EU)	Europe (Non-EU)	Japan	Rest of Asia/ Australia	Africa
Trendfon Demand	11	5	20	3	2	2	1
Sharpfon demand	12	1	4	8	7	3	1
Total	23	6	24	11	9	5	2
Import duties (%)	3	20	4	15	4	22	25

Table 2: Plant Capacities and Costs

		Capacity	Fixed cost / year	Variable cost/unit
	Europe (EU)	20	100	6.2
Trendfon	N. America	20	100	5.7
	S. America	10	60	5.5
	Europe (EU)	20	100	6.2
Sharpfon	N. America	20	110	5.7
	Rest of Asia	10	60	5.2

Table 3 Transport costs between regions ($ per Unit)

	N. America	S. America	Europe (EU)	Europe (Non EU)	Japan	Rest of Asia/ Australia	Africa
N. America	1	2.5	2.5	2.8	2.7	3	3.2
S. America	2.5	1	2.7	3	2.9	3.2	3.2
Europe (eu)	2.5	2.7	1	2.2	2.8	2.7	2.4
Europe (non eu)	2.8	3	2.2	1	2.8	2.6	2.5
Japan	2.7	2.9	2.8	2.8	1	2.2	2.9
Rest of Asia / Australia	3	3.2	2.7	2.6	2.2	1	2.8
Africa	3.2	3.2	2.4	2.5	2.9	2.8	1

The merged company has estimated that scaling back one of the 20-million-unit plant to 10 million units can save 30 percent in fixed costs. Variable costs at a scaled-back plant are unaffected.

1. What is the lowest cost achievable for the production and distribution network prior to the merger?
2. What is the lowest cost achievable for the production and distribution network after the merger if none of the plants are scaled down?
3. What is the lowest cost achievable for the production and distribution network after the merger if one of the 20 million units of capacity plants is scaled to 10 million units of capacity? Which plants needs to be scaled down?
4. How is the cost affected if all duties are reduced to 0 without any plants being scaled down?

Need step by step solution help using Excel Solver.