Financial Forecasting Models | dbrownconsulting

6 Things You Must Do When Building Financial Forecasting Models

An in-depth look at financial forecasting models Modeling a five box balance sheet for financial forecasting

Financial modeling drives major business decisions in the corporate world. A model can highlight the risks and potential advantages of implementing a specific strategy, such as mergers and acquisitions, raising funds, planning, and making investment decisions, to mention a few examples. It is no surprise that the need for competent financial modeling analysts increases by the day.

I have built financial models for more than 20 years now and every modeling engagement excites me. I am eager to see what levers really make the business tick and how I can identify and sensitize the critical success factors that provide answers to why things happen and what happens if we do certain actions.

I have also learned that it is important to hold your horses, never fall into the trap of thinking your model can answer all things, and understanding that it is merely an estimate.

In this article, I will share with you 6 principles I live by when building financial forecast models. Take them as that guide that helps you focus on what is important. Make sure to go over these principles before building your next financial forecasting model. 

1. Think Like an Architect

When an architect begins a project, they start with a vision that appears in their mind. This vision directs the architect as he creates his design.

The way architects think is useful for financial modeling analysts. First, see the big picture, then lay the groundwork and develop the model.

This principle will allow you to consider problems and find potential solutions. It will also enable you to answer questions about how the model works, what the outputs are, and how useful it is for the company.

When developing a merger and acquisition (M&A) model, for example, you need to consider the end goal. The question in this case is whether the merger or acquisition will have enough synergies that both merged entities will be better off (1 + 1 = 3). You can then create a model that calculates the formed company’s earnings per share (EPS) and compares it to the current one.

2. Keep it Simple

When creating a financial forecasting model, it should only satisfy the goal it is required to achieve and nothing more. It is critical to lay out the information in a way that it is easy to follow and understand, regardless of who you’re presenting it to. Adding complexity and functionality will only add clutter and ultimately interfere with the user’s experience.

One of the best ways to simplify your models is to identify your key metric or that critical question the model needs to answer and then build around it. Some key metrics include Return on invested capital (ROIC), Leverage ratios (total Assets / Total Equity), Working capital ratios, Profit Margin Ratios and more.

Before you even think about diving into the model mechanics, do a quick back-of-the-envelope calculation to see if the numbers make sense, this helps you organize your thoughts and determine what aspects of the model build may need more work and which can be skipped. A great method I pioneered is to simulate the past historical financials using shapes, no numbers and just read the story of the business in less than a minute. Check this animation out and tell me in the chat what you think of this business.

3. At Least 2 sides to every story

When creating your models, keep in mind that every story has at least two sides. Understanding the model’s purpose is essential for determining its structure, but this does not imply that there is only one path to the same conclusion.

If you’re stuck and don’t know where to start, try viewing the problem from a different angle; you might be surprised. To think like a financial modeler is to know other sides exist and decipher the unique stories each side brings to the table, enriching your understanding of the business you are modeling.

For example, a balance sheet typically has two sides: Assets and Funding. Assets made up of Fixed Assets (FA) and Current Assets (CA) and Funding made up of Equity/Shareholders Funds (SF), Long Term Loans (LTL), and Current Liabilities (CL).

Another way to look at the balance sheet is to identify the Longside and the ShortSide. The Longside being the Assets and Funding that typically stay on the books for more than 1 year, this will be FA, SF & LTL.  The shortside are those line items that typically get consumed before one year, these are your CA & CL.

So, wherever you see a figure in the financials, think of it in at least two ways. Working Capital is a classic example; it can be viewed as either: a piece of capital employed (Capital employed is SF + LTL) or the excess of current assets over current liabilities; two ways of looking at the same thing.

4. Visualize the Model Before You Build

I believe this is my favourite Principle. I once built what we call an industry model, a single model for an eerie company in the industry. To do this well I had to see the model before I built it. Here I mean, I had to be able to close my eyes and visualize how it looked, how it worked, what could break I critiqued every aspect of the model before even building it.

You have seen top athletes before a tournament do it, they are mentally prepared, they can see it. For you, maybe scibling a few things on paper or having a pictorial view of the model structure may help you visualize the model.

