For many strategists, self-developed Excel sheets are the tool of choice to support strategic planning. Despite its popularity, Excel and other spreadsheet programs have their downsides. I have discussed the pros and cons as well as some alternatives in an earlier article. However, by following a set of proven best practices, you can minimize these downsides and significantly increase the credibility of your model.

I will start with a brief discussion on the nature and characteristics of financial models for better understanding.

Characteristics of financial models

Financial models as an abstraction

Like all other models, a financial model is a simplified image of reality. It is an abstraction that reduces to complexity of real-world financial flows to a degree that allows meaningful calculations.

This abstraction describes an operational system with regard to its financial flows (cash) and – if necessary – other relevant economic flows (e.g. depreciation, amortization). This takes place on two levels:

External view

The model determines the relevant boundaries of the organization or the issue to be calculated

• What goes in – input values / variables
• What comes out – output values

Internal view

The model determines the internal structure and the relationships between the components to be calculated (e.g. relationships and interdependencies between profit & loss account, balance sheet and cash flow statement). These are the functions and formulas

Basic structure of every financial model

From this systematology we can derive the basic structure of every financial model:

Input values – Calculation (Functions) – Output values

Basic struture of financial models: Input – calculation – output

Four basic principles of financial models

A financial model has to fulfill four basic principles in order to support strategic decision making:

1. Abstraction
   Models are simplified images of reality. They should not aim to replicate reality. Models that go into too much detail may foster bias and distract from the need to analyze outcomes.
2. Transparency
   A clear and transparent structure and layout is the precondition for the model to gain trust.
3. Presentation
   The model needs to present relevant outcomes in a user-focused way. Therefore, it should include charts and summaries.
4. Flexibility
   Short-term flexibility allows calculating different scenarios. Long-term strategic flexibility comes from a design that allows the model to evolve over time with changing needs.

Best practices for financial modelling

Spreadsheet models can become very large and complex. In order to keep them usable, reliable and accepted, you should always follow some best practices.

Practices to build trust in your financial model

In most cases, financial models are built by a small team of experts or even one single person. The outcomes are used and interpreted by somebody else. The average manager or user is easily inclined to see the model as some sort of black box, for its complexity.

Hence, it is easy to question the models credibility. Typical situations are

• Unexpected results that are inconsistent with common believes
• “unwanted” results that point to a course of action that does not fit the decision-makers intention
• Decisions that were (partly) taken on the basis of the models results turned out wrong (no matter if that was the models fault or not)

In order to avoid inefficient discussions about the models reliability, modelers should build trust in their models right from the beginning by applying these practices:

• Don’t promise anything a financial modal cannot keep.
• Stay in touch with the model’s internal customers and users to make sure your model meets their specifications.
• Make sure your tool looks professional and trustworthy. It needs to have a clean and consistent look that makes it easy for users to orientate themselves.
• The model also has to work neatly without any error messages. Needless to say, the model has to produce consistent and replicable results.
• Generously offer to explain your tool in any detail to everybody interested. Thus you make sure that your tool is well understood by all users and by the recipients of the results.
• Follow the practices for testing and maintenance described below. Make sure the users know about these activities.

Practices to build a tool that everybody can work with

Even if a financial model is a bit like “your baby”, build it in a way that you can hand it over to any other expert to work with at any time. Therefore you should follow some general conventions of financial models:

• Have separate sections (in most cases: sheets) for data entry (input values and general parameters), data processing / calculation, and output of results. Thus you’ll have a user interface that focuses users’ attention on those parts of the tool that are relevant for them.
  

  Input section of a financial model: input fields in light yellow

• Design your model in separate modules for every part of the calculation. This makes it easier to change and update particular parts of the model or to add additional calculations.
• Have a consistent color scheme throughout the model, e.g. different colors for input fields, parameter fields, row and column headers etc.
• Use consistent naming conventions, number formats etc.
• Always have your time axis in the same direction. In most cases, this will be from left to right in ascending order
• Build your model in a logical order from top to bottom
• Build every calculation only once. If you need a particular result for several following steps, always reference back to the first calculation. It is ok to have auxiliary calculations at a suitable place within the model.
• Generously use the Insert Comments function to explain what you are doing.
• Avoid #DIV/0! error messages by using an If-function with the result 0.
• Think twice about circular references. Sometimes they make sense. In many cases, however, you should prefer a manual plug (manually insert the value that would lead to a circular reference)
• If external links are unavoidable, have an import sheet in which you insert all external links. Thus, they are in one place, are easy to find and easier to check for errors.
• Never insert values into formulas. Always use parameter entry fields instead. For each parameter, only use one entry field to which you reference from wherever needed. Thus it is not only easier to change parameters. You can also be sure that the parameter is changed everywhere throughout the model.
• KISS: Use short formulas. As a rule of thumb, it should be possible to explain every formula within 30 seconds and to recalculate it with a pocket calculator.
  

  This formula is too long and hard to understand

Practices for review and maintenance of finacial models

Review and maintenance are important processes for every financial model. They have two major objectives:

1. Make sure the model delivers reliable and correct results
2. Increase trust and credibility

Even though you may be inclined to consider your work finished once the model is programmed and working, the following practices will ensure its quality and durability. You should plan to do some maintenance work on a regular schedule.

• Include a check sheet where you have several checksums and plausibility checks.
• Always have somebody else check your tool from tip to toe. This may even be a job for external consultants. Thus you can eliminate existing errors, get valuable advice for improvements and significantly increase your tools credibility.
• It is also advisable to have some future users do some sample calculations before the tool goes life. Users will best know about typical input values and plausible results.
• Do sensitivity tests, also for limit ranges. Do your results change as expected, if you change particular input values? Document these tests and tell everybody about them.
• Even if it is a pain in the neck: Write documentations on the tools you build. This is not so much about transcribing the formulas. You should rather explain what the tool calculates in which fields, which way and why this way, i.e. what does the tool actually do.
• Update your documentation whenever something is changed within the tool.
• Check your model for error messages and eliminate them on a regular basis.
• Delete formulas that are no longer needed. Check for possibilities to reduce the complexity of long formulas by splitting them up into two or more separate calculation steps.
• Check all basic assumptions for your model on a regular basis.

One last advice

I always offered extensive user support for the tools I had developed. Thus I quickly got a feeling for what worked well and what didn’t. I also learned about the users’ needs. On that basis I was able to develop several updates with increased flexibility or user friendliness.

With all these practices, my financial models and I became well accepted throughout the organization. Nobody ever blamed the models for poor business results and deviations from plan. Even Internal Revision and external auditors regularly confirmed the models’ quality.

Our book recommendations on financial modelling

• Building Financial Models
  John Tjia
  Our no. 1 recommendation: This is a practical step-by-step guide that takes you through the process of developing a financial model in every detail. The book also helps you practice good thinking and apply sound knowledge of their tools–two key attributes to producing robust and easy-to-use models.
• Mastering Financial Modeling: A Professional’s Guide to Building Financial Models in Excel
  Eric Soubeiga
  What sets this practical guide apart is its “learning-on-the-job” approach. Unlike other books that teach modeling in a vacuum, this method uses a diverse collection of case studies to convey each step of the building process.