Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, + Website

CHAPTER 9

Common Uses of Tools in Financial Modelling

Now that we know how to use many of the useful tools, functions, and features of Excel, let’s take a look at actually using these tools to solve common problems, calculations, and situations encountered in financial models.

ESCALATION METHODS FOR MODELLING

There are several different ways of applying a growth, indexation, or escalation method over a period of time to a principal amount. We often need to be able to include escalation in our models for the purpose of forecasting sales growth year over year for example, or increasing costs by an inflation amount. Calculating a growth amount might seem straightforward initially, but there are several different methods that can be used, and when applying growth rates in a model and you need to be clear about which method you are using.

Whilst we can use functions to calculate future values based on set or varying growth rates, we’ll firstly calculate them manually so that we understand the mechanics of the calculations, and then use the functions.

Using Absolute (Fixed) Growth Rate

Here we have compound growth over five years at a fixed interest rate. The capital grows each year by 5 percent. At the simplest level, our formula is base amount*(1+growth). The reason we are adding 1 (effectively 100 percent) to the growth rate (5 percent) is because we want the capital sum returned along with the accrued interest. Multiplying an amount by (1+growth) is a very common calculation in financial ...