Skip to main content
Home
The Online Resource for Modern FP&A Professionals
Please register to receive the latest FP&A news, updates and tips Login

Main menu

  • Home
  • FP&A Insights
    • FP&A Trends Digest
    • FP&A Trends Research
    • FP&A Trends Insight Paper
    • FP&A Trends Survey
    • Short Videos
    • Our Contributors
  • FP&A Events
    • International FP&A Board
    • FP&A Trends Webinars
    • Digital FP&A Circles
  • AI/ML Committee
    • Introduction
    • Members
    • Resources
    • Meetings
  • FP&A Tools
    • FP&A Trends Maturity Model
  • About Us
    • Company Policy
    • Privacy Policy
    • Editorial Guidelines
    • Our Ambassadors
    • Our Sponsors & Partners
    • Contact Us
image
6 Best Practice Tips for Building Great Financial Models
November 4, 2020

By Michael Zimmel, Senior Financial Professional

FP&A Tags
Modelling and Forecasting
Financial Planning and Analysis
FP&A Skills

Financial modellingFinancial models are crucial to the business but building them in Excel can be both complicated and frustrating. Setting up formulas and stipulating conditions takes time. And just when you think your model is water-tight, there is another error.

Although errors are unfortunately very common in Excel models, it is important to understand how you can avoid them. In this article, I would like to share my own financial modelling best practice advice that I hope will help you create reliable financial models that generate accurate financial data.

1. Always plan ahead

Before starting working on the financial model, many finance teams neglect the preparation process. As a result, they create over-complicated models that lack critical features and fail to address operational needs.

There are several questions that you need to ask yourself before starting to work on a model: 

  • What is the financial problem the model needs to solve?
  • Who are your end-users are and how are they likely to use the model?
  • What are the model’s key inputs, outputs, and processes?
  • How can you ensure that all inputs are in one place, and quick and easy to access during development?

Having a vision and keeping the goal of the project in front of mind throughout the development process is key to ensuring a successful and useful financial model. 

You will inevitably encounter issues and experience changes, so it’s vital for the model-building analyst and the client to work closely together throughout the project lifecycle.

2. Ensure your financial model is logically structured

Your financial model is likely to require several spreadsheets and they must be organized logically to avoid navigational difficulties for the end-user. 

You need to identify the factors that affect the model and display the content in a way that enables users to easily move between sheets. The model should be built around three basic components:

  • Assumptions
  • Calculations
  • Output

Following this structure will ensure your financial model is architecturally sound and robust. Users will be able to work out the areas they need to focus on, distinct from the areas where the computer works. And if assumptions are gathered in one place, errors are avoided as your model is unlikely to be compromised due to some old assumptions that were left from earlier analyses.

3. Keep your financial model simple 

Most users of financial models will decide whether they like it or not within the first 30 seconds. The easier your financial model is to follow and interpret, the more likely it is to receive positive feedback. 

  • Limit the number of implicit assumptions to 10 or 15. The more assumptions you make, the less realistic your decisions are likely to be.
  • Work towards having formulas that are no longer than about half the length of the formula bar. Longer formulas increase the chances of making a mistake.
  • Do not overuse cell names. While using cell names and range names might seem a good way of reducing the complexity of formulas, it can create more problems. This can leave your formula littered with phantom names and confuse users. Also, you might soon run out of relevant names to use and resort to random names with no real meaning. 
  • Use the same formula across rows. This helps making the model easier to alter, more error-free, and more straightforward to test and review.
  • Follow a consistent formatting. All your column headings should be identical from sheet to sheet. Your fonts, borders, labels, and the overall style should all be the same. To avoid any confusion, rows and columns should be clearly described.
  • Always add an executive summary. An executive summary allows you to outline your model’s assumptions and drivers. It should include references to balance sheets, profit and loss, and a graphical representation of the cashflow. You should also consider creating a table of contents and having as many instructions and explanations as you deem necessary to make the model easier to use.

