Financial Modelling Best Practice Guidelines

White Paper
Introduction
The purpose of these guidelines is to provide an overview of some of the best practices that
should be considered when building spreadsheets so as to minimise the risk of errors
occurring and improve reliability, usability, robustness and accuracy.

Not all guidelines will be applicable to all spreadsheets, and the guidelines will not necessarily
be applied in all circumstances.

However, by knowing the guidelines and the purpose of each, the financial modeller will also
know when to depart from the guidelines.

The most important thing is to adopt a consistent approach, ideally one that is shared
amongst all those doing financial modelling in your organisation.

This white paper sets out some guidelines we’ve found useful in our business when building
financial models.

About the Author

JEFF ROBSON

Qualifications and Background

Masters (Applied Finance), B.Comm (Accounting & Information
Systems), CISA, F Fin, FAIM, Excel Expert

Jeff Robson is the Principal Consultant of Access Analytic Solutions, a Perth-

based consulting company that provides financial modelling services in model
development, audit/review, and training.

Jeff holds a Certified Information Systems Auditor from the Information

Systems Audit and Control Association. He has 20 years experience with
Microsoft Excel, 6 years experience in Information Systems Audit, and worked
for 4 years in external audit.

He has been involved in many Excel development projects for clients around the world
including sophisticated financial modelling, auditing and review, and advanced macro
programming.

Access Analytic is the Information Systems audit partner for a number of chartered accounting
firms in Perth and performs IT system reviews, Information Systems audit and control reviews,
and systems selection and implementation for a wide variety of clients.

Phone: +61 8 6210 8500 or +61 4 1258 1486 (0412 581 486 in Australia)
jrobson@accessanalytic.com.au

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 2 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

Guidelines
SPREADSHEET DESIGN
 Use modular spreadsheet blocks so that one part of the file can be changed independently
and without necessarily affecting the other areas. A modular design also allows individual
components to be used as building blocks for future extensions.
 Separate data inputs, calculations, and outputs and clearly identify each to make them
easier to recognise and utilise.
 Where there are many similar sheets, design a template that can be applied to all sheets.
This makes it far easier to maintain than having to edit multiple sheets individually.
 Ideally, information and headings should be entered once then these will flow through the
file to wherever they are required. This makes the file far easier to maintain and reduces
the opportunity for error.
 Label sheets, columns and rows with their applicable headings to make files easy to
follow. The sheet name and/or subheading should provide an indication of the sheet’s
function. Column and row headings should include units and currencies where relevant.
Columns and rows should only contain data in one unit and/or currency.
 Consider the use of sheet and workbook protection to reduce the occurrence of
unauthorised changes.
 When linking multiple files together, ensure dependency information (eg “must have X, Y,
and Z files open when making changes to this file”) is provided in the file to assist with
future maintenance.
 Consider using dynamic ranges for chart data and named ranges to ensure these always
cover all your data.
 Don’t use the same field name in a database table more than once as this causes
readability issues when using pivot tables or filters.
 Minimise the use of blank lines in data/calculation blocks as these cause problems with
formatting and manipulation.

TEMPLATES
 Use separate sheets for Setup (all standing data for the file), Workings (reference and
lookup tables, data storage, and calculations that are not relevant to the user), and About
(details regarding the file’s overall purpose, spreadsheet owner, version, changes, etc).
Workings should be well-documented to assist with maintainability.
 Named ranges should be named consistently throughout the file as
SourcesheetTypeName eg workingsTblMonth (or just wTblMonth) or setupStrFilename.
 Establish templates to be used by spreadsheet builders when constructing new models.
These should include all the basic formatting styles, key sheets, and documentation
templates required for constructing a model in accordance with company policy.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 3 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

FORMATTING
 Data input areas should be easily recognisable eg different background colour. Data
inputs should not be mixed with calculations and inputs should not be hard-coded into
calculations.
 Use sheet tab colours to assist with visually identifying sheets with a similar function.
 Consider using “Center Across Selection” rather than “Merge Cells” – merged cells cause
problems in many situations.

FORMULAS & FUNCTIONS

 Design formulas that can be easily applied in blocks rather than using spaghetti links to
