Case 1 - Nelson Company (18 marks) The Nelson Company is planning a new division to make a specialised product. This is the only product that the division will make. As a financial analyst in the planning department, you've been asked to make the decision on whether this is a good idea or not. Sales will commence in Year 1, after installation on the final day of Year 0. The marketing department believes that first year revenue will be $1,800,000 rising to $7,000,000 in Year 8. The company will pay no tax if Earnings before Tax are negative. The company will purchase equipment for $4 million, which will have to be replaced at the end of Year 8. You've been asked to create a discounted cash ow analysis in order to calculate the project's net present value and internal rate of return. The company has a policy of only accepting projects with a return greater than 7.0%. Remember that a common financial shortcut to calculate free cash flow is to add depreciation to net income. 1. Open the Nelson worksheet. 2. In Cell F25 (Tax Rate) set up a data validation input message to read "Tax Rate". 3. In Cell F27 (Discount Rate) set up a data validation so that the rate can only be between 3.0% and 8.0%; the Input Message must read "Allowable Discount Rates"; and the Error Message must read "Wrong Number". Use the fill function to complete the Revenue row from Year 1 to Year 8. Use the growth trend. Using absolute cell references, calculate the cost of goods sold for all years. Calculate the Gross Profit. Use the fill function with a linear trend to complete the Operating Cost row. Use the appropriate function for declining balance depreciation to complete row 12. Complete the final three rows (13, 14, and 15). Remember the Company only pays taxes if Earnings before Tax is above $0. 10. In row19 determine the annual free cash flow. Remember that a common financial shortcut to calculate free cash flow is to add depreciation to net income. 11. In the appropriate cells (B21 and B23) 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 F22 on the worksheet. 13. Format the worksheet so that it fits on 1 page with Landscape orientation. 14. Put your name in the right footer. 15. Save your workbook. P PWNP'P