Reference no: EM132939049

Financial Modelling Assignment Brief

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.

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

LO3 - Understand the inter- relationship between items in the financial statements.

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

LO5 - Demonstrate a knowledge of modelling, income statements, cash flows, current and non- current assets and liabilities and of current "best practice" techniques

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 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 30th 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 1st January and all disposals are made 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 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.

Assignment Brief

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.

Attachment:- Financial Modelling Assignment.rar