1.   Introduction

Organisations rely heavily on spreadsheets as a key tool for financial reporting and to manage their financial planning and operations. As a result spreadsheets are an integral part of the information and decision making tools of a company. As spreadsheets are standalone documents which anyone can create, manipulate and distribute, these documents are not supported by the same control environment as formally developed software applications. The result being that almost any employee can cause any number of issues while working on the document. In developing these documents, organisations need to balance the ease of use and flexibility of spreadsheets, against the importance of controlled, reliable information for making management decisions. Implementation of effective, auditable controls surrounding critical spreadsheets and their use can have a direct impact on an organisation’s financial reporting and governance.

2.   Background

This document is primarily designed to assist in creating format and structure to the creation and review of financial models (predominantly spreadsheets) within an organisation. It should act as a governance tool to ensure the business critical model is created and implemented in a structured way, adhering to modelling best practice.

3.   Overview

This document looks at the roles of the model developer, user and reviewer, their tasks and responsibilities during the planning, documentation, implementation, control and maintenance phases of the process. It goes through best practice spreadsheet design and shows users the various forms of modelling techniques which can be used to simplify and clarify a model. The document will assist in identifying roles, responsibilities and provide a process flow for ensuring the integrity of data and calculations within a financial model.

4.   Scope

The use of this plan will aid in business critical spreadsheet development, evaluation and provided a guideline for acceptable standards to be developed, documented and followed. Business critical models can be defined in the graph below;

Business critical models are those which can have high 'Financial Impact' (e.g. the model output may feed into Financial Planning or Forecasting activities or are used to guide operational management decision making). In this example the level of risk needs to be managed to a low level and the plan is one action to be followed that can help mitigating this risk. Typically such models are also 'Complex' and the above diagram illustrates where (red square) 'business critical' (high financial impact and complex) and (blue diamond) 'functional model' (low financial impact and low complexity) are plotted on the axis.

An organisations budgeting and reporting model by way of example often meet both criteria of high financial impact and complexity. Its outputs are approved by directors, board members and in government, ministers and it is a important planning and analytical tool for all organisations. While a financial model which calculates stationary spend for a particular cost center may be important to the owner but when considered against the overall spend of the organisation it may not be material. In relation to this model the application of best practice modelling is advisable but without all of the rigors in place for business critical models. The result of quality reviews and audits should be given appropriate management support. It should be mandatory for business critical financial models to follow these procedures within an organisation.

5.    Model User and Model Reviewer Responsibilities

The model user, if different from the developer, needs to understand the models workings, the business logic behind the model and any risk factors which may exist. Where necessary this should be communicated in person as a training exercise, in addition to a set of robust user documentation procedures.

6.   Developer Responsibilities

The developer is responsible for creating a robust, consistent model which is in keeping with best practice methodology. This involves taking responsibility for planning, design, developing, testing and documenting the model. This completed process assists in handing over the model to new users in a structured manner.

The diagram above demonstrates the process flow to follow from planning to use. This process should be followed when developing business critical financial models.

6.1. Planning                                 

The developer should conduct adequate research to determine the objective(s) of the model and the information required to design and               develop the model. This process should identify;

Doing this successfully usually involves speaking to the relevant stakeholders to get a detailed understanding of the business drivers or sensitivity adjustments which are expected to flow through the model.

6.2. Designing

Clear model design makes a model easy to use and understand. As a result the user is less likely to make errors while using the model and mistakes are easier to identify when they do occur.

Additionally, the model can easily be transferred from one user to another. The developer should create an overall layout diagram of the model architecture, data flow and its related outputs to be put together in the development phase.

Above is an example of a very simple process flow diagram. The original data is coming out of an ERP system and flowing through as inputs to a spreadsheet. The inputs are calculated in a separate sheet and the results flow through to the output page(s). Things to consider include;

7. Developing

The fundamentals of spreadsheet development are important for the clear communication of financial data. Spreadsheets are generally designed poorly with users not being taught how to design and develop efficient, effective best practice models. The following sets out how to follow best practice in the development phase.

The development of the spreadsheet becomes more efficient and less error prone if the planning and design phases have been successfully completed. A good model should read like a book, with data flowing from left to right and top to bottom. Inputs page(s) being on the far left, calculations in the middle and outputs on the far right. Data should not flow backwards between the elements of the model, always from left to right, top to bottom. By top to bottom, the model’s calculations should be of the cells above it not the cells below. Following these rules reduces the chances of circular references in the calculations.

