Contents

					Best Practice Modelling
SMA 8. Calculation Formulae - Best Practice Model Example
Go to Table of Contents




Primary Developer: BPM

Cover Notes
 - This is a simple example of a best practice forecast business planning model.
 - The purpose of this model is to provide an example of a forecast business planning model developed using bpmToolbox in
   accordance with the Best Practice Spreadsheet Modelling Standards.
 - To navigate or view the content of this model, click on the 'Go to Table of Contents' hyperlink above.
 - Subscribe to the Best Practice Modelling Network to be notified of new best practice example models.
 - Use of this model is subject to the training model terms and conditions on the Best Practice Modelling website.
 - For more information see: Model Notes
  Table of Contents
  SMA 8. Calculation Formulae - Best Practice Model Example
  Go to Cover Sheet



 Section & Sheet Titles                                      Page

   1. Overview                                                 4
      1.1. Notes                                               5
            a. Model Notes                                     6
            b. Operational Forecasts - Formula Schematic       7
      1.2. Keys                                                8
            a. Keys                                            9
               - Formats & Styles Key                          -
               - Sheet Naming Key                              -
               - Range Naming Key                              -
   2. Assumptions                                             12
      2.1. Time Series Assumptions                             13
            a. Time Series Assumptions                         14
      2.2. Forecast Assumptions                                15
            a. Assumptions                                     16
               - Operational - Assumptions                      -
               - Working Capital - Assumptions                  -
               - Assets - Assumptions                           -
               - Capital - Assumptions                          -
               - Taxation - Assumptions                         -
               - Other Balance Sheet Items - Assumptions        -
   3. Outputs                                                 21
      3.1. Forecast Outputs                                    22
            a. Outputs                                         23
               - Operational - Outputs                          -
               - Working Capital - Outputs                      -
               - Assets - Outputs                               -
               - Capital - Outputs                              -
               - Taxation - Output Summary                      -
               - Other Balance Sheet Items - Outputs            -
      3.2. Financial Statements                                31
            a. Income Statement                                32
            b. Balance Sheet                                   33
            c. Cash Flow Statement                             35
  Table of Contents
  SMA 8. Calculation Formulae - Best Practice Model Example
  Go to Cover Sheet



 Section & Sheet Titles                                        Page

               - Direct Cash Flow Statement                      -
               - Indirect Cash Flow Statement                    -
               - Capital Providers - Cash Flow Reconciliation    -
      3.3. Dashboard Outputs                                     38
            a. Business Planning Summary                         39
   4. Appendices                                                40
      4.1. Checks                                                41
            a. Checks                                            42
               - Error Checks                                     -
               - Sensitivity Checks                               -
               - Alert Checks                                     -
      4.2. Lookup Tables                                         45
            a. Time Series Lookup Tables                         46
            b. Capital - Lookup Tables                           49
            c. Dashboards - Lookup Tables                        50


  Total Pages:                                                   50
                           Overview
                           Section 1.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Section Cover Notes
                           Contains notes explaining the purpose and use of this model and where more help can be obtained.
                           Contains diagrams summarising designated components of the model.
                           Also contains keys explaining the Formats & Styles, Sheet Naming & Range Naming principles used in this model.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Overview_SC
Printed: 4:32 PM on 12/18/2011                                                              Page 4 of 50
                           Notes
                           Sub-Section 1.1.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes
                           Contains general notes about the purpose and use of this model.
                           Also contains contact details for BPM.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Notes_SSC
Printed: 4:32 PM on 12/18/2011                                                               Page 5 of 50
     Model Notes
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


         Area:                     Notes
         General                   This model has been designed to provide an example of how bpmToolbox can be used within Microsoft
                                   Excel to efficiently build a basic forecast business planning model in strict accordance with the Best
                                   Practice Spreadsheet Modelling Standards & Conventions.

         Intended Audience         This model has been developed for those who:

                                      - Have purchased bpmToolbox;
                                      - Are currently trialing bpmToolbox; or
                                      - Are interested in understanding the benefits of using bpmToolbox.
         Simplification            Many components of this model have been significantly simplified to prevent confusion.

         Standards                 This model has been built in accordance with the Best Practice Spreadsheet Modelling Standards. For
                                   more information on the standards go to www.ssrb.org.

         Applied Theme             This workbook has been built using the bpmToolbox default theme - i.e. Styles, Colors, Page Setups,
                                   etc. These settings may differ from your personal or corporate theme settings.

         Annotations               Brief notes have been included throughout the Section Cover Sheets and Assumption Sheets within
                                   this workbook. These notes have been included in accordance with Best Practice and are not designed
                                   to provide detailed insight into how this model was developed or how it could be improved or
                                   enhanced.

         Further Training          BPM specializes in the provision of Best Practice Spreadsheet Modelling products and services,
                                   including the provision of training and support services. This training and support ranges from our
                                   base courses (such as the bpmToolbox Fundamentals course) to courses customized to meet specific
                                   client needs. For more information on BPM's training and support services, contact BPM on the details
                                   provided below.

         Contact BPM               Email:                       Info@bpmglobal.com
                                   Website:                     www.bestpracticemodelling.com

                                   To order the Best Practice Spreadsheet Modelling Standards, go to:
                                   Downloads:                   www.bestpracticemodelling.com/downloads/standards




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Notes_BO
Printed: 4:32 PM on 12/18/2011                                                                 Page 6 of 50
     Operational Forecasts - Formula Schematic
     SMA 8. Calculation Formulae - Best Practice Model Example
     Go to Table of Contents
                                                    x             h           O


     Formula

               =IF(J$12=1,Fcast_TA!J18,I18*(1+Fcast_TA!J18))



     Options

                   ShowFALSE Formatting?
                        Logic



     Key

                      Purpose                           Purpose              Purpose                        Purpose      Purpose
                      Operator                          Function              Range                          Value       Not Used
                       Result                            Result               Result                         Result       Result



     Sketch

        TRUE         Final Result           TRUE      Logical Test   TRUE   Argument 1
                          IF                               =                   J$12
        125             125.0               TRUE         TRUE         1          1

                                                                     TRUE   Argument 2
                                                                                1
                                                                      1         1

                                            TRUE        If True
                                                     Fcast_TA!J18
                                            125          125.0

                                            TRUE        If False     TRUE   Argument 1
                                                            ×                   I18
                                             0              0         0          0

                                                                     TRUE   Argument 2           TRUE     Argument 1
                                                                                +                             1
                                                                     126       126                    1       1

                                                                                                 TRUE      Argument 2
                                                                                                          Fcast_TA!J18
                                                                                                  125         125.0



ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Op_Formula_P_MS
Printed: 4:32 PM on 12/18/2011                                                         Page 7 of 50
                           Keys
                           Sub-Section 1.2.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes
                           Contains Formats & Styles, Sheet Naming and Range Naming Keys explaining the approaches adopted throughout this model.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Keys_SSC
