Question Information:

• CoolWipes was founded in the late 1980s and produces

(i) baby wipes

(ii) diaper ointment

• The company currently has one factory in Chicago that produces both products:

Capacity and Cost data for factory in Chicago
	Capacity (Millions unit)	Annual Fixed Cost (Millions $)	Variable (production) cost ($/unit)
Wipe Line	5	5	10
Ointment Line	1	1.5	20

• Demand for two products are as follows:

Regional Demand at Cool Wipes (Thousands)
Zones	Wipes Demand	Ointment Demand
Northwest	500	50
Southwest	700	90
Upper Midwest	900	120
Lower Midwest	800	65
Northeast	1000	120
Southeast	600	70

• Due to significant transportation cost, CoolWipes wants to take a quick decision on building one or more new plants

• The potential sites to build the new plants

1. Princeton (New Jersey)

2. Atlanta

3. Los Angeles

• The data on capacity, fixed cost, and variable costs of the new factory are as follows:

Capacity and Cost data for new factory
	Capacity (Millions unit)	Annual Fixed Cost (Millions $)	Variable (production) cost ($/unit)
Wipe line	2	2.5	8
Ointment line	1	2	20

• The transportation costs per unit are shown below

Transportation Cost per Unit
Demand Region	Chicago	Princeton	Atlanta	Los Angeles
Northwest	$ 6.32	$ 6.60	$ 6.72	$ 4.36
Southwest	$ 6.32	$ 6.60	$ 6.48	$ 3.68
Upper Midwest	$ 3.68	$ 5.76	$ 5.92	$ 6.32
Lower Midwest	$ 4.04	$ 5.92	$ 4.08	$ 6.32
Northeast	$ 5.76	$ 3.68	$ 4.04	$ 6.72
Southeast	$ 5.96	$ 4.08	$ 3.64	$ 6.60

QUESTION:

If the manager could design a new network from scratch (assume the manager did not have the Chicago plant but could build it at the cost and capacity specified in the case), what production network would you recommend?

NOT A PART OF THE QUESTION, JUST GENERAL INFO:

* I understand this question has been answered on Chegg before. However, the values have been changed a little for this question *

* PLEASE USE EXCEL SOLVER TO SOLVE THIS QUESTION *

* Screenshots of excel solver would be much appreciated *