As part of our series on the 10 Rules of Financial Modelling, this post looks at why any good financial model developer needs to add flow to their models to make it easier for the user to follow.
Flow is quite a broad topic. However, the critical point is that all information needs to flow through your model, and you can do this by setting it through the structure, with good planning and doing all you can to make it easy for your users to engage with it.
Good practice – begin with the outputs
Success starts with a well-planned financial model.
Some people say that you should structure your model like a book. Their idea is that you’ll start with page one and then read through to the final tab on the last page.
However, this isn’t the best approach to take. It’s better to consider what your outputs are and then build your structure around them. In your financial model, you’ll have input sheets, working sheets and output sheets, as no doubt you’ll have read about in our previous rules.
The user will most likely want to see the outputs of the financial statements, cash flow, income statement, and balance sheets. You will also have ratio sheets, summary sheets and charts, and documentation that communicates the different aspects of the spreadsheet.
Once you have those identified, what should you do next? Well, again, if you’re interrogating or trying to get behind, say, a set of ratios on a ratio sheet, you’ll naturally want to work your way back through the logic.
Overall, the financial model is a dense web or a matrix of relationships between different components of the cash flows and earnings and so forth, ultimately manifesting itself in the balance sheet, which is one of the classic audit checks. But that’s something that needs to be appreciated, that it’s not just a series of calculations. They are related and dependent calculations, and therefore, you’ll be making changes in one place and expecting to see a change in other places.
You’ve also got things like the distinction between cash and earnings. We know from accounting 101, cash isn’t earnings, and earnings isn’t cash. To make changes to the cash side, there will need to be some compensation by adjusting the earnings.
So it’s that flow of information within that sheet that becomes all-important.
The cash flow approach
It’s essential to consider the priority of the cash flow report.
That’s the one that the users will be focusing on to understand the ratios. For example, if you’re doing the debt cover ratios, the numerator of the fraction, which is the cash flow available for debt service, or CFADS, can be identified as project cash flow with various inclusions and exclusions, of course, according to the agreements. And the denominator, debt service, as repayments and interest, is also shown on the cash flow. And then you’d want to go back to the workings to understand the facts as calculated based on the individual line items.
Working sheet
It’s good to ensure that the workings sheet follows the same layout as per the cash flow report. Browsing from the top we would expect to see revenues, operating costs, working capital, capex, and tax, in just the same order as they are reported on the cash flow.
Then you would have a section for the earnings and the cash flows themselves. Then there would be all the financing instruments that follow on. The financing instruments are listed in order of priority that is again reflected on the cash flow report. These might be laid out as a cash cascade.
This would include the senior debt, reserve accounts, subordinated debt, down to the equity and the dividends.
In this way the cash flow report layout feeds into the layout of the workings, and the flow idea can be mapped right back to the inputs sheet.
Logical flow
If you’re already reasonably familiar with cash flow reports, then the workings sheet is a massive version of that because it’s all the precedent calculations for the cash flow line items. Tracking back to the input sheet is still following that same overall layout, and therefore, there’s a logical flow of content.
What happens if there is no flow
It’s when the flow isn’t there that it becomes problematic. For example, when you have unexpected detours to different spreadsheet sections that don’t naturally follow the ordinary flow. Sales tax logically follows on from revenues and costs so you would probably expect to see the calculations in the same part of the workings. But where do you start looking if they aren’t there? Then you find them tucked away underneath dividends. What is the thought process behind that? What else is going to be inconsistent?
Recap
So flow then is very important. And again, you’ll be aware that some shortcuts and methods to give you keyboard functionality to rapidly follow the audit trail of anything from output right back to inputs in a very short space of time.
Don’t misunderstand or misinterpret the idea flow. It is the logical structuring of your thinking and the logic of the model, and it should be an obvious flow through from inputs to workings to outputs.
In summary, you don’t want people to get lost en route. You shouldn’t send them off down blind alleys. You want them to go through that little journey with the desired outcome. In the end, they find what they’re looking for in the process.
If you’re ready to invest in your modelling future enrol on our on-demand Financial Essentials Course now!
