A case in corporate finance

A high-tech company, Robotic Vehicles Ltd., has hired you as a financial specialist to determine the financial consequences associated with investing in developing an autonomous car (self-driving) solution for traffic in the central areas of New York. One alternative is to sell the electric autonomous cars to the city “government” and provide electricity swapping stations as well as electricity fill up at full cost (=zero net present value). The other alternative is to deliver the autonomous electric cars as well as electricity swapping stations at full cost and charge each passenger an electricity consumption mileage fee for car usage. Robotic Vehicles ltd. has provided you the following information:
 
General

The full cost of producing a car in nominal terms is estimated to $10 000. The New York City government wants to order 2000 cars in total. They want 200 cars per year to be delivered to them for the next 10 years, starting this year. The cars will be paid upon delivery the 1st of January each year. Each car has an expected lifetime of 10 years with zero scrap value.
The New York City government also demands in total three swapping stations installed at different central city locations. They require that the three swapping stations are up and running when the first 200 cars are delivered. The full cost of each swapping station is estimated to $1 000 000 at full cost. The swapping stations are supposed to last forever.
The corporate tax rate is expected to remain at the current level 28%
Yearly inflation rate is estimated to 2%
Robotic Vehicles Ltd. has a D/E ratio equal to 2 and the cost of debt is expected to be constant at 7% and the cost of equity is estimated to be constant at 10% forever.
Today is the 1st of January 2015.

The autonomous car sales alternative

The price per car for the first 1000 cars delivered is estimated to $20000 in real terms.
The price per car for the remaining 1000 cars delivered is estimated to $15000 in real terms
Electricity fill-up is provided at full-cost (zero net present value)
Robotic Vehicles Ltd also in this investment alternative sells a maintenance service of the cars to the New York city Government. The service is provided at the end of each year for each car. Robotic Vehicles ltd’s current profit per car and year for providing this service is estimated to be $100 in nominal terms. This profit-level is expected to grow yearly at a 10% rate
The investment will be financed according to Robotic Vehicles Ltd’s current overall proportion of debt and equity (assume zero additional cost for issuing new debt or equity).

The electricity charging alternative

The yearly use of each car is estimated to 10 000 miles.
The current fee charged per mile each electric autonomous car is driven is currently estimated to $0.50 in nominal terms. The fee is expected to grow at a 5% rate yearly.
Robotic Vehicles ltd is currently charged for electricity use of each car by a utility company. The electricity cost per mile the car is driven is estimated to $0.20 in nominal terms. By the end of each year there is a 20% chance that the electricity cost per mile and car decreases by $0.01 in nominal terms and an 80% chance that the price increases by $0.02 in nominal terms.
The investment will be financed solely by equity capital.

Your task
Your task is to create a spreadsheet model (using Microsoft Excel) showing your friend which alternative is to prefer from a financial point of view. In particular your friend wants you to show her the following in the spreadsheet model:

Show by calculation the net present value for the two options (selling cars, mileage fees). Also, according to NPV suggest which alternative you advise your friend to choose
Show by calculation (creating a formula) the discount rate for the mileage alternative at which (assuming all else the same as in the instructions above) Robotic Vehicles Ltd is indifferent between the two investment alternatives.
Robotic Vehicles Ltd is a bit uncertain about whether the cost of debt will remain constant at 7% over time. Therefore they want you to show by calculation how the company’s cost of capital is influenced by a 1% increase and decrease respectively in cost of debt (i.e. by the cost of debt being 6%, 7%, or 8% forever starting today). Also, they want you show by calculation how Re (return on equity, hint WACC) is influenced by the increase and decrease in cost of debt.

Finally, since Robotic Vehicles Ltd. wants to be able to use this spreadsheet in the future as well you need to use cell references in the formulas (for a, b and c above).