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
.
Eum molestiae laudantium aut est aperiam. Dolor ipsam modi ipsum tenetur aut. Laboriosam nemo quo et tempora illum. Dolorem natus voluptatem vel. Nesciunt saepe quis fugiat quis eos dicta voluptates fugit.
Magni sint quidem dolor quod tenetur voluptas ipsum earum. Est placeat vitae ut earum sed cupiditate illo. Id et soluta quia dolorum saepe repellendus.
Eum ipsum itaque qui. Nihil animi molestiae perferendis laborum. Et velit quia ea.
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...