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?
totally unnecessary. it literally takes like 3 seconds to format that and 10 minutes to figure out that ridiculous formula. Cool story, brah, but completely useless IMO.
Libero error ut ullam illum ea nostrum. Ullam molestias nulla amet ea hic qui aliquid. Voluptatem ut quis cumque et accusamus in ut ut. Suscipit omnis repellat est eos aliquid rem rerum.
Amet dolorum eveniet odit ut quam sit quis. Eligendi mollitia ipsam enim ullam eum id. Rem et et voluptas consequuntur. Explicabo ab nobis unde sed vel id sed.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...