Financial modelling

Document Sample
Financial modelling Powered By Docstoc
					4     Financial modelling
4.1   Structure of the financial model

      ·    There are 4 sheets within the industry version of the financial model. These
           are:
          - A single input sheet;
          - Two calculation sheets – Calc and Qtrcalc; and
          - KSAT (K Solve audit trail).

      ·    The initial K is calculated automatically from data in the input sheet. The K
           solving functions of the financial model are controlled by the graphical user
           interface which take users through the correct sequence of steps after the
           initial K has been calculated. These are:
          - Fix Initial K
          - Gearing adjustment;
          - Cost of capital selection;
          - Targeting financial indicators;
          - Confirm/entering bankable K profile;
          - Consider other K profiles;
          - Apply OPA adjustment; and
          - Saving scenario.

      ·   The remainder of this section explains how to use the financial model.


4.2   Open the financial model

      ·    To open the financial model there are two options to either open a:
          - New scenario; or
          - Saved scenario.


      Open a new scenario

      ·   From the Aquarius 3 main menu select ‘Financial Model’ and ‘New’.

      ·   Use the scroll lists to select the desired company, data set, revenue
          assumptions and industry assumptions.




                                        30
·   NB. The financial model requires tariff basket model outputs to be
    generated. Even if these are not amended from your final business
    plan tables you must run the tariff basket model and save a revenue
    assumption set before running the financial model.




·   Select <OK>. The input sheet will be displayed on screen.


Open a saved scenario

·   From the Aquarius 3 main menu select ‘Financial Model’ and ‘Open’.

·   Expand the scenario selection tree view in the left-hand pane.




                                 31
·   Highlight the required scenario in the right-hand pane.

·   Select <OK>. This will take you to the input sheet.

·   If the required scenario was created in a previous version of the model,
    Aquarius gives you the option to open the scenario either in the version the
    scenario was created in or in the current version of the model (see section
    1.1 for the list of model versions).




·   Opening a previous model scenario in the current version of the model will
    invalidate and cause differences in some outputs.

·   Other options available in Aquarius for saved scenarios are in the ‘Actions’
    and ‘View’ menu in the select selection dialogue box. They are:


    Relink

·   The ‘Relink’ option allows users to connect existing scenario decisions to
    different datasets, industry and revenue assumption sets.

·   To relink a scenario, highlight the relevant scenario and select ‘Actions’
    <Relink>. Alternatively, click the right mouse button and select <Relink>.

·   Use the scroll lists to select the desired dataset, industry and revenue
    assumptions.




                                  32
·   A message will confirm that the scenario has been successfully relinked.

·   The scenario will reflect the inputs in the new dataset, industry and revenue
    assumptions. Audit changes (made by editing the input sheet) will be
    retained. The outputs of the scenario will be invalid until K Solving is run
    again.


    Copy

·   The ‘Copy’ function allows you to take a copy of a saved scenario and save
    it as a new scenario.

·   Highlight the required scenario and select <Copy>.

·   You will be prompted to enter a new description for the scenario.




·   Enter a new description. Click <Save>.


    Delete

·   To delete a saved scenario, highlight the required scenario and select
    <Delete>.

·   At the user prompt click <Yes>.




                                 33
    Change owner

·   This option allows you to transfer access rights to a scenario you have
    created to another user. The option is for OFWAT internal purposes only.


    Rename

·   The ‘Rename’ function allows you to change the name of a saved scenario.

·   Highlight the required scenario and select <Rename>.

·   You will be prompted to enter a new name for the scenario.




·   Enter a new name. Click <Save>.


    Audit

