Please note that I only need assistance with questions a) b) and d), mainly a) and d). You will need to use excel spreadsheet.
Part 3 (40 marks)
MK Wafflemakers (MKW) is assessing an investment decision. It is considering launching a new product: the Crispy Waff. The project is based on market research undertaken at a cost of £200,000.
The initial investment required is £30M. This attracts capital allowances in equal amounts over 4 years. The company’s depreciation policy is to depreciate such investment straight line over 5 years. There will also need to be an investment in working capital at the start (Year 0) of £5M, which is expected to be recovered when production stops at the end of Year 6.
Production will take place in an empty factory that MKW owns. If the project does not proceed, the building will be rented out for £1.5M per annum.
Revenues are estimated to be £15M in Year 1 and to rise by 2% per annum until the end of Year 6. Costs are estimated to be 40% of revenues. However, it is estimated that the Crispy Waff will cause sales of Waffle Basic (another product made by the company) to fall by 20%. The Waffle Basic has estimated sales after costs, in Year 1, of £7M, rising at 1% per annum.
For reporting purposes, head office allocate their costs to each department on the basis of 10% of revenues.
Tax is payable at 19%, a year in arrears. The required return is 8.25% per annum.
• a.Design a spreadsheet model to assess this project. Your spreadsheet should be designed in a way that allows variables to be easily changed in order to facilitate sensitivity analysis. Using this model, find the net present value, the internal rate of return and the payback period in years and months. (20 marks)
• b.What is your recommendation? Explain your answer. (5 marks)
• c.Using the ‘goal seek’ function in Excel, find what the revenue growth would need to be for you to change your recommendation in part (b). Take a screenshot of the goal seek answer and insert the image into your TMA Word document. (2 marks)
• d.Insert a data table into your spreadsheet, showing the impact on the net present value of varying percentages of lost Waffle Basic sales and revenue growth. You should include a range of lost sales from 15 to 25% and revenue growth from 1.5 to 2.5%. (4 marks)
• e.Explain, using your own words, why net present value is generally considered to be a better appraisal test than the internal rate of return. (9 marks)
