A manufacturer is considering two investment programs to supply a new transportation-related technology. Market research anticipates rapid market growth: sales are expected to be 100,000 the first year, 200,000 the second, 300,000 the third and 400,000 the fourth year. However, the company recognizes that actual sales may differ by 15%.

The net profit per item sold is $750. The company has three plans to produce the units:

Plan A. Build a single plant today that could produce up to 400,000 units. Construction would cost $360 million (in year 0). The M&O cost of the plant is $200,000 and increases by $700,000 per year.

Plan B. Build two plants, one in year 0 and one in year 2, which can produce up to 200,000 units. The cost to build each plant is $190 million. The M&O per plant is $90,000 per year and increases by $6,000 per year.

Plan C. Build four 100,000 unit plants, one each year (beginning in year 0 and ending in year 3), in an effort to match expected annual demand. The cost to build each plant is $90 million. The M&O per plant is $40,000 per year and increases by $5,000 per year.

Construction for all plants are completed within a year. There are pros and cons of each plan. For example, Plan A involves a large amount of excess capacity in the earlier years until market demand grows; and there is always a chance that demand falls short of expectations. For example, demand in year 4 might be as low as 360,000 or as high as 440,000 units.

The CFO asks you to prepare spreadsheets to analyze this decision. As the company will want to carry out extensive sensitivity analyses on the spreadsheet, all the input variables must be set in an input sheet, so that the rest of the spreadsheet will be an automated black-box that generates the required results.

1. Using only the expected demands each year, set up a spreadsheet to calculate the net present worth for Plan A, B and C. Which plan is better? (Assume demand is exactly 100,000 in year 1, 200,000 in year 2, 300,000 in year 3 and 400,000 in year 4).

2. Since demand is not exact, you decide to generate 20 random scenarios for the demand. Use the RANDBETWEEN function in excel and then copy and paste the values so that they do not change as you manipulate the sheet. The lower unit of RANDBETWEEN should be the minimum estimate for units sold that year and the upper unit the higher estimate for units sold. Calculate the present worth for each plan. For each random scenario, what is the best option?

3. Comment on the general trends. Which plan is best and why? Given the uncertainity in demand, how would go about recommending which facility should be used? Use an interest rate of 9%. For simplicity, assume that a new technology will replace the current product in the 5th year so that there will be no sales in year 5 and beyond.