Delta2 plc was set up a number of years ago to purchase and install domestic air conditioning units (ACUs). It buys both large and small air conditioning units from a single manufacturer and uses its own installation teams to install them into customer sites. You have been asked to produce financial forecasts for the next ten years and carry out a DCF valuation of the company. You are given all of the input data you will need to complete this exercise in the starting position worksheet. In addition you need to know that:

 All monetary values are expressed in nominal values – you do not have to worry about inflation factors

 You are required to present all your reports in €’000. Note that if any number on your reports is more than six digits long then you have not met this requirement and will be penalised.

 All numbers should be formatted the same way as they are on the input page: Prices and unit costs in € should show two decimal places, sales volumes (in thousands) and installation times (in hours) should show one decimal place, all other numbers should show no decimal places

 Delta2 plc only deals with two different ACU models’ known as “Small” and “Large”. Separate figures are given for both types of ACU

 All income and expenditure take place evenly over the year except where stated below

 All labour rates are fixed at the beginning of each year and are raised on 1st January once a year with the new rate being effective for the whole of the next twelve months

 The company is financed by a mix of ordinary shares and bank debt

 Rent is paid in full on 1st January each year

 Senior debt repayments are made on the last day of the year

 Senior debt interest is paid monthly in arrears

 Tax should simply be taken as 20% of accounting Profit before Tax. You should not try to include a full tax computation or any deferred tax in this model.

 Delta2 plc owns two different types of fixed asset.

o Motor vehicles – these are used solely by the installation teams. Depreciation of these should be calculated as a percentage of the closing balance of the cost assets (after disposals) and shown as a direct cost of sales. This means that their depreciation policy is to charge a full years depreciation in the year of acquisition but none in the year of disposal.

o Office equipment – these are used by the central administration departments. Depreciation of these should be calculated as a percentage of the cost after additions but before disposals and shown as a central administrative cost. This means that the depreciation policy on these assets is to charge a full years depreciation in both the year of acquisition and the year of disposal Please note that these depreciation policies are different from each other so you will therefore need to have different calculations for the two asset types

 All asset additions are made on the 1st January and all disposals on the 31st December

 Ordinary dividends in each year comprise two parts:

o A fixed dividend which is equal to 100% of the nominal value of the shares

o A variable dividend which is 45% of the profit for the year after deducting the fixed part of the dividend. Note that you will need to check that the dividends you have calculated are less than the legal limit for dividends.

 Your model should incorporate sensitivity drivers for performing sensitivity analysis on:

o Sales volume

o Sales price

o Cost of goods sold (other than installation labour cost)

o Installation labour cost

 These sensitivities should be set to the following values before submitting your answer:

o A 5% reduction in the sales volume

o A 5% increase in the sales price

o A 10 % reduction in cost of goods sold

o A 10% increase in installation labour costs

The overall aims of this exercise are to: 

 Produce a complete financial forecasting model for Delta2 plc that shows the forecast income statement, balance sheet and cash flow statement for the next ten years. Proformas for these reports have been included in the starting worksheet.

 Report the DCF valuation of the company as at 1st January 2018.

 Plot graphs to show the effect on this DCF valuation for changes in each of the four sensitivity drivers

Attendance and participation - 10% of total marks

The lectures cover a wide variety of topics that extend beyond those areas assessed by the modelling assignment. Attendance and class participation is therefore an important part of gaining the full gamut of knowledge offered by this course.

Your model should follow the example that has been worked on throughout the course remembering that the most important aspect of any financial forecasting model is clarity – if your model is unclear then it will lead to confusion and greatly increase the chance of the model containing errors. Also note:

 You have been given proformas for all the reports that are required from your model. These proformas should not be edited or extended with extra rows.

 You need to provide values for every item listed in the proforma reports, even if they are zero under the assumptions of the current scenario.

 Your backing schedules should be laid out in the same order as the items appear in the reports.

 Each formula on the final reports should refer to a single cell elsewhere in the model. This means that you may need summaries, for example the net financing cost will be the sum of the interest on senior debt and the interest on cash. You will need a calculation where the first line shows the interest on senior debt, the second line shows the interest on cash and the final third line shows the sum of the above lines. The formula for ‘net financing cost’ in the income statement will refer to the last line of this calculation.

 You should use a time weighted average cash calculation to work out the interest on cash.

 The effect of any sensitivity analysis should be shown on a separate line when you are calculating a figure, as we have done in the class exercise.

 The same formulae should be copied across all column of the model in all cases except where this is not possible e.g. the opening balance at the beginning of the first year will have to refer to a value on the ‘Input’ page but from year two onwards the opening balance will be the closing balance from the previous year.

 Use the accountants’ convention that you should have a single underline above any total and a double line to show when a calculation has finished,

 Remember that the data tables you will need to add to produce the graphs that have been specified must be on the ‘Input’ page otherwise they will not calculate correctly.

 Your backing schedules page should be set up with appropriate page breaks to allow landscape printing on A4 paper at 75% scaling.

Answer rating: 100% (QA)

financial forecasting model for Delta2 plc Income Statement Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Sales 100 110 121 1... View the full answer