individual cells. Use data tables and lookups to pull data out and place it where it needs to
go.
 Split long formulas into smaller chunks to aid readability. Document longer formulas in
English (eg via cell note) to assist yourself and other users.
 Consider using user-defined functions where long, complex formulas are required. This
simplifies readability and maintainability of formulas and allows better documentation,
portability, and re-use.
 Spreadsheets will generally be read from left to right and top to bottom. As such,
formulas should refer to cells to the left and above.
 Use range names to aid formula readability.
 Calculate results using different cells and/or methods to detect potential errors. Use
Conditional Formatting to provide a visual indication of any errors that exist.
 Use COLUMN() or ROW() to automatically calculate the offset required for VLOOKUP,
HLOOKUP, and other reference functions. This ensures the offset will be correct even if
new columns or rows are inserted.
 Never clear a cell using the space bar, use the Delete key instead. This will prevent errors
from occurring when a formula includes a cell that contains a space character.
 Ensure formulas can handle unusual situations such as zero, negative values or text values
in cells (particularly where formulas use division) eg via data validation, error messages, or
by handling the error in a formula.
 Calculate subtotals using SUBTOTAL rather than SUM to allow easier maintenance.
 Avoid copying a formula that includes a named range from one spreadsheet to the other
as this creates a hidden link between the workbooks.

CHARTS
 Limit the number of data series on a chart to 5 or less, otherwise it becomes too cluttered
and cannot be easily understood.
 Ensure chart axes scale and titles are correct. Consider linking them to a cell.
 When using a chart type such as “line”, ensure there is a logical relationship between the
data.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 4 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

VBA MACROS
 Establish a VBA Style Guide that contains rules and details about coding standards.
 If the same code is written more than once, it may be better to place this into a separate
function that can be called with parameters.
 Use named ranges in macros rather than absolute cell references so that the file can be
amended (eg new row/column inserted or deleted) without requiring amendment to
macro code.
 VBA code should always be well-documented with proper indentation according to the
style manual.
 Use the macro recorder sparingly. The code it produces is lengthy, inefficient, and hard-
coded.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 5 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

Access Analytic Overview
OVERVIEW
Access Analytic provides AMAZING Excel solutions to enable companies to grow, control their
costs and reduce their risks.

Figure 1: Access Analytic Solutions Overview

Our Services fall into three groups:
1. Helping Companies Grow:
 Process Improvement: streamlining processes to create flexible, scalable processes.
 Financial Modelling: to forecast results (e.g. equity/loan capital raising, business
planning, feasibility studies, project economics etc.) with sensitivity and scenario
analysis to support decision-making with confidence.
 Reporting: tailored dashboards and KPIs using Excel and Power BI to connect to both
cloud and on-premise data, thereby allowing companies to keep on top of their
operations, gain insights, and make data-driven decisions.
 Training: training in all of these areas to transfer skills to support further growth.
2. Helping Companies Control Cost
 Process Improvement: efficient Excel-based processes that reduce the need for
manual input.
 Financial Modelling: Excel-based budgeting and forecasting systems that enable
companies to efficiently control and monitor their costs.
 Reporting: reporting to assist Finance, Human Resources and Operations with their
areas of responsibility for cost control.
 Training: assisting staff to become more productive and reduce the need for
additional resources. Courses are provided on an in-house basis, which often results
in a cost saving of 50% or more compared to public courses.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 6 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

3. Helping Companies Reduce Risk
 Process Improvement: by automating processes, companies not only save time, they
also reduce the errors and risks that result from manual inputs. This results in more
accurate data, which can provide better analysis.
 Financial Modelling: independent model auditing services to provide assurance that
key models are producing accurate results and can be relied upon.
 Reporting: reporting that focuses on the right data at the right time to provide
transparency & visibility.
 Training: financial modelling best practices to reduce risk and improve quality.

We are based in Perth, Western Australia and the business was established in 2000.

Access Analytic provides services to clients located in: Australasia, Asia, Africa and the Middle
East. A selection of our clients is shown below:

ACCESS ANALYTIC: RISING STAR

Access Analytic was named as one of the WA Business News’ Rising Stars for 2012, recognising
the success and achievements of our company.

Further details regarding Access Analytic Solutions have been included at Appendix A: Access
Analytic Details on page 10 below.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 7 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

