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 125,000 the first year, 250,000 the second, and 375,000 the third. However, the company recognizes that actual sales may differ by 15%. The net profit per item sold is $550.

The company has two plans to produce the units:

Plan A. Build a single plant today that could produce up to 375,000 units. Construction would cost $290 million.

Plan B. Build three 125,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 $100 million.

Construction for all plants are completed within a year. 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 low as 318,750 or as high as 431,250 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.

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 125,000 in year 1, 250,000 in year 2 and 375,000 in year 3).

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

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?

Use an interest rate of 9%. 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.

Project 1 of 2