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, 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 = 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%) ? 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, 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 = 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%)