·   To view changes made to the input sheet of a scenario highlight the relevant
    scenario and select ‘View’ <Audit>.




                                 34
      ·   If required the audit trail can be exported. Click <Export>.

      ·   Select the desired format of the file. The format can either be in Excel, CSV,
          Word or HTML.

          Scenario Details

      ·   To view the scenario details attached to a scenario, highlight the required
          scenario and select <Scenario Details>.

      ·   A message box will appear, detailing the scenario name, ID, version of
          model scenario created, associated datasets, revenue and industry
          assumptions that are attached to the scenario.


          Ad-Hoc Reporting

      ·   This feature of the model is currently not available in the Industry version of
          the model.


4.3   Navigating the input sheet

      ·   The input sheet is divided into two main sections according to the source of
          the data and the calculations that use it. These are:
          - Section 1: Projected expenditure and assumptions;
          - Section 2: Previous determination and actuals to the base year;
          - Section 3: Interim determination of K (IDoK) inputs; and
          - Section 4: Model parameters.

      ·   There are further sub-headings used within the four sections to aid
          navigation. These sub-headings are:

      Section 1
         - Base opex
         - Capital maintenance
         - Quality enhancements
         - Supply demand balance
         - Grants and contributions
         - Enhanced service levels
         - Depreciation
         - Company financial projections
         - Revenue projections
         - Industry Assumptions

      Section 2
         - Out performance and adjustments
         - Projected capital expenditure
         - Actual capital expenditure
         - Historic financial data


                                        35
      Section 3
         - Base opex
         - Capital maintenance
         - Quality enhancements
         - Supply demand
         - Enhanced service levels
         - Land sales
         - Capital charges
         - Revenue assumptions

      Section 4
         - General parameters
         - Target Financial Indicators

      ·   Different input items need to be populated for different time periods. The
          range of years that need to be populated for each item is indicated in the
          definitions manual.


4.4   Calculation sheets

      ·   The calculation sheets are protected. You cannot make any changes on
          these sheets.

      ·   This section of the user manual merely describes the structure of the
          calculation sheets.

      ·   It is possible to report the formula of any calculation in the model using the
          precedents and dependants report. See section 5 for further details.

      ·   As far as possible the Calc sheet follows the structure of the Aquarius 3
          financial model rule book where main headings cross reference the
          flowcharts in the rule book. This sheet calculates the revenue requirement,
          initial K profile and information for the standard reports. It also applies the
          solved K values when running the ‘K solving’ functions.

      ·   The Qtrcalc sheet performs calculations for a subset of items on a quarterly
          basis. It feeds back into the Calc sheet on an annual basis.


4.5   Solving K

      ·   The initial K is calculated automatically from data in the input sheet.

      ·   The K solving functions of the financial model are controlled by the
          graphical user interface which take users through the correct sequence of
          steps after the initial K has been calculated. These are:
          - Fix initial K
          - Gearing adjustment;
          - Interest adjustment;



                                        36
    -   Cost of capital selection;
    -   Targeting financial indicators;
    -   Confirm/entering bankable K profile;
    -   Consider other K profiles;
    -   Apply OPA adjustment; and
    -   Saving scenario.

·   If modelling for a rights issue sensitivity refer to the instructions in annex 2 -
    Rights issue modelling which outline the inputs that will need to be
    populated.

Fix initial K

·    Select ‘K solving’ from the menu bar. Select ‘Fix initial K’ from the drop
     down list. At this stage only the ‘Fix Initial K’ option is available on the drop
     down list.




·   This will bring up the message box “Do you want to apply the latest K
    Factors to non-tariff basket revenues?”. By selecting <Yes> the non-tariff
    basket revenues from large users and special agreements increase in line
    with K, adjusted by a multiplier. The multipliers are the proportions of K that
    are applied to large users, large user trade effluent and special agreements.




·   Select <No> if you do not want to make any adjustments to initial K.




                                   37
Gearing adjustment

·   Select ‘K solving’ from the menu bar. Select ‘Gearing adjustment’ from the
    drop down list. At this stage all other options, except ‘Fix initial K’ on the
    drop down list are unavailable.




·   The gearing and dividend adjustment dialogue form will then appear with
    suggested adjustments to bring the gearing ratio within the range specified
    in the target financial indicators section of the input sheet.