KEY STAFF

Jeff Robson, Principal Business Analyst

Masters (Applied Finance), BCom (Accounting & Information Systems), CISA, MAICD,
FAIM, F Fin, Microsoft Excel Expert

Jeff is the founding Director and Principal Business Analyst at Access Analytic
Solutions. He has over 20 years’ experience in chartered accounting, business
management and consulting.

Jeff holds a Masters in Applied Finance and is a Certified Information Systems

Auditor. He is highly regarded and recognised around the world as an expert in
the field of financial modelling.

His achievements have been recognised by his peers and he is a Fellow of both
the Financial Services Institute of Australasia and the Australian Institute of Management.

Jeff has extensive experience in best practice financial modelling, management reporting,
business analysis, and spreadsheet auditing and is also an entertaining international presenter
and trainer on these subjects.

Wyn Hopkins, Senior Manager

B.Sc. (Hons) Accounting & Financial Management, CA, MS Excel Expert

Wyn gained his Chartered Accountant qualification at

PricewaterhouseCoopers in the UK in 2000.

He has extensive Business Analyst experience in FTSE 100 financial services

companies (HBOS and Barclays in the UK) as well as GESB in Perth and
telecommunications companies industry in Australia.

Wyn is skilled in financial analysis, strategic financial modelling, management

reporting and forecasting, and value-based management techniques.

Yury Tokarev (Manager)

CA, PhD (Finance & Economics), GradDip (Accounting), BBS, Microsoft Excel Expert

Yury joined Access Analytic from WHK Chartered Accountants, where he was
involved in business services and advisory for four years.

Prior to this, he worked as a Corporate Finance Consultant at one of the top

European Investment Banks.

Yury has highly advanced skills in Excel and financial modelling, including
complex financial models and the development of VBA macro solutions.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 8 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

Anthony Kitson, Senior Business Analyst (Contract)
Chartered Accountant (CA), B.Bus (Accounting & Finance), B.Eng (Electronics &
Comms), CISA, Prince2 Practitioner

Anthony has extensive expertise in consulting and business management, with

over 25 years in combined experience.

He specialises in the areas of Financial Modelling, Data Analytics, reporting,

revenue leakage, Business Intelligence, and Information Management.

Anthony’s experience has been gained across the Government, Energy, Power
& Natural Resources, Financial Services, and Industrial sectors.

He has specialised expertise in financial modelling for mining, including building and reviewing
models for Bank Feasibility Studies, project finance, consolidated corporate models,
production planning and workforce planning.

Neale Blackwood, Senior Business Analyst (Contract)

CPA, B.Bus (Accounting), Microsoft Excel Expert

Neale is a CPA with over 20 years’ experience in accounting, budgeting,

management reporting, and developing spreadsheet applications.

Neale has extensive experience across a broad range of industries including

mining, education, property, forestry, government, and construction.

Neale is the CPA magazine’s authority on spreadsheets and related subjects,

and writes a monthly column for this publication.

He is also a sought-after speaker and trainer regarding the use of Excel,

particularly by accountants.

Brett Rawcliffe, Senior Database Analyst (Contract)

MACS

Brett is a highly experienced database analyst and is regularly in contact with Microsoft
regarding their database software.

Over the last 13 years Brett has developed hundreds of database solutions for companies of
all sizes, from sole traders needing a simple contact manager to multi-nationals needing to
develop specialised systems to complement their organisation-wide systems.

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 9 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC

Appendix A: Access Analytic Details
Access Analytic Solutions Details

Full Name: Access Analytic Solutions Pty Ltd

Address: Suite 143, Level 1 580 Hay St

Perth WA 6000
Australia

Phone: +61 8 6210 8500

Fax: +61 8 6210 1662

Web: www.accessanalytic.com.au

ACN: 091 625 697

ABN: 64 091 625 697

Contact: Jeff Robson, CEO

E: jrobson@accessanalytic.com.au
M: +61 4 1258 1486

© ACCESS ANALYTIC SOLUTIONS PTY LTD PAGE 10 OF 10

\\AAS-SRV-SBS2\COMPANY\OPERATIONS\EXCEL\WHITE PAPERS - AA\AA FINANCIAL MODELLING BEST PRACTICES.DOC