The project Klaatu Co. has recently completed a $300,000 marketing study. Based on the results of the study, Klaatu 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, Klaatu pays income taxes at a 40 percent rate. Klaatu uses a 20 percent required rate of return on projects such as this one. Instructions Using an Excel spreadsheet (to be submitted), perform the following tasks (hint: re-use Excel file already provided): 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 NPV with +1% of 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%) ? ? ? ? ? Write a report (to be submitted, up to one page of text maximum) summarizing your analytical findings (NPV and sensitivity analysis) and your recommendation (recommend to proceed or decline the project, and why). Deadline and submission The project Klaatu Co. has recently completed a $300,000 marketing study. Based on the results of the study, Klaatu 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, Klaatu pays income taxes at a 40 percent rate. Klaatu uses a 20 percent required rate of return on projects such as this one. Instructions Using an Excel spreadsheet (to be submitted), perform the following tasks (hint: re-use Excel file already provided): 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 NPV with +1% of 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%) ? ? ? ? ? Write a report (to be submitted, up to one page of text maximum) summarizing your analytical findings (NPV and sensitivity analysis) and your recommendation (recommend to proceed or decline the project, and why). Deadline and submission
