Need Help with this question to solve with Excel Sheet thanks

6. StayFresh, a manufacturer of refrigerators in India, has two plants one in Mumbai and the other in Chennai. Each plant has a 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 produc- tion and transport costs (in thousands of rupees; 1 U.S. dollar is worth about 45 rupees) per refrigerator from each potential production site to each market are as shown in Table 5-12 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 bilion rupees Assume that StayFresh plans to meet all demand (prices are sufficiently high) and that capacity for each year must be in place by the beginning of the year. Also assume that the cost for the fifth year will continue for the next 10 years, that is, years 6 to 15. The problem can now be solved for different discount factors. To begin with, assume a discount factor of 0.2, that is, 1 rupee spent next year is worth 1-0.2 0.8 rupee this year a. How should the production network for the company b. How d c. How does your decision change for a discount factor of d. What investment strategy do you recommend for the evolve over the next five years? 15 percent? 25 percent? 0.250.15? company? oes your answer change if the anticipated g is Table 5-12 Production and Transport Cost ('000s Rupees) per Refrigerator North 20 East West South Chennai Delhi Kolkata Mumbai 20 20 19 20 PS1 Data -Excel File Home Insert Page Layout Formulas Data Review View FactSet Acrobat Tell me what you want to do. 129 2 TABLE 5-12: Production and Transport Cost per Unit ('000s of Rupees) South Current Capacity North East West Discount Factor 4 5 Chennai 6 Delhi 7 Kolkata 8 Mumbai 20 19 17 17 20 300 18 18 17 100 20 19 17 20 300 Current Demand 9 (000s units) 10 11 Growth rate 12 13 Year 1 demand 14 Year 2 demand 15 Year 3 demand 16 Year 4 demand 17 Year 5 demand 18 19 20 21 Cost of 150,000 unit plant ("000,000 Rupees)- Cost of 300,000 unit plant ('000,000 Rupees)- 120.0 144.0 172.8 207.4 248.8 60.0 72.0 86.4 103.7 124.4 180.0 216.0 259.2 311.0 373.2 180.0 216.0 259.2 311.0 373.2 25 27 6. StayFresh, a manufacturer of refrigerators in India, has two plants one in Mumbai and the other in Chennai. Each plant has a 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 produc- tion and transport costs (in thousands of rupees; 1 U.S. dollar is worth about 45 rupees) per refrigerator from each potential production site to each market are as shown in Table 5-12 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 bilion rupees Assume that StayFresh plans to meet all demand (prices are sufficiently high) and that capacity for each year must be in place by the beginning of the year. Also assume that the cost for the fifth year will continue for the next 10 years, that is, years 6 to 15. The problem can now be solved for different discount factors. To begin with, assume a discount factor of 0.2, that is, 1 rupee spent next year is worth 1-0.2 0.8 rupee this year a. How should the production network for the company b. How d c. How does your decision change for a discount factor of d. What investment strategy do you recommend for the evolve over the next five years? 15 percent? 25 percent? 0.250.15? company? oes your answer change if the anticipated g is Table 5-12 Production and Transport Cost ('000s Rupees) per Refrigerator North 20 East West South Chennai Delhi Kolkata Mumbai 20 20 19 20 PS1 Data -Excel File Home Insert Page Layout Formulas Data Review View FactSet Acrobat Tell me what you want to do. 129 2 TABLE 5-12: Production and Transport Cost per Unit ('000s of Rupees) South Current Capacity North East West Discount Factor 4 5 Chennai 6 Delhi 7 Kolkata 8 Mumbai 20 19 17 17 20 300 18 18 17 100 20 19 17 20 300 Current Demand 9 (000s units) 10 11 Growth rate 12 13 Year 1 demand 14 Year 2 demand 15 Year 3 demand 16 Year 4 demand 17 Year 5 demand 18 19 20 21 Cost of 150,000 unit plant ("000,000 Rupees)- Cost of 300,000 unit plant ('000,000 Rupees)- 120.0 144.0 172.8 207.4 248.8 60.0 72.0 86.4 103.7 124.4 180.0 216.0 259.2 311.0 373.2 180.0 216.0 259.2 311.0 373.2 25 27