The above shows a clear distinction between the inputs, calculations and outputs. The inputs sheet(s) should contain the source data which the models assumptions are based on. Calculations should draw only on the Input sheet(s) and the Outputs sheet(s) should draw only from the Calculation(s) sheets. Each sheet element should be used for the purpose it was designed for and additional inputs should not creep into either the Calculation or Output sheet(s).

For smaller models, these three elements can be put on the one page. This would have a more top to bottom feel with inputs at the top of the sheet, calculations in the middle and outputs at the bottom. In very simple models the developer might just have inputs and calculations acting as the final output.

The above graphic shows the way a smaller model would be laid out on a single page. This is shown in more detail in the following example;

The above shows the three distinct building blocks of any model. The inputs are separate from the calculation areas of the model, the calculations draw on the inputs and finally the outputs summarise the model’s data drawing from the calculations section.

In the development phase there should be a dedicated inputs sheet which is kept separate from the other sheets. Percentages and variables should be kept in this sheet for small workbooks and in larger workbooks an assumptions sheet should be established so data can flow through to the rest of the model.

7.1 Model Consolidation

There are three common methods for connecting worksheets;

• Using multiple sheets

Using multiple sheets is the most ideal method to organise and calculate a model. Putting all of a company's business units into one workbook with a consolidation tab makes great sense. Making all of the business unit worksheets identical and having a calculations sheet which is in the same format as the business unit sheets makes the process of consolidation simple.

Advantages of this method include:

• Disadvantages of this method include:

Using Macros

The second method is to use macros to automatically copy and consolidate the individual systems into one workbook. The business units in the above example would be contained in other workbooks all in the same folder and code would be used to loop through the workbooks and bring in the summary data to a consolidation workbook. Calculations would then draw on this single sheet to generate the outputs pages and graphical displays.

The advantages of this method:

• Disadvantages of this method include;

• Linking Workbooks

This is one of the most common methods for consolidating data in finance departments. The advantage is that data is linked quickly and there is an audit trail to the other workbook. There are disadvantages of linking workbooks. Organisations have hundreds and potentially thousands of workbooks which are linked. Here is an illustrative example of a problem which can occur.

While auditing a model there were 11 workbooks attached to a single workbook as links. Upon opening all 11 workbooks, these workbooks had between 6 and 10 workbooks linked to them. Upon opening the third tier of workbooks, it was revealed more workbooks were linked to these workbooks. If the average amount of linked workbooks in the second tier was 8, then there is 88 workbooks feeding into the consolidation workbook before considering tier 3 workbooks. The implications is hundred and perhaps thousands of workbooks feeding into the final model. The result of conducting an audit on such a model is an exhaustive task and the potential for errors is high. Additionally, the likelihood that the model will crash due to the size of the backing data feeding into is also high. All endeavors should be sought to purge workbooks of their links by using (the multiple sheets or multiple workbooks) method mentioned above.

7.2 Use of Formula

Formulae are generally the building blocks of models and therefore are very important. The use of a single formula for an entire row or column will ensure consistency. The developer needs to anticipate all the situations a formula will have to deal with for a given row or column. Changing formula part way down a row or column causes confusion and makes the job of finding errors in the audit process more difficult. Just as clear writing style can be achieved by breaking up long sentences, long formula can be broken up to make the model more understandable.

Avoid using constants in formulas, these hard coded numbers hide assumptions upon which the model is based. For example, the formula;

Prior Year Maintenance multiplied by CPI, written as;

Prior Year Maintenance x 1.025 (where CPI is 2.5%)

While the assumption above is correct, this will have to be manually changed across all of the cells which are affected by the CPI calculation. To avoid this issue, set up a named range originally titled ‘CPI’, so instead of typing the constant above, the named range ‘CPI’ can be referred to. When CPI assumptions change, only one associated input cell needs to change.

7.3 Text

In order to establish the type of data contained within a particular cell, it is useful to highlight input data in blue text. Calculations on the other hand should be in normal black text. This enables a reviewer to see before clicking into a cell what type of information is contained within – hard coded text or formula. This has the advantage of cutting down on the amount of time a review takes and informs the user about the static data and calculations without the need to highlight a cell.

