Financial forecasts are an essential part of business management and leadership, and are best supported by a robust Excel-based model. Rob Bayliss describes how to go about financial modelling.
Excel is a powerful and accessible tool. Anyone can use it, and there are multiple ways to accomplish each task. This openness and flexibility is a mixed blessing when it comes to financial modelling. It takes practice and discipline to select the best approaches if you want a model that both empowers your decision-making and is credible to stakeholders. It is all too easy for a model to become too complex to use or update, and all too easy for errors to arise, leading to damaging criticism or even the wrong investment decision.
These risks can be addressed through planning and care. Much can be achieved by embracing the core principles of teamwork and good financial modelling practice.
Financial modelling as a collaborative process
Like any business process, financial forecasting and financial modelling should be tackled with the combined resources and wisdom you have as a management team.
We sometimes draw parallels with building a house, where you’d want a credible and structured team of specialists. Builders and foremen carrying out the work is just a part of the story. It begins with architects and project planners and ends with a standards inspector. Having just one person show up and start work without any of the planning or quality assurance would be dangerous.
That’s often, however, what happens with financial modelling. The work is left to someone bright with strong Excel skills to get their head down and come back with a finished set of numbers.
Great financial forecasting needs to be the result of a collaborative process, that involves the right people in the right roles at the right times. We think a good collaborative process should include the input of the following three segments:
1 The board
The board should provide leadership and oversight from the start to determine the scope and detail needed in the financial modelling work, to form the strategic view of what the model should achieve (see more on this below), and to sign off the model design before the detailed build work starts. Once the model has been built, they should then review and challenge the model, and take ownership of financial forecasting – fuelled by understanding and comfort with the principles that built it up – and move onto putting the story alongside the numbers for presentation to the ultimate audience.
2 The builder
The model builder is responsible for generating ideas for the design of the model, and then for developing the model in line with best practice principles once the design has been signed off.
3 The manager
The model builder’s manager is an important bridge between them and the board. They should support the model builder by collaboratively working on the design of the model, and then providing independent scrutiny during the build and review process to make sure it really meets the needs of the business. In addition, one of their most important responsibilities is to make sure that the model builder has sufficient time away from their day-to-day duties to do the job well.
Planning the project
With the right people in place, we see the following process as a clear and efficient way to get a model and set of forecasts put together:
Direction and vision: Planning
Set single purpose
Understand key questions to answer
Level of detail and flexibility
Development and data: Model structure
Build Excel model shell
Use a template and up-front set-up
Apply good habits
Fill in dummy data as you go
Development and data: Data gathering
Parallel sourcing of inputs needed
Accounting data and historics
Senior view of macro assumptions
Testing and challenge: First cut model
Initial data entry
Peer review and code testing
Polish up outputs
Sense check and share for senior approval
Testing and challenge: Scenarios
Lock down base case
Run key scenarios or sensitivities
Words, numbers and pictures
Planning the model: what are your objectives?
Going hand in hand with making sure the right people are involved, is to agree what you’re trying to achieve from a financial modelling process.
The design of any model is a trade-off between detail, flexibility, and ease of use. Good financial modelling creates a tool that’s quick and easy to use and update, contains the flexibility you require to assess changes to your business, and has sufficient detail to provide insight to your decision-making process.
It’s essential to decide the purpose of any financial modelling work at the start of the planning process, because trying to use a single model for all of your financial forecasting requirements can rapidly lead to a behemoth of a file that becomes so complex and cumbersome that people stop using or trusting it, making its existence pointless and a waste of time and resources.
For example, a model for annual bottom-up budgeting is likely to be far too detailed to be efficient to use for long-term strategic planning, while a short-term cash flow planning tool has a further alternative set of requirements. Trying to achieve these purposes all in the same file will quickly lead to a model that is unsuitable for any of them.
A single purpose for financial modelling
I believe that a financial modelling tool should have a single, primary purpose. It’s a vitally important step at the start of any forecasting process to consider what you’re aiming to use the model for, and the clear answers and insights you want to gain from it.
When the overall purpose of your financial model is finalised, this should guide all of the more detailed design discussions with stakeholders as you consider and agree the detail and flexibility that the model should include. If you’re ever in doubt, step back and think of your overall goal and what will help you to achieve it.
Building the model: keep it simple
Once the level of design and flexibility within the financial modelling task has been agreed, it’s time to build the model.
A good model is easy to use, easy to understand, and easy to update. All too often we see people prioritising speed (ie, trying to get to a set of numbers as quickly as possible) over everything else. But this backfires once their initial attempt needs to be updated, refined, or explained to other people. This can be time-consuming and inefficient if the model hasn’t been built in the right way in the first place.
Excel is a powerful tool, and there are a lot of different ways to approach the same tasks. Financial modelling shouldn’t be an exercise in demonstrating how much you know about Excel, or writing the longest formula.
Some key principles for financial modelling
There’s a lot of literature about financial modelling 'best practice', but most of the rules and principles can be summarised as trying to keep a model simple, transparent and consistent in layout. Some of the most impactful principles that we live by are:
1 Segregate inputs, calculations and outputs
2 Do things in simple steps
3 Avoid complex functions
4 Use colour and formats as a guide
5 Don't hide things
6 Logic reads like a book
7 Use columns consistently
8 Clear labels and units
9 Don't hard-wire assumptions
10 Include systematic checks
In addition, one of the most important aspects of credible financial modelling is a set of integrated financial statements. This is where the cash movement in the balance sheet matches the cash flow for the period, the reserves movement in the balance sheet matches the profit for the period, and the balance sheet balances (without a magic ‘balancing figure’ being included somewhere). This means that the profit and loss, balance sheet, and cash flow are all drawn from the same consistent information, and will give any model users more confidence in the tool.
Testing and refinement: fine-tuning to get things right
Once the first version of the model is finished, it’s very tempting to share it widely and move on to the next stage of the financial forecasting process. However, before you start using it 'in anger' to flex assumptions to see what happens to your forecasts, it’s important to spend some time reviewing and sense-checking the model to make sure it’s working as you expect.
Does the first set of financial statements look reasonable, with profit and loss and balance sheet lines showing reasonable trends? If you change some key assumptions, do the outputs move in a sensible way?
A second pair of eyes, if not more, is essential at this stage to provide a fresh perspective and to provide some independent review to bring more confidence to your financial modelling work and the financial forecasts it produces.
When the model has been tested and seems to be working well, it’s then over to the business to refine the financial forecasts, challenge assumptions, and start to gain real value from its new flexible decision-making tool.
The right people and the right process
Financial forecasting is primarily a thinking exercise, and works best when it’s a collaborative effort, using a range of perspectives and skills to arrive at a shared view of the future. However, a good forecasting process can be taken to the next level by competent financial modelling (and conversely can also be obstructed and undermined by a poorly-built model).
Great financial modelling includes the information needed to support and explain your business plan, with just the right level of detail, with input assumptions that are easy to find and change, and a clear link from these assumptions to the ultimate financial forecasts. Flexible financial models allow you to easily quantify the impact of risks in key assumptions to drive insight into your strategic options, and well-presented outputs can summarise your forecasts in a clear and compelling manner to bring your business plan to life.
A successful financial forecasting exercise is about including the right people and following the right process, underpinned by the right financial modelling. Get those ingredients in place and you’ll be well on the way to achieving greatness.
Contact Rob to discuss how to overcome your financial modelling challenges.
Financial modelling is an exercise in teamwork and leadershipFind out more