The project Klaatu Co. has recently completed a $500,000, two-year marketing study. Based on the results of the study, Klaatu has estimated that 12,000 of its new RUR-class robots could be sold annually over the next eight years at a price of $10,130 each. Variable costs per robot are $7,700; fixed costs total $12 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. Finally, start-up would entail fully deductible expenses of $1.4 million at year 0. An ongoing profitable business with ample tax capacity, Klaatu pays income taxes at a 40 percent rate. Klaatu uses an 18 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, ill) 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 = 12,000 ? price per unit = 10,130 variable cost per unit - 7,700 fixed costs = 12,000,000 ? hurdle rate = 18% (-1% is 17.82%) ? 7 ? ? ? ? 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). The project Klaatu Co. has recently completed a $500,000, two-year marketing study. Based on the results of the study, Klaatu has estimated that 12,000 of its new RUR-class robots could be sold annually over the next eight years at a price of $10,130 each. Variable costs per robot are $7,700; fixed costs total $12 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. Finally, start-up would entail fully deductible expenses of $1.4 million at year 0. An ongoing profitable business with ample tax capacity, Klaatu pays income taxes at a 40 percent rate. Klaatu uses an 18 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, ill) 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 = 12,000 ? price per unit = 10,130 variable cost per unit - 7,700 fixed costs = 12,000,000 ? hurdle rate = 18% (-1% is 17.82%) ? 7 ? ? ? ? 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)