Excel has numerous features to help you simplify your financial models. If creating a simple formula is not possible, you should look to break it down into several cells. Other ways you can reduce complexity on Excel include:

  • Using flags
  • Avoiding nested IFs
  • Simplifying IF statements using different functions
  • Considering logics like MIN, MAX, OR, AND
  • Employing functions such as VLOOKUP, HLOOKUP, INDEX, or (with care) OFFSET

4. Avoid using values instead of formulas

It is always a good idea to avoid hard coding. It may initially save you time, but ultimately it shows a lack of discipline and threatens the reliability and transparency of the model. There are several problems with using hard values instead of formulas:

  • The model becomes over-complicated and difficult for readers.
  • Assumptions are not obvious, and this might be confusing to end-users.
  • The flow of reasoning becomes less clear.
  • End-users are less confident in the data’s reliability since they are unaware of where the data is coming from. 

If hard coding certain values is unavoidable, you should insert a comment against the cell explaining it and revealing where the number originates from. It is easy to remember the value while you are creating the model, but if you come back to it later, you will notice that you likely have forgotten something about it.

Hard values do have their place. For example, when you have to deal with assumptions and inputs that drive key elements of the model, such as revenues and costs. Clearly and consistently formatting such cells as inputs increases a reader’s confidence in the data’s reliability.

5. Ensure your cash flow calculations and balance sheets are fully integrated

Your cash flow and balance sheet calculations are vital components of a financial model. So, do not be tempted to omit them when building start-up models or calculating isolated business cases. They must be fully integrated to ensure proper representation of stock turnover, debtor days, creditor days and other features are part of your model. Lack of integration could lead to problems when the reader needs to confidently forecast a cash flow position.

6. Check the accuracy of your model 

There are several steps that should be taken to ensure the accuracy of your model:

  • Group your rows. It is almost inevitable to have rows of redundant data and calculations that are not required in the final version of the model. Hiding them might give you a cleaner-looking presentation, but it often leads to incorrect calculations and can cause you to miss crucial data and inconsistent formulas. Grouping rows is a much better solution than hiding them, but even this should be limited.
  • Check and double-check your formulas. Incomplete information or copying errors will lead to inaccuracies. That is why the writing of formulas needs to be undertaken with the utmost care. You should also pay particular attention when you insert rows or columns close to the edges of ranges, or where references in the cell are fixed.
  • Establish a systematic naming convention for your file versions. The numeric and alphabetic order of your file version names should align with the chronological order of your file. You should in general also avoid including the date in your file names, and do not add words like ‘final’ or ‘latest’ to those names.   
  • Do not neglect the Excel audit function. It makes it easy to run automatic error checks on your financial model. It is good practice to carry these out regularly across the entire model. 
  • Perform sanity checks. Here you should answer some basic business questions. For example, will your sales double if you recruit an extra sales professional into your team of two? If your model shows adding the extra person would triple sales, a sanity check would help verify the accuracy of an outcome that appears, on the face of it, far-fetched!
  • Ask a third party to check your model. A fresh pair of eyes can find problems you might have overlooked. You can ask a colleague that did not participate in the development process. A check by an external consultant will be more time-consuming but is likely to be worthwhile. It is important that whoever checks the model understands its purpose, reviews the model line by line and sheet by sheet, and tests to see what happens when the inputs are changed.

In summary

Your chief goal when building a financial model is to ensure you create a clear and effective tool that users can easily benefit from. Building such models on Excel is complicated, with so many details and features to consider. Errors are almost inevitable, but by following the best practice tips above, your path to creating a useful and practical financial model will be that much clearer.

The full text is available for registered users. Please register to view the rest of the article.
  • Log In
  • or
  • Register

Related articles

Financial Modelling: What is Common Between Football and Finance?
March 27, 2020

This article will focus on is the modeling of a company as a whole, its consolidated...

Read more
Financial Modeling: Types of Models and How to Design Them
March 12, 2020

Starting with the end in mind is one of the simplest ideas that is frequently ignored...

Read more
Sensitivities, Scenarios, What-if Analysis – What’s the Difference?
November 22, 2019

Scenario analysis, sensitivity analysis and what-if analysis are very similar concepts and are really only slight...