Printed: 4:32 PM on 12/18/2011                                                            Page 8 of 50
     Keys
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


     Formats & Styles Key

             Color Name                     Color Description / Purpose                                                       Example

             Font Colors

             Constant                       Indicates ranges contain 100% constant (e.g. text/numbers).                       Constant

             Formula                        Indicates ranges contain pure formulas / output calculations.                     Formula

             Mixed                          Indicates ranges contain a mixture of formulas and constants (e.g.                 Mixed
                                            formulas that contain embedded text or numbers).

             Check                          Indicates operative checks – normally used as a conditional format.                Check

             Hyperlink                      Indicates ranges contain hyperlinks to other ranges within the workbook           Hyperlink
                                            or to other linked models.

             Fill Colors

             Color A                        Used to distinguish assumption sheets and/or cells.

             Work In Progress               Indicates ranges contain data or formulas that remain uncertain or are
                                            subject to change.

             Color B                        Used to distinguish assumption sheets and/or cells. May also be used for
                                            a custom purpose.




             Hyperlink Type                 Hyperlink Description / Purpose                                                   Example
             Cover Hyperlink                Links Contents Sheet to Cover Sheet.                                         Go To Cover Sheet

             Home Hyperlink                 Links worksheets to Contents Sheet.                                        Go To Table of Contents

             Custom Hyperlink               Links worksheet ranges to other worksheet ranges in the model.                Linked Cell Text

             Sheet Top Hyperlink            Scrolls worksheet to the upper-most viewable section.                                
             Sheet Left Hyperlink           Links active worksheet to the previous visible worksheet.                            
             Sheet Right Hyperlink          Links active worksheet to the next visible worksheet.                                




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Keys_BO
Printed: 4:32 PM on 12/18/2011                                                                                              Page 9 of 50
     Keys
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


     Sheet Naming Key

             Base Sheet Type                        Sheet Description / Purpose                                                       Suffix
             Cover*                                 Indicates the start of a workbook.                                                 Cover

             Contents*                              Contains the workbook Table of Contents.                                         Contents

             Section Cover                          Indicates the start of a workbook section.                                          SC

             Sub-Section Cover                      Indicates the start of a workbook sub-section.                                     SSC

             Blank Assumption                       Residual category (contains assumptions).                                           BA

             Time Series Assumption                 Contains time series titles for entering assumptions over a set time                TA
                                                    frame.


             Blank Output                           Residual category (contains outputs).                                               BO

             Time Series Output                     Contains time series titles for calculating outputs over a set time frame.          TO



             Lookup                                 Contains lookup data for use in forms / controls and in worksheet                   LU
                                                    formulas.


             Model Schematic                        Contains model diagrams and flow charts.                                            MS

             Chart                                  Contains a chart.                                                                   Cht

             Model Import**                         Contains formulas that reference worksheet ranges in another                        MI
                                                    workbook.

             Model Export**                         Contains worksheet ranges that are referenced by formulas in another                ME
                                                    workbook.


             Presentation**                         Contains presentation outputs that are exempt from the requirements of               P
                                                    the best practice modelling standards and conventions.



         Notes:
         * The names of the Cover and Contents sheets are always "Cover" and "Contents" respectively.
         ** Model Import, Model Export and Presentation Sheet suffixes are used in addition to the other sheet naming suffixes.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Keys_BO
Printed: 4:32 PM on 12/18/2011                                                                                                    Page 10 of 50
     Keys
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


     Range Naming Key

             Range Type / Purpose           Range Description / Purpose                                       Prefix
             Row Array                      Single row, multiple column, single area array.                    RA_

             Column Array                   Single column, multiple row, single area array.                    CA_

             Block Array                    Single area, multiple cell, non-row, non-column array.             BA_

             Multiple Area Array            Multiple area (includes areas of any type).                        MAA_

             Base Cell                      Single cell base cell (for OFFSET function reference, etc).        BC_

             Lookup                         Names a Lookup Table Array on a Lookup Sheet.                      LU_

             Hyperlink                      Hyperlink cell reference.                                          HL_

             Check Box                      Check box cell link.                                               CB_

             Drop Down Box                  Drop down box cell link.                                           DD_

             List Box                       List box cell link.                                                LB_

             Option Button                  Option button cell link.                                           OB_

             Spin Button                    Spin button cell link.                                              S_

             Scroll Bar                     Scroll bar cell link.                                              SB_

             Residual                       Residual category (i.e. single cell non-base cells, etc).        No Prefix




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Keys_BO
Printed: 4:32 PM on 12/18/2011                                                                            Page 11 of 50
                           Assumptions
                           Section 2.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Section Cover Notes
                           Contains base case assumptions used to generate the base case outputs.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Assumptions_SC
Printed: 4:32 PM on 12/18/2011                                                            Page 12 of 50
                           Time Series Assumptions
                           Sub-Section 2.1.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes:
                           Contains assumptions used to drive the time series analysis within the model.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
TS_Ass_SSC
Printed: 4:32 PM on 12/18/2011                                                               Page 13 of 50
     Time Series Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


     Time Series Assumptions

         Core Time Series Assumptions

             Title                                                                              Primary
             Periodicity                                                                        Annual
             Financial Year End                                                      31   31    December 12
             Start Date                                                                         1-Jan-10
             Periods                                                                               10
             Denomination                                                           $Millions      2


         Historical & Forecast Period Titles

             Include in Period Titles?                                                           TRUE
             Actual Periods                                                                        -
             Budget Periods                                                                        -

         Data & Projections - Timing Assumptions

             Data Term Basis                                                                     1
                                                                                    Active Data Periods
             Data - Active Periods                                                                  3
             Projections - Start Date                                                           1-Jan-13

         Notes
           - A Financial Year End assumption of 28th of February is assumed to be a month end financial year end, even in a leap year.
           - The "Model Denomination" assumption will not necessarily automatically change the denomination of the outputs of this model.
           - A "Budget Period" refers to either a period in the current financial year or periods containing combined actual and forecast data.
           - "Data & Projections - Timing Assumptions" are used as the basis for related data and projections time series sheets.
           - Use the bpmToolbox "Update Time Series Columns" tool to hide inactive data and projections time series periods.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
TS_BA
Printed: 4:32 PM on 12/18/2011                                                                             Page 14 of 50
                           Forecast Assumptions
                           Sub-Section 2.2.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes:
                           Contains forecast assumptions for all areas within the underlying business.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_Ass_SSC
Printed: 4:32 PM on 12/18/2011                                                                Page 15 of 50
     Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                           2010 (F)     2011 (F)     2012 (F)    2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Operational - Assumptions
                                                                                  Base Amount    Periodic Growth Rate (%)
         Revenue                                                                         125.0         2.5%        2.5%        2.5%       2.5%       2.5%       2.5%       2.5%
         Cost of Goods Sold                                                               25.0         2.5%        2.5%        2.5%       2.5%       2.5%       2.5%       2.5%
         Operating Expenditure                                                            40.0         2.5%        2.5%        2.5%       2.5%       2.5%       2.5%       2.5%
         Capital Expenditure - Assets                                                     15.0         2.5%        2.5%        2.5%       2.5%       2.5%       2.5%       2.5%
         Capital Expenditure - Intangibles                                                 2.5         2.5%        2.5%        2.5%       2.5%       2.5%       2.5%       2.5%

         Notes
          1. Revenue and expense base amount assumptions are specified in $Millions.
          2. Revenue and expense assumptions are entered as positive numbers.



     Working Capital - Assumptions
                                                             Opening Balance       Debtors/Creditors Days
         Accounts Receivable                                             21.0              30          30             30         30         30         30         30         30
         Accounts Payable                                                16.0              45          45             45         45         45         45         45         45

         Error Check (Invalid Days Assumption)                                -              -              -          -           -          -          -          -          -

         Notes
          1. Opening balance assumptions are specified in $Millions.
          2. Debtors/creditors days assumptions cannot be greater than the number of days in that period.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TA
