[2/5] How to calculate Last 12 Months (L12M) Total and L12M Average figures


  • Challenge level: 4 out of 5

  • Estimated reading time: 20 min

  • Excel functions covered: SUM(), OFFSET(), MATCH() and IF()

  • Excel attachment reference sheets: template_Excel


BEFORE YOU START: Get your FREE Excel template that accompanies this five-part tutorial by subscribing here!


 

Step 1. Calculate Rolling Last-12-Months (L12M) Total

The first complex calculation we are going to tackle here is the total rolling last 12 months’ figure.

The Excel formula for this consists of embedded OFFSET, MATCH and IF functions within a SUM function.

 Excel formula rolling last 12 month (L12M) total using SUM, OFFSET, MATCH, IF

Phew, we know! Let’s break down the formula further. Let’s look at our ROWS field.

 

Step 1.1. Start at the right row

First, you would need to define the latest month you want to base your calculations off. In this example, we have chosen “200806” from the drop down list in cell G3.

To determine the correct row that Excel should start from to sum up figures, the MATCH function helps return the correct position for this. You will need to subtract 1 from the returned position value to exclude the header row (i.e. the row listing the words “category” and the different months ).

 Excel formula rolling last 12 month (L12M) total using MATCH to find row position to SUM
 Excel formula rolling last 12 month (L12M) total finding row position visually explained

 

Step 1.2. Start at the right column

To determine the correct column that Excel should start from to sum up figures, the MATCH function helps return the correct position for this. You will need to subtract 1 from the returned position value to exclude the category column.

 Excel formula rolling last 12 month (L12M) total using MATCH to return column position for SUM
 Excel formula rolling last 12 month (L12M) total finding column position visually explained

 

Step 1.3. Narrow down the number of rows to sum

With the optional HEIGHT field at 1, the selected data range to be summed gets narrowed down to $AB$9:$BL$9.

 Excel formula last 12 month (L12M) total narrow rows to SUM
 Excel formula last 12 months (L12M) total rows to SUM visually explained

 

Step 1.4. Define the number of columns to sum

As the final step, we need to define the correct number of columns to sum. We utilise the IF function here to account for situations when there aren’t 12 months to sum up.  

We also need to reflect a negative figure for the correct number of columns to be summed as we are counting backwards to consolidate past months values from the latest YYYYMM.

 Excel formula last 12 month (L12M) total columns to SUM
 Excel formula last 12 month (L12M) total columns to SUM visually explained

 

Step 2. Calculate Last-12-Months Average (L12M_Average) figures

The IF function is used here again to factor in situations where there are less than 12 months to sum up and hence to divide against to get an accurate average figure.

 Excel formula rolling last 12 month (L12M) average using IF, MATCH

With this base understanding of how the L12M formula works, we push further and explore how slight tweaks to the formula can easily give us other important figures like Year-to-Date (YTD) and Financial Year-to-Date (FYTD). But that's a topic for tomorrow!