Use balancing cell(s) and make sure they stay in balance. Use sums to cross-check data and make sure the model reconciles. It is useful to add conditional formatting to indicate when there is an out of alignment issue.

The simple ‘IF statement’ above on the check line helps protect the integrity of the model. In the above example, if the model falls out of balance, it will be clear to see there is a problem as the word “Check” will appear under the grand total line.

7.4 Security and Protection

Protecting the key assumptions and calculations will help reduce the chance of intentional or inadvertent changing of the model. It is worth pointing out that password cracking software is readily available and free on the internet. Internally protecting the sheet and workbook will only protect against people who are ignorant to this fact. Even so it is still worthwhile making an attempt to hide and protect workbooks.

It is advised that workbooks be protected when ever a model developer is required to:

Adherence to the above controls will at the very least signal to users that the document is intended to be kept in its original state.

7.5 Risks in Development

The development of spreadsheets can at times be a complex exercise with misunderstanding of the business logic, input and formulaic errors unwittingly creeping into any model. There are three types of complexities which are commonly found in financial models, inconsistent application of input data, complexity of the methodology and complexity of size. When these three elements are combined the model has the potential to go awry.

Input Error

When populating a model the source data is often incorporated incorrectly. This type of error is difficult to spot. Having audit check balances won’t typically protect the model from input errors as the model, if working correctly will reconcile. Clear model specifications (user documentation), good training of staff and an operator with an understanding of the expected results will greatly reduce the potential for this sort of problem.

Methodology

A complex model is one where the relation between inputs and calculated outputs are difficult to articulate and as a result can be misunderstood. When this is the case it is very important that the complexities are explained clearly and the user documentation is very precise and detailed.

Size

The size of a model causes its own issues separate from its complexity. Large models take longer to incorporate input data, take longer to calculate and longer to save. Turning off the calculations will speed up the model, however, this is not always ideal. There comes a point where the model is simply too large and consideration needs to be given to storing the data in an external database for efficiency. A well designed model can have its back end (heavy input data) stored in a database and its front end, output data, stored in a spreadsheet. It takes an experienced modeler with very sound skills to develop this type of model.

8. Testing

The developer should ensure there are sufficient audit checks in the model to trap any potential errors. This assists the overall testing  rocess and provides some surety when the reviewer is validating the underlying data and formulaic calculations. Testing or running scenarios over the model to ensure it works as required is initially the role of the model developer but it is recommended that the model reviewer does this as well.

Check the validity of the model by entering data in the inputs area and cross checking the results. Enter extreme data, positive and negative to see if the model reacts as expected. Be sure that a plausible change across a set of inputs has a corresponding and plausible change to a set of outputs.

The use of spreadsheet checking software to audit the formulas within the model can be extremely helpful. There are a range of spreadsheet addins which check the validity and consistency of formula. One such software package is Spreadsheet Detective. Excel’s inbuilt auditing software should also be run over the spreadsheet.

The above is an example of how Excel’s standard audit software can be used.  The software can help to:

• It gives the user a helpful visual of the data flows within a particular sheet.

9. Documentation

The documentation acts as a set of procedures which sets out the models objectives, model workings, functionality, layout and any potential areas of risk where there is a departure from the process. "The best-laid plans of mice and men..." begins the famous saying. This has direct application to writing user documentation because even the most thought-out process is not immune to change requirements. The documentation should outline the steps necessary for maintaining business logic and the production of the agreed output reports. This includes actions for planned and unplanned contingencies, as well as an explicit definition of the responsibilities of each party involved in the production and maintenance of the model.

The document should use a consistent format for each section, for instance:

The development of a model will generally flesh out issues such as errors with processes and errors in the coding of a model. These errors can be incorporated in the user documentation. For example, if you run a process without first performing these tasks this is the result you can expect.

10. Use

By following the above methods in building a best practice financial model, the end user should find their task of maintaining the model is a lot easier. The model user will usually understand the data and detail of how the model operates much better than anyone else. It is their responsibility to use this knowledge to interpret and present the models results in the most meaningful way to the business.

Establish a look and feel for the reports which is consistent throughout the model. The output sheets should look and feel the same and present well. A well presented document is easier for the end user to follow. The following are examples of reports with a consistent look and feel.

Above is an example of a report which shows an allocation of revenue and expenses. The theme and colour scheme is the same as the below graphical display.

