Why should you follow best practice guidelines for financial models

As spreadsheets have become more widespread and are increasingly used to make important decisions, it has become critical to make sure that they are built in the safest and most efficient way.

As a result, based on years of experience, a Spreadsheet Best Practice of sorts has evolved to help address the common issues associated with spreadsheet use and building. This applies equally to financial models (which we are most familiar with) and all other decision type models.

Before we go into what spreadsheet best practice is, some useful information on why it is important (perhaps your FD should see this).

Why is Spreadsheet Best Practice important

The reasons this issue has gained more prominence is:

1. Extensive use of spreadsheets

   • Spreadsheets are the most common tool used for financial modelling, budgeting and forecasting in the business world. Whether it is Microsoft Excel or one of the other spreadsheet packages available, the strengths of spreadsheets are ideally suited to this process.
   • Spreadsheets strengths include:
     • Ease of use- fairly quick to get started on spreadsheets
     • Flexibility- same tool can be used for financials or to build the space shuttle (look it up)
     • Transferability- you can send your spreadsheet to anyone, they will be able to open it.
     • Powerful calculation and graphic properties- are there any calculations that can’t be done on a spreadsheet?

2. However, some of the very strengths identified above are also weaknesses and as a result there are many examples of spreadsheet horror stories. Some real world errors:

   • US$ 26 million mismatch error- budgeted for more than needed
   • Rounding of numbers on budget- underestimate of $36 million
   • Inserting/ deleting rows or columns- underbid of project amounting to $250 000
   • Incorrect references- $1.5 million for using last year’s price
   • Incorrect signs- $2.3 billion information distortion
   • For more look on the EUSPRIG Spreadsheet horror stories page

3. Furthermore, research shows that the majority of spreadsheets contain errors. In our experience, any spreadsheet built with any time pressures or any last minute ‘adjustments’ are bound to have an error in it. The research below shows this

4. There is limited know how to address the risks. Excel has a number of tools built into it to help address these risks. Most users are unaware of these and as a result are more likely to make errors. More importantly, the best ways to build a spreadsheet are not taught.

5. In theory the process of developing a financial or decision spreadsheet is easy. Take the knowledge in your head and put it into rows and columns. However, there are a number of reasons why such a simple process is difficult and error prone, and these include:

   • Spreadsheets are often built as part of the planning process instead of being the result of the planning purpose. It is very rarely properly scoped and would be better described as a proto type.
   • Spreadsheets are built under time pressures. How often have you been told that you can take as much time as you need to get it right?
   • Spreadsheet models are often compiled by multiple people/ departments and need to be aggregated. Confusion often results due to misinterpretations
   • The spreadsheet developers skill sets don’t match the task. Asking an engineer to build a financial model or an accountant to build an engineering model is risky.
   • There are often last minute changes to the model and more than one iteration of the model. It would be easier if you received a scope and that is all you have to model. More often than not, the scope changes along the way. You end up bolting on bits and pieces. What starts out as a perfect model, quickly degenerates to a black box of calculations that only one person knows how to work
   • Staff will ignore the rules of the process/ spreadsheet. You need to build in as many checks and balances to address these
   • You will want to compare the model to actuals and forecasts at some stage. If you don’t take this into account when building it, you may find that you need to reconcile the model to actuals via complicated workings instead of a direct comparison.

The fundamental concepts

So what are the fundamentals of best practice with regards spreadsheets?

They consist of:

1. Consistent design
2. Separation of input, calculation and output areas, and
3. Integrity of calculations

The overriding result of the above is simplicity and structure.

Contrary to perception, by applying good spreadsheet design techniques, your spreadsheet actually becomes simpler and not more complex. As a result it is simpler:

• to use,
• to review or audit,
• to change,
• to redevelop,
• to correct, and
• to understand.

In the next lessons we are going to look at these fundamentals and some other important issues that will insure you are using best practice. In all cases we will also show you why using these methods will benefit you.