My auto-LTM formula

I thought I'd share my excel formulas for automatically calculating LTM and having the column containing my first forecasted quarter shaded. I added the latter today.

Why do this - simply to avoid having to alter formulas to calculate LTM every time a quarter changes. It also helps to pull most recent cash / debt for use in valuation.

I've broken the formula into several cells, but you could probably combine them.

First, I type MRQ above the most recent quarter in cell H1

In columns Y through AB I have the most recent four quarters, then sum them in AC to get the LTM

The driver is in cell AB2
=match("MRQ",$A$1:$X$1) -- this gives the column that MRQ is in and is the reference point for all the formulas

In cell AB3 I have the address of the most recent quarter
=Address(4,AB2) - note that in row 4, I have the headings Q1, Q2, ... FY'12, FY'13, etc

In cell AB4, I pull the heading of the most recent quarter
=Indirect(AB3,True)

Here's the fancy cell that will drive most of the automation
In cell AB5, I create the R1C1 references that will help pull the data from the correct quarters.

="R[0]c[-"&text(columnAB2)-AB2,"0")&"]"

Now let's pull the first piece of data...

In row 7, I have "Revenue"
So in AB7, I want to pull the most recent quarter's revenue
=indirect(AB$5,FALSE)

You can copy that down through the P&L

Now for the other quarters. The only trick is that if Q1 is the MRQ, the column directly to the left is not one quarter prior. It'd be the summation of the FY.

So, now to find the prior quarter's column.
AA2 the formula is:
=if(AB4="Q1",AB2-2,AB2-1)

AA3 = Address(4,AA2)
AA4 = Indirect(AA3,True)
AA5 = "R[0]c[-"&text(columnAa2)-Aa2,"0")&"]"

AA7, to pull revenue for prior quarter = Indirect(AA$5,False)

You can then copy that range of formulas into Z, and Y.

Now you've got the four most recent quarters of data all lined up, so in column AC I simply sum them.

It's also useful on the balance sheet where you can pull most recent cash / debt for use in valuation (which I do on a separate tab).

CONDITIONAL FORMATTING
I like to have the next quarter (first quarter I am projecting) shaded in a light gray.

The formula is:
=If($AB$4="Q4",if(column()-2=$AB$2,1,0),if(column()-1=$AB$2,1,0))

Again, if the MRQ is Q4, the next column to the right is the fiscal year, not Q1 of the next, so first you have to check for that, otherwise, the formula uses my "LTM cells" to find where "MRQ" is and shade the column to the right.

You then want to apply this formatting across the spreadsheet, something like $A$1:$AC$2000

The only problem I have encountered is when I update the spreadsheet and move "MRQ" to the right by one column, if I cut and paste, for some reason the conditional formatting range will get screwed up. So instead, copy, paste, then delete the old MRQ.

Thoughts? Anyone have a better way?