Above is an output page for a banking group.  The page ties in with the P&L report page above with the look and feel being the same for both.

The above is the control page where the assumptions and processes are run. It has the same theme as the rest of the model. This page gives the model its flexibility. Assumption such as the year calculations begin, the discount rate, folder location and the models processes are kept here. This page enables the user to keep control of the model.

Retaining control of the model is one of the greatest challenges for the person using the model. Additional changes to the model need to be incorporated in a planned and structured manner. Some of the methods for maintaining change controls will be discussed in the following sections.

Outcomes of the Quality Assurance

The outcomes of a quality assurance will scope any issues within a model, in particular, material control deficiencies which impact the integrity of the model. All identified risks and points of uncertainty, either material or not, should be documented. In relation to any proposed changes, the reviewer and the relevant parties need to agree a next steps process to review the outcomes of the quality assurance and agree on any changes that need to occur to correct the model.

Change Control

When using a business critical financial model, there needs to be a process in place for major changes to the either model design and/or underlying assumptions. A model baseline should be developed, the purpose of which is to isolate a point in time that the spreadsheet is functioning as expected. It is important that changes occur after this point in a controlled and coordinated manner. Putting a robust procedure in place where changes to the underlying model design and/or assumptions need to be approved for business critical models will ensure the ongoing integrity and output of major financial models.

Above is a simple example of a change request form. Having a simple control mechanism for business critical financial models signals the importance of maintaining business critical model integrity. Further, approved changes provide a valuable control mechanism for the model and assist the model user to track any changes.

The model user needs to approve any business critical model changes and to ensure any requested changes are documented in the change control tab. This highlights and provides an audit trail all of the approved instances of the above forms. Below is an example of entries in the change control tab.

The form states who the user is, importantly who signed off on the change and what sheet will be affected by the change.

To test the validity of these changes, a control set of inputs with expected results could be run through the model. This process reduces the possibility that unnecessary changes will be introduced to a system without forethought.

11.  Review Process

In order to maintain the independency of model development and assumptions, the person reviewing the model should not have anything to do with creation of the model. The reviewer should spend time with the principal model developer to go through the key model assumptions and workings. This reviewer should look at the source data, the underlying assumptions, the process to update the model, key risks in this process and make recommendations to mitigate the risks and improve the model.

The unique formula in the model should all be checked in turn. This can be a long and quite tedious task, however, there are several error types to look out for when conducting this type of review and these include;

Produce a document that includes a process flow of the model, how the data moves through the model from data entry, to calculations and to the final output.

12.  Reviewer Documentation

This documentation should highlight issues and findings associated with the model review. From the mundane to the material issues which affect the integrity of the operation and output of the model. It is advisable to split the issues into categories.

Presenting this information to the model developer and users will get the group thinking about the correcting or clarifying these issues. 

13.  Quality Assurance Process Flow

There are two streams when quality assuring business critical financial models, models in production and completed models not in production. To prevent new errors infiltrating a completed model, it is recommended that a phased review take place. This has the advantage of potentially identifying any input or calculation errors which may have developed since the initial review or model development. This constant checking and rechecking the model will help reduce the chance of material errors populating an organisation’s model

The business critical review process is shown above with changes being fed back in all instances to the model developer to keep track of developments and to act as a sense check for models already in production.

CONCLUSION

The stages of model development planning, designing, developing, documenting and testing provide the building blocks for effective financial modelling. Well planned, designed and maintained business critical models will cause fewer issues as models can deteriorate over time. Models often start well, producing useful results, however, as changes occur, errors are introduced, the functionality changes and the results also change without much thought as to why. Staying in control of a financial model is a significant challenge for any organisation with input often coming from disparate parts of the organisation.

Errors in business critical financial models can be reduced by good model design and development, continual testing and structure around approved model changes. The use of balance sheets which stay in balance. The creation of a baseline model and running control data through it with expected results will further assist with identifying issues.

Periodic model reviews by an independent person who was not involved in the creation of the model will further assist with trapping errors. The reviewer should make a list of the issues, be they administrative, mechanical or material. A formal discussion with the key stakeholders should be organised to go through the issues so corrective action can be taken and the model brought back into alignment. Following these processes diligently will significantly mitigate financial risks in this area and provides best practice procedure for an organisation.