Prices in an Excel financial model often escalate for example by inflation.
Escalation factors in a financial model: the problem
In financial modelling a problem can occur when the escalation factor (e.g. the Retail Price Index) is published at the end of one time period (e.g. quarterly), contracted prices escalate at the end of another time period (e.g. monthly, quarterly or annually) and the model itself forecasts different time periods (monthly, quarterly, or annually).
Financial modelling with escalation factors
The example attached shows you both how to get it right, and how to get it wrong in a financial model: download the Excel spreadsheet escalation factors. To get it right, you need an understanding of the escalation factor itself (when does the escalation factor increase?), contractual prices (what do the contracts say about when prices increase?), the dates used for the model (what time periods are we modelling?) and how those three things interrelate. In a long dated model, where revenues escalate and are locked in contractually, and you have high debt costs, inaccurate modelling of escalation factors can result in a long range forecast that proves disastrously wrong – perhaps with actual profits turning out nowhere near those originally expected.
Modelling with escalation factors: how to get it right
Look at cell B14 in the example attached (escalation factors). It shows you how to get it right in a financial model. In the example imagine we have succeeded in understanding the escalation factor itself, how it is applied contractually, and how all that relates to model’s own time periods. In the example we have an annual escalation factor (3%) that is applied to rent prices 1/4ly, and we have a 1/4ly model.
The impact on the model
The 3% escalation factor is an annual figure but we are imagining that, under contracts for the project, prices escalate 1/4ly. With prices growing or compounding 1/4ly, by the time we get to Q4, prices will have escalated by a factor great than 1.03. Click on cell E4 to see how the escalation factor has been calculated. In this case, with a 3% escalation factor, with that escalation applied to contracted prices quarterly, and a quarterly model, total rent for Y2 of the model totals 35,693.3 – the correct figure.
Financial modelling with escalation factors: how to get it wrong
At line 22 in the spreadsheet, you’ll see how revenue has been calculated incorrectly. We’re imagining the same set of assumptions (annual escalation factor, applied to contracted revenues quarterly), but this time we’ve decided to create an annual model and, in doing so, lulled ourselves into making a careless mistake. Although the 3% escalation factor itself is an annual figure, and the model is an annual model, in this case we’ve forgotten that the contracts themselves escalate quarterly. As a result, the escalation factors at line 21 are too low, and the forecast revenue at line 22 is too low as well.
Financial modelling with escalation factors: course tips
The lesson from our financial modelling course should be very clear: be careful with escalation factors. You need to make sure you are modelling correctly, given the escalation %, contractual arrangements, and the periods used for the model itself. Making a simple error could throw your model out as the forecast rolls forward. Perhaps the safest thing to do, to help you avoid any inadvertent mistakes, is to build a financial model that uses periods that relate to contractual arrangements (i.e. if contracts escalate at the end of a particular 1/4er think about building a 1/4ly model).
About the financial modelling course material
You’re looking at a free online material covering Excel functions, extracted from FTA’s financial modelling course.