Printed: 4:32 PM on 12/18/2011                                                                        Page 16 of 50
     Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                       2010 (F)     2011 (F)    2012 (F)      2013 (F)      2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Assets - Assumptions
                                                             Opening Balance    Depreciation/Amortization - % of Capital Expenditure
         Assets                                                        145.0        90.0%       90.0%         90.0%        90.0%         90.0%      90.0%      90.0%      90.0%
         Intangible Assets                                               11.5       25.0%       25.0%         25.0%        25.0%         25.0%      25.0%      25.0%      25.0%

         Notes
          1. Opening balance assumptions are specified in $Millions.



     Capital - Assumptions

         Debt - Assumptions

             Funds Drawn ($Millions)

                  Opening Balance                                                      50.0        50.0           50.0       50.0          50.0       55.0       55.0       55.0
                  Debt Drawdowns                                                          -           -              -          -          50.0          -          -          -
                  Debt Repayments                                                         -           -              -          -          45.0          -          -          -
                  Closing Debt Balance                                                50.0        50.0           50.0       50.0          55.0       55.0       55.0       55.0

                  Drawdowns/Repayments % into Period                                50.0%        50.0%           50.0%     50.0%         50.0%      50.0%      50.0%      50.0%

             Interest Expense

                  Opening Interest Payable                                                -

                  Base Interest Rate (% p.a.)                                       5.00%        5.00%           5.00%     5.00%         5.00%      5.00%      5.00%      5.00%
                  Margin (% p.a.)                                                   1.50%        1.50%           1.50%     1.50%         1.50%      1.50%      1.50%      1.50%
                  All-In Interest Rate (% p.a.)                                     6.50%        6.50%           6.50%     6.50%         6.50%      6.50%      6.50%      6.50%




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TA
Printed: 4:32 PM on 12/18/2011                                                                   Page 17 of 50
     Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                         2010 (F)       2011 (F)       2012 (F)      2013 (F)     2014 (F)   2015 (F)   2016 (F)   2017 (F)



         Ordinary Equity - Assumptions

             Ordinary Equity Balances ($Millions)

                  Opening Balance                                                        75.0           75.0           75.0           75.0       75.0       75.0       75.0       75.0
                  Equity Raisings                                                           -              -              -              -          -          -          -          -
                  Equity Repayments                                                         -              -              -              -          -          -          -          -
                  Closing Ordinary Equity                                               75.0           75.0           75.0           75.0       75.0       75.0       75.0       75.0

             Dividends Payable & Paid

                  Opening Balance                                                            -


                  Dividend Determination Method:                                 % of NPAT
                                                                                      1


                  Dividend Declaration Period?                                        Yes           Yes            Yes            Yes         Yes        Yes        Yes        Yes
                  Dividend Payout Ratio - % of NPAT                                    50.0%         50.0%          50.0%          50.0%       50.0%      50.0%      50.0%      50.0%
                  Assumed Dividends - Not Applied                                           -             -              -              -           -          -          -          -


                  ### Include Opening Retained Profits in NPAT?

                  ### Limit dividends to prevent negative Cash at Bank?


             Notes
              1. Dividends cannot be negative.
              2. Dividends cannot exceed accumulated Retained Profits (Opening Retained Profits + Net Profit After Tax) in any period.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TA
Printed: 4:32 PM on 12/18/2011                                                                        Page 18 of 50
     Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                                2010 (F)        2011 (F)      2012 (F)   2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Taxation - Assumptions

         Tax Payable ($Millions)

              Opening Tax Payable                                                                 3.5

         Taxation Rate

              Corporate Taxation Rate                                                         30.0%

         Notes
          1. Tax is assumed to be paid in the period after tax expense is incurred.
          2. The Corporate Taxation Rate is limited to a minimum of 0% and a maximum of 100%.
          3. Tax calculations do not allow for tax losses, deferred tax assets or deferred tax liabilities - i.e.
             negative tax expense will result in cash tax receipts.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TA
Printed: 4:32 PM on 12/18/2011                                                                                 Page 19 of 50
     Assumptions
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                        2010 (F)      2011 (F)       2012 (F)    2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Other Balance Sheet Items - Assumptions

         Cash at Bank ($Millions)

             Opening Cash at Bank                                                       15.0

         Other Balance Sheet Items ($Millions)
                                                           Opening Balance
             Other   Current Assets                                    2.0               3.0           4.0            5.0        6.0        7.0        8.0        9.0       10.0
             Other   Current Liabilities                               3.0               4.0           5.0            6.0        7.0        8.0        9.0       10.0       11.0
             Other   Non-Current Assets                                4.0               5.0           6.0            7.0        8.0        9.0       10.0       11.0       12.0
             Other   Non-Current Liabilities                           5.0               6.0           7.0            8.0        9.0       10.0       11.0       12.0       13.0

             Notes
              1. Opening balance assumptions are specified in $Millions.
              2. Retained Profits are used to balance the Opening Balance Sheet.
              3. Other Current Assets and Other Current Liabilities assumed to impact Other Operating Cash Flows.
              4. Other Non-Current Assets and Non-Other Current Liabilities assumed to impact Other Investing Cash Flows.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TA
Printed: 4:32 PM on 12/18/2011                                                                       Page 20 of 50
                           Outputs
                           Section 3.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Section Cover Notes
                           Contains base case outputs - i.e. includes only the impacts of base case assumptions.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Base_OP_SC
Printed: 4:32 PM on 12/18/2011                                                               Page 21 of 50
                           Forecast Outputs
                           Sub-Section 3.1.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes:
                           Contains forecast outputs for all areas within the underlying business other than financial statements.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_OP_SSC
