Financial modelling: Demystifying the Excel beast

At some point in your company’s life and, in our experience, that point is normally very early on; you will need to put together a financial model either for operational or funding reasons.

To a lot of business owners, the thought of multiple spreadsheets and thousands of calculations that go into a financial model is very daunting. There is no shame in this because you might be a great founder but not necessarily a finance modelling guru. In reality, it takes years and hundreds of models to become an expert. But as the founder of a company, you need to be prepared to understand what goes into the financial model and what role it plays in your company’s life.

No strategy, no financial model

Let’s start with the basics of what it is and why it is important. A dry and technical description of a financial model is an integrated set of financial statements that show how the company will behave financially if different assumptions are applied. But the real description that reflects the importance of the financial model can be summarised as a numerical articulation of the company’s strategy and future. Or as we like to describe it – a map of a company’s commercial journey. This means that the strategy and financial model must be connected to tell a story of the business. Thus, before you even try to attempt to build a model, first take out a whiteboard and focus on strategy.

At the end of the strategy exercise, you will be surprised how that will feed the assumptions to a financial model. Every detail about your business; from your team’s growth to pricing increases, to stock holding will have to be reflected in the financial model. It will be due to your strategy that this will result in financial consequences.

But beware, if the strategy changes (or in modern language ‘pivots’) then the model will need changing too.

Three financial statements test your strategy

If you wonder how strategy creates assumptions and how that influences a financial model, let me paint you a picture.

Let’s say you are planning to sell software to enterprise clients one thing you will certainly need to think about is what are the payment terms for a client, e.g. monthly, quarterly, annually etc. this decision will be reflected in three statements on your financial model – the Profit & Lost will register the sale, the Balance sheet will record the sale as an asset, and the cash flow statement will allocate the money when it is actually received.

It is essential to understand the significance of these movements within the model and record them on a monthly basis because it will show the working capital cycle and would give you an idea of how much capital you need, and when, in order to keep afloat before sales and working capital cycle will catch up with each other.

To make it a little more complicated think about what will drive sales – sales staff and marketing spend – this also needs to be planned and recorded in your financial model. Some sales staff will work out, and others will leave. In other words, every single strategic decision you will make will have a direct effect on the financials of the company.

A financial model is a decision tool – make it agile

Your strategic decisions will be reflected in the financial model and will be used as a decision tool by various stakeholders from finance directors to banks/investors. This is another moving part – understanding the receiver and what purpose it should serve.

If you are applying for a business loan the bank will be keen to understand how the debt facility will be financed, i.e. will the company have enough cash flow to service it; while an investment professional would be interested in growth and value creation. So it is important to build a dynamic financial model.
Because most businesses are constantly moving and changing, so do their financial models, and so they need to be built in an agile way. As our financial modelling expert, Nathan Young says “Before blood hits the Excel page focus on the dashboard planning”. The dashboard is an engine room for inputs and assumptions. A good modeller would always spend a lot of time in making sure that all key assumptions are stored here and can be easily adjusted, showing the effect of it in the intergraded financial statements. Dashboard input areas should allow you to create different scenarios to see what effect each would have on the business. For instance, what would happen if we add debt or if sales growth is slower. This allows you to simulate different situations without rebuilding the model. Thus, even if you don’t build your model yourself, make sure whoever does it creates a dashboard for your assumptions based on your company’s strategy and evidence.

Don’t reply on hope bringing evidence to your assumptions; unfortunately, hope does not pass any due diligence process.

Avoid pitfalls early on

We build and review many financial models every year and time, and time again, we come across issues in the financial models that could be avoided. So here is our quick crash course for you in where not to trip when it comes to financial models.

  • Rule number one avoid hardcoded numbers. Once it is hardcoded, it is prone to mistakes, and it is no longer dynamic – stick to simple formulas and a dashboard.
  • Be neat – avoid jumbled data sets – the financial model should be read like a book from left to right, up and down. Also, top tip: keep all three financial statements in one sheet, this will make it easy and neat!
  • Capital structure and returns – make it easy for yourself and the receiver of the model to see debt or capital allocation and its movements in the business. Incorporate your CAP table in the dashboard.
  • Seek professional advice when it comes to taxation, especially VAT treatment and, in some cases, even cost allocation. We have seen a few businesses where the gross profit margin was squeezed due to the wrong allocation of cost, which should have been the overheads. These mistakes can be real hurdles when looking for funding.
  • Make sure you have three integrated financial statements and your balance sheet balances! You would be surprised how many people think that a P&L is a financial model. The P&L is just a snapshot; it doesn’t tell us anything about cash flows or working capital movements.

Financial modelling is a numerical representation of your business, and you know it best so instead of feeling overwhelmed by financial modelling we hope you will feel a sense of clarity where you should channel your energy.

As a founder, you play a key role in building strategy and developing well-evidenced assumptions. So even if you are not building it yourself but trusting someone else to do it, your inputs and understanding what goes into the model are much more valuable than your technical skills in building it.

For any further information on financial modelling, you can contact the Strategic Corporate Finance team at Price Bailey via the form below.

 

We always recommend that you seek advice from a suitably qualified adviser before taking any action. The information in this article only serves as a guide and no responsibility for loss occasioned by any person acting or refraining from action as a result of this material can be accepted by the authors or the firm.

 

Subscribe

For more insight, events and webinars sign up to the Price Bailey mailing list…

Sign up

 

 

Have a question about this post? Ask our team…