The Hobbes Company is planning a new division to make a specialised Refractosphere. This is the only product that the subsidiary will make. As a financial analyst in the planning department, youve been asked to make the decision on whether this is a good idea or not. Sales will commence in 2021 after installation on the final day of 2020. The marketing department believes that first year sales will be $1,000,000 rising to $4,500,000 in 2028. The company will pay no tax if Earnings before Tax are negative. Equipment will have to be replaced at the end of 2028, so youve been asked to create a discounted cash flow analysis to support the calculation of the projects net present value and internal rate of return. The company has a policy of only accepting projects with a return greater than 9.0%. Remember that a common financial shortcut to calculate free cash flow is to add depreciation to net income. 1. Open the Hobbes worksheet. 2. In Cell F6 set up a data validation input message to read Tax Rate only.

3. In Cell F8 (Discount Rate) set up a data validation rule so that the rate can only be between 6.0% and 10.0%; the Input Message must read Allowable Discount Rates; and the Error Alert Message must read Think first!!. 4. Use the fill function to fill the revenue row from 2021 to 2028. Use the growth trend. 5. Using absolute cell references to help you, calculate the cost of goods sold for all years. 6. Calculate the Gross Profit. 7. Use the fill function with a linear trend to complete the Operating Cost row. 8. Use the appropriate function for straight line depreciation to complete the Depreciation row. 9. Complete the final three rows (20, 21, 22). See above information regarding taxes. 10. In row 26 determine the annual free cash flow. See hint in opening paragraphs. 11. In cells B28 and B30 determine the net present value (NPV) and internal rate of return (IRR) for the project. 12. Answer the question Would you invest and why? in cell A32 on the worksheet. 13. Format the worksheet so that it fits on 1 page with Horizontal orientation. 14. Put your name in the centre footer. 15. Save your workbook.