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


1. Calculating 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.

day 2 step 1 formula (transparent).png

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

1.1 Starting 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 ).

day 2 step 1.1 formula (transparent).png
day 2 step 1.1 graph (further crop edit).png

1.2 Starting 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.

day 2 step 1.2 formula (transparent).png
day 2 step 1.2 graph (further crop edit).png

1.3 Narrowing 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.

day 2 step 1.3 formula (transparent).png
day 2 step 1.3 graph (further crop).png

1.4 Defining 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.

day 2 step 1.4 formula (transparent).png
day 2 step 1.4 graph (further crop).png

 

2. Calculating 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.

day 2 step 2 formula (transparent).png

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!