Total Monthly Figures to Quarter and Year

5 posts / 0 new
Last post
Aaron Zhou X 2
AZ
Total Monthly Figures to Quarter and Year

I have the Modano spreadsheets built by month, from Jan 2019 to Dec 2024, 60 months. I want at the end of each quarter and year, have a column to show the total quarterly and Annual P&L. How do I do it?

Michael Hutchens A+ 189

Hi Aaron,

Like with Excel without Modano, there is no right or wrong way to do this, but here's my personal view...

When you want to include periodic summations within time series sheets - such as quarterly and annual totals in a P&L - I would leave your model calculation outputs alone and add this analysis in a new module (or add a module component to an existing module), locating it within a presentation outputs module component based on a newly-created period titles set which reflects your required summation periodicities.

The issue with trying to include periodic summations in your base P&L is that you'll either need to include inconsistent formulas for months, quarters, years, etc. - which is messy, risky and won't scale when you roll or extend your model time series.

I've demonstrated how to do this in the attached monthly historical & forecast model, in which I've:

  1. Created a new period titles set called Monthly All Periods Summations by duplicating the existing Monthly All Periods period titles set and then customizing the (grouped and collapsed) period titles detail rows to include flags to identify each period as a month, quarterly summation or annual summation);
  2. Inserted a new module named 'Income Statement Summations' with a presentation time series outputs module component based on the new Monthly All Periods Summations period titles set;
  3. Copied the content from the existing Annual Income Statement module;
  4. Customized the period allocations (in the grouped and collapsed rows 17 - 20 of the Income Statement Summations worksheet) to reflect each period potentially being either a month, quarterly summation or annual summation); then
  5. Added a periods counter to the bottom of the time series assumptions before hooking the Monthly All Periods Summations period titles set final period titles block to this cell, thereby ensuring that the number of periods on the Income Statement Summations sheet increases when the term of the model is increased.

I've made some simplifying assumptions for this example - e.g. sheets based on the Monthly All Periods Summation period titles set will always show a whole number of years and the first period on this sheet has assumed to be the first month of a year - but these assumptions could be relaxed to reflect different time series structures.

This is certainly not simple stuff - with or without using Modano - but hopefully the attached example demonstrates a best practice, scalable way of including multi-periodicity summations in the presentation outputs of a model without touching the core model assumptions and outputs.

M.

Aaron Zhou X 2
AZ

Hi Michael

Thank you so much for the response.

Regards

Aaron

Michael Hutchens A+ 189

It's a pleasure Aaron. Our primary challenge is inspiring our new users to they realise that pretty much anything is possible using Modano, it's just a matter of getting up the learning curve, which can be steep at first.

We're working hard to release a lot more content over the next 12 months, thereby making it easier and easier for our users to build a model closely aligned with their requirements.

The great thing is that it's all re-usable due to the modular and scalable nature of the platform, so we only need to do each thing once and we can leverage it forever!

M.

jessegraham98 X 0
CG

Thanks for sharing useful information. I appreciate you for your work and dedication.