SOLVE USING EXCEL - SOLVER (STEP BY STEP)

StayFresh, a manufacturer of refrigerators in India, has two plantsone in Mumbai and the other in Chennai. Each plant has an annual capacity of 300,000 units. The two plants serve the entire country, which is divided into four regional markets: the north, with a demand of 100,000 units; the west, with a demand of 150,000 units; the south, with a demand of 150,000 units; and the east, with a demand of 50,000 units. Two other potential sites for plants include Delhi and Kolkata. The variable production and transport costs (in thousands of rupees) per refrigerator from each potential production site to each market are as shown in the table below. Production and Transport Cost (Thousands of Rupees) per Refrigerator North East West South Chennai 20 19 17 15 Delhi 15 18 17 20 Kolkata 18 15 20 19 Mumbai 17 20 15 17 StayFresh is anticipating a compounded growth in demand of 20 percent per year for the next five years and must plan its network investment decisions. Demand is anticipated to stabilize after five years of growth. Capacity can be added in increments of either 150,000 or 300,000 units. Adding 150,000 units of capacity incurs a one-time cost of 2 billion rupees, whereas adding 300,000 units of capacity incurs a one-time cost of 3.4 billion rupees. StayFresh plans to meet the increase in demand in a five-year plan.

A. How is the current production/distribution network for the company?

B. How should the production network for the company evolve five years from now?

C. How does your answer change if all the production and transport costs are doubled in 5-years?

D. How does your answer change if the anticipated growth is 15 percent?

E. How does your answer change if the anticipated growth is 25 percent?