Our journey through the 10 Rules of Financial Modelling continues. This post looks at the importance of staying left to right consistent in your financial model. Overall, using your spreadsheet obviously and placing one formula per row will make all the difference for your users.
There’s no doubt that reading from left to right is an accepted and established rule of the Western World. We are all programmed and trained for our eyes to go from left to right when digesting information. When it comes to creating financial models, the importance of running from left to right is recognised as being best practice.
It’s also important to note that assuming that anything will happen on a specific date is not good practice in financially modelling as it isn’t in life. It rarely happens like that – even in the most well-managed projects. Formulas need to handle changes to the assumptions about the timing and duration of events over the forecast period..
Saying that, let’s move onto some other imperative points.
One formula on each row
The principle of Left-Right consistency can be summarized as “one formula per row”.
The typical financial model has a timeline representing the forecast period, covering various aspects, such as construction, operations and abandonment, etc. Whatever the case, you should only have one formula on each row during that forecast period.
Of course, there are different considerations for a project’s lifecycle, such as the various events that occur over time.
Even with time-dependent events, such as the repayment of a loan or the depreciation of an asset over time, problems can occur because you have periods before, during and after these routines.
The one thing you must avoid is to simply write the formula in the relevant columns, leaving blanks cells at the start and end of the row, and hoping that nobody ever changes the timings!
Staying with the one formula per row rule is essential no matter what happens.
Why one formula per row?
It’s never wise to assume that an event happens at a specific time or is of a specific duration.
For example, if we were modelling a loan payment and using the model to work out the affordability of the repayments. We might want to consider extending the repayment period. We might want to do the opposite and shrink down the repayment period.
If you don’t follow the rule of Left-Right consistency, the risk is that you might say, “well, for my loan repayments, I’ll leave the cells in columns D and E blank because I know that’s when the loan drawdown takes place. And then, I’ll start my loan recalculation formulas from column F onwards and ignore the previous columns. Then I’ll only copy it across ten periods because it will be repaid over ten periods. So there are no formulas beyond the end of that.
The outcome is a messy financial model that nobody will understand apart from you.
How do you Ensure Left to Right Consistency?
So the question is, how do you do the calculation? So that it only works during the repayment period and not during the drawdown period and after the loan has been paid off. And same applies to the capex?
There are checks you can run to prove left-to-right consistency using Excel functionality.
But it is considered one of those fundamentals that you write. For example, if your first period is column D and your final forecast column is Z, then the formula in column Z must be the same as in column D. One formula per row. The clever bit is to write the formula in such a way as to anticipate all potential timing issues as described. So right from day one, as you start your first formulas, you must start thinking, I can’t just put my formulas in the appropriate spaces where I believe they are relevant. I’ve got to think of somehow writing the formula in the first column and copying it to the end of the row without any changes.
Again, if you are new to modelling, you’ll be taught many great ideas on how that particular problem can be solved.
The final word on left to right consistency
It isn’t just us who believe this to be a fundamental modelling rule. It is widely accepted in the financial modelling community.
To find out more about this very important topic, refer to a great ICAEW article on consistency at the link below. Or to sign up for our Financial Essentials Modelling Course click here.