Printed: 4:32 PM on 12/18/2011                                                                Page 22 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                        2010 (F)   2011 (F)    2012 (F)      2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Operational - Outputs

         Revenue                                      125.0      128.1          131.3      134.6      138.0      141.4      145.0      148.6
         Cost of Goods Sold                            25.0       25.6           26.3       26.9       27.6       28.3       29.0       29.7
         Operating Expenditure                         40.0       41.0           42.0       43.1       44.2       45.3       46.4       47.5
         Capital Expenditure - Assets                  15.0       15.4           15.8       16.2       16.6       17.0       17.4       17.8
         Capital Expenditure - Intangibles              2.5        2.6            2.6        2.7        2.8        2.8        2.9        3.0




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                  Page 23 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                     2010 (F)    2011 (F)    2012 (F)        2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Working Capital - Outputs

         Accounts Receivable Balances ($Millions)

             Opening Balance                                        21.0        10.3            10.5        10.8        11.1        11.3        11.6        11.9
             Revenue                                               125.0       128.1           131.3       134.6       138.0       141.4       145.0       148.6
             Cash Receipts                                        (135.7)     (127.9)         (131.1)     (134.3)     (137.7)     (141.1)     (144.7)     (148.3)
             Closing Balance                                       10.3        10.5            10.8        11.1        11.3        11.6        11.9        12.2

             Closing Balance Periodic Growth (% per Year)                      2.5%           2.2%         2.8%        2.5%        2.5%        2.2%        2.8%
             Debtors Days                                            30           30             30           30          30          30          30          30

             Total Error Checks Result                      -          -           -               -           -           -           -           -           -

         Accounts Payable Balances ($Millions)

             Opening Balance                                        16.0         8.0             8.2         8.4         8.6         8.8         9.1         9.3
             Costs                                                  65.0        66.6            68.3        70.0        71.7        73.5        75.4        77.3
             Cash Payments                                         (73.0)      (66.4)          (68.1)      (69.8)      (71.5)      (73.3)      (75.2)      (77.0)
             Closing Balance                                        8.0         8.2             8.4         8.6         8.8         9.1         9.3         9.5

             Closing Balance Periodic Growth (% per Year)                      2.5%           2.2%         2.8%        2.5%        2.5%        2.2%        2.8%
             Creditors Days                                          45           45             45           45          45          45          45          45

             Total Error Checks Result                      -          -           -               -           -           -           -           -           -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                                Page 24 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                          2010 (F)    2011 (F)    2012 (F)        2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Assets - Outputs

         Assets Balances ($Millions)

             Opening Balance                            145.0       146.5           148.0       149.6       151.2       152.9       154.6       156.3
             Capital Expenditure - Assets                15.0        15.4            15.8        16.2        16.6        17.0        17.4        17.8
             Depreciation                               (13.5)      (13.8)          (14.2)      (14.5)      (14.9)      (15.3)      (15.7)      (16.0)
             Closing Balance                           146.5       148.0           149.6       151.2       152.9       154.6       156.3       158.1

             Total Error Checks Result           -          -           -               -           -           -           -           -           -

         Intangibles Balances ($Millions)

             Opening Balance                             11.5        13.4            15.3        17.3        19.3        21.4        23.5        25.7
             Capital Expenditure - Intangibles            2.5         2.6             2.6         2.7         2.8         2.8         2.9         3.0
             Amortization                                (0.6)       (0.6)           (0.7)       (0.7)       (0.7)       (0.7)       (0.7)       (0.7)
             Closing Balance                            13.4        15.3            17.3        19.3        21.4        23.5        25.7        27.9

             Total Error Checks Result           -          -           -               -           -           -           -           -           -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                     Page 25 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                            2010 (F)    2011 (F)    2012 (F)       2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Capital - Outputs

         Debt - Outputs

             Funds Drawn ($Millions)

                  Opening Balance                          50.0        50.0           50.0        50.0        50.0        55.0        55.0        55.0
                  Debt Drawdowns                              -           -              -           -        50.0           -           -           -
                  Debt Repayments                             -           -              -           -       (45.0)          -           -           -
                  Closing Debt Balance                    50.0        50.0           50.0        50.0        55.0        55.0        55.0        55.0

             Interest Expense

                  Base Interest Rate (% p.a.)            5.00%       5.00%           5.00%      5.00%       5.00%       5.00%       5.00%       5.00%
                  Margin (% p.a.)                        1.50%       1.50%           1.50%      1.50%       1.50%       1.50%       1.50%       1.50%
                  All-In Interest Rate (% p.a.)          6.50%       6.50%           6.50%      6.50%       6.50%       6.50%       6.50%       6.50%

                  Months in Financial Year                   12          12           12            12          12          12          12          12
                  Period % of Full Year                 100.0%      100.0%       100.0%        100.0%      100.0%      100.0%      100.0%      100.0%
                  Drawdowns/Repayments % into Period     50.0%       50.0%        50.0%         50.0%       50.0%       50.0%       50.0%       50.0%
                  Average Debt Outstanding                 50.0        50.0         50.0          50.0        52.5        55.0        55.0        55.0
                  Interest Expense                         3.3         3.3          3.3           3.3         3.4         3.6         3.6         3.6

                  Opening Interest Payable                    -           -              -           -           -           -           -           -
                  Interest Expense                          3.3         3.3            3.3         3.3         3.4         3.6         3.6         3.6
                  Interest Paid                            (3.3)       (3.3)          (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)
                  Closing Interest Payable                    -           -              -           -           -           -           -           -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                       Page 26 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                              2010 (F)    2011 (F)    2012 (F)       2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



         Ordinary Equity - Outputs

             Ordinary Equity Balances ($Millions)

                  Opening Balance                            75.0        75.0           75.0        75.0        75.0        75.0        75.0        75.0
                  Equity Raisings                               -           -              -           -           -           -           -           -
                  Equity Repayments                             -           -              -           -           -           -           -           -
                  Closing Ordinary Equity                   75.0        75.0           75.0        75.0        75.0        75.0        75.0        75.0

             Dividends Payable & Paid

                  Opening Balance                               -           -              -           -           -           -           -           -
                  Dividends Declared During Period           14.9        15.3           15.7        16.2        16.5        16.9        17.4        17.8
                  Dividends Paid During Period              (14.9)      (15.3)         (15.7)      (16.2)      (16.5)      (16.9)      (17.4)      (17.8)
                  Closing Dividends Payable                     -           -              -           -           -           -           -           -

                  Total Error Checks Result          -          -           -              -           -           -           -           -           -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                         Page 27 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                  2010 (F)      2011 (F)    2012 (F)       2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



             Dividends Payable & Paid - Calculation

                  Dividend Determination Method:           % of NPAT

                  Dividend Declaration Period?                 Yes          Yes          Yes           Yes        Yes        Yes        Yes        Yes

                  Opening Retained Profits                           -         60.9            76.2       92.0      108.1      124.7      141.6      158.9
                  Net Profit After Tax (NPAT)                     29.8         30.6            31.5       32.3       33.1       33.8       34.7       35.7
                  Maximum Dividends Allowed                      29.8         91.6           107.7      124.3      141.2      158.5      176.3      194.6
                  Opening Cash at Bank                            15.0         38.6            50.7       63.2       76.0       94.1      107.5      121.2
                  Cash Flow Available for Dividends               38.5         27.5            28.2       29.0       34.6       30.3       31.2       32.0
                  Total Available Cash For Dividends             53.5         66.0            78.9       92.2      110.6      124.4      138.6      153.2
                  Dividend Payout Ratio - % of NPAT             50.0%        50.0%           50.0%      50.0%      50.0%      50.0%      50.0%      50.0%
                  Assumed Dividends - Not Applied                    -            -               -          -          -          -          -          -
                  Target Dividends Declared                       14.9         15.3            15.7       16.2       16.5       16.9       17.4       17.8
                  Actual Dividends Declared                      14.9         15.3            15.7       16.2       16.5       16.9       17.4       17.8

                  Alert Check (Limited Dividends)      -               -          -               -          -          -          -          -          -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                               Page 28 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                        2010 (F)    2011 (F)    2012 (F)       2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Taxation - Output Summary

         Tax Expense ($Millions)

             Revenue                                  125.0       128.1          131.3       134.6       138.0       141.4       145.0       148.6
             Cost of Goods Sold                       (25.0)      (25.6)         (26.3)      (26.9)      (27.6)      (28.3)      (29.0)      (29.7)
             Operating Expenditure                    (40.0)      (41.0)         (42.0)      (43.1)      (44.2)      (45.3)      (46.4)      (47.5)
             Depreciation                             (13.5)      (13.8)         (14.2)      (14.5)      (14.9)      (15.3)      (15.7)      (16.0)
             Amortization                              (0.6)       (0.6)          (0.7)       (0.7)       (0.7)       (0.7)       (0.7)       (0.7)
             Interest Expense                          (3.3)       (3.3)          (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)
             Net Profit Before Tax (NPBT)             42.6        43.8           44.9        46.2        47.2        48.3        49.6        51.0

             Accounting Taxable Profit / (Loss)       42.6        43.8           44.9        46.2        47.2        48.3        49.6        51.0

             Corporate Taxation Rate                 30.0%       30.0%           30.0%      30.0%       30.0%       30.0%       30.0%       30.0%

             Tax Expense / (Benefit)                  12.8        13.1           13.5        13.8        14.2        14.5        14.9        15.3

         Tax Payable (& Paid)

             Opening Balance                            3.5        12.8           13.1        13.5        13.8        14.2        14.5        14.9
             Tax Expense / (Benefit)                   12.8        13.1           13.5        13.8        14.2        14.5        14.9        15.3
             Tax Paid                                  (3.5)      (12.8)         (13.1)      (13.5)      (13.8)      (14.2)      (14.5)      (14.9)
             Closing Balance                          12.8        13.1           13.5        13.8        14.2        14.5        14.9        15.3




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                   Page 29 of 50
     Outputs
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                          2010 (F)   2011 (F)    2012 (F)    2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Other Balance Sheet Items - Outputs

         Other Current Assets ($Millions)

             Opening Balance                             2.0        3.0           4.0       5.0        6.0        7.0        8.0        9.0
             Movement                                    1.0        1.0           1.0       1.0        1.0        1.0        1.0        1.0
             Closing Balance                             3.0        4.0           5.0       6.0        7.0        8.0        9.0       10.0

         Other Current Liabilities ($Millions)

             Opening Balance                             3.0        4.0           5.0       6.0        7.0        8.0        9.0        10.0
             Movement                                    1.0        1.0           1.0       1.0        1.0        1.0        1.0         1.0
             Closing Balance                             4.0        5.0           6.0       7.0        8.0        9.0       10.0       11.0

         Other Non-Current Assets ($Millions)

             Opening Balance                             4.0        5.0           6.0       7.0        8.0        9.0        10.0       11.0
             Movement                                    1.0        1.0           1.0       1.0        1.0        1.0         1.0        1.0
             Closing Balance                             5.0        6.0           7.0       8.0        9.0       10.0       11.0       12.0

         Other Non-Current Liabilities ($Millions)

             Opening Balance                             5.0        6.0           7.0       8.0        9.0        10.0       11.0       12.0
             Movement                                    1.0        1.0           1.0       1.0        1.0         1.0        1.0        1.0
             Closing Balance                             6.0        7.0           8.0       9.0       10.0       11.0       12.0       13.0




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Fcast_TO
Printed: 4:32 PM on 12/18/2011                                    Page 30 of 50
                           Financial Statements
                           Sub-Section 3.2.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes:
                           Contains the forecast Income Statement, Balance Sheet and Cash Flow Statement.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