·   Select <Close> if you do not want to make a gearing adjustment.

·   Select <Apply> to use the suggested value or enter an adjustment in the
    ‘Actual’ dialogue box to adjust reserves. Select <Apply>.




                                 38
·   If you have opted to apply the gearing adjustment you will then need to
    adjust the % dividend to balance the Balance Sheet. Select <Apply> to use
    the suggested value or enter an adjustment in the ‘Actual’ dialogue box and
    select <Apply>.




·   If a gearing adjustment has been applied, you should select <Yes> to make
    an interest adjustment. The interest adjustment will only affect tax
    calculations. It will be sufficient to make the tax charge after the gearing
    adjustment equal to the tax charge underpinning initial K (K1). See Interest
    Adjustment below and section 4.8 - Capital structure sensitivity modelling for
    further details.

·   If you select <No> at this stage then the interest adjustment option can be
    selected again later. See Interest Adjustment section below.

·   This process is iterative. The interest adjustment will affect gearing. The
    model will suggest a further adjustment. You may choose whether to apply
    this suggestion.

·   Select <Close> to apply the outcome of the last iteration. Section 2 of the
    KSAT sheet will be displayed showing the outcome of the gearing and
    dividend adjustment.




                                  39
Interest adjustment

·   If an adjustment to gearing has been made then select ‘K solving’ from the
    menu bar. Select ‘Interest Adjustment’ from the drop down list.




·   This option is still available if an interest adjustment has been made during
    the gearing adjustment stage. A further interest adjustment at this point will
    ensure that the tax charge is exactly equal to the tax arising from initial K
    factors (the last gearing adjustment may have an impact on tax).

·   The interest adjustment function will calculate the difference in interest
    between the actual balance sheet and the adjusted notional balance sheet.
    See section 4.8 - Capital structure sensitivity modelling below for further
    details.


Cost of capital

·   Select ‘K solving’ from the menu bar. Select ‘Cost of capital’ from the drop
    down list.

·   This will bring up the dialogue form.




·   Select <Apply> to use the default setting.

·   Alternatively select another option and then <Apply>.




                                  40
·   Section 3 of the KSAT sheet will be displayed showing the three cost of
    capital profiles and associated K profiles. The ‘selected’ cost of capital is
    flagged in Column S, with the marker ‘S’.




Targeting financial indicators

·   Before selecting targeting, a target should be entered for each financial
    indicator to be targeted on the KSAT sheet itself. The mid-blue cells are not
    protected, and may be edited. The criteria boxes have four possible variants
    for selection: Min, Max, Equal or None.




·   For target values, there are two options:
    1. Fill in a default target value for the indicator (to apply in all years); or
    2. Enter an annual target for each indicator in each year.

                                    41
·   For tolerances, a tolerance must be entered if a user wishes a different
    tolerance. A value of 0 will be replaced with the default tolerance (0.04)
    when the model is working. Guidance on the meaning of ‘tolerance’ is given
    in the Aquarius 3 financial model rule book appendix B.

·   To check that the K profile (resulting from the selected cost of capital
    profile) produces financial indicators that satisfy the criteria specified above,
    select ‘K solving’ from the menu bar. Select ‘Targeting’ from the drop down
    list.

·   When targeting is selected from the menu, the following dialogue box
    appears:




·   The model will target only for the specified year range. If the “default” box is
    checked, the default values for each indicator will be written in as the target
    value for those years. Otherwise, any annual figure written in the mid-blue
    cells below each indicator will be taken as the target for that indicator. A
    blank cell will be treated as no target, regardless of whether the indicator is
    switched on or off.

·   The model will calculate a single K profile sufficient to meet all target criteria
    and / or a target rate of return equal to the cost of capital. This will appear in
    the “bankable” K line, and can be overwritten as required, as it is not
    protected.




                                   42
