MK Wafflemakers (MKW) is preparing a forecast for next year. It only has one product, the WaffleEez, which this year sold 3,450,250 units for £21.99 each. It is forecast that sales volumes will rise by 2% and the price will rise by 0.5%.
The materials cost (which is variable) is currently £5.78 per unit but is forecast to rise by 1.25%. Other variable costs are currently £3.75 per unit and that cost is not forecast to change.
Salary costs are currently £19.25M but wage inflation is estimated to be 5.75% for next year. Other fixed costs, which are currently £17M, are not forecast to change.
MKW currently has debt of £54M, on which it pays interest of 4.25%. It intends to refinance next year by borrowing a further £2M and paying 3.75% interest (on all outstanding debt).
MKW’s current effective tax rate is 18.25% but it intends to use 19% for the forecast.
The company’s working capital position is summarised in Table 3.
Table 3 Working capital
Current Forecast
Payables days 40 45
Receivables days 35 30
a.Produce a Microsoft Excel based model that shows the current income statement and the forecast position. The model should be capable of being used to sensitise the forecast. (5 marks)
b.Calculate the forecast cash received from customers for next year. (5 marks)
c.Next to your model, insert a pie chart that demonstrates how the forecast revenue gets split among the various costs and net profit. (5 marks)
d.Using the ‘goal seek’ function in Excel, find the volume growth (or fall) in percentage terms that will produce a net profit break even. Post an image of the input panel next to your model. (Note: the input panel is the small table where you enter which cell you are setting and which you wish to change. Take the screenshot before you press ‘ok’.) (5 marks)
Part 2 (30 marks)
MK Wafflemakers (MKW) is going to launch a new product: the Waffle Super-Max. Demand is likely to be in two stages: one for the initial period (of three years) and then a subsequent longer term demand. In both stages, it is assumed that demand may be high or low.
MKW has to decide whether to invest in a small factory with a limited amount of equipment or in a large factory with a correspondingly larger amount of equipment. Should MKW decide to start with a small factory, it can expand this after the initial period. However, if it starts with a large factory, it cannot subsequently downsize.
Building and equipping a small factory will cost £4M, or £12M for a large one. Expanding a small factory to a large one costs an additional £8m.
The marketing department estimate the probabilities of demand shown in Tables 4 and 5.
Initial period:
Table 4 Probabilities of demand
Probabilities of demand in the initial period
High 0.60
Low 0.40
Subsequent period:
Table 5 Subsequent probabilities
Demand in initial period Demand in subsequent period Probability of demand in subsequent period
High High 0.80
High Low 0.20
Low High 0.30
Low Low 0.70
The finance department has estimated the payouts (you may ignore the time value of money), before investment costs, shown in Tables 6 and 7.
Initial period:
Table 6 Factory demand
High demand Low demand
Large factory £4M £1M
Small factory £2M £1M
Subsequent period:
Table 7 Subsequent factory demand
High demand Low demand
Large factory £26M £5M
Small factory £10M £5M
(For example, if the company built a small factory, then expanded and had high demand in both periods, the total payout would be £2M + £26M = £28M, less any investment costs.)
a.Draw the decision tree for this decision. The tree should show the various nodes and branches, together with relevant payouts and probabilities. You may produce the tree using any medium (Excel, by-hand, PowerPoint etc.). If you use Excel, submit the file. If you use another medium, produce a clear image (e.g. a screenshot or photograph) and insert it into your TMA Word document. (10 marks)
b.Calculate the appropriate expected values for each node. Show your workings. (17 marks)
c.Based on your answer to part (b), state your decision and enlargement strategy. (3 marks)
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)