Shao Company needs to launch a new model of driverless car. It would cost $200,000,000 today to buy all necessary equipment. The driverless car project would last for 5 years. Shao Company believes it could sell 5,000 units in the first year, and the units sold will grow by 5% starting from the second year. The car could sell for $65,000/unit in each year, and the company estimates that variable costs would amount to $40,000/ unit and non-variable costs would be $30,000,000 in each year. Net working capital is needed at the beginning of a year in an amount equal to 20% of the years projected sales.

5-Year MACRS depreciation schedule will be used for the equipment with depreciation rates at: 20%, 32%, 19.20%, 11.52%, 11.52%, and 5.76%. The estimated market value of the equipment at the end of year 5 is $5,000,000. The tax rate is $35% and the cost of capital (the WACC variable in Excel) is 10%. There is no consideration on inflation needed.

1. Construct a base model to determine the NPV of this project.

Note: you may revise the sample models based on the above description. Use appropriate number scales in your data entry. Create new variables when needed in the input area, for example, the growth rate in the units sold.

2. Using a data table and graphing feature in Excel, draw an NPV profile of projects value.

This is essentially a sensitivity analysis with the cost of capital as the changing variable. Set the range of cost of capital from 5% to 15% at a 1% interval. Make sure your graph has the necessary elements to be informative.

3. Still using the base model, what is the breakeven point in price that makes NPV equal to zero?

Record your value in your model and RESTORE your price value to its original value before moving to the next step.

4. Conduct a scenario analysis.

In addition to the normal condition (as completed in part 1), assume that there is a 20% probability of best-case condition of high market demand, where the growth rate in units sold will be 8%, and variable costs would drop to $30,000 per unit.

There is a 10% probability of worst-condition, the growth rate in units sold will be 1% given the low market demand, and variable costs would be $50,000 per unit.

In your scenario analysis, name your variables appropriately before running a summary report, and estimate the expected value of NPV.