Financial modelling for dummies

finance education

Running a business is all about making big decisions and taking risks, but most entrepreneurs still prefer to gauge the outcome of their actions before committing.

This is where financial modelling comes in. As a data-driven tool, it gives businesses insight into how certain situations could affect their financial outlook.

Though immensely useful to business owners, these are complex structures which beginners could find incredibly hard to work with at first. Here, we explain all the basic elements of a financial model, and how businesses can create and customise them for a variety of purposes.

What is financial modelling?

Financial modelling is a tool which predicts a business’s future financial performance. This is usually built into Excel, using the company’s historical performance, and various assumptions about inputs, outputs, and calculations to produce results. It also allows a business to analyse the hypothetical outcomes to its base, best, and worst-case scenarios, as well as the impact of circumstances like increased interest rates, or company discounts. The data from these assumptions then creates a set of standard financial forecasts, traditionally including a balance sheet, income statement, and cash flow statement.

The key to a financial model is flexibility. As this tool’s purpose is to forecast future projections, changing any inputs will alter the calculations and final results on the spreadsheets, enabling business owners to examine different possible outcomes. Such flexibility extends to all the variables in the financial model. For instance, a change in sales volume means that revenue, sales commissions, and all other expenses will change in line with this alteration.

Overall, financial modelling allows businesses to calculate the effects an event could have on costs and profits.

Why is financial modelling important?

When businesses use a financial model, they’re able to see the potential results of a real-life situation, and take this information to make better decisions. For example, they could see which projects and marketing campaigns bring the biggest return on investment, deduce whether it would be worth purchasing new machinery or equipment, and set budgets accordingly. They could also rely on financial modelling to prepare for any changes outside of the company, like a new economic policy or a shortage of a particular raw material.

Financial modelling is an effective numerical representation of a business’s operations, where various models can be produced to convey different results. Common examples of financial models include discounted cash flow, which estimates the value of an investment based on future cash flows, and a merger model, analysing the consolidation of two companies through the mergers and acquisitions process. However, it’s important to remember that a financial model’s usefulness entirely depends on the inputs and assumptions within the spreadsheets, so they will need to be refined in order to obtain any meaningful data.

What is the best financial modelling software?

EASA

Financial models are typically created through Excel but rather than relying on traditional spreadsheets, EASA software allows users to access Excel files through web applications. If businesses want to share these models with third parties—like investors or financial analysts—web apps can ensure that only the current version is shared.

Not only does this prevent any mix-ups between old and new models but, as only approved users can access and edit the web app, any newly input mistakes will not affect the underlying core spreadsheets. EASA’s software is also completely customisable, allowing businesses to include all the functions from their existing financial models.

Modano

An Excel add-in, Modano builds extremely useful, high-quality standardised financial models, without the need for templates. As a content management system, it gives users the choice between thousands of existing models, or simply mix and match elements to create a customised, best practice financial model in minutes. It also automates time-consuming tasks like adding and removing categories, importing historical data, and changing the periods of all time series sheets.

Analytica

Alternatively, some companies may decide to avoid Excel altogether and opt for a program like Analytica. Its creators argue that spreadsheets are flawed because dealing with cells prevents business owners from directly interacting with higher-level entities such as variables, uncertainties, and hierarchies. Analytica, on the other hand, displays all these entities, making financial models easier to write and review.

The software incorporates influence diagrams, similar to interactive whiteboards, allowing users to build and navigate a model visually. Multidimensional diagrams and Monte Carlo simulation are also included, enabling businesses to handle complex problems, evaluate risk, and identify the most important variables.

What is best practice for financial modelling?

In general, best practices relate to formatting, design, and layout: Most businesses will need to be confident using Excel, as this is the software they’ll most likely use for financial modelling. This means being aware of keyboard shortcuts, keeping formulas as simple as possible, and being familiar with the INDEX MATCH formula, and CHOOSE function. All this information is covered in the Corporate Finance Institute’s free Excel crash course, which was created with professional financial analysis and financial modelling in mind.

Formatting

Distinguish between the inputs (assumptions) and outputs (calculations) in a financial model. This is conventionally achieved by labeling inputs as blue and formulas as black. However, this could also be done by shading cells, using borders, or any other means of visually separating the two.

Design

A financial model’s design must be logical and easy to follow, typically built on a single worksheet. Grouping can create different sections, making expanding, contracting, and moving around the model more simple. All section titles should also be highlighted in the same colour.

Layout

The following sections make up a simple, end-to-end financial model for a startup:

  • Assumptions
  • Income statement
  • Balance sheet
  • Cash flow statement
  • Debt
  • Summary

How hard is financial modelling?

Financial modelling can be difficult for people with little experience in accounting, finance, maths, valuation, or Excel. For someone unversed in these skills, it would be wise to take a financial modelling course before getting started. Professional training will help students make sense of all the complex elements, and teach them how to link the income statement, balance sheet, and cash flow statement—also known as the three financial statements—in Excel. This is something many financial professionals find challenging.

Otherwise, the only way to learn financial modelling is by practising. Step-by-step video tutorials can guide beginners through the process, while it’s crucial to get to grips with all the data within the three financial statements. Then, business owners can try creating simple scenario models, focusing on situations such as changes in expenses, goods sold, or price per item. After defining formulas in an additional spreadsheet, finances can be calculated based on each of these possible scenarios. Reading templates and case studies can additionally inspire more complex financial models.