·   The bottom half of section 4 on the KSAT will then be completed showing
    the financial indictor values for the K profile applied.


Overwrite K profile

·   The bankable K profile and the associated financial indicators are captured
    for the audit trail in rows 61 to 69.

·   The bankable K can be overwritten on the KSAT sheet itself. Select ‘K
    solving’ from the menu bar and select ‘Overwrite bankable K’ from the drop
    down list.

·   This will return the model to this stage and place the cursor on the
    “bankable” K line. Overwrite values as necessary.

·   If modelling outperformance of PR04 and future determinations refer to the
    detailed instructions in annex 3 - Modelling outperformance.


Reprofiling K

·   The reprofiling option gives the option to reprofile the bankable K profile for
    each five-year period. The model will calculate the constant K value that
    maintains the net present value of the revenue arising from the bankable K
    profile over the period selected.

·   Select ‘K solving’ from the menu bar. Select ‘Reprofiling’ from the drop down
    list. The following dialogue box will appear:




·   Check the box for each 5-year period to be reprofiled. On first selection, if a
    5-year block is not checked, the “bankable” K will be copied down.

·   Once calculated, the profile can be adjusted by typing a value into the K21
    line. After typing values in, select the re-profile option again, and the smooth
    K factor will be recalculated for all cells that have not been over-written.

·   If, at any point, you wish an over-written value to be replaced with a smooth
    K factor, simply delete the value and select the reprofile option again.


                                  43
      ·   The re-profiling steps are optional. If not used the bankable K profile
          recorded in Section 4 of the KSAT copies into Section 5.


      Apply OPA adjustment

      ·   Section 6 on the KSAT is the final step in the K solving process. It is the
          application of the Overall Performance Adjustment (OPA) and Weighted
          Average Charge Increase Adjustment (WACI). The model simply adds the
          adjustment values (as entered on the input sheet) to the solved K profile
          and recalculates the financial statements taking account of the OPA only.

      ·   The WACI adjustment is purely for presentation in reports and does not
          have any impact on revenue in the financial model.

      ·   Select ‘K solving’ from the menu bar. Select ‘Apply OPA adjustment’ from
          the drop down list to populate the final section of the KSAT sheet.

4.6   Saving Scenario

      ·   To save the new scenario select the ‘Save changes’        icon on the menu
          bar.

      ·   The save dialogue form appears.




      ·   Enter a description for the scenario in the 'Description' text box. In the
          'Reason for change' text box enter an audit comment that will represent
          changes to values on the input sheet of the scenario. Select <Save> to save
          and return to the Aquarius 3 main menu.

      ·   To exit without saving the scenario select the ‘Exit’   icon on the menu bar.




                                         44
4.7   Standard reports

      ·   There are 28 standard reports available for selection when a scenario is
          open. The reports do not exist as fixed worksheets within the model. They
          can be generated within the model as and when selected from the report
          dialogue box.

      ·   Select ‘Data’ on the menu bar and select ‘Reports’ from the drop down list.




      ·   The report dialogue box will appear.




                                       45
·   From the list of standard reports in the dialogue box highlight the reports to
    be generated for viewing or printing.

·   The default price base for the standard reports is financial year average
    2002-03 (base year prices). This can be changed using either of the three
    options:
    - Outturn;
    - Year and month; or
    - Financial year average.

·   There is an option to change the currency to Euros. This applies a single
    conversion factor to all years if Euros is selected from the currency drop
    down list.

·   The default setting is for the ‘BoN Numbers’ to be shown on the standard
    reports. Remove the tick to remove these codes from the standard reports
    when printed.

·   There is an option to change the number of decimal points (dp) on the
    reports. The default setting is 3dp for values in £ million, 0dp for values in
    £’s and 0dp for values in percentages such as financial indicators. To
    change these settings tick ‘Change rounding settings’ and select from the
    dropdown boxes the required setting for each of the type of values.

