Article

Excel financial modelling ratios - how much is too much?

Rob Bayliss Rob Bayliss

If you have too many ratios in your Excel financial modelling, the audience is likely to find itself suffering from ‘paralysis by analysis’ – they’ll have so much information swimming in front of their eyes that they may not be sure what’s most important.

On the other hand, too few ratios and there’s a risk that a key trend or anomaly will be completely missed, which a funder may discover and question when they have the sense to calculate those figures for themselves. With ratios, less can be more, so the art is covering all the main areas but strengthening the messages by strictly limiting the set of ratios presented.

Let's go through some key ratios that it may be worth adding to your model.

Operational ratios

Here, at a minimum, consider including:

  • volume figures that drive underlying operations (pause to think about what’s of most interest in the business’s monthly management reporting packs – perhaps also including average pricing trends, as well as volume changes)
  • revenue growth, showing how quickly the business thinks it can expand
  • gross profit margin, meaning gross profit divided by revenue. An increasing trend highlights the extent to which the business thinks it can exploit premium pricing, and/or reduce the direct costs of production
  • EBITDA margin (earnings before interest, tax and depreciation divided by revenue). You could expect an increasing trend in a business that has some spare capacity and, for example, doesn’t suddenly need to start paying for the running costs of a massive new warehouse as it starts to ship products in greater numbers.

How you pick from these, and what else you include, will depend on your sector and your stakeholders.

Balance Sheet

Most people’s list of Balance Sheet ratios would include those that check the business is investing in assets that will support business growth. That is, ratios highlighting a consistent relationship between proposed expansion and the cash flow of the business.

A minimum list of balance sheet ratios might include:

  • forecast debtors (receivables) against revenue. This could be presented as a percentage, eg debtors as a proportion of sales or as debtor days (the value of debtors divided by sales multiplied by the number of days in a year)
  • forecast creditors (payables) against, eg the costs of goods sold, perhaps expressed as creditor days
  • inventory (stock) against, eg costs of goods sold, again shown as stock days, giving an indication of how long the business has goods sat waiting to be sold
  • a ratio or ratios to check that growth in Balance Sheet fixed assets is consistent with the business’s plans for expansion. If the business has a new empty warehouse right now, you might expect sales/fixed assets to increase in the short term. But, as soon as that spare capacity is used up and cash has to be invested in the next warehouse build, you’d expect to see sales/fixed assets declining.

Debt ratios

Where the balance sheet contains debt and is being reviewed by your bankers, you’d expect to see a few ratios they’d want. Here’s an idea: ask them about the detail of what they’d like to track. They’ll probably be happy to share that with you and they could well have a raft of precise figures they’d like you to calculate. Where debt is significant in the model we’d expect to see:

  • a ratio that is considering the amount of debt, perhaps against assets and/or profitability, eg debt/EBITDA
  • ratios that are considering the affordability of the debt burden, eg cash flow available for debt service (all cash flow prior to debt costs) divided by cash interest and debt repayment costs
  • something that shows how quickly debt gets repaid.

Often these ratios will be included as conditions (“covenants”) set by your bankers. Make sure your model anticipates this and helps you see how business changes will affect your compliance with banking ratios.

Other ratios

Investors will be expecting a return on the funds they provide, so you might want to include a pay-back ratio or a money multiple (cash out divided by cash in) or an internal rate of return (IRR) on equity (the effective interest rate that turns the cash-in into the cash-out. There are IRR and XIRR functions in Excel to help.).

You could easily include a lot of other ratios, including those that draw directly on cash flow. For example, we regularly see a ‘cash conversion’ ratio showing the percentage of EBITDA that makes it into operating cash flow, which may help explain the working capital needs of the business.

Ratios are not the time to go wild in your Excel financial modelling

You could easily overdo the number of ratios you include, but the art is in putting a short list together that highlights key trends or answers key questions. Use ratios as a tool for concise and focused communication. Think about your audience and what they’re used to seeing. Pick the best ones and show them in graphs, and make them prominent in ‘dashboard’ or summary pages.

And remember, less is most likely more.

Excel financial modelling

Rob Bayliss heads our team of Excel modellers who pull together beautiful forecasts for clients with important decisions to make.

To discuss modelling and forecasting in more detail, contact Rob.

This post is part of the series How confident are you about your financial forecasts.