Trailing 12 Month Financial – Income Statements
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.
I continue the Mark’s theme with a brief description of my method for the problem solving. My method is do not use the ‘Trailing 12 Time Utility’ matrix at all for data sampling purposes. Instead, a single formula calculates on-the-fly OnTheFly_key_list for the select() function based on the matrix structure data with the source IS data and compares the resulting OnTheFly_key_list with two boundary values: LowerLimit and UpperLimit. In turn, LowerLimit and UpperLimit are calculated using several simple formulas based on the entered StatusDate parameters.
Now, when there are both OnTheFly_key_list and LowerLimit with UpperLimit, it is very simple to use the select() function to select only valid 12 months data from the matrix with the source IS data for 24 months into the reporting matrix according to the following scheme:
*
select(24MonthIS; and(OnTheFly_key_list >= LowerLimit; OnTheFly_key_list <= UpperLimit)
*
This method also does not use the Date element that is present in the Mark model. This allows to me to link two matrices with the source and reporting data by means of ISItems category, which simplifies both the structure and logic of the model.
Below is an animated demonstration of a simplified model based on dummy source data. The slider is used only for convenience of demonstration.
***
**
**
***
Good luck
Similar to SAU’s great use of a filter, on the home page of the financials, I also employed a date filter that drives the trailing-12 month P&L.
If you want to include these views in your reporting, Rich and Sau (and Stephen too) are experts. Don’t be afraid to ping them with questions here in the forum.
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.
*
Regards,
SAU
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.
Wow – this is fantastic. Well done!