It is helpful to visualize the model in your mind before putting pen to paper or using Excel to build it. This exercise will help you consider the problem from various perspectives and identify potential issues. It may prompt you to ask more questions and conduct additional research before building out a faulty financial forecasting model.

Visualization can help you uncover difficult situations before they happen. The advantages of this method are twofold: you will save time and work more efficiently.

A typical corporate model, sometimes called a deterministic model, has many moving parts. Connecting the dots in the diagram can help you quickly discover if you’re missing prerequisites and assumptions which could put your project on hold.

5. Identify Risks & Uncertainties

Budgeting, Forecasting and Modeling are methods to predict the future, this essentially is an exercise in risk reduction. Using an effective risk and uncertainty management approach can help you in better understanding and planning for the financial forecasting model you are developing. The world is uncertain, and your financial forecasting model must reflect this.

This method is related to the mental visualization and mapping of the model principle. Before you even open the spreadsheet, you can likely identify several potential risks and uncertainties. You can then account for these across multiple scenarios within your financial forecasting model.

The STEEP approach (Social, Technological, Economic, Environmental, Political) is a great tool for identifying your risks and uncertainties. The Model helps you evaluate how various external factors can affect business performance.

As a modeler, you need to identify those critical risks that will need to be modeled in multiple scenarios and even some that you may need to run some sensitivities on. You must know this before building the model because this information will inform the approach you must take in building the model. It also helps you cover all the bases to get a more complete picture of your modeling project.

6. Strike a Balance Between Value & Cost of Information

It is unrealistic to model every moving part of the business in a bid for 100% accuracy; this just adds unnecessary complexity to your model development. An element of uncertainty and randomness is unavoidable. Your job as a modeler is not to model every detail, your job is to provide as much detail as is needed to answer the key question and no more.

The more detail you include in your model, the more time you spend and the more expensive the model will be. There are circumstances where more detail may actually lead to more errors and poor forecasting accuracy.

For example, how would you model annual staff salaries for a company with 1,500 staff? Here are some options:

  1. List all staff and their salaries and grow the salaries year-on-year by inflation
  2. Break staff down into job categories and use average salaries and staff count in each category to project pay
  3. Pick the annual salary from last year and apply a growth rate to it based on historical trend

Method A will use up at least 1,500 rows, method B probably 100 rows, and Method C will use only one or two rows. The correct method will depend on context, but 1,500 rows means 1,500 chances that things will go wrong, who will have the time to update all those numbers. This is what we mean by striking a balance between value and cost of information.

Conclusion

Now that you’ve learned my 6 principles for thinking like a financial forecasting model, it is time to get to work on creating powerful models that will aid your organization in making data-driven financial decisions. Thinking like a modeler involves combining the ideas and processes of other professionals, such as architects, engineers, and economists.

As a financial modeler, your skills do not end there. While you don’t need to be an expert on the industry you are modeling, gaining an understanding of best practices will enable you to visualize, simplify, assess risks, and create more useful models. Don’t hesitate to revisit this guide before building your next financial forecast model – or, if you would like to speak to the experts, feel free to contact us at any time. 

Subscribe

Get the very latest from dbrownconsulting: tips and guides on data analytics, business productivity, and financial modeling; also, exclusive offers on our courses, webinars, events, and company updates.

About the Author

David Brown

David Brown leads a talented team of passionate experts as the managing partner of dbrownconsulting; a firm dedicated to helping organizations and individuals achieve more with their data.

David has spent 20+ years working with individuals and organizations to improve their efficiency and productivity with data. He is an internationally recognized speaker, adviser, consultant, and trainer. He has helped pioneer innovative methodologies and frameworks that simplify the process of automating reports and analytics for everyday users as well as great intuitive training methodologies to pass this knowledge to anyone.

David is a 4-time recipient of the prestigious MVP Award by Microsoft, he is also an ATD Master Trainer and ATD Master Instructional Designer. He is a regular speaker at international conferences and events.

David has worked as an international consultant to the world bank for seven years, worked as a Tax specialist for Andersen, and as a corporate finance specialist for KPMG.

David has curated thousands of hours of content for dbrownconsulting and officetraininghub and has trained and mentored thousands of young professionals over the years.