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 200,000 the first year, 400,000 the second, and 600,000 the third. However, the company recognizes that actual sales may differ by 20%. The company would net $2,000 per item sold (not including unit manufacturing costs).

The company has two plans to produce 600,000 units:

Plan A. Build a single plant today that could produce 600,000 units. Construction would cost $530 million and be finished in a year. The incremental manufacturing costs beyond the capital cost of the plant is expected to be $1,400 per item.

Plan B. Build three 200,000 unit plants, one each year (beginning in year 0), in an effort to match expected annual demand. The capital expenditure for each small plant is $200 million. The smaller plant has a unit manufacturing cost of $1,450.

Note that both plans have drawbacks. Plan A involves a large amount of excess capacity in the first two years until market demand grows; and there is always a chance that demand falls short of expectations. Thus the demand in year 3 might be as high as 480,000 or as low as 720,000 units. Plan B is less efficient. Also, if the demand grows faster than expected, it cannot take advantage of it.

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, and Plan B. Which plan is better? (Assume demand is exactly 200,000 in year 1, 400,000 in year 2 and 600,000 in year 3).

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 for 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. Assume that for Plan B all three plants will be built regardless of actual demand. For each random scenario, what is the best option?

3. Comment on the general trends. Under what conditions does Plan A have a higher value over Plan B. Given the uncertainity in demand, how would go about recommending which facility should be used?

Assumptions for analysis:

Use a discount rate of 9%.

No salvage value for Plan A at year 3. Salvage value for Plan B is $100 million at year 3.

For simplicity, assume that a new technology will replace the current product in the 4th year so that there will be no sales in year 4 and beyond.

This is a really tough question. I have to do this in excel, so I need a tutor to walk me through the steps of doing this in excel.