FS_OP_SSC
Printed: 4:32 PM on 12/18/2011                                                           Page 31 of 50
     Income Statement
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                       2010 (F)      2011 (F)       2012 (F)      2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Income Statement - Base Case ($Millions)

             Revenue                                                                  125.0         128.1          131.3        134.6       138.0       141.4       145.0       148.6
             Cost of Goods Sold                                                       (25.0)        (25.6)         (26.3)       (26.9)      (27.6)      (28.3)      (29.0)      (29.7)

         Gross Margin                                                                100.0         102.5           105.1       107.7       110.4       113.1       116.0       118.9

             Operating Expenditure                                                    (40.0)        (41.0)          (42.0)      (43.1)      (44.2)      (45.3)      (46.4)      (47.5)

         EBITDA                                                                       60.0          61.5            63.0        64.6        66.2        67.9        69.6        71.3

             Depreciation & Amortization                                              (14.1)        (14.5)          (14.8)      (15.2)      (15.6)      (16.0)      (16.4)      (16.8)

         EBIT                                                                         45.9          47.0            48.2        49.4        50.6        51.9        53.2        54.5

             Interest Expense                                                          (3.3)          (3.3)          (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)

         Net Profit Before Tax                                                        42.6          43.8            44.9        46.2        47.2        48.3        49.6        51.0

             Tax Expense / (Benefit)                                                  (12.8)        (13.1)          (13.5)      (13.8)      (14.2)      (14.5)      (14.9)      (15.3)

         Net Profit After Tax                                                         29.8          30.6            31.5        32.3        33.1        33.8        34.7        35.7

         Error Check                                                        -             -              -              -           -           -           -           -           -

         Notes
          1. Revenues and expenses enter the Income Statement as positive and negative numbers respectively.

         Go to Balance Sheet




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
IS_TO
Printed: 4:32 PM on 12/18/2011                                                                     Page 32 of 50
     Balance Sheet
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                        2010 (F)   2011 (F)    2012 (F)       2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



     Balance Sheet - Base Case ($Millions)

         Current Assets

             Cash at Bank                              38.6       50.7            63.2       76.0       94.1      107.5      121.2      135.4
             Accounts Receivable                       10.3       10.5            10.8       11.1       11.3       11.6       11.9       12.2
             Other Current Assets                       3.0        4.0             5.0        6.0        7.0        8.0        9.0       10.0
             Total Current Assets                     51.8       65.3            79.0       93.1      112.4      127.1      142.1      157.6

         Non-Current Assets

             Assets                                   146.5      148.0           149.6      151.2      152.9      154.6      156.3      158.1
             Intangibles                               13.4       15.3            17.3       19.3       21.4       23.5       25.7       27.9
             Deferred Tax Assets                          -          -               -          -          -          -          -          -
             Other Non-Current Assets                   5.0        6.0             7.0        8.0        9.0       10.0       11.0       12.0
             Total Non-Current Assets                164.9      169.3           173.9      178.5      183.2      188.1      193.0      198.0

         Total Assets                                216.7      234.6           252.9      271.6      295.7      315.1      335.1      355.6

         Current Liabilities

             Accounts Payable                           8.0        8.2             8.4        8.6        8.8        9.1        9.3        9.5
             Tax Payable                               12.8       13.1            13.5       13.8       14.2       14.5       14.9       15.3
             Debt Interest Payable                        -          -               -          -          -          -          -          -
             Ordinary Equity Dividends Payable            -          -               -          -          -          -          -          -
             Other Current Liabilities                  4.0        5.0             6.0        7.0        8.0        9.0       10.0       11.0
             Total Current Liabilities                24.8       26.3            27.9       29.5       31.0       32.6       34.2       35.8




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
BS_TO
Printed: 4:32 PM on 12/18/2011                                  Page 33 of 50
     Balance Sheet
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                        2010 (F)   2011 (F)    2012 (F)       2013 (F)   2014 (F)   2015 (F)   2016 (F)   2017 (F)



         Non-Current Liabilities

             Debt                                      50.0       50.0            50.0       50.0       55.0       55.0       55.0       55.0
             Deferred Tax Liabilities                     -          -               -          -          -          -          -          -
             Other Non-Current Liabilities              6.0        7.0             8.0        9.0       10.0       11.0       12.0       13.0
             Total Non-Current Liabilities            56.0       57.0            58.0       59.0       65.0       66.0       67.0       68.0

         Total Liabilities                            80.8       83.3            85.9       88.5       96.0       98.6      101.2      103.8

         Net Assets                                  135.9      151.2           167.0      183.1      199.7      216.6      233.9      251.8

         Equity

             Ordinary Equity                           75.0       75.0           75.0        75.0       75.0       75.0       75.0       75.0
             Retained Profits                          60.9       76.2           92.0       108.1      124.7      141.6      158.9      176.8

         Total Equity                                135.9      151.2           167.0      183.1      199.7      216.6      233.9      251.8

         Total Error Check Result              -          -          -               -          -          -          -          -          -
         Alert Check (Negative Cash)           -          -          -               -          -          -          -          -          -

         Go to Income Statement
         Go to Cash Flow Statement




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
BS_TO
Printed: 4:32 PM on 12/18/2011                                  Page 34 of 50
     Cash Flow Statement
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                              2010 (F)    2011 (F)       2012 (F)     2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Direct Cash Flow Statement

         Cash Flow from Operating Activities

             Cash Receipts                                  135.7       127.9           131.1       134.3       137.7       141.1       144.7       148.3
             Cash Payments                                  (73.0)      (66.4)          (68.1)      (69.8)      (71.5)      (73.3)      (75.2)      (77.0)
             Interest Paid                                   (3.3)       (3.3)           (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)
             Tax Paid                                        (3.5)      (12.8)          (13.1)      (13.5)      (13.8)      (14.2)      (14.5)      (14.9)
             Decrease in Other Current Assets                (1.0)       (1.0)           (1.0)       (1.0)       (1.0)       (1.0)       (1.0)       (1.0)
             Increase in Other Current Liabilities            1.0         1.0             1.0         1.0         1.0         1.0         1.0         1.0
             Net Cash Flow from Operating Activities        56.0        45.4            46.6        47.8        48.9        50.1        51.5        52.8

         Cash Flow from Investing Activities

             Capital Expenditure - Assets                   (15.0)      (15.4)          (15.8)      (16.2)      (16.6)      (17.0)      (17.4)      (17.8)
             Capital Expenditure - Intangibles               (2.5)       (2.6)           (2.6)       (2.7)       (2.8)       (2.8)       (2.9)       (3.0)
             Decrease in Other Non-Current Assets            (1.0)       (1.0)           (1.0)       (1.0)       (1.0)       (1.0)       (1.0)       (1.0)
             Increase in Other Non-Current Liabilities        1.0         1.0             1.0         1.0         1.0         1.0         1.0         1.0
             Net Cash Flow from Investing Activities       (17.5)      (17.9)          (18.4)      (18.8)      (19.3)      (19.8)      (20.3)      (20.8)

         Cash Flow from Financing Activities

             Debt Drawdowns                                     -           -               -           -        50.0           -           -           -
             Debt Repayments                                    -           -               -           -       (45.0)          -           -           -
             Equity Raisings                                    -           -               -           -           -           -           -           -
             Equity Repayments                                  -           -               -           -           -           -           -           -
             Dividends Paid During Period                   (14.9)      (15.3)          (15.7)      (16.2)      (16.5)      (16.9)      (17.4)      (17.8)
             Net Cash Flow from Financing Activities       (14.9)      (15.3)          (15.7)      (16.2)      (11.5)      (16.9)      (17.4)      (17.8)

         Net Increase / (Decrease) in Cash Held             23.6        12.1            12.5        12.8        18.1        13.4        13.8        14.1




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
CFS_TO
Printed: 4:32 PM on 12/18/2011                                                  Page 35 of 50
     Cash Flow Statement
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                      2010 (F)    2011 (F)       2012 (F)     2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Indirect Cash Flow Statement

         Cash Flow from Operating Activities

             Net Profit After Tax                                    29.8        30.6            31.5        32.3        33.1        33.8        34.7        35.7
             (Add Back) Tax Expense                                  12.8        13.1            13.5        13.8        14.2        14.5        14.9        15.3
             (Add Back) Total Debt Interest Expense                   3.3         3.3             3.3         3.3         3.4         3.6         3.6         3.6
             (Add Back) Total Book Depreciation & Amortisation       14.1        14.5            14.8        15.2        15.6        16.0        16.4        16.8
             Decrease in Accounts Receivable                         10.7        (0.3)           (0.2)       (0.3)       (0.3)       (0.3)       (0.3)       (0.3)
             Increase in Accounts Payable                            (8.0)        0.2             0.2         0.2         0.2         0.2         0.2         0.3
             Interest Paid                                           (3.3)       (3.3)           (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)
             Tax Paid                                                (3.5)      (12.8)          (13.1)      (13.5)      (13.8)      (14.2)      (14.5)      (14.9)
             Decrease in Other Current Assets                        (1.0)       (1.0)           (1.0)       (1.0)       (1.0)       (1.0)       (1.0)       (1.0)
             Increase in Other Current Liabilities                    1.0         1.0             1.0         1.0         1.0         1.0         1.0         1.0
             Net Cash Flow from Operating Activities                56.0        45.4            46.6        47.8        48.9        50.1        51.5        52.8

         Cash Flow from Investing Activities

             Capital Expenditure - Assets                           (15.0)      (15.4)          (15.8)      (16.2)      (16.6)      (17.0)      (17.4)      (17.8)
             Capital Expenditure - Intangibles                       (2.5)       (2.6)           (2.6)       (2.7)       (2.8)       (2.8)       (2.9)       (3.0)
             Decrease in Other Non-Current Assets                    (1.0)       (1.0)           (1.0)       (1.0)       (1.0)       (1.0)       (1.0)       (1.0)
             Increase in Other Non-Current Liabilities                1.0         1.0             1.0         1.0         1.0         1.0         1.0         1.0
             Net Cash Flow from Investing Activities               (17.5)      (17.9)          (18.4)      (18.8)      (19.3)      (19.8)      (20.3)      (20.8)

         Cash Flow from Financing Activities

             Debt Drawdowns                                             -           -               -           -        50.0           -           -           -
             Debt Repayments                                            -           -               -           -       (45.0)          -           -           -
             Equity Raisings                                            -           -               -           -           -           -           -           -
             Equity Repayments                                          -           -               -           -           -           -           -           -
             Dividends Paid During Period                           (14.9)      (15.3)          (15.7)      (16.2)      (16.5)      (16.9)      (17.4)      (17.8)
             Net Cash Flow from Financing Activities               (14.9)      (15.3)          (15.7)      (16.2)      (11.5)      (16.9)      (17.4)      (17.8)

         Net Increase / (Decrease) in Cash Held                     23.6        12.1            12.5        12.8        18.1        13.4        13.8        14.1




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
CFS_TO
Printed: 4:32 PM on 12/18/2011                                                          Page 36 of 50
     Cash Flow Statement
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Year Ending 31 December                                                    2010 (F)    2011 (F)       2012 (F)     2013 (F)    2014 (F)    2015 (F)    2016 (F)    2017 (F)



     Capital Providers - Cash Flow Reconciliation

             Net Cash Flow from Operating Activities                               56.0        45.4            46.6        47.8        48.9        50.1        51.5        52.8
             Net Cash Flow from Investing Activities                              (17.5)      (17.9)          (18.4)      (18.8)      (19.3)      (19.8)      (20.3)      (20.8)
             (Add Back) Interest Paid                                               3.3         3.3             3.3         3.3         3.4         3.6         3.6         3.6
             Cash Flow Available To Capital Providers                             41.7        30.7            31.5        32.2        33.0        33.9        34.7        35.6
             Interest Paid                                                         (3.3)       (3.3)           (3.3)       (3.3)       (3.4)       (3.6)       (3.6)       (3.6)
             Debt Drawdowns                                                           -           -               -           -        50.0           -           -           -
             Debt Repayments                                                          -           -               -           -       (45.0)          -           -           -
             Cash Flow Available to Equity (CFAE)                                 38.5        27.5            28.2        29.0        34.6        30.3        31.2        32.0
             Equity Raisings                                                          -           -               -           -           -           -           -           -
             Equity Repayments                                                        -           -               -           -           -           -           -           -
             Cash Flow Available for Dividends                                    38.5        27.5            28.2        29.0        34.6        30.3        31.2        32.0
             Dividends Paid During Period                                         (14.9)      (15.3)          (15.7)      (16.2)      (16.5)      (16.9)      (17.4)      (17.8)
             Net Increase / (Decrease) in Cash Held                               23.6        12.1            12.5        12.8        18.1        13.4        13.8        14.1

             Total Error Checks Result                                     -          -           -               -           -           -           -           -           -

         Notes
          1. All values are stated in $Millions unless stated otherwise.

         Go to Income Statement
         Go to Balance Sheet




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
CFS_TO
Printed: 4:32 PM on 12/18/2011                                                                        Page 37 of 50
                           Dashboard Outputs
                           Sub-Section 3.3.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes:
                           Contains a dashboard-style presentation output sheet.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Dashboards_SSC
