Most operating models are standard three-statement models with balance sheet, income, and cash flow statements. The general purpose of the model is to have historic data overwrite the forecast values and reflect what actually happened when the project is live.
A simple way is to create a set of proforma statements matching the format of the model; for every line of the cash flow, there is a corresponding line on the actual cash flow where one inputs data.
However, we will discuss some problems with that approach later. Here are a few things to consider when building a model like this:
• Indices – If you have cash flows explicitly dictated by an index, most notably inflation, then the index needs to be kept up to date and behave correctly at the start of the forecast period.
• Tax balances – Check for any tax losses you may be eligible to use and that any tax depreciation is calculated correctly.
Why populating historic data is not straightforward?
The main issue people have is a mismatch between the form of the data the model needs and the data they have. It can happen for many reasons, but generally, it comes down to the fact that the model was not set up to take the actual data. It is particularly true when converting a transaction model into an operating one, which we strongly advise against.
Generally, the focus of the financial model is cash flow; it is very detailed, showing where the money is coming from and where it’s going to. The profit and several balance sheets are still part of the model, but they are not the main outputs of the model.
On the other hand, the accounting team, which provides the actual data, is more focused on trial balances, having detailed items of profit, loss and
balance sheets and ensuring that they all balance. The cash flow is often a sort of an afterthought; it is often reduced to a couple of cash balances without many details.
Essentially this means you have far too much detail about things you don’t care about and not enough detail about things you do.
Thankfully, there are things you can do about this.
How to fix it?
Since it is almost impossible to know for sure what the data format will be, it is essential to allow flexibility to the solution you build, allowing for some amendments to the inputs where necessary.
Trial balance interface
The main advantage of a trial balance interface is that once programmed, it remembers how to process the data inputs, making recurrent updates much faster and easier.
The idea is to assign the hundreds of codes that constitute the trial balances from your accounting software to their equivalent in your model. For example, Cash account 1 is Net Cash, and Receivables balance 1 is accounts receivable.
Once done, you can simply paste your trial balance into this interface, and it will automatically identify everything. If there are a couple of new codes, it will alert you that you need to assign them. The interface converts the accounting data into the format the model expects and can use and is easier to track.
This process is relatively easy for the profit, loss and balance sheets. The tricky thing you might have to do is use these balances and these profit and loss items to infer a cash flow. But again, once set up, your model will automatically update and recalculate balances and cash flow each time you feed in the latest accounting data.
The main advantage of this approach is that although it will take more time to set up, once done, importing historic data every period becomes relatively trivial. It is particularly helpful as updating the model is often relatively infrequent, meaning that if you do not do something like this, you will need to constantly remind yourself of how the model works.
Helper spreadsheet
If a trial balance interface is not for you, then the next best thing is to create a helper spreadsheet. Half of the battle when updating a model with historic data is remembering the meaning of each accounting code; while some are straightforward, others can be difficult to remember. Therefore, creating a spreadsheet listing all the trial balances and their matching terms in the model, indicating where they are used, will save you time and ensure consistency.
Pragmatic approach
A final solution is to adopt a more pragmatic approach to modelling and only focus on what matters.
The three key elements you should focus on are:
• Making sure all the cash balances are correct in the model, i.e., do they balance?
• Fixing any audit tests contained in your model, especially those checking that the balance sheet balances, the net movement on cash flow, and the net movement of profit and loss match the movements reported on your balance sheet. Having those three things correct does not mean your model is sound. But, at least, it indicates nothing has gone catastrophically wrong, especially in the first period, which is the main focus here.
• Ensuring key reporting metrics such as CFADS and debt service cover ratio are correct, at least in the early periods. You should have a feel for what those should be, so ensuring that they are roughly correct provides you with some comfort that your model is not too far away.
If you are interested in setting a trial balance interface and need some help building it, Operis will be delighted to support you. Contact us to discuss your specific requirements.
