Financial Modelling

Learning Outcomes

On successful completion of this unit a delegate will:

Assessment criteria

To achieve each outcome a delegate must demonstrate the ability to:

LO1 – Produce financial models that follow best practice techniques and use a structure that allows subsequent changes to be made easily, efficiently and without introducing errors.

1.1 Design a model where the information flows logically through each page from the input to the final reports.

1.2 Ensure that the calculations are made in a logical order.

1.3 Ensure that each calculation is laid out like a recipe showing: Ingredients, process, result

1.4 Demonstrate their understanding of each area covered by the lectures

1.5 Ensure that you can follow any number through the model from the reports back through the calculations to the input data

LO2 – Develop robust, fully integrated financial forecasting models which include inflation, DCF valuation, sensitivity analysis, data tables and graphics.

2.1

2.2

2.3

Ensure that each of the values required for the final reports is provided and properly linked to the appropriate cells in the Calculations page

Produce a full set of integrated reports including the Income

Statement, a balancing Balance Sheet and the Cash Flow Statement

Value the case study company using DCF methodology

LO3 – Understand the interrelationship between items in the financial statements.

3.1

3.2

Produce a model that will still behave properly when the value for any item of input data is revised

Calculate each number using an appropriate methodology and without making any errors

3.3

Incorporate sensitivity analysis into the models without losing any part of the audit trail through the model.

3.4

Use data tables to provide figures for graphs that show how the overall value of the company is affected by changes to the sensitivity drivers.

LO4 – Understand tax computations, managing tax losses and deferred tax.

4.1

Ensure that the tax charge in the Modelling Assignment model is calculated in accordance with the instructions in the Assignment Details section below.

LO5 – Demonstrate a knowledge of modelling, income statements, cash flows, current and noncurrent assets and liabilities

and of current “best practice” techniques

5.1

5.2

5.3

Use the time weighted average method to calculate interest on cash

Use appropriate backing schedules to calculate the balance of the senior debt and the interest charged on it

Ensure the final model allows subsequent changes to be made easily, efficiently and without introducing errors.

Modelling assignment - 100% of total marks

Case Study

Pi2 plc

Pi2 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 at 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. Proformas of all the reports that are required from the model can be found on the reports page of this worksheet. Please note that no lines should be added to or deleted from these reports.

In addition, you need to know that:

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

• You are required to present all your final reports in €’000 with no decimal places. Note that if any number in your reports is more than five digits long then you have not met this requirement and will be penalised. All numbers should contain five or fewer digits.

• Pi2 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 1^st 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 1^st January each year

• Senior debt repayments are made on 30^th August in each year that repayments are due.

• Senior debt interest is paid monthly in arrears

• Tax should simply be taken as a percentage of accounting profit before tax. You should NOT try to include a full tax computation nor any tax losses nor any deferred tax in this model.

• Pi2 plc owns two different types of fixed asset.

o Motor vehicles – these are used solely by the installation teams. Depreciation should be calculated as a percentage of the closing balance of the cost of assets (after disposals) and shown as a direct cost of sales.

This means that their depreciation policy is to charge a full year’s depreciation in the year of acquisition but none in the year of disposal.

o Office equipment – these assets are used by the central administration departments. Depreciation 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 two 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 1^st January and all disposals are made on the 31^st 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 30% of the profit for the year remaining 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, and 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 10% increase in the sales volume o A 5% reduction in the sales price o A 10% reduction in cost of goods sold o A 5% increase in installation labour costs The overall aims of this exercise are to:

• Produce a complete financial forecasting model for Pi2 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 2021.

• Plot four line graphs on a single set of axes to show the effect on this DCF valuation of changes in each of the four sensitivity drivers.

Attendance and participation

The lectures cover a wide variety of topics that extend beyond those areas assessed by the modelling assignment. Attendance is therefore an important part of gaining the full gamut of knowledge offered by this course but since attendance cannot be fairly determined for online courses attendance does not form part of the overall course mark

