Quarterly Revenue Tracker that rolls over YoY

Rick Girard

I've created a revenue tracker for 4 quarters but the date ranges are fixed and last timeframe has passed it will need to be updated. How can i add a function so revenue can continue to be tracked without my intervention?

Here is a snap of my formula which actually covers the last 2 Q's of 2021 and first 2 Q's of 2022. Once we're past 2Q-2022 I have to revise the formulas as i have it now:

thank you!

Rick Girard

FYI: I am only showing the formula for Q3 2021, the others are just incremental Date shifts to capture the range within those Quarters.

Adrian Mandile CHESS

Hi Rick,

This is a bit of a tricky one – because your column names are also fixed (i.e. "Q3 2021", "Q4 2021", etc.) and this is an even bigger issue than the formulas – from an automatic maintenance perspective – as next you will need to add new columns for "Q3 2022", "Q4 2022", and so on...

When do you need to 'roll forward' – is it each quarter, or only once per year?

Assuming you need a rolling four-quarter look ahead view, you could possibly rename the columns more generically to suit: e.g. "Current Qtr", "Qtr+1", "Qtr+2", "Qtr+3".

Use the MONTH(TODAY()) function in generic formulas for the four columns to identify which quarter is the 'current' one, and subsequently which is Q+1, Q+2, etc. Then parse the Revenue dollar values similar to what you are already doing based on the Recognition Date.

For example: if TODAY() is 14/06/2021, calculate the 'current quarter number' using:

=IF(MONTH(TODAY() > 9, 4, IF(MONTH(TODAY() > 6, 3, IF(MONTH(TODAY() > 3, 2, 1)))

=2 (so, the next three quarters numbers should be: 3, 4, 1)

Calculate the rolling quarter numbers for each of the four columns respectively:

Column "Current Qtr" =MOD('Current Qtr number' -1, 4) +1 ... equals 2

Column "Qtr+1" =MOD('Current Qtr number', 4) +1 ... equals 3

Column "Qtr+2" =MOD('Current Qtr number' +1, 4) +1 ... equals 4

Column "Qtr+3" =MOD('Current Qtr number' +2, 4) +1 ... equals 1

Then, match the quarter number of the Recognition Date to the quarter number of the column to parse the Revenue dollars into the correct columns.

To label the generic columns meaningfully, use a spare 'helper' row at the very top of the Sheet to calculate a Qtr/Year display for each column, based on that column's quarter number, as follows:

="Q" + 'column quarter number' + CHAR(160) + if('column quarter number' < 'current quarter number', YEAR(TODAY()) +1, YEAR(TODAY()))

Note: CHAR(160) is a non-breaking space, or you could just use " " for a regular space

Bit long, but hope it helps :-)

Rick Girard

I'm going to have to map that out , not sure why the MOD function is needed vs a SUM function to get the output (I may be missing something). I'll put together your suggestion and let you know what the output is. thanks !