**Please show all excel formulas and work

KA Co. has recently completed a $300,000 marketing study. Based on the results of the study, KA has estimated that 14,000 of its new RUR-class manufacturing robots could be sold annually over the next eight years at a price of $10,000 each. Variable costs per robot are $7,800 and fixed costs total $11 million per year.

Start-up costs include $2.5 million in land, $39 million to build production facilities, and $10 million in net working capital. The $39 million facility is made up of a building valued at $9 million that will belong to CCA class 3 and $30 million of manufacturing equipment (belonging to CCA class 8). CCA rates are 5% for class 3 and 20% for class 8. At the end of the project's life, the facilities (including the land) will be sold for an estimated $10.3 million (assuming the building's value will be $4 million). When the project is over, there will still be other assets in the CCA class. The value of the land is not expected to change. (hint: do the CCA balances exceed sale prices leading to residual tax shields?)

Finally, start-up would entail fully deductible expenses of $1.2 million at the initiation of the project (hint: year 0). An ongoing, profitable business with ample tax capacity, KA pays income taxes at a 40 percent rate. KA uses a 20 percent required rate of return on projects such as this one.

Instructions

Using an Excel spreadsheet , perform the following tasks :

• Find the NPV and the IRR of the project using the pro forma financial statement method to determine cash flows.

• Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven; do not put any numbers in cell equations, only cell references. Keep the spreadsheeted clean (e.g. no color).

• Conduct a NPV sensitivity analysis to +/- 1% changes to i) the sale volume, ii) the price per unit, iii) the variable cost per unit, iv) the fixed costs and v) the hurdle rate to populate a table structured as follows:

NPV with -1% of forecasted value ?

Forecasted value sale volume = 14,000 price per unit = 10,000 variable cost per unit = 7,800 fixed costs = 11,000,000 hurdle rate = 20% (-1% is 19.80%)

NPV with +1% of forecasted value ?