Help Me Excel Nerds - Interactive LTM Column
The columns in my model (assuming 1Q24 is in cell B1) are 1Q24, 2Q24, 3Q24, 4Q24, FY24, 1Q25, 2Q25. Assume below each is the date (i.e., in cell B2 it says 3/31/2024). The data in the below rows are items like Revenue and COGS, which would be in cells B3 and B4, respectively. I have an LTM column with the corresponding date, say it currently says 3/31/2025, but earnings come out this week and I will change it to 6/30/2025. I will input 2Q numbers in the corresponding row of the 2Q25 column, but I want the LTM column to automatically register the prior four quarterly periods, while skipping the FY (fiscal year) figure, to return the LTM (last twelve months) data. Anyone know how to do this? I have been trying a bunch with ChatGPT and CoPilot, but keep getting dud formulas.
Would j use a sumifs, criteria 1 is dates and criteria 2 is if Quarter or FY, should be easy enough to implement, might j have to add a row or 2
Couple options
1. Set a driver date (cell where you manually enter 3/31/25, 6/30/25, etc. Then write a sumifs where the criteria is in the last 12 months
2. Set up your file in a way so that you are summing monthly/quarterly figures and have a date structure set-up. In this example I have my driver date set to 6/30/25 and for my TTM period I am summing everything that is in the last 12 months of 6/30/25
.
Deserunt molestiae ut sed. Et nobis repudiandae enim voluptatem aut ullam. Alias maiores laborum id rem.
Dolorum ex quia dolorum repellat rerum consectetur. Temporibus delectus dolore quae sint et. Commodi praesentium eos reiciendis eos ab ut debitis. Sunt omnis autem culpa enim a debitis amet.
Quo magnam facilis ab. Atque est quis rerum officia dolorem perspiciatis esse dolore. Qui ut et voluptate ipsa id sunt autem enim. Deleniti excepturi eveniet accusamus velit quia. Id aut totam praesentium nihil voluptatibus reiciendis sunt a.
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...