Task 1 - LO1-LO5 (Demonstrate their ability to build a well structured, error free, best practice, financial model)

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 formula for 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 formula will refer to the closing balance from the previous year.

• Use the accountants’ convention and have a single underline above any total and have 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.

Merit descriptor

To achieve a merit the learner must:

Indicative Characteristics The learner’s evidence shows:

Achieve an overall mark between 60% and 69%

Model uses calculations that in some instances do not calculate the appropriate results.

Some calculations refer to the wrong cells e.g. refer directly to input sales volume figures when they should refer to the sensitised versions of the input sales volume data.

Some sensitivity analysis adjustments are incorrectly calculated.

Model is not clearly laid out so that it is difficult to trace how some values are calculated.

Model does not consistently follow the “best practice” techniques taught during the course.

Some of the output detailed in the Assignment Details section has not been provided, e.g. Graphs showing how the overall value of the company is affected by changes to the sensitivity analysis drivers.

The model does not consistently use underlines to denote that the next row is a total or double underlines to indicate that the above figure is the final result of a calculation.

Distinction descriptor

To achieve a distinction the learner must:

Indicative Characteristics The learner’s evidence shows:

Achieve an overall mark of 70% or more

Model uses appropriate calculations to compute each value for the final reports.

The model consistently follows the “best practice” techniques taught during the course.

The model is clearly laid out so it is easy to see how each number is calculated.

All calculations are accurately coded, mathematically correct and refer to the correct input cells on the “Input” page or calculated values on the “Backing” page (with the exception of the tax calculation which will need to refer to the ‘Profit before tax’ figure calculated on the “Reports” page).

Each value is calculated once and only once in the model to prevent disagreement within the model when subsequent changes are made.

There are no gaps in the chain linking each number in the final reports back through each calculation to the appropriate pieces of input data.

All of the output detailed in the Assignment Details section has been provided, including the cash flow statement.

Note: An imbalance on the balance sheet may be caused by just one calculation being wrong and an imbalanced balance sheet will not prevent a model from scoring a distinction even though it is technically wrong.

Criteria

70– 100% Distinction

60-69% Merit

50-59% Pass

0 -49%

Fail

Model structure (20%)

Perfectly structured model that is well laid out so that it is easy to find where each calculation is located. Calculations placed in the order in which they appear in the final reports.

Well-structured model which follows appropriate format but where some items are not ordered logically

Reasonably well structured model but poor model structure making it hard to determine where numbers are coming from

Little or no structure to the model with calculations being presented in a haphazard order making it hard to use

Model clarity (20%)

Each calculation laid out logically making it easy to see how each number is calculated just by looking at a printed report. Correct use of single and double underlines before and after totals.

Most calculations are laid out logically but some use a complex way of reaching a result. Single and double underlines not consistently used correctly

Many calculations are laid out logically but some intermediate steps are skipped. Single and double underlines not used correctly

Generally poor layout of calculations with many intermediate steps not shown making it very hard to follow. Single and double underlines not used correctly

Correctness of calculations

(30%)

All calculations perform the correct operations required to produce the desired results with the minimum of complexity.

Most calculations perform the correct operations and produce the desired results but may do so with unnecessary complexity

Some calculations perform the correct operations and produce the desired results but some may be unnecessary complex or wrong

Some calculations may produce the desired results but many are blank, wrong or unnecessary complex

Accuracy of calculations (20%)

Calculations refer to the correct source cells and feed their results to the appropriate places.

Some calculations referring to the wrong source cells or are linked to the wrong destination

Many calculations referring to the wrong source cells and are not linked to anywhere

Many calculations completely unlinked or linked to random cells

Final reports and valuation

(10%)

Final reports use the given proformas without alteration, addition or deletion and contain the correct links which work under all circumstances.

Final reports use the given proformas without alteration, addition or deletion but have links which only work under some circumstances

Final reports may use modified versions of the given proformas but some figures are not linked to anything and some of the other links are wrong

Final reports may use modified versions of the given proformas but many figures are not linked to anything and most of the other links are wrong