Printed: 4:32 PM on 12/18/2011                                                     Page 38 of 50
     Business Planning Summary
     SMA 8. Calculation Formulae - Best Practice Model Example
     Go to Table of Contents
                x      h    O


                                             Income Statement                                                                                                                 [Insert Tile 2 Heading]                                                                        Year Ending 31 December                                                    2010 (F)      2011 (F)      2012 (F)    2013 (F)    2014 (F)   2015 (F)   2016 (F)   2017 (F)
                                                                                                                                                  Revenue                                                                   Cost of Goods Sold
     Year Ending 31 December                  2010 (F)    2011 (F)    2012 (F)    2013 (F)    2014 (F)    2015 (F)                                                                                                                                                           Chart 1 Data
                                                                                                                                                   Revenue                                                                     Cost of Goods Sold
     Revenue                                     125.0       128.1       131.3       134.6       138.0       141.4    155                                                                                                                                                        Title                   Revenue
                                                                                                                                                                                             31
     Cost of Goods Sold                          (25.0)      (25.6)      (26.3)      (26.9)      (27.6)      (28.3)   150                                                                    30
     Gross Margin                               100.0       102.5       105.1       107.7       110.4       113.1     145                                                                                                                                                        Revenue                                                                      125          128           131         135        138        141        145        149
                                                                                                                                                                                             29
     Operating Expenditure                       (40.0)      (41.0)      (42.0)      (43.1)      (44.2)      (45.3)   140                                                                    28
     EBITDA                                      60.0        61.5        63.0        64.6        66.2        67.9     135                                                                                                                                                    Chart 2 Data
                                                                                                                                                                                             27
     Depreciation & Amortization                 (14.1)      (14.5)      (14.8)      (15.2)      (15.6)      (16.0)
                                                                                                                      130                                                                    26
     EBIT                                        45.9        47.0        48.2        49.4        50.6        51.9                                                                                                                                                                Title                   Cost of Goods Sold
                                                                                                                      125                                                                    25
     Interest Expense                             (3.3)       (3.3)       (3.3)       (3.3)       (3.4)       (3.6)
     Net Profit Before Tax                       42.6        43.8        44.9        46.2        47.2        48.3     120                                                                    24                                                                                  Cost of Goods Sold                                                             25          26            26          27         28         28         29         30
     Tax Expense / (Benefit)                     (12.8)      (13.1)      (13.5)      (13.8)      (14.2)      (14.5)   115                                                                    23
     Net Profit After Tax                        29.8        30.6        31.5        32.3        33.1        33.8     110                                                                    22                                                                              Chart 3 Data
                                                                                                                            2010   2011    2012    2013   2014   2015   2016    2017                      2010    2011      2012   2013    2014      2015   2016      2017
                                                                                                                             (F)    (F)     (F)     (F)    (F)    (F)    (F)     (F)                       (F)     (F)       (F)    (F)     (F)       (F)    (F)       (F)
     EBITDA Margin                              48.0%       48.0%       48.0%       48.0%       48.0%       48.0%                                                                                                                                                                Title                   Operating Expenditure

                                                                                                                                                                                                                                                                                 Operating Expenditure                                                          40          41            42          43         44         45         46         48
                                               Balance Sheet
                                                                                                                                                                              [Insert Tile 4 Heading]                                                                        Chart 4 Data
     Year Ending 31 December                  2010 (F)    2011 (F)    2012 (F)    2013 (F)    2014 (F)    2015 (F)
                                                                                                                                      Operating Expenditure                                                                 EBITDA Breakdown
                                                                                                                                                                                                                 Revenue                           Cost of Goods Sold            Title                   EBITDA Breakdown
                                                                                                                                             Operating Expenditure
     Current Assets                               51.8        65.3        79.0        93.1       112.4       127.1                                                                                               Operating Expenditure             EBITDA
     Non-Current Assets                          164.9       169.3       173.9       178.5       183.2       188.1    50                                                                                                                                                         Revenue                                                                      125          128           131         135        138        141        145        149
                                                                                                                                                                                             200
     Total Assets                               216.7       234.6       252.9       271.6       295.7       315.1     48                                                                                                                                                         Cost of Goods Sold                                                           (25)         (26)          (26)        (27)       (28)       (28)       (29)       (30)
                                                                                                                                                                                             150                                                                                 Operating Expenditure                                                        (40)         (41)          (42)        (43)       (44)       (45)       (46)       (48)
                                                                                                                      46
     Current Liabilities                          24.8        26.3        27.9        29.5        31.0        32.6                                                                           100                                                                                 EBITDA                                                                        60           62            63          65         66         68         70         71
     Non-Current Liabilities                      56.0        57.0        58.0        59.0        65.0        66.0    44
     Total Liabilities                           80.8        83.3        85.9        88.5        96.0        98.6     42                                                                      50                                                                             Chart 4 Data
                                                                                                                      40                                                                          -
     Net Assets                                 135.9       151.2       167.0       183.1       199.7       216.6                                                                                                                                                                Title                   Operating Expenditure - Committed vs. Discretionary - 2010 (F)
                                                                                                                      38                                                                     (50)
     Ordinary Equity                              75.0        75.0        75.0        75.0        75.0        75.0    36                                                                    (100)                                                                                Selected Period         2010 (F)
                                                                                                                                                                                                                                                                                                               1
     Retained Profits                             60.9        76.2        92.0       108.1       124.7       141.6          2010   2011    2012    2013   2014   2015   2016    2017                      2010     2011     2012   2013    2014      2015    2016     2017
     Total Equity                               135.9       151.2       167.0       183.1       199.7       216.6            (F)    (F)     (F)     (F)    (F)    (F)    (F)     (F)                       (F)      (F)      (F)    (F)     (F)       (F)     (F)      (F)       Committed                                                  47.5%            19.0
                                                                                                                                                                                                                                                                                 Discretionary                                              52.5%            21.0

                                            Cash Flow Statement                                                                                                                                                                                                              Chart 6 Data
                                                                                                                                                                 [Insert Tile 6 Heading]
     Year Ending 31 December                  2010 (F)    2011 (F)    2012 (F)    2013 (F)    2014 (F)    2015 (F)
                                                                                                                             Operating Expenditure - Committed vs.                                                        Net Assets - 2010 (F)                                  Title                   Net Assets - 2010 (F)
                                                                                                                                    Discretionary - 2010 (F)
                                                                                                                                                                                             250.0
     Cash Receipts                               135.7       127.9       131.1       134.3       137.7       141.1                                                                                                                                                               Selected Period         2010 (F)
                                                                                                                                                                                                                                                                                                               1
     Cash Payments                               (73.0)      (66.4)      (68.1)      (69.8)      (71.5)      (73.3)
     Other Operating Cash Flows                   (6.8)      (16.0)      (16.4)      (16.7)      (17.3)      (17.7)                                                                          200.0                                                                                                                                                                    Change       Invisible    Visible     Total
                                                                                                                                                                                                                                                                                                                                                        Value
     Operating Cash Flows                        56.0        45.4        46.6        47.8        48.9        50.1                                                                                                                         80.8                                   Graph Inputs                                                                          (+/-)        Column      Column      Value
                                                                                                                                                                  Committed                                                                                                      Assets                                                                     216.7                          -        216.7      216.7
                                                                                                                            Discretionar                                                     150.0
     Capital Expenditure                         (17.5)      (17.9)      (18.4)      (18.8)      (19.3)      (19.8)                                                 47%                                                                                                          Liabilities                                                                              (80.8)       135.9         80.8      216.7
                                                                                                                                 y
     Other Investing Cash Flows                      -           -           -           -           -           -                                                                                                                                                               Net Assets                                                                135.9                           -       135.9      135.9
                                                                                                                                53%                                                                                216.7
     Investing Cash Flows                       (17.5)      (17.9)      (18.4)      (18.8)      (19.3)      (19.8)                                                                           100.0

     Debt Drawdowns/(Repayments)                     -           -           -           -         5.0           -                                                                                                                                            135.9
     Equity Drawdowns/(Repayments)                   -           -           -           -           -           -                                                                            50.0
     Equity Dividends Paid                       (14.9)      (15.3)      (15.7)      (16.2)      (16.5)      (16.9)
     Financing Cash Flows                       (14.9)      (15.3)      (15.7)      (16.2)      (11.5)      (16.9)
                                                                                                                                                                                                      -
                                                                                                                                                                                                                   Assets            Liabilities            Net Assets
     Net Increase/(Decrease) in Cash Held        23.6        12.1        12.5        12.8        18.1        13.4




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
BS_Sum_P_MS
Printed: 4:32 PM on 12/18/2011                                                                                                                                                                              Page 39 of 50
                           Appendices
                           Section 4.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Section Cover Notes
                           Contains checks and lookup tables.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Appendices_SC