·   The range of years displayed in the reports can be customised by selecting
    the start and end year. To change the default start and/ or end year select
    the required year from each dropdown box on the dialogue box. There is
    also an option to display 5-year totals, a grand total and annual values on
    the reports. To change these settings remove or add tick for each option as
    required.

·   There is an option to enter a description on the headers to each report. The
    description will appear in addition to the report titles. In the ‘description’ text
    box on the dialogue box, enter the description to appear on the report. This
    description will only feature on the reports selected.

·   Select <Ok> to view on screen all the selected reports in the chosen price
    base and currency with the description (if used) and with or without BoN
    numbers.

·   Select <Close> to close the output reports dialogue form and return to the
    spreadsheet.

·   If additional reports are required after the reports dialogue form has closed
    then reopen the dialogue form. By holding down the <Ctrl> button on the
    keyboard select additional reports from the list as required. Selecting
    different settings for new reports will not change previously selected
    settings on existing reports.

·   If previously generated reports are no longer required then reopen the
    report dialogue form. By holding down the <Ctrl> button on the keyboard
    select from the list the report to be removed and click <Ok> to confirm
    removal of the report.
                                 46
      ·   To preview a report before printing select the 'Print Preview'       icon on the
          menu bar.

      ·   To print a report select the 'Print'   icon on the menu bar.

      ·   To close all generated reports select ‘Data’ on the menu bar and select
          ‘Close Reports’ from the drop down list.




4.8   Capital structure sensitivity modelling

      ·   This section details the background and further information on the 'Interest
          adjustment' stage of the K Solving process described in section 4.5 above.

      ·   As explained in Setting water and sewerage price limits for 2005 – 10,
          where necessary, we will adjust companies’ opening balance sheets to
          ensure the level of gearing is consistent with our view of the weighted
          average cost of capital. Our assumptions about the level of interest
          reflected in the cashflow and the financial statements will also reflect this
          view. However, the amount of interest taken into account in the tax
          calculations will reflect the company’s actual balance sheet for the base
          year.

      ·   Aquarius 3 includes a switch to adjust the amount of interest that flows
          through the tax calculation to reflect either our view of efficient capital
          structure or the actual balance sheet.

      ·   The switch is on the input sheet in cell T837 of the input sheet. Where the
          outcome of the modelling will be as follows:

          -   Input value of null – actual balance sheet interest and actual tax for non-
              highly geared companies;




                                          47
          -   Input value of ‘0’ – notional balance sheet interest and notional tax for
              highly geared companies; and

          -   Input value ‘1’ – notional balance sheet interest and actual tax for highly
              geared companies.

      ·   The initial setting of the switch value (in cell T837) is ‘blank’. The model
          outcome will be the same as for a setting of ‘0’.

      ·   When the switch is set to ‘1’ the interest adjustment cells on row 139 of the
          KSAT sheet must be populated. The interest adjustment is the difference in
          interest between actual balance sheet and the adjusted notional balance
          sheet.

      ·   The interest adjustment can be populated when the switch is set to ‘0’. In
          this setting the interest adjustment is not picked up in the calculations.

      ·   Select <Ctrl><L> to toggle the switch setting.

      ·   The title lines on the KSAT sheet will show the status of the switch.


4.9   Efficiency sensitivity modelling

      ·   To establish the sensitivity of efficiencies Aquarius 3 includes a switch to
          turn ‘off’ or ‘on’ all the efficiency calculations.

      ·   The switch is on the input sheet in cell T858. Where the status of the
          modelling can be as follows:

          -   Input value of ‘0’ – On - efficiency calculations included in modelling;

          -   Input value of ‘1’ – Off - efficiency calculations not included in modelling.

      ·   The title lines on all the sheets in the model will show ‘Pre-efficiency’ if the
          switch has been set to ‘1’.

      ·   The switch can also be activated by selecting <Ctrl>K> to toggle the switch
          setting.




                                         48

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:9/23/2011
language:English
pages:19