Within the first few minutes of starting to build a financial model for project finance, corporate finance or any other sector it becomes clear that a financial model occupies more columns than your screen can show – that shouldn’t be news to you and here is a great way to solve the constraint.
What is a “Line Summary”?
The implications of this the number of columns you can show on the screen is that you spend less time analysing columns further out in the model as there is nothing ‘special’ about them, unlike those at the beginning of the worksheet, which are by their nature more frequently viewed.
One very simple method of keeping track of what is happening in those columns is to aggregate them and have a cell at the beginning of the worksheet, just before the unique formula start, which contains an arithmetic sum of the range from the first to the last column of the model.
How are line summaries applied in a financial model
I use a Line Summary for virtually every line in the model that makes algebraic sense to sum. For example, this doesn’t apply to opening and closing balances but does to most other lines. I personally use them extensively in order to rapidly:
- Understand the quantum and sign of lines such as Revenue, Costs, Tax, and Dividends, CFADS, Principal and Interest etc over the entire project.
- Check that the same lines on each worksheet have the same totals (for example CFADS on the Annual cashflow is the same as on a quarterly cashflow.
- Troubleshoot with a colleague working over the telephone to compare model versions. This is an amazingly fast way of finding differences in model versions or scenario settings.
- Prepare “Sources and Uses” analysis
- Comparing Scenarios to a Base Case.
- Used with debt sizing macros to ensure the principal is not under or over paid.
A more advanced application
If you adopt the use of Line summaries, like most inherently simple tools, you will find they have number of uses. A useful variant is to prepare conditional Line Summaries. This is where the simple sum is replaced by a SUMIF driven off of whether or not it is a construction, construction delay or operations phase. In this case I would have 3 columns dedicated to Line Summaries before the main coding on the line starts.
I have seen that “line summaries” as described in this article is becoming a more common practice, which is a good thing.
One thing that has often puzzled me (and the same applies to the example illustrated in the article) is “Why are the opening and closing balances not included in the summary?”
I don’t mean adding up all the opening and closing balances, but including the opening balance, and calculating the final closing balance (the grey hashed cell in the example) using the same formula as in each period. The final closing balance can then be checked against the balance calculated in the last model period.
In many projects where there is no opening balance sheet and all balance sheet items clear out to zero by the project end, showing the opening closing figures this doesn’t make much difference as they would both be zero.
But if there is an opening balance sheet and/or closing balance sheet, overall sense checking and debugging will be easier.
It also means that balances at the end of the model are far more evident as users do not need to scroll all the way to the right to see final balances.