Printed: 4:32 PM on 12/18/2011                                  Page 40 of 50
                           Checks
                           Sub-Section 4.1.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes
                           Contains error, sensitivity and alert checks.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Checks_SSC
Printed: 4:32 PM on 12/18/2011                                             Page 41 of 50
     Checks
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Error Checks

         ## Include summary in model name?


         Errors Detected - Summary

             Total Errors:                              -
             Error Message (Empty if None):

         Error Checks

             Error Checks                                   Check   Include?       Flag

             Working Capital - Assumptions                    -       Yes              -
             Accounts Receivable Balances ($Millions)         -       Yes              -
             Accounts Payable Balances ($Millions)            -       Yes              -
             Assets Balances ($Millions)                      -       Yes              -
             Intangibles Balances ($Millions)                 -       Yes              -
             Ordinary Equity - Outputs                        -       Yes              -
             Income Statement                                 -       Yes              -
             Balance Sheet                                    -       Yes              -
             Cash Flow Statement                              -       Yes              -

             Total Errors:                                                             -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Checks_BO
Printed: 4:32 PM on 12/18/2011                                         Page 42 of 50
     Checks
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Sensitivity Checks

         ## Include summary in model name?


         Sensitivities Detected - Summary

             Total Sensitivities:                   -
             Sensitivity Message (Empty if None):

         Sensitivity Checks

             Sensitivity Checks                            Check   Include?       Flag

             Total Sensitivities:                                                     -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Checks_BO
Printed: 4:32 PM on 12/18/2011                                        Page 43 of 50
     Checks
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
    x h O


    Alert Checks

         ## Include summary in model name?


         Alerts Detected - Summary

             Total Alerts:                    -
             Alert Message (Empty if None):

         Alert Checks

             Alert Checks                                  Check   Include?       Flag

             Ordinary Equity - Outputs                       -       Yes              -
             Balance Sheet                                   -       Yes              -

             Total Alerts:                                                            -




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Checks_BO
Printed: 4:32 PM on 12/18/2011                                        Page 44 of 50
                           Lookup Tables
                           Sub-Section 4.2.
                           SMA 8. Calculation Formulae - Best Practice Model Example
                           Go to Table of Contents
                            



                           Sub-Section Cover Notes
                           Contains model lookup tables.




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
LU_SSC
Printed: 4:32 PM on 12/18/2011                                  Page 45 of 50
     Time Series Lookup Tables
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
   


     Time Series Lookup Tables

         Month Days                         Names

                           Month Days       LU_Mth_Days
                               1
                               2
                               3
                               4
                               5
                               6
                               7
                               8
                               9
                               10
                               11
                               12
                               13
                               14
                               15
                               16
                               17
                               18
                               19
                               20
                               21
                               22
                               23
                               24
                               25
                               26
                               27
                               28
                               29
                               30
                               31




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
TS_LU
Printed: 4:32 PM on 12/18/2011                               Page 46 of 50
     Time Series Lookup Tables
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
   


     Time Series Lookup Tables

         Month Names                           Names

                          Month Names          LU_Mth_Names
                             January
                            February
                              March
                               April
                               May
                               June
                               July
                             August
                           September
                             October
                            November
                            December

         Denomination                          Names

                          Denomination         LU_Denom
                             $Billions         Billions
                            $Millions          Millions
                              $'000            Thousands
                                 $             Currency

         Data Term Basis                       Names

                         Data Term Basis       LU_Data_Term_Basis
                         Active Data Periods
                          Projections Start




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
TS_LU
Printed: 4:32 PM on 12/18/2011                                      Page 47 of 50
     Time Series Lookup Tables
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
   


     Time Series Lookup Tables

         Periodicity                         Names

                            Periodicity      LU_Periodicity
                              Annual         Annual
                            Semi-Annual      Semi_Annual
                             Quarterly       Qtrly
                              Monthly        Mthly

         Period Type Names                   Names

                        Period Type Names    LU_Period_Type_Names
                                Year         Yr_Name
                             Half Year       Half_Yr_Name
                              Quarter        Qtr_Name
                               Month         Mth_Name

         Periods In Year                     Names

                         Periods In Year     LU_Pers_In_Yr
                                1            Yrs_In_Yr
                                2            Halves_In_Yr
                                4            Qtrs_In_Yr
                               12            Mths_In_Yr

         Conversion Factors                  Names

                        Conversion Factors
                                 10          Ten
                                100          Hundred
                               1,000         Thousand
                             1,000,000       Million
                           1,000,000,000     Billion




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
TS_LU
Printed: 4:32 PM on 12/18/2011                                      Page 48 of 50
     Capital - Lookup Tables
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
   


     Capital - Lookup Tables

         Dividend Method Lookup                Names:

                        Dividend Method        LU_Eq_Ord_Div_Meth
                            % of NPAT
                     Assume Dividend Amounts




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Capital_LU
Printed: 4:32 PM on 12/18/2011                                      Page 49 of 50
     Dashboards - Lookup Tables
     SMA 8. Calculation Formulae - Best Practice Model Example
    Go to Table of Contents
  


     Dashboard Lookup Tables

         Selected Period Lookup             Names

                         Selected Period    LU_Dashboard_Selected_Period
                             2010 (F)
                             2011 (F)
                             2012 (F)
                             2013 (F)
                             2014 (F)
                             2015 (F)
                             2016 (F)
                             2017 (F)




ca3b338f-6896-4a37-885e-e9f3250832c0.xlsx
Dashboards_LU
Printed: 4:32 PM on 12/18/2011                                             Page 50 of 50

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:19
posted:12/18/2011
language:
pages:50