Trailing 12 Month Financial – Income Statements

582 viewsTips and Techniques

This is not a question.
I’ve been using Quantrix for years, and I consider myself an advanced novice – and I’m even proud of that designation.
But I’ve been trying to figure out a simple way to create a trailing-12 P&L presentation while using a timeline in a model. For you experts, this is like falling off of a log. But if you are in my boat, it’s not super-easy. But I decided to heed the advice of Rich and Stephen – create a simple time utility matrix – but it’s a utility that serves just this one purpose – allowing a financial statement reader to see a trailing 12 view of the numbers. See my results below where actuals run through August 31, 2020:

Sorry for the image – my model is too large.
Notice the category header and the first item in the row – that’s the key to pulling this off. That column category and the Date ‘item’ reference the date utility I created below:

The date utility takes advantage of the following functions: edate, date, year, and month – nothing difficult. Notice the current date item above is redundant and not needed – I just wanted it visible at all times while creating the formulae (that item references an input matrix for the current time period).
With respect to the trailing 12 presentation, pulling the numbers for each P&L classification did not require a complex formula based on the way my detailed actuals are stored in the model. I did use a ‘using as’ for month and year to get the right numbers in the summarized version you see above.
My solution is probably a 2 on a 10 scale for elegance. So why am I showing it? Because Rich’s videos and this forum is my first point of reference when trying to figure something out. The concept above is not found in this forum, so it was time for me to give back. But I will yield if there’s a better way.

Answered question

Hi Mark,
I welcome your demonstration of TTM implementation in QM. Without any doubt, this is a mature implementation. Please continue to share your experience further.

Posted new comment

Thank you, SAU. First, you are amazing. So I would and will default to any better and more elegant solution you come up with. I’ll keep sharing ideas over the next few weeks. Between you, Rich, and Stephen, I’m a bit intimidated to share too much.

You are viewing 1 out of 3 answers, click here to view all answers.