Read more
Financial Modelling and Automation
December 6, 2018

Disclaimer: Financial Modelling has no strict “right” or “wrong” method of application. It does, however, have...

Read more
Analytic Model vs Spreadsheets
September 21, 2017

Spreadsheets are without doubt the ‘killer’ application that turned the PC into an indispensable business tool...

Read more
+

Subscribe to
FP&A Trends Digest

We will regularly update you on the latest trends and developments in FP&A. Take the opportunity to have articles written by finance thought leaders delivered directly to your inbox; watch compelling webinars; connect with like-minded professionals; and become a part of our global community.

Create new account

image

Event Calendar

Pagination

  • Previous
  • May 2025
  • Next
Su Mo Tu We Th Fr Sa
27
28
29
30
1
2
3
 
 
 
 
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Transforming FP&A Together: Human & AI Synergy
 
18
19
20
21
22
23
24
Moving from FP&A to Extended Planning and Analysis (xP&A)
 
Five Critical Roles for Building a World-Class FP&A Team
 
25
26
27
28
29
30
31
FP&A Business Partnering and AI: A New Era
 
All events for the year

Future Meetings

The Face-to-Face Amsterdam FP&A Board
The Face-to-Face Amsterdam FP&A Board Transforming FP&A Together: Human & AI Synergy

May 15, 2025

The Face-to-Face Milan FP&A Board
The Face-to-Face Milan FP&A Board Moving from FP&A to Extended Planning and Analysis (xP&A)

May 20, 2025

The Face-to-Face Frankfurt FP&A Board
The Face-to-Face Frankfurt FP&A Board Five Critical Roles for Building a World-Class FP&A Team

May 22, 2025

BPAI
The FP&A Trends Webinar FP&A Business Partnering and AI: A New Era

May 28, 2025

The Face-to-Face London FP&A Board: Data Management & Analytics: Unlocking FP&A Value
The Face-to-Face London FP&A Board Mastering Data in FP&A: Smarter Analytics, Better Decisions

June 5, 2025

FP&A Trends Webinar The Evolving Role of FP&A: From Number Cruncher to Strategic Advisor
The FP&A Trends Webinar Making FP&A Teams Fit for the Future

June 11, 2025

The Face-to-Face New York FP&A Board
The Face-to-Face New York FP&A Board From Insight to Impact: FP&A Business Partnering in Action

June 17, 2025

The Face-to-Face Sydney FP&A Board
The Face-to-Face Sydney FP&A Board Modern Financial Planning and Analysis (FP&A): Latest Trends and Developments

June 26, 2025

The Face-to-Face Singapore FP&A Board: Modern Financial Planning and Analysis (FP&A): Latest Trends and Developments
The Face-to-Face Singapore FP&A Board Modern Financial Planning and Analysis (FP&A): Latest Trends and Developments

July 8, 2025

AI/ML FP&A
AI/ML FP&A
Data and Analytics
Data & Analytics
FP&A Case Studies
FP&A Case Studies
FP&A Research
FP&A Research
General
General
Integrated FP&A
Integrated FP&A
People and Culture
People and Culture
Process
Process
Technology
Technology

Please register to receive the latest FP&A news, updates and tips.

info@fpa-trends.com​

              

Foot menu

  • FP&A Insights
  • FP&A Board
  • FP&A Videos

Footer countries

  • Amsterdam
  • Austin
  • Boston
  • Brisbane
  • Brussels
  • Chicago
  • Copenhagen
  • Dubai
  • Frankfurt
  • Geneva
  • Helsinki
  • Hong Kong
  • Houston
  • Kuala Lumpur
  • London Board
  • London (Circle)
  • Melbourne
  • Miami
  • Milan
  • Munich
  • New York
  • Paris
  • Perth
  • Riyadh
  • San Francisco
  • Seattle
  • Shanghai
  • Singapore
  • Stockholm
  • Sydney
  • Tokyo
  • Toronto
  • Washington D.C.
  • Zurich

Copyright © 2025 fpa-trends.com. All rights reserved.

0