Download - Best Practice Model Example 6.1
-
7/28/2019 Best Practice Model Example 6.1
1/74
Best Practice ModellingExample Best Practice Model 6.1
Primary Developer: BPM
Cover Notes
- This is a simple example of a best practice historical and forecast business planning model.
-
- 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.
- For more information see: Model Notes
Go to Table of Contents
IMPORTANT NOTE: BPM does not provide any warranties or guarantees relating to the correctness of
the formulas or outputs contained in this model.
The purpose of this model is to provide an example of a historical and forecast business planning model developed using
bpmToolbox in accordance with the Best Practice Spreadsheet Modelling Standards (Version 6.1).
http://www.bestpracticemodelling.com/network/subscribehttp://www.bestpracticemodelling.com/network/subscribehttp://www.bestpracticemodelling.com/network/subscribe -
7/28/2019 Best Practice Model Example 6.1
2/74
Table of ContentsExample Best Practice Model 6.1
Section & Sheet Titles Page
4
56
7
1516
- -
- -
- -
19
2021
2223
24
26
2829
- -
- -
- -
- -
- -
- -
34
3536
37
39
4142
- -
- -
Time Series Assumptions
2.2. Historical Assumptions
b. Balance Sheet - Historical Assumptions
c.
Go to Cover Sheet
Keysa. Keys
Range Naming Key
a. Income Statement - Historical Assumptions
a.
3.1. Historical Outputs
c. Cash Flow Statement - Historical Outputs
a. Forecast Outputs
a. Income Statement - Historical Outputs
b. Balance Sheet - Historical Outputs
1. Overview
1.1. Notes
Operational - Outputs
Working Capital - Outputs
a. Model Notes
1.2.
Formats & Styles Key
Sheet Naming Key
b. Standards Illustration Sheet
3. Outputs
2.3. Forecast Assumptionsa. Forecast Assumptions
Assets - Assumptions
Operational - Assumptions
Working Capital - Assumptions
2. Assumptions
2.1. Time Series Assumptions
3.2. Forecast Outputs
Cash Flow Statements - Historical Assumptions
Capital - Assumptions
Taxation - Assumptions
Other Balance Sheet Items - Assumptions
-
7/28/2019 Best Practice Model Example 6.1
3/74
Table of ContentsExample Best Practice Model 6.1
Section & Sheet Titles Page
Go to Cover Sheet
- -
- -
- -
- -
49
5052
5657
58
60
6263
64
6566
- -
- -
- -
6970
73
74
Total Pages: 74
a. Time Series Lookup Tables
Balance Sheet - Forecast Outputs
b. Income Statement - Forecast Outputs
Other Balance Sheet Items - Outputs
Capital - Outputs
Taxation - Output Summary
a. Income Statement - All Periods Outputs
c.
Assets - Outputs
4. Appendices
d. Cash Flow Statement - Forecast Outputs
3.3. All Periods Outputs
c. Cash Flow Statement - All Periods Outputs
b. Balance Sheet - All Periods Outputs
Sensitivity Checks
b. Capital - Lookup Tables
3.4. Dashboard Outputsa. Business Planning Summary
4.2. Lookup Tables
c. Dashboards - Lookup Tables
4.1. Checksa. Checks
Alert Checks
Error Checks
-
7/28/2019 Best Practice Model Example 6.1
4/74
OverviewSection 1.Example Best Practice Model 6.1
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.
Go to Table of Contents
139825453.xls.ms_office
Overview_SC
Printed: 1:10 PM on 4/14/2013 Page 4 of 74
-
7/28/2019 Best Practice Model Example 6.1
5/74
NotesSub-Section 1.1.Example Best Practice Model 6.1
Sub-Section Cover Notes
Contains general notes about the purpose and use of this model.
Also contains contact details for BPM.
Go to Table of Contents
139825453.xls.ms_office
Notes_SSC
Printed: 1:10 PM on 4/14/2013 Page 5 of 74
-
7/28/2019 Best Practice Model Example 6.1
6/74
Model NotesExample Best Practice Model 6.1
x h O
Area: Notes
To order the Best Practice Spreadsheet Modelling Standards, go to:
www.bestpracticemodelling.com/downloads/standardsDownloads:
Telephone:
Email:
Website:
+613 9244 9800
www.bestpracticemodelling.com
Australia 3000
Address:
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.
BPM
Level 8, 330 Collins Street
Melbourne, Victoria
Contact BPM
Annotations
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.
Further Training
Standards
Many components of this model have been significantly simplified to prevent confusion.
This model has been built in accordance with Version 6.1 of the Best Practice Spreadsheet Modelling
Standards.
General
Intended Audience
This model has been designed to provide an example of the implementation of the Best Practice
Spreadsheet Modelling Standards Version 6.1.
Simplification
Go to Table of Contents
This model has been developed for those interested in implement spreadsheet best practices.
http://www.bestpracticemodelling.com/downloads/standardsmailto:[email protected]?subject=Inquiry%20-%20Re%20Forecast%20Business%20Planning%20Model%206.0http://www.bestpracticemodelling.com/http://www.bestpracticemodelling.com/mailto:[email protected]?subject=Inquiry%20-%20Re%20Forecast%20Business%20Planning%20Model%206.0http://www.bestpracticemodelling.com/downloads/standards -
7/28/2019 Best Practice Model Example 6.1
7/74
Standards Illustration SheetExample Best Practice Model 6.1
SMA 1. General Concepts
BPMS 1-1 Workbook Purpose
The purpose of a workbook should be the primary consideration of a model developer during every stage of a workbooks development.
The purpose of a workbook can be universally segregated into three levels as follows:
a) The purpose of the workbook;
b) The purpose of each sheet; and
c) The purpose of each component within each sheet.
BPMS 1-2 Sheet Classification
The sheet content and sheet purpose of every sheet in a workbook should be visually identifiable at all times.
BPMS 1-3 Sheet Content
Every sheet in a workbook should be visually identifiable as being one of the following sheet types:
a) Cover sheetb) Contents sheet
c) Section cover sheet
d) Model schematic sheet
e) Time series sheet
f) Blank sheet
g) Lookup sheet
h) Chart sheet
BPMS 1-4 Sheet Purpose
Every sheet in a workbook should have the purpose of either collecting assumptions or not collecting assumptions.Hence, every sheet in a workbook should be visually identifiable as having one of the following sheet purposes:
a) Assumption sheet; or
b) Output sheet.
BPMS 1-5 Cell Classification
The cell content and cell purpose of every cell in every worksheet should be visually identifiable at all times.
BPMS 1-6 Cell Content
Every cell in every worksheet should be visually identifiable as containing one of the following content types:
a) Constant;b) Formula; or
c) Mixed (combination of constant and formula).
Go to Blank Sheet Example
Go to Lookup Sheet Example
Go to Table of Contents x h O
Go to Workbook Purpose Example
Go to Sheet Classification Example
Go to Cover Sheet Example
Go to Cell Classification Example
Go to Cell Content Example
Go to Contents Sheet Example
Go to Section Cover Sheet Example
Go to Assumption Sheet Example
Go to Output Sheet Example
Go to Time Series Sheet Example
-
7/28/2019 Best Practice Model Example 6.1
8/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
BPMS 1-7 Cell Purpose
Every cell in every worksheet should have the purpose of either collecting assumptions or not collecting assumptions.
Hence, every cell in every worksheet should be visually identifiable as having one of the following cell purposes:
a) Assumption cell; or
b) Output cell.
BPMS 1-8 Assumption Classification
An assumption is defined as anything within a workbook that is intended to be manipulated by model users to affect output.
Every assumption in a workbook must be classified as one of the following types:
a) Base assumption; or
b) Sensitivity assumption.
BPMS 1-9 Assumption Cell Content
Every assumption cell in every worksheet should contain constant cell content.
SMA 2. Workbook Structure
BPMS 2-1 Workbook Cover Sheet
Every workbook that contains more than one sheet should contain a separate cover sheet as the first sheet in the workbook.
BPMS 2-2 Workbook Sections
Every workbook that contains multiple categories or similar types of information should be separated into sections.
A separate section should be created in a workbook for each sheet or group of sheets containing similar types of information.
BPMS 2-3 Section Cover Covers
A section cover sheet should be used at the start of each section in a workbook to indicate the commencement of each new section.
BPMS 2-4 Table of Contents
Every workbook with more than one sheet should contain a table of contents outlining the structure and composition of the underlying workbook.
BPMS 2-5 Table of Contents Information
A Table of Contents should:
a) Show the sections of the workbook (if any sections have been created);b) Reference the sheet title of each sheet in the model;
c) Clearly number each section and sheet; and
d) Be located near the front of the workbook (generally the second sheet in the workbook).
BPMS 2-6 Workbook Navigation
Go to Contents Sheet Example
Go to Contents Sheet Example
Go to Workbook Navigation Example
Go to Assumption Cell Example
Go to Assumption Cell Content Example
Go to Cover Sheet Example
Go to Section Cover Sheet Example
Go to Section Cover Sheet Example
Go to Output Cell Example
-
7/28/2019 Best Practice Model Example 6.1
9/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
SMA 3. Sheet Structure
BPMS 3-1 Sheet Titles
Every sheet in a workbook should contain a clearly highlighted sheet title that is:
a) Consistently formatted on every sheet;
b) Consistently located on every sheet type; and
c) Always in view on the screen when that sheet is active.
BPMS 3-2 Sheet Type Consistency
Sheets of the same sheet type within a workbook should be consistently structured and formatted.
This standard applies to:
a) Sheet title, styles and positioning;
b) Heading styles and spacing;
c) Column and row dimensions;
d) Data entry points;e) Hyperlink positioning;
f) Visibility of gridlines;
g) Grouping levels;
h) Zoom and viewing properties;
i) Window panes and splits; and
j) Formats and colours.
BPMS 3-3 Grouping Rows or Columns
When hiding rows or columns in a worksheet, the rows or columns should always be grouped, not hidden.
SMA 4. Formats & Styles
BPMS 4-1 Formats and Styles Key
Every workbook should contain a key or legend that explains the purpose of each format and style that has been applied to the cells in the workbook.
BPMS 4-2 Worksheet Data Alignment
All data of the same type on a worksheet should be consistently aligned down rows or across columns.
BPMS 4-3 Denomination Identification
Every number in a workbook should clearly indicate what type of denomination it is by either:
a) Stating the denomination of a number in an appropriate corresponding heading, title column, row or label; or
b) Formatting the number such that it is displayed as its denominator (e g $20 20 tonnes 20% or 20 0x)
Go to Worksheet Data Alignment Example A
Go to Denomination Identification Example
Go to Sheet Type Consistency Example B
Go to Sheet Type Consistency Example C
Go to Worksheet Data Alignment Example B
Go to Sheet Titles Example
Go to Sheet Type Consistency Example A
Go to Grouping Rows or Columns Example
Go to Formats and Styles Key Example
-
7/28/2019 Best Practice Model Example 6.1
10/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
BPMS 4-5 Hyperlink Consistency
All hyperlinks within a workbook should use a consistent, dedicated style or format so that they are visually identifiable as being hyperlinks.
BPMS 4-6 Work in Progress
Any cell within a workbook that is subject to further work or not finalised should be visually identifiable as being work in progress.
SMA 5. Assumption Entry Interfaces
BPMS 5-1 Assumptions Location
All assumptions contained in a workbook should be located on dedicated and visually identifiable assumption sheets.
Assumptions should never be located on output sheets.
BPMS 5-2 No Assumption Repetition
Any single assumption should never be entered more than once into a workbook.
BPMS 5-3 Control Cell Link Placement
Every cell link that is attached to a control in a workbook should be located in the top left cell of the range over which its control is placed.
BPMS 5-4 Control Lookup Data
When using a control in a workbook that requires an input range (lookup data), the lookup data should always be located on a separate lookup sheet.
BPMS 5-5 In-Cell Drop Down Lists
A cell in which data validation is used to create in cell drop down lists the range in which the drop down list is inserted should always be formatted as an assumption cell.
SMA 6. Sensitivity Analysis
BPMS 6-1 Separate Sensitivity Assumption Sheets
Every workbook that contains sensitivity analysis functionality should contain a dedicated sensitivity assumptions section (which is separate to the base assumptions section).
BPMS 6-2 Sheet Type for Sensitivity Assumption Entry Interfaces
All sensitivity assumptions in a workbook should be located on assumption sheets.
BPMS 6-3 Separate Sensitivity Assumption Entry InterfacesSensitivity assumptions should always be located on a dedicated sensitivity assumption sheet which is separate to its corresponding base assumption sheet.
Go to Hyperlink Consistency Example
Go to Work in Progress Example
Go to Control Cell Link Placement Example
Go to In-Cell Drop Down Lists Example
Go to Lookup Sheet Example
Go to Assumption Sheet Example
Go to No Assumption Repetition Example
-
7/28/2019 Best Practice Model Example 6.1
11/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
SMA 7. Outputs & Presentations
BPMS 7-1 Segregation of Outputs
Output sheets and presentations, which may take the form of tables, graphs, diagrams or pictures, amongst other forms, should always be located in either:
a) a separate, clearly labelled section of a workbook; orb) a separate dedicated output workbook.
BPMS 7-2 Presentation Sheets
A workbook may contain sheets which do not comply with the standards and conventions, but these sheets must be presentation sheets.
A presentation sheet is a sheet that is included in a workbook in order to present output which is exempt from the standards and conventions in order to meet aesthetic or corporate requ
BPMS 7-3 Presentation Sheet Usage
Presentation sheets should only be included in a workbook where it is not possible to use non-presentation sheets to achieve the same objective.
SMA 8. Calculation Formulae
BPMS 8-1 Consistent Formulae
When more than one adjacent cell contains a similar type of output the structure and components of the formulae within the cells should always be consistent,
so that the cell can be copied across / down the relevant range without needing to make changes.
BPMS 8-2 No Assumptions in Mixed Cell Content
Assumptions should not be embedded in cells containing mixed cell content i.e. cells containing content with a combination of constant and formula.
BPMS 8-3 Circular ReferencesA workbook or group of linked workbooks should never contain a circular reference.
SMA 9. Naming Principles
BPMS 9-1 Workbook Naming
Each workbook should be named such that the name:
a) Allows for different versions of the workbook;
b) Remains consistent between versions of the workbook; and
c) Differentiates the workbook from other workbooks.
BPMS 9-2 Sheet Naming
Every sheet name in a workbook should indicate the sheet type.
BPMS 9-3 Range Naming
Go to Segregation of Outputs Example
Go to Presentation Sheets Example
Go to Presentation Sheets Example
Go to Consistent Formulae Example
Go to Circular References Example
Go to Sheet Naming Example
Go to Range Naming Example
-
7/28/2019 Best Practice Model Example 6.1
12/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
BPMS 9-4 Standardised Naming Prefixes
Every range name in a workbook should have a standardised prefix to identify what type of range the name refers to or the purpose of that range.
SMA 10. Time Series Analysis
BPMS 10-1 Time Series Assumptions
Every workbook that undertakes time series analysis should clearly state, for each distinct time series:
a) the time series start date; and
b) the time series periodicity.
BPMS 10-2 Time Series Period Labels
A time series should always contain a consistent set of periodicity labels and counters that are located in the same position on every relevant worksheet in the workbook.
The periodicity labels and counters that should appear in every time series sheet are:
a) Period start date;b) Period end date; and
c) Period number (counter).
BPMS 10-3 Time Series Period End Dates
The period end date label for each period in a time series sheet should always be in view on the screen.
BPMS 10-4 Time Series Periodicity Identification
The periodicity of each time series sheet should be clearly identified and always in view on each time series sheet.
BPMS 10-5 Time Series Number of PeriodsA workbook that undertakes time series analysis should always include a cell or cell range that indicates the number of periods in each distinct time series.
BPMS 10-6 Time Series Sheet Consistency
Time series sheets for each distinct time series within a workbook should always:
a) Contain the same number of periods; and
b) Have the first period starting in the same column (or more rarely, row).
SMA 11. Checks
BPMS 11-1 Checks Classification
All checks in a workbook should be classified as being one of the following check types:
a) Error check;
b) Sensitivity check; or
c) Alert check
Go to Error Check Example
Go to Alert Check Example
Go to Checks Classification Example
Go to Time Series Assumptions Example
Go to Time Series Period Labels Example
Go to Time Series Period End Dates Example
Go to Time Series Periodicity Identification Example
Go to Time Series Number of Periods Example
Go to Time Series Sheet Consistency Example
Go to Standardised Naming Prefixes Example
-
7/28/2019 Best Practice Model Example 6.1
13/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
BPMS 11-3 Sensitivity Checks
Every workbook that contains one or more sensitivity assumptions should contain sensitivity checks to identify when there is an operative sensitivity assumption.
BPMS 11-4 Alert Checks
Every workbook that requires checks that are not classified as error checks or sensitivity checks should contain alert checks to identify when such a check has been triggered.
BPMS 11-5 Error Checks Summary
The outcome of every error check in a workbook should be displayed in a dedicated and separate error checks summary.
BPMS 11-6 Sensitivity Checks Summary
The outcome of every sensitivity check in a workbook should be displayed in a dedicated and separate sensitivity checks summary.
BPMS 11-7 Alert Checks Summary
The outcome of every alert check in a workbook should be displayed in a dedicated and separate alert checks summary.
BPMS 11-8 Check Indicator Flag
A message or indicator that clearly notifies the model developer or user that a check has been triggered in a workbook should always be in view on every worksheet in a workbook.
BPMS 11-9 Check Cell Formatting
Each check cell in a workbook should be formatted in such a way that it will visually indicate when an error, sensitivity or alert check has been triggered.
BPMS 11-10 Dedicated Checks Summaries
A workbook should not contain more than one of each of the following types of check summaries:
a) Error checks summary;
b) Sensitivity checks summary; and/orc) Alert checks summary.
SMA 12. Printing & Viewing
BPMS 12-1 Table of Contents Page Numbers
Every workbook with more than one sheet should contain a table of contents that displays the corresponding printed page numbers for each sheet.
As such a workbook should always print with a Table of Contents that is consistent with any page numbers printed on the individual sheet pages.
BPMS 12-2 Sheet Page NumbersEvery sheet within a workbook should contain page numbers that correspond with the printed page numbers stated in the workbook table of contents, when printing the entire workboo
BPMS 12-3 Page Margin Consistency
The page margins on every sheet in a workbook should be consistent.
Go to Sheet Page Numbers Example
Go to Alert Check Example
Go to Error Checks Summary Example
Go to Alert Checks Summary Example
Go to Check Indicator Flag Example
Go to Check Cell Formatting Example
Go to Dedicated Checks Summaries Example
Go to Table of Contents Page Numbers Example
-
7/28/2019 Best Practice Model Example 6.1
14/74
Standards Illustration SheetExample Best Practice Model 6.1Go to Table of Contents x h O
BPMS 12-5 Page View Consistency
The view type should be the same for each sheet in a workbook.
BPMS 12-6 Worksheet View Consistency
Prior to providing a workbook to a model user, the view of every worksheet in the workbook should be set such that the top-left corner of the worksheet is in view (i.e. cell A1 is selected).
SMA 13. Multiple Workbooks
BPMS 13-1 External Workbook Imports
All links from an external workbook into a workbook should be made via dedicated and separate model import sheets.
BPMS 13-2 External Workbook Exports
All links to an external workbook from a workbook should be made via dedicated and separate model export sheets.
BPMS 13-3 Workbook Output Links
All formulae on a model export worksheet should always be linked directly to the workbook calculations.
Content on a model export worksheet should never be moved from one workbook to another workbook in a manner (e.g. copied and pasted as values) which creates static data that will not c
when changes are made to the workbook from which the data originated.
SMA 14. Security and Protectionn/a
SMA 15. Visual Basic Programmingn/a
SMA 16. Miscellaneous
BPMS 16-1 Automatic Calculation Setting
A workbook should, where practical, be set to calculate automatically.
-
7/28/2019 Best Practice Model Example 6.1
15/74
KeysSub-Section 1.2.Example Best Practice Model 6.1
Sub-Section Cover Notes
Contains Formats & Styles, Sheet Naming and Range Naming Keys explaining the approaches adopted throughout this model.
Go to Table of Contents
139825453.xls.ms_office
Keys_SSC
Printed: 1:10 PM on 4/14/2013 Page 15 of 74
-
7/28/2019 Best Practice Model Example 6.1
16/74
KeysExample Best Practice Model 6.1
x h O
Formats & Styles Key
Color Name Color Description / Purpose
Font Colors
Fill Colors
Hyperlink Type Hyperlink Description / Purpose
Sheet Right Hyperlink Links active worksheet to the next visible worksheet.
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.
Hidden White (White) Indicates Assumption Cells when used on the interior of Assumption
Sheets.
Example
Sheet Left Hyperlink Links active worksheet to the previous visible worksheet.
Cover Hyperlink Links Contents Sheet to Cover Sheet. Go To Cover Sheet
Home Hyperlink Links worksheets to Contents Sheet. Go To Table of Contents
Work in Progress (WIP) (Yellow) Indicates ranges contain data or formulae that remain uncertain or are
subject to change.
ErrorError (Red) Indicates calculation errors normally used as a conditional format.
HyperlinkHyperlink Indicates ranges contain Hyperlinks to other ranges within the workbook
or to other linked models.
Go to Table of Contents
Example
Input (Blue) Indicates ranges contain 100% input text / numbers. Input
Assumption Sheet (Grey) Indicates sheet is an Assumption Sheet.
Output (Black) Indicates ranges contain 100% formulas / output calculations. Output
Mixed CellMixed Cell (Green) Indicates ranges contain a mixture of input text / numbers and formulae
/ output calculations.
-
7/28/2019 Best Practice Model Example 6.1
17/74
KeysExample Best Practice Model 6.1
x h OGo to Table of Contents
Sheet Naming Key
Base Sheet Type Sheet Description / Purpose
Notes
* The names of the Cover and Contents sheets are always "Cover" and "Contents" respectively.
** Model Import and Export Sheet suffixes are used in addition to the other sheet naming suffixes.
MEModel Export** Contains worksheet ranges that are referenced by formulas in another
workbook.
LULookup Contains lookup data for use in forms / controls and in worksheet
formulas.
Model Schematic Contains model diagrams and flow charts. MS
Chart Contains a chart. Cht
MIModel Import** Contains formulas that reference worksheet ranges in another workbook.
Blank Assumption Residual category (contains assumptions). BA
TATime Series Assumption Contains time series titles for entering assumptions over a set t ime
frame.
Blank Output Residual category (contains outputs). BO
TOTime Series Output Contains time series titles for calculating outputs over a set time frame.
Suffix
Cover* Indicates the start of a workbook. CoverContents* 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
-
7/28/2019 Best Practice Model Example 6.1
18/74
KeysExample Best Practice Model 6.1
x h OGo to Table of Contents
Range Naming Key
Range Type / Purpose Range Description / Purpose
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
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_
Lookup Names a Lookup Table Array on a Lookup Sheet. LU_
Block Array Single area, multiple cell, non-row, non-column array. BA_
Multiple Area Array Multiple area (includes areas of any type). MAA_
Prefix
Base Cell Single cell base cell (for OFFSET function reference, etc). BC_
Row Array Single row, multiple column, single area array. RA_
Column Array Single column, multiple row, single area array. CA_
-
7/28/2019 Best Practice Model Example 6.1
19/74
AssumptionsSection 2.Example Best Practice Model 6.1
Section Cover NotesContains base case assumptions used to generate the base case outputs.
Go to Table of Contents
139825453.xls.ms_office
Assumptions_SC
Printed: 1:10 PM on 4/14/2013 Page 19 of 74
-
7/28/2019 Best Practice Model Example 6.1
20/74
Time Series AssumptionsSub-Section 2.1.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains assumptions used to drive the time series analysis within the model.
Go to Table of Contents
139825453.xls.ms_office
TS_Ass_SSC
Printed: 1:10 PM on 4/14/2013 Page 20 of 74
-
7/28/2019 Best Practice Model Example 6.1
21/74
Time Series AssumptionsExample Best Practice Model 6.1
x h O
Time Series Assumptions
Core Time Series Assumptions
Title
Periodicity
Financial Year End 31 12
Start Date
Periods
Denomination
Historical & Forecast Period Titles
Include in Period Titles?
Actual PeriodsBudget Periods
Data & Projections - Timing Assumptions
Data Term Basis
Data - Active Periods
Projections - Start Date
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.
- "Inactive Columns Treatment" will only be operative if macros have been included in the active workbook to manage inactive data and projectio
1-Jan-13
-
1
3
2
TRUE
3
Go to Table of Contents
Primary
Annual
1-Jan-10
8
139825453 xls ms office
-
7/28/2019 Best Practice Model Example 6.1
22/74
Historical AssumptionsSub-Section 2.2.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains historical financial statement assumptions.
Go to Table of Contents
139825453.xls.ms_office
Hist_Ass_SSC
Printed: 1:10 PM on 4/14/2013 Page 22 of 74
-
7/28/2019 Best Practice Model Example 6.1
23/74
Income Statement - Historical AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Income Statement
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 (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)
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 Tax29.8 30.6 31.5 32.3 33.1 33.8 34.7 35.7
Notes
1. All assumptions are entered in $Millions.
2. Revenue and expense assumptions are entered as positive and negative numbers respectively.
Go to Table of Contents
139825453.xls.ms_office
-
7/28/2019 Best Practice Model Example 6.1
24/74
Balance Sheet - Historical AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Balance Sheet Opening1-Jan-10
Current Assets
Opening Cash at Bank 5.0 7.5 12.1 24.6 37.4 55.5 68.9 82.7
Change in Cash at Bank 2.5 4.6 12.5 12.8 18.1 13.4 13.8 14.1
Cash at Bank 5.0 7.5 12.1 24.6 37.4 55.5 68.9 82.7 96.8
Accounts Receivable 10.3 10.3 10.5 10.8 11.1 11.3 11.6 11.9 12.2
Other Current Assets 3.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0
Total Current Assets 18.3 20.8 26.7 40.4 54.5 73.9 88.5 103.5 119.0
Non-Current Assets
Assets 146.5 146.5 148.0 149.6 151.2 152.9 154.6 156.3 158.1Intangibles 13.4 13.4 15.3 17.3 19.3 21.4 23.5 25.7 27.9
Deferred Tax Assets - - - - - - - - -
Other Non-Current Assets 4.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0
Total Non-Current Assets 163.9 163.9 168.3 172.9 177.5 182.2 187.1 192.0 197.0
Total Assets 182.1 184.6 195.0 213.3 232.0 256.1 275.6 295.5 316.0
Current Liabilities
Accounts Payable 8.0 8.0 8.2 8.4 8.6 8.8 9.1 9.3 9.5Tax Payable 12.8 12.8 13.1 13.5 13.8 14.2 14.5 14.9 15.3
Interest Payable - - - - - - - - -
Ordinary Equity Dividends Payable - - - - - - - - -
Other Current Liabilities 5.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0
Total Current Liabilities 25.8 25.8 27.3 28.9 30.5 32.0 33.6 35.2 36.8
Go to Table of Contents
139825453.xls.ms office
-
7/28/2019 Best Practice Model Example 6.1
25/74
Balance Sheet - Historical AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Go to Table of Contents
Non-Current Liabilities
Debt 50.0 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 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0
Total Non-Current Liabilities 56.0 56.0 57.0 58.0 59.0 65.0 66.0 67.0 68.0
Total Liabilities 81.8 81.8 84.3 86.9 89.5 97.0 99.6 102.2 104.8
Net Assets 100.3 102.8 110.7 126.4 142.6 159.1 176.0 193.4 211.2
Equity
Ordinary Equity 75.0 75.0 75.0 75.0 75.0 75.0 75.0 75.0 75.0Other Equity 5.0 5.0 5.0 5.0 5.0 5.0 5.0 5.0 5.0
Retained Profits - Unadjusted 20.3 22.8 30.7 46.4 62.6 79.1 96.0 113.4 131.2
Retained Profits - Balancing Item - - - - - - - - -
Retained Profits 20.3 22.8 30.7 46.4 62.6 79.1 96.0 113.4 131.2
Total Equity 100.3 102.8 110.7 126.4 142.6 159.1 176.0 193.4 211.2
Total Error Check Result - - - - - - - - - -
Total Alert Check Result - - - - - - - - - -
Notes
1. All assumptions are entered in $Millions.
139825453.xls.ms_office
-
7/28/2019 Best Practice Model Example 6.1
26/74
Cash Flow Statements - Historical AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Cash Flow Statement
Cash Flow from Operating Activities
Revenue 125.0 128.1 131.3 134.6 138.0 141.4 145.0 148.6
Decrease in Accounts Receivable 10.7 (0.3) (0.2) (0.3) (0.3) (0.3) (0.3) (0.3)
Cash Receipts 135.7 127.9 131.1 134.3 137.7 141.1 144.7 148.3
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)
Increase in Accounts Payable (8.0) 0.2 0.2 0.2 0.2 0.2 0.2 0.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)
Go to Table of Contents
139825453 xls ms office
-
7/28/2019 Best Practice Model Example 6.1
27/74
Cash Flow Statements - Historical AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Go to Table of Contents
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)
Increase in Other Equity 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1
Net Cash Flow from Financing Activities (14.8) (15.2) (15.6) (16.1) (11.4) (16.8) (17.3) (17.7)
Net Increase / (Decrease) in Cash Held 23.7 12.2 12.6 12.9 18.2 13.5 13.9 14.2
Notes
1. All assumptions are entered in $Millions.
139825453.xls.ms_office
CFS_Hist_TA
Printed: 1:10 PM on 4/14/2013 Page 27 of 74
-
7/28/2019 Best Practice Model Example 6.1
28/74
Forecast AssumptionsSub-Section 2.3.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains forecast assumptions for all areas within the underlying business.
Go to Table of Contents
139825453.xls.ms_office
Fcast_Ass_SSC
Printed: 1:10 PM on 4/14/2013 Page 28 of 74
-
7/28/2019 Best Practice Model Example 6.1
29/74
Forecast AssumptionsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Operational - Assumptions
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.7Operating 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
Notes
1. Revenue and expense assumptions are entered in $Millions.
2. Revenue and expense assumptions are entered as positive numbers.
Working Capital - Assumptions
Accounts Receivable1-Jan-13
Opening Balance 10.8
Debtors Days 30 30 30 30 30 30 30 30
Accounts Payable1-Jan-13
Opening Balance 8.4
Creditors Days 45 45 45 45 45 45 45 45
Notes
1. Debtors/creditors days assumptions cannot be greater than the number of days in that period.
Go to Table of Contents
139825453.xls.ms_office
-
7/28/2019 Best Practice Model Example 6.1
30/74
Forecast AssumptionsExample Best Practice Model 6.1
x h OGo to Table of ContentsAssets - Assumptions
Assets
1-Jan-13
Opening Balance 149.6
Depreciation - % of Capital Expenditure 90.0% 90.0% 90.0% 90.0% 90.0% 90.0% 90.0% 90.0%
Intangibles1-Jan-13
Opening Balance 17.3
Depreciation - % of Capital Expenditure 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0%
Capital - Assumptions
Debt
Funds Drawn ($Millions)
1-Jan-13
Opening Balance 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 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
1-Jan-13Opening Interest Payable ($Millions) -
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%
139825453.xls.ms office
-
7/28/2019 Best Practice Model Example 6.1
31/74
Forecast AssumptionsExample Best Practice Model 6.1
x h OGo to Table of ContentsOrdinary Equity
Ordinary Equity Balances ($Millions)
1-Jan-13
Opening Balance 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
Dividends Payable & Paid
1-Jan-13
Opening Dividends Payable ($Millions) -
Determination Method: 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 - - - - - - - -
###
###
Notes
1. Dividends cannot be negative.
2. Dividends cannot exceed accumulated Retained Profits (Opening Retained Profits + Net Profit After Tax) in any period.
Include Opening Retained Profits in NPAT?
Limit dividends to prevent negative Cash at Bank?
139825453 l ffi
-
7/28/2019 Best Practice Model Example 6.1
32/74
Forecast AssumptionsExample Best Practice Model 6.1
x h OGo to Table of Contents
Taxation - Assumptions
Tax Payable1-Jan-13
Opening Tax Payable ($Millions) 13.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.
139825453.xls.ms_office
Fcast_TA
-
7/28/2019 Best Practice Model Example 6.1
33/74
Forecast AssumptionsExample Best Practice Model 6.1
x h OGo to Table of Contents
Other Balance Sheet Items - Assumptions
Cash at Bank
Opening Cash at Bank 24.6
Retained Profits
Opening Retained Profits 46.4
Other Balance Sheet Items Opening
1-Jan-13
Other Current Assets 5.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0
Other Non-Current Assets 6.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0
Other Current Liabilities 7.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0
Other Non-Current Liabilities 8.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0
Other Equity 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8
Notes
1. Balance sheet items are specified in $Millions.
2. Other Current Assets and Other Current Liabilities assumed to impact Other Operating Cash Flows.
3. Other Non-Current Assets and Non-Other Current Liabilities assumed to impact Other Investing Cash Flows.
3. Other Equity is assumed to impact Other Financing Cash Flows.
139825453.xls.ms_office
-
7/28/2019 Best Practice Model Example 6.1
34/74
OutputsSection 3.Example Best Practice Model 6.1
Section Cover Notes
Contains base case outputs - i.e. includes only the impacts of base case assumptions.
Go to Table of Contents
139825453.xls.ms_office
Outputs_SC
Printed: 1:10 PM on 4/14/2013 Page 34 of 74
-
7/28/2019 Best Practice Model Example 6.1
35/74
Historical OutputsSub-Section 3.1.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains historical financial statement outputs.
Go to Table of Contents
139825453.xls.ms_office
Hist_OP_SSC
Printed: 1:10 PM on 4/14/2013 Page 35 of 74
-
7/28/2019 Best Practice Model Example 6.1
36/74
Income Statement - Historical OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Income Statement
Revenue 125.0 128.1 131.3 - - - - -
Cost of Goods Sold (25.0) (25.6) (26.3) - - - - -
Gross Margin 100.0 102.5 105.1 - - - - -
Operating Expenditure (40.0) (41.0) (42.0) - - - - -
EBITDA 60.0 61.5 63.0 - - - - -
Depreciation & Amortization (14.1) (14.5) (14.8) - - - - -
EBIT 45.9 47.0 48.2 - - - - -
Interest Expense (3.3) (3.3) (3.3) - - - - -
Net Profit Before Tax 42.6 43.8 44.9 - - - - -
Tax Expense / (Benefit) (12.8) (13.1) (13.5) - - - - -
Net Profit After Tax 29.8 30.6 31.5 - - - - -
Notes
1. All revenues and expenses are specified in $Millions.2. Revenues and expenses appear as positive and negative numbers respectively.
Go to Table of Contents
139825453.xls.ms_office
IS_Hist_TOf
-
7/28/2019 Best Practice Model Example 6.1
37/74
Balance Sheet - Historical OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Balance Sheet Opening1-Jan-10
Current Assets
Cash at Bank 5.0 7.5 12.1 24.6 - - - - -
Accounts Receivable 10.3 10.3 10.5 10.8 - - - - -
Other Current Assets 3.0 3.0 4.0 5.0 - - - - -
Total Current Assets 18.3 20.8 26.7 40.4 - - - - -
Non-Current Assets
Assets 146.5 146.5 148.0 149.6 - - - - -
Intangibles 13.4 13.4 15.3 17.3 - - - - -
Deferred Tax Assets - - - - - - - - -
Other Non-Current Assets 4.0 4.0 5.0 6.0 - - - - -Total Non-Current Assets 163.9 163.9 168.3 172.9 - - - - -
Total Assets 182.1 184.6 195.0 213.3 - - - - -
Current Liabilities
Accounts Payable 8.0 8.0 8.2 8.4 - - - - -
Tax Payable 12.8 12.8 13.1 13.5 - - - - -
Interest Payable - - - - - - - - -
Ordinary Equity Dividends Payable - - - - - - - - -Other Current Liabilities 5.0 5.0 6.0 7.0 - - - - -
Total Current Liabilities 25.8 25.8 27.3 28.9 - - - - -
Go to Table of Contents
139825453.xls.ms_office
BS Hist TO
-
7/28/2019 Best Practice Model Example 6.1
38/74
-
7/28/2019 Best Practice Model Example 6.1
39/74
Cash Flow Statement - Historical OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Cash Flow Statement
Cash Flow from Operating Activities
Cash Receipts 135.7 127.9 131.1 - - - - -
Cash Payments (73.0) (66.4) (68.1) - - - - -
Interest Paid (3.3) (3.3) (3.3) - - - - -
Tax Paid (3.5) (12.8) (13.1) - - - - -
Decrease in Other Current Assets (1.0) (1.0) (1.0) - - - - -
Increase in Other Current Liabilities 1.0 1.0 1.0 - - - - -
Net Cash Flow from Operating Activities 56.0 45.4 46.6 - - - - -
Cash Flow from Investing Activities
Capital Expenditure - Assets (15.0) (15.4) (15.8) - - - - -Capital Expenditure - Intangibles (2.5) (2.6) (2.6) - - - - -
Decrease in Other Non-Current Assets (1.0) (1.0) (1.0) - - - - -
Increase in Other Non-Current Liabilities 1.0 1.0 1.0 - - - - -
Net Cash Flow from Investing Activities (17.5) (17.9) (18.4) - - - - -
Go to Table of Contents
139825453.xls.ms_office
CFS Hist TO
-
7/28/2019 Best Practice Model Example 6.1
40/74
Cash Flow Statement - Historical OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) - - - - -
Go to Table of Contents
Cash Flow from Financing Activities
Debt Drawdowns - - - - - - - -
Debt Repayments - - - - - - - -
Equity Raisings - - - - - - - -Equity Repayments - - - - - - - -
Dividends Paid During Period (14.9) (15.3) (15.7) - - - - -
Increase in Other Equity 0.1 0.1 0.1 - - - - -
Net Cash Flow from Financing Activities (14.8) (15.2) (15.6) - - - - -
Net Increase / (Decrease) in Cash Held 23.7 12.2 12.6 - - - - -
Notes
1. All cash flows are specified in $Millions.
139825453.xls.ms_office
CFS_Hist_TO
Printed: 1:10 PM on 4/14/2013 Page 40 of 74
-
7/28/2019 Best Practice Model Example 6.1
41/74
Forecast OutputsSub-Section 3.2.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains forecast outputs for all areas within the underlying business other than financial statements.
Go to Table of Contents
139825453.xls.ms_office
Fcast_OP_SSC
Printed: 1:10 PM on 4/14/2013 Page 41 of 74
-
7/28/2019 Best Practice Model Example 6.1
42/74
Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Operational - Outputs
Revenue - - - 134.6 138.0 141.4 145.0 148.6
Cost of Goods Sold - - - 26.9 27.6 28.3 29.0 29.7
Operating Expenditure - - - 43.1 44.2 45.3 46.4 47.5Capital Expenditure - Assets - - - 16.2 16.6 17.0 17.4 17.8
Capital Expenditure - Intangibles - - - 2.7 2.8 2.8 2.9 3.0
Go to Table of Contents
139825453.xls.ms_office
Fcast_OP_TO
-
7/28/2019 Best Practice Model Example 6.1
43/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Working Capital - Outputs
Accounts Receivable Balances ($Millions)
Opening Balance - - - 10.8 11.1 11.3 11.6 11.9
Revenue - - - 134.6 138.0 141.4 145.0 148.6
Cash Receipts - - - (134.3) (137.7) (141.1) (144.7) (148.3)
Closing Balance - - - 11.1 11.3 11.6 11.9 12.2
Closing Balance Periodic Growth (% per Year) - - N/A 2.5% 2.5% 2.2% 2.8%
Debtors Days - - - 30 30 30 30 30
Total Error Checks Result - - - - - - - - -
Accounts Payable Balances ($Millions)
Opening Balance - - - 8.4 8.6 8.8 9.1 9.3
Costs - - - 70.0 71.7 73.5 75.4 77.3
Cash Payments - - - (69.8) (71.5) (73.3) (75.2) (77.0)Closing Balance - - - 8.6 8.8 9.1 9.3 9.5
Closing Balance Periodic Growth (% per Year) - - N/A 2.5% 2.5% 2.2% 2.8%
Creditors Days - - - 45 45 45 45 45
Total Error Checks Result - - - - - - - - -
139825453.xls.ms_office
Fcast_OP_TO
-
7/28/2019 Best Practice Model Example 6.1
44/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Assets - Outputs
Assets Balances ($Millions)
Opening Balance - - - 149.6 151.2 152.9 154.6 156.3
Capital Expenditure - Assets - - - 16.2 16.6 17.0 17.4 17.8
Depreciation - - - (14.5) (14.9) (15.3) (15.7) (16.0)
Closing Balance - - - 151.2 152.9 154.6 156.3 158.1
Total Error Checks Result - - - - - - - - -
Intangibles Balances ($Millions)
Opening Balance - - - 17.3 19.3 21.4 23.5 25.7
Capital Expenditure - Intangibles - - - 2.7 2.8 2.8 2.9 3.0
Amortization - - - (0.7) (0.7) (0.7) (0.7) (0.7)
Closing Balance - - - 19.3 21.4 23.5 25.7 27.9
Total Error Checks Result - - - - - - - - -
139825453.xls.ms_office
Fcast OP TO
-
7/28/2019 Best Practice Model Example 6.1
45/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Capital - Outputs
Debt - Outputs
Funds Drawn ($Millions)
Opening Balance - - - 50.0 50.0 55.0 55.0 55.0
Debt Drawdowns - - - - 50.0 - - -Debt Repayments - - - - (45.0) - - -
Closing Debt Balance - - - 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%
Margin (% p.a.) - - - 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%
Months in Financial Year - - - 12 12 12 12 12Period % of Full Year - - - 100.0% 100.0% 100.0% 100.0% 100.0%
Drawdowns/Repayments % into Period - - - 50.0% 50.0% 50.0% 50.0% 50.0%
Average Debt Outstanding - - - 50.0 52.5 55.0 55.0 55.0
Interest Expense - - - 3.3 3.4 3.6 3.6 3.6
Opening Interest Payable ($Millions) - - - - - - - -
Interest Expense - - - 3.3 3.4 3.6 3.6 3.6
Interest Paid - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Closing Interest Payable - - - - - - - -
3982 3 l ff
-
7/28/2019 Best Practice Model Example 6.1
46/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Ordinary Equity - Outputs
Ordinary Equity Balances ($Millions)
Opening Balance - - - 75.0 75.0 75.0 75.0 75.
Equity Raisings - - - - - - -
Equity Repayments - - - - - - -
Closing Ordinary Equity - - - 75.0 75.0 75.0 75.0 75.0
Dividends Payable & Paid
Opening Balance - - - - - - -
Dividends Declared During Period - - - 16.2 16.5 16.9 17.4 17.
Dividends Paid During Period - - - (16.2) (16.5) (16.9) (17.4) (17.
Closing Dividends Payable - - - - - - -
Total Error Checks Result - - - - - - - -
Alert Check (Limited Dividends) - - - - - - - -
Dividends Payable & Paid - Calculation
Determination Method: % of NPAT
Dividend Declaration Period? - - - Yes Yes Yes Yes Yes
Opening Retained Profits - - - 46.4 62.6 79.1 96.0 113.
Net Profit After Tax (NPAT) - - - 32.3 33.1 33.8 34.7 35.
Maximum Dividends Allowed - - - 78.7 95.6 112.9 130.7 149.0Opening Cash at Bank - - - 24.6 37.8 56.0 69.5 83.
Cash Flow Available for Dividends - - - 29.4 34.7 30.4 31.3 32.
Total Available Cash For Dividends - - - 54.0 72.5 86.4 100.7 115.5
Dividend Payout Ratio - % of NPAT - - - 50.0% 50.0% 50.0% 50.0% 50.0%
Assumed Dividends - Not Applied - - - - - - -
Target Dividends Declared - - - 16.2 16.5 16.9 17.4 17.
Actual Dividends Declared - - - 16.2 16.5 16.9 17.4 17.8
-
7/28/2019 Best Practice Model Example 6.1
47/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Taxation - Output Summary
Tax Expense ($Millions)
Revenue - - - 134.6 138.0 141.4 145.0 148.6
Cost of Goods Sold - - - (26.9) (27.6) (28.3) (29.0) (29.7)
Operating Expenditure - - - (43.1) (44.2) (45.3) (46.4) (47.5)
Depreciation - - - (14.5) (14.9) (15.3) (15.7) (16.0)Amortization - - - (0.7) (0.7) (0.7) (0.7) (0.7)
Interest Expense - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Net Profit Before Tax (NPBT) - - - 46.2 47.2 48.3 49.6 51.0
Accounting Taxable Profit / (Loss) - - - 46.2 47.2 48.3 49.6 51.0
Corporate Taxation Rate - - - 30.0% 30.0% 30.0% 30.0% 30.0%
Tax Expense / (Benefit) - - - 13.8 14.2 14.5 14.9 15.3
Tax Payable (& Paid)
Opening Balance - - - 13.5 13.8 14.2 14.5 14.9
Tax Expense / (Benefit) - - - 13.8 14.2 14.5 14.9 15.3
Tax Paid - - - (13.5) (13.8) (14.2) (14.5) (14.9)
Closing Balance - - - 13.8 14.2 14.5 14.9 15.3
139825453.xls.ms office
-
7/28/2019 Best Practice Model Example 6.1
48/74
Forecast OutputsExample Best Practice Model 6.1
x h OGo to Table of Contents
Other Balance Sheet Items - Outputs
Other Current Assets ($Millions)
Opening Balance - - - 5.0 6.0 7.0 8.0 9.0
Movement - - - 1.0 1.0 1.0 1.0 1.0
Closing Balance - - - 6.0 7.0 8.0 9.0 10.0
Other Non-Current Assets ($Millions)
Opening Balance - - - 6.0 7.0 8.0 9.0 10.0
Movement - - - 1.0 1.0 1.0 1.0 1.0
Closing Balance - - - 7.0 8.0 9.0 10.0 11.0
Other Current Liabilities ($Millions)
Opening Balance - - - 7.0 8.0 9.0 10.0 11.0
Movement - - - 1.0 1.0 1.0 1.0 1.0
Closing Balance - - - 8.0 9.0 10.0 11.0 12.0
Other Non-Current Liabilities ($Millions)
Opening Balance - - - 8.0 9.0 10.0 11.0 12.0
Movement - - - 1.0 1.0 1.0 1.0 1.0
Closing Balance - - - 9.0 10.0 11.0 12.0 13.0
Other Equity ($Millions)
Opening Balance - - - 5.0 5.4 5.5 5.6 5.7
Movement - - - 0.4 0.1 0.1 0.1 0.1Closing Balance - - - 5.4 5.5 5.6 5.7 5.8
139825453.xls.ms_office
Fcast_OP_TO
-
7/28/2019 Best Practice Model Example 6.1
49/74
Income Statement - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Income Statement
Revenue - - - 134.6 138.0 141.4 145.0 148.6
Cost of Goods Sold - - - (26.9) (27.6) (28.3) (29.0) (29.7)
Gross Margin - - - 107.7 110.4 113.1 116.0 118.9
Operating Expenditure - - - (43.1) (44.2) (45.3) (46.4) (47.5)
EBITDA - - - 64.6 66.2 67.9 69.6 71.3
Depreciation & Amortization - - - (15.2) (15.6) (16.0) (16.4) (16.8)
EBIT - - - 49.4 50.6 51.9 53.2 54.5
Interest Expense - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Net Profit Before Tax - - - 46.2 47.2 48.3 49.6 51.0
Tax Expense / (Benefit) - - - (13.8) (14.2) (14.5) (14.9) (15.3)
Net Profit After Tax - - - 32.3 33.1 33.8 34.7 35.7
Error Check - - - - - - - - -
Notes
1. All revenues and expenses are specified in $Millions.
2. Revenues and expenses enter the Income Statement as positive and negative numbers respectively.
Go to Balance Sheet - Forecast Outputs
Go to Table of Contents
139825453.xls.ms_office
IS F TO
-
7/28/2019 Best Practice Model Example 6.1
50/74
Balance Sheet - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Balance Sheet
Current Assets
Cash at Bank - - - 37.8 56.0 69.5 83.4 97.6
Accounts Receivable - - - 11.1 11.3 11.6 11.9 12.2
Other Current Assets - - - 6.0 7.0 8.0 9.0 10.0
Total Current Assets - - - 54.9 74.4 89.1 104.2 119.8
Non-Current Assets
Assets - - - 151.2 152.9 154.6 156.3 158.1
Intangibles - - - 19.3 21.4 23.5 25.7 27.9
Deferred Tax Assets - - - - - - - -
Other Non-Current Assets - - - 7.0 8.0 9.0 10.0 11.0Total Non-Current Assets - - - 177.5 182.2 187.1 192.0 197.0
Total Assets - - - 232.4 256.6 276.2 296.2 316.8
Current Liabilities
Accounts Payable - - - 8.6 8.8 9.1 9.3 9.5
Tax Payable - - - 13.8 14.2 14.5 14.9 15.3
Debt Interest Payable - - - - - - - -
Ordinary Equity Dividends Payable - - - - - - - -Other Current Liabilities - - - 8.0 9.0 10.0 11.0 12.0
Total Current Liabilities - - - 30.5 32.0 33.6 35.2 36.8
Go to Table of Contents
139825453.xls.ms_office
-
7/28/2019 Best Practice Model Example 6.1
51/74
Balance Sheet - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
Non-Current Liabilities
Debt - - - 50.0 55.0 55.0 55.0 55.0
Deferred Tax Liabilities - - - - - - - -
Other Non-Current Liabilities - - - 9.0 10.0 11.0 12.0 13.0Total Non-Current Liabilities - - - 59.0 65.0 66.0 67.0 68.0
Total Liabilities - - - 89.5 97.0 99.6 102.2 104.8
Net Assets - - - 143.0 159.6 176.6 194.1 212.0
Equity
Ordinary Equity - - - 75.0 75.0 75.0 75.0 75.0
Other Equity - - - 5.4 5.5 5.6 5.7 5.8Retained Profits - - - 62.6 79.1 96.0 113.4 131.2
Total Equity - - - 143.0 159.6 176.6 194.1 212.0
Total Error Check Result - - - - - - - - -
Alert Check (Negative Cash) - - - - - - - - -
Notes
1. All balances are specified in $Millions.
Go to Income Statement - Forecast Outputs
Go to Cash Flow Statement - Forecast Outputs
139825453.xls.ms_office
S O
-
7/28/2019 Best Practice Model Example 6.1
52/74
Cash Flow Statement - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Direct Cash Flow Statement
Cash Flow from Operating Activities
Cash Receipts - - - 134.3 137.7 141.1 144.7 148.3
Cash Payments - - - (69.8) (71.5) (73.3) (75.2) (77.0)
Interest Paid - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Tax Paid - - - (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)
Increase in Other Current Liabilities - - - 1.0 1.0 1.0 1.0 1.0
Net Cash Flow from Operating Activities - - - 47.8 48.9 50.1 51.5 52.8
Cash Flow from Investing Activities
Capital Expenditure - Assets - - - (16.2) (16.6) (17.0) (17.4) (17.8)
Capital Expenditure - Intangibles - - - (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)
Increase in Other Non-Current Liabilities - - - 1.0 1.0 1.0 1.0 1.0
Net Cash Flow from Investing Activities - - - (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 - - - (16.2) (16.5) (16.9) (17.4) (17.8)
Increase in Other Equity - - - 0.4 0.1 0.1 0.1 0.1
Net Cash Flow from Financing Activities - - - (15.8) (11.4) (16.8) (17.3) (17.7)
Net Increase / (Decrease) in Cash Held - - - 13.2 18.2 13.5 13.9 14.2
Go to Table of Contents
-
7/28/2019 Best Practice Model Example 6.1
53/74
Cash Flow Statement - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
Indirect Cash Flow Statement
Cash Flow from Operating Activities
Net Profit After Tax - - - 32.3 33.1 33.8 34.7 35.7
(Add Back) Tax Expense - - - 13.8 14.2 14.5 14.9 15.3
(Add Back) Total Debt Interest Expense - - - 3.3 3.4 3.6 3.6 3.6
(Add Back) Total Book Depreciation & Amortisation - - - 15.2 15.6 16.0 16.4 16.8
Decrease in Accounts Receivable - - - (0.3) (0.3) (0.3) (0.3) (0.3)
Increase in Accounts Payable - - - 0.2 0.2 0.2 0.2 0.3
Interest Paid - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Tax Paid - - - (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)
Increase in Other Current Liabilities - - - 1.0 1.0 1.0 1.0 1.0
Net Cash Flow from Operating Activities - - - 47.8 48.9 50.1 51.5 52.8
Cash Flow from Investing Activities
Capital Expenditure - Assets - - - (16.2) (16.6) (17.0) (17.4) (17.8)
Capital Expenditure - Intangibles - - - (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)
Increase in Other Non-Current Liabilities - - - 1.0 1.0 1.0 1.0 1.0
Net Cash Flow from Investing Activities - - - (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 - - - (16.2) (16.5) (16.9) (17.4) (17.8)
Increase in Other Equity - - - 0.4 0.1 0.1 0.1 0.1
Net Cash Flow from Financing Activities - - - (15.8) (11.4) (16.8) (17.3) (17.7)
Net Increase / (Decrease) in Cash Held - - - 13.2 18.2 13.5 13.9 14.2
Cash Flow Statement - Forecast Outputs
-
7/28/2019 Best Practice Model Example 6.1
54/74
Cash Flow Statement - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
139825453.xls.ms_office
CFS_Fcast_TO
Printed: 1:10 PM on 4/14/2013 Page 54 of 74
-
7/28/2019 Best Practice Model Example 6.1
55/74
Cash Flow Statement - Forecast OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December - - - 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
Capital Providers - Cash Flow Reconciliation
Net Cash Flow from Operating Activities - - - 47.8 48.9 50.1 51.5 52.8
Net Cash Flow from Investing Activities - - - (18.8) (19.3) (19.8) (20.3) (20.8)
(Add Back) Interest Paid - - - 3.3 3.4 3.6 3.6 3.6
Cash Flow Available To Capital Providers - - - 32.2 33.0 33.9 34.7 35.6
Interest Paid - - - (3.3) (3.4) (3.6) (3.6) (3.6)
Debt Drawdowns - - - - 50.0 - - -
Debt Repayments - - - - (45.0) - - -
Increase in Other Equity - - - 0.4 0.1 0.1 0.1 0.1
Cash Flow Available to Equity (CFAE) - - - 29.4 34.7 30.4 31.3 32.1
Equity Raisings - - - - - - - -
Equity Repayments - - - - - - - -
Cash Flow Available for Dividends - - - 29.4 34.7 30.4 31.3 32.1
Dividends Paid During Period - - - (16.2) (16.5) (16.9) (17.4) (17.8)Net Increase / (Decrease) in Cash Held - - - 13.2 18.2 13.5 13.9 14.2
Total Error Checks Result - - - - - - - - -
Notes
1. All values are stated in $Millions unless stated otherwise.
Go to Income Statement - Forecast Outputs
Go to Balance Sheet - Forecast Outputs
-
7/28/2019 Best Practice Model Example 6.1
56/74
All Periods OutputsSub-Section 3.3.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains the Income Statement, Balance Sheet and Cash Flow Statement for all periods.
Go to Table of Contents
139825453.xls.ms_office
All_Pers_OP_SSC
Printed: 1:10 PM on 4/14/2013 Page 56 of 74
Income Statement - All Periods Outputs
-
7/28/2019 Best Practice Model Example 6.1
57/74
Income Statement - All Periods OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Income Statement
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
Notes
1. All revenues and expenses are specified in $Millions.2. Revenues and expenses appear as positive and negative numbers respectively.
Go to Table of Contents
139825453.xls.ms_office
Balance Sheet - All Periods Outputs
-
7/28/2019 Best Practice Model Example 6.1
58/74
Balance Sheet - All Periods OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Balance Sheet Opening1-Jan-10
Current Assets
Cash at Bank 5.0 7.5 12.1 24.6 37.8 56.0 69.5 83.4 97.6
Accounts Receivable 10.3 10.3 10.5 10.8 11.1 11.3 11.6 11.9 12.2
Other Current Assets 3.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0
Total Current Assets 18.3 20.8 26.7 40.4 54.9 74.4 89.1 104.2 119.8
Non-Current Assets
Assets 146.5 146.5 148.0 149.6 151.2 152.9 154.6 156.3 158.1
Intangibles 13.4 13.4 15.3 17.3 19.3 21.4 23.5 25.7 27.9
Deferred Tax Assets - - - - - - - - -
Other Non-Current Assets 4.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0Total Non-Current Assets 163.9 163.9 168.3 172.9 177.5 182.2 187.1 192.0 197.0
Total Assets 182.1 184.6 195.0 213.3 232.4 256.6 276.2 296.2 316.8
Current Liabilities
Accounts Payable 8.0 8.0 8.2 8.4 8.6 8.8 9.1 9.3 9.5
Tax Payable 12.8 12.8 13.1 13.5 13.8 14.2 14.5 14.9 15.3
Interest Payable - - - - - - - - -
Ordinary Equity Dividends Payable - - - - - - - - -Other Current Liabilities 5.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 12.0
Total Current Liabilities 25.8 25.8 27.3 28.9 30.5 32.0 33.6 35.2 36.8
Go to Table of Contents
139825453.xls.ms office
Balance Sheet - All Periods Outputs
-
7/28/2019 Best Practice Model Example 6.1
59/74
Balance Sheet All Periods OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
Non-Current Liabilities
Debt 50.0 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 6.0 7.0 8.0 9.0 10.0 11.0 12.0 13.0Total Non-Current Liabilities 56.0 56.0 57.0 58.0 59.0 65.0 66.0 67.0 68.0
Total Liabilities 81.8 81.8 84.3 86.9 89.5 97.0 99.6 102.2 104.8
Net Assets 100.3 102.8 110.7 126.4 143.0 159.6 176.6 194.1 212.0
Equity
Ordinary Equity 75.0 75.0 75.0 75.0 75.0 75.0 75.0 75.0 75.0
Other Equity 5.0 5.0 5.0 5.0 5.4 5.5 5.6 5.7 5.8Retained Profits 20.3 22.8 30.7 46.4 62.6 79.1 96.0 113.4 131.2
Total Equity 100.3 102.8 110.7 126.4 143.0 159.6 176.6 194.1 212.0
Total Error Check Result - - - - - - - - - -
Alert Check (Negative Cash) - - - - - - - - - -
Notes
1. All balances are specified in $Millions.
139825453.xls.ms_office
Cash Flow Statement - All Periods Outputs
-
7/28/2019 Best Practice Model Example 6.1
60/74
Cash Flow Statement All Periods OutputsExample Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 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)
Increase in Other Equity 0.1 0.1 0.1 0.4 0.1 0.1 0.1 0.1
Net Cash Flow from Financing Activities (14.8) (15.2) (15.6) (15.8) (11.4) (16.8) (17.3) (17.7)
Net Increase / (Decrease) in Cash Held 23.7 12.2 12.6 13.2 18.2 13.5 13.9 14.2
Go to Table of Contents
139825453 xls ms office
Cash Flow Statement - All Periods OutputsE l B P i M d l 6 1
-
7/28/2019 Best Practice Model Example 6.1
61/74
Example Best Practice Model 6.1
x h O
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F) 2016 (F) 2017 (F)
Go to Table of Contents
Total Error Checks Result - - - - - - - - -
Notes
1. All values are stated in $Millions unless stated otherwise.
139825453.xls.ms_officeCFS_All_TO
Printed: 1:10 PM on 4/14/2013 Page 61 of 74
-
7/28/2019 Best Practice Model Example 6.1
62/74
Dashboard OutputsSub-Section 3.4.Example Best Practice Model 6.1
Sub-Section Cover Notes:
Contains dashboard-style presentation output sheets.
Go to Table of Contents
139825453.xls.ms_office
Dashboards_SSC
Printed: 1:10 PM on 4/14/2013 Page 62 of 74
Business Planning SummaryExample Best Practice Model 6.1Go to Table of Contents
-
7/28/2019 Best Practice Model Example 6.1
63/74
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F)
Revenue 125.0 128.1 131.3 134.6 138.0 141.4
Cost of Goods Sold (25.0) (25.6) (26.3) (26.9) (27.6) (28.3)
Gross Margin 100.0 102.5 105.1 107.7 110.4 113.1
Operating Expenditure (40.0) (41.0) (42.0) (43.1) (44.2) (45.3)
EBITDA 60.0 61.5 63.0 64.6 66.2 67.9
Depreciation & Amortization (14.1) (14.5) (14.8) (15.2) (15.6) (16.0)
EBIT 45.9 47.0 48.2 49.4 50.6 51.9Interest 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
Tax Expense / (Benefit) (12.8) (13.1) (13.5) (13.8) (14.2) (14.5)
Net Profit After Tax 29.8 30.6 31.5 32.3 33.1 33.8
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F)
Current Assets 20.8 26.7 40.4 54.9 74.4 89.1
Non-Current Assets 163.9 168.3 172.9 177.5 182.2 187.1
Total Assets 184.6 195.0 213.3 232.4 256.6 276.2
Current Liabilities 25.8 27.3 28.9 30.5 32.0 33.6
Non-Current Liabilities 56.0 57.0 58.0 59.0 65.0 66.0
Total Liabilities 81.8 84.3 86.9 89.5 97.0 99.6
Net Assets 102.8 110.7 126.4 143.0 159.6 176.6
Ordinary Equity 75.0 75.0 75.0 75.0 75.0 75.0
Other Equity 5.0 5.0 5.0 5.4 5.5 5.6
Retained Profits 22.8 30.7 46.4 62.6 79.1 96.0
Total Equity 102.8 110.7 126.4 143.0 159.6 176.6
Year Ending 31 December 2010 (A) 2011 (A) 2012 (A) 2013 (F) 2014 (F) 2015 (F)
Cash Receipts 135.7 127.9 131.1 134.3 137.7 141.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)
Operating Cash Flows 56 0 45 4 46 6 47 8 48 9 50 1
Income Statement
Cash Flow Statement
x h O
Balance Sheet
-20
40
60
80
100
120
140
160
2010(A)
2011(A)
2012(A)
2013(F)
2014(F)
2015(F)
2
Revenue
Revenue (Historical)
Revenue (Forecast)
150
200
Revenue (Historica
Operating ExpenditCost of Goods Sold
EBITDA
-5
10
15
20
25
30
35
40
45
50
2010(A)
2011(A)
2012(A)
2013(F)
2014(F)
2015(F)
Operating Expenditure
Operating Expenditure (Forecast)
Operating Expenditure (Historical)
-
7/28/2019 Best Practice Model Example 6.1
64/74
AppendicesSection 4.Example Best Practice Model 6.1
Section Cover Notes
Contains the model appendices
Go to Table of Contents
139825453.xls.ms_office
Appendices_SC
Printed: 1:10 PM on 4/14/2013 Page 64 of 74
-
7/28/2019 Best Practice Model Example 6.1
65/74
ChecksSub-Section 4.1.Example Best Practice Model 6.1
Sub-Section Cover Notes
Contains model checks and lookup tables.
Go to Table of Contents
139825453.xls.ms_office
Checks_SSC
Printed: 1:10 PM on 4/14/2013 Page 65 of 74
Checks
-
7/28/2019 Best Practice Model Example 6.1
66/74
ChecksExample Best Practice Model 6.1
x h O
Error Checks
##
Errors Detected - Summary
Total Errors: -
Error Message (Empty if None):
Error Checks
Error Checks Check Include? Flag
Accounts Receivable Balances ($Millions) - Yes -
Accounts Payable Balances ($Millions) - Yes -
Assets Balances ($Mill ions) - Yes -
Intangibles Balances ($Millions) - Yes -Ordinary Equity - Outputs - Yes -
Income Statement - Forecast Outputs - Yes -
Balance Sheet - Forecast Outputs - Yes -
Cash Flow Statement - Forecast Outputs - Yes -
Total Errors: -
Go to Table of Contents
Include summary in model name?
Checks
-
7/28/2019 Best Practice Model Example 6.1
67/74
Example Best Practice Model 6.1
x h OGo to Table of Contents
Sensitivity Checks
##
Sensitivities Detected - Summary
Total Sensitivities: -
Sensitivity Message (Empty if None):
Sensitivity Checks
Sensitivity Checks Check Include? Flag
Total Sensitivities: -
Include summary in model name?
Checks
-
7/28/2019 Best Practice Model Example 6.1
68/74
Example Best Practice Model 6.1
x h OGo to Table of Contents
Alert Checks
##
Alerts Detected - Summary
Total Alerts: -Alert Message (Empty if None):
Alert Checks
Alert Checks Check Include? Flag
Balance Sheet - Historical Assumptions - Yes -
Balance Sheet - Historical Outputs - Yes -
Ordinary Equity - Outputs - Yes -
Balance Sheet - Forecast Outputs - Yes -
Total Alerts: -
Include summary in model name?
-
7/28/2019 Best Practice Model Example 6.1
69/74
Lookup TablesSub-Section 4.2.Example Best Practice Model 6.1
Sub-Section Cover Notes
Contains model lookup tables.
Go to Table of Contents
139825453.xls.ms_office
LU_SSC
Printed: 1:10 PM on 4/14/2013 Page 69 of 74
Time Series Lookup TablesExample Best Practice Model 6.1Go to Table of Contents
-
7/28/2019 Best Practice Model Example 6.1
70/74
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
1718
19
20
21
22
23
24
25
26
27
28
29
30
31
Go to Table of Contents
139825453.xls.ms_office
TS_LU
Printed: 1:10 PM on 4/14/2013 Page 70 of 74
Time Series Lookup TablesExample Best Practice Model 6.1G t T bl f C t t
-
7/28/2019 Best Practice Model Example 6.1
71/74
Time Series Lookup Tables
Go to Table of Contents
Month Names Names
Month Names LU_Mth_Names
January
February
March
AprilMay
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
139825453.xls.ms_office
TS_LU
Printed: 1:10 PM on 4/14/2013 Page 71 of 74
Time Series Lookup TablesExample Best Practice Model 6.1G t T bl f C t t
-
7/28/2019 Best Practice Model Example 6.1
72/74
Time Series Lookup Tables
Go to Table of Contents
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 Ten100 Hundred
1,000 Thousand
1,000,000 Million
1,000,000,000 Billion
139825453.xls.ms_office
TS_LU
Printed: 1:10 PM on 4/14/2013 Page 72 of 74
Capital - Lookup TablesExample Best Practice Model 6.1Go to Table of Contents
-
7/28/2019 Best Practice Model Example 6.1
73/74
Capital - Lookup Tables
Dividend Method Lookup Names:
Dividend Method LU_Eq_Ord_Div_Meth
% of NPAT
Assume Dividend Amounts
139825453.xls.ms_office
Capital_LU
Printed: 1:10 PM on 4/14/2013 Page 73 of 74
Dashboards - Lookup TablesExample Best Practice Model 6.1Go to Table of Contents
-
7/28/2019 Best Practice Model Example 6.1
74/74
Dashboard Lookup Tables
Selected Period Lookup Names
Selected Period LU_Dashboard_Selected_Period
2010 (A)
2011 (A)
2012 (A)
2013 (F)
2014 (F)2015 (F)
2016 (F)
2017 (F)
139825453.xls.ms_office
Dashboards_LUPrinted: 1:10 PM on 4/14/2013 Page 74 of 74