Question 1: Your company is considering the replacement of production equipment. There are two choices to consider and the company must choose only one of the available models. The required rate of return is 9% p.a. Assume a corporate tax rate of 30% and there is no additional working capital requirements in this case study. Depreciation follows a straight-line method on the cost of the equipment. The company expects to sell the equipment at the end of the terminal year. INPUT AREA Cost of equipment Useful life (years) Additional revenue before-tax (per annum) Additional operating costs before-tax (per Salvage value at end of useful life Model A $ Model B 800,000 $ 5 $ $ 725,000 $ 180,000 $ $ 200,000 $ Tax rate ROR 30% 9.0% 1,200,000 8 760,000 265,000 Operating expenses comprise of both VC and FC, there is no break-down in this question. 240,000 Question 1A: Set up the table of incremental cash flows for both Models A and B in the areas below. Use cell referencing (instead of hard coding the values). Determine the capital budgeting metrics for Models A and B, in the areas marked yellow below. Ensure your results are formula driven (instead of hard coding). Otherwise you will be unable to complete Parts B and C. Question 1A is worth 2 marks Model A: Revenue -Operating expenses EBITDA -D&A EBIT -Tax NOPAT +D&A CF from Operations - Capital Expenditure -Tax paid on CGT or Tax Saving on capital loss Incremental after-tax FCFS Cash flows to work out discounted payback period Cash flows to work out MIRR 0 1 2 3 5 Model B: Revenue -Operating expenses EBITDA -D&A EBIT UA NOPAT +D&A CF from Operations Capital Expenditure -Tax paid on CGT or Tax Saving on capital loss Incremental after-tax FCFs Cash flows to work out discounted payback period Cash flows to work out MIRR 0 6 7 8 Summary of base case estimates NPV IRR MIRR PI PI Payback Period, years Discounted Payback Period, years NPV Perpetuity Equilvalent Annual Annuity (EAC) Model A Model B Question 1B: Conduct a sensitivity analysis for Optimistic and Pessimistic variations to Revenues and Operating Costs. Report/summarise the NPVS from each outcome in the areas marked yellow below. Determine the range NPVs for each model under the Optimistic and Pessimistic scenarios to enhance your discussions. Hint: You can either make changes directly to the input (green) area and copy the NPVS from Row 60 as hard coded values into the yellow area below, OR you can make copies of the base case worksheet and make changes to the components. Question 1B is worth 0.5 marks Sensitivity Changes to Revenue 35%. Model A Model B Analysis (summary table) Changes to Op. Costs 35%: Model A Model B Base Case Optimistic Pessimistic Range Y Question 1C: Determine the NPV breakeven level of operating costs. How high must additional operating costs rise to, for each Model, in order for the alternative to be unfeasible? Report this in the areas marked yellow below. You can work out the percentage increase from base case estimates, to enhance your discussions. Question 1C is worth 0.5 marks NPV Breakeven Analysis (summary of findings) Maximum level of operating costs % increase Model A Model B Question 1D: Discuss if your company should replace production equipment with Model A or Model B. Elaborate on your reasoning. Enter your discussions in the textbox provided. Frame your discussions as an investment recommendation. Your recommendation should be based on evaluating the capital budgeting metrics for both alternatives and considers your findings from your sensitivity and breakeven analysis. Minimum 150 words. Maximum 250 words. Question 1D is worth 1 mark