Most businesses have a gold mine of data that’s just waiting to be used and made sense of. With every business operation, transaction and sale, data is collected about customers, customer behaviour, and your business’s financial health.
And this data might just be one of your company’s most valuable assets — with valuable insights at your fingertips, you can make the best decisions for your business and achieve data-driven success.
But first, you need to make sense of your data and present it in an easy-to-understand way. That’s where financial models come in handy.
Financial models, when done right, should be a complete abstract representation of your business’s finances using spreadsheets, tables, charts and graphs. Data should be broken down and insights should be summarised so that conclusions can be quickly drawn about the business’s performance and well-informed decisions can be made. Financial models can also be used to help you predict and prepare for possible future scenarios and meet the requirements of investors or auditors.
So there’s no denying the importance of financial models for business success, yet financial modelling still remains a mystery to many business owners.
If you’re new to this branch of financial analysis but want to start reaping the benefits of financial modelling, you have two options: you can get expert assistance from a financial modelling consultant, or you can learn how to create financial models yourself.
In this article, you’ll find 6 expert tips on how to build a financial model to help you start making sense of your data and create visual, easy-to-follow data representations. For more in-depth, technical instructions, check out my free Excel tutorial: an Introduction to Financial Modelling.
1. Create an Assumptions Section and List Business Drivers
The first thing you need to do is create a new sheet in Excel or Google Sheets and in the spreadsheet, list all of the main drivers of your business (factors that affect operational and financial results). Separate these drivers into categories such as revenues, user numbers, labour costs, market assumptions, overhead costs, and product or service prices, so that they are easy to find and follow. And add a ‘Notes’ column so that you have space to explain how you came up with these drivers.
This will be the assumptions section of your financial model, which can be used to forecast your business’s income and cash flow and create a basic three-statement model.
This sheet will give clarity and structure to your financial model and will be a valuable asset when it comes to presenting your business to investors, lenders and auditors. By outlining the drivers of your business, you can show that you know and understand your business, and the factors that play a role in your business’s success.
2. Pay Attention to the Structure and Clarity of Your Model
Financial models will involve several different sheets and datasets, but your Excel or Google Sheet workbook should be more than just spreadsheets — your financial model needs to present your business as a story.
The structure of your model should direct viewers through your business data in a logical progression. Start with a table of contents, then the main assumptions, followed by detailed calculations, summaries, graphs, etc.
Different sheets also need to link to each other. This way if you go from left the right of the sheets you start with the details and questions and by the time you look at annual summaries of your numbers, you know exactly which data is being referred to and where the data appears.
You want to show how the numbers interact with each other and you want your model to be fun and easy to play with. If you have more than 6-8 sheets consider grouping the workbook into different sections. Remember, if you send your sheet to a potential investor they will most likely need to share and explain it to their colleagues so aim to make their job as easy as possible.
You can download my Simple Cash Flow Model template to see how to structure your model:
3. Consider the Structure of Your Cash Flow Calculations Sheet
As well as considering the structure of your overall model, structure and clarity are also important when creating a sheet with calculations within your model. The top rows of your cash flow calculation sheet should contain your timeline, followed by the main drivers that influence your calculations.
You should also list the assumptions used in a given row to the left of the sheet and make sure they are all linked to your assumptions sheet. And if possible, avoid hardcoded numbers for your assumptions and instead, link and reference numbers to your assumption sheet. If any data needs to be amended or updated, this will make it easier to update amounts without needing to manually locate and update affected rows. However, if you have to use hardcoded numbers, highlight them in yellow and add a note to make it extra clear.
It’s also a good idea to freeze the rows and columns of your spreadsheets to make it easier for users to navigate through your data. Freezing panes will mean that when users scroll, the rows or columns are locked into place and are always visible. So for example, you might want to freeze rows or columns that show users what numbers refer to. This will allow users to focus on the numbers, and draw fast conclusions from your data.
4. Double-Check Calculations and Ensure Summaries Are Accurate
Your summaries sheet should give an overview of the calculations in the cash flow calculations sheet, giving users a summary of valuable data insights. So it’s important to make sure this sheet is structured clearly and calculations are correct.
When structuring your summaries, aggregate the smaller numbers into broader categories. Keep enough detail to make it easy to sanity check the numbers. Most of your numbers should be sums or references to the values in the cash flow calculation sheets, so avoid using any hardcodes.
And make sure you add the main metrics so that it’s easy to double-check that the calculations work as expected. For example, if your business relies on sales, list the volumes sold on the top and then average price somewhere below. That way if you multiply the two numbers together you should approximately come out with the same number as you see in the revenue lines. If the numbers aren’t close, you know something is wrong with the calculations.
5. Add Metrics and Validation Flags to Your Summaries
Adding metrics and validation flags to your summaries can help you improve the clarity of your data and ensure accuracy.
The most straightforward validation flags will compare the sum of all revenues in your ‘detailed calculations’ sheet to your total revenue. You can use an IF formula to make sure that they are both equal and conditional formatting to highlight the result of the check (TRUE or FALSE) in green or red.
6. Make Sure Your Graphs Are Easy to Find and Follow
Your financial model should consist of both spreadsheets and visual representations of your data, such as graphs and charts. But rather than having your spreadsheets and graphs merged into one sheet, create a new sheet just for your graphs. And don’t overcomplicate your graphs and charts — stick to using visual representations for main data like volumes, revenues, costs, EBITDA. Use an axis and make the colors easy to differentiate. Make the graph shapes and sizes easy to copy and paste to a presentation. You can also add a small table with numerical values to summarise the main numbers on the graph.
If you need a little extra help in learning how to build a financial model, check out my free Excel tutorials, which include an introduction to financial modelling for beginners. Or if you’re hoping to become a data whizz and want to take your data analytics and financial modelling skills to new levels, I offer one-to-one Excel and data analytics courses.
Alternatively, you can unlock the value of your business data and get the clear, visual and easy-to-follow financial models you need with expert financial modelling consultancy services.