“If you can’t describe what you are doing as a process,
you don’t know what you’re doing”
W. Edwards Deming

Timeline Conversion – Flows: Quarterly to Annual

Working with more than one timeline in a model is common. Modellers need to be able to take series data calculated in a high resolution timeline (e.g. quarterly) and aggregate it in a low resolution timeline (e.g. annual).

In this modelling guide, we will explain how to aggregate quarterly series data in an annual model using SUMIF and SUMPRODUCT.

Comments

  1. Hi Col,

    These are all excellent questions. Please see our response to each one below:

    Q: I also think importing (as hard-coded values) the high-res (Qtly) flow values into the low-res (Yearly) time-series sheet.

    A: We agree with you that the hardcoded values representing flows should be a link. In our example, we have marked these hardcoded values with hard yellow shade to suggest that the line is a temporary placeholder for “Flow – quarterly” values. A placeholder means that it is a temporary line which will be built at a later stage, when we have sufficient data.

    Please note: The end user should always import the flow line which is to be converted from high resolution to low resolution timeline and hence, it should not be hardcoded.

    To know more about the placeholders, you can refer to our FMH – Using Placeholders by clicking on the link:

    http://www.financialmodellinghandbook.com/guides/using-placeholders/

    Q: I also think importing (as hard-coded values) the high-res (Qtly) flow values into the low-res (Yearly) time-series sheet and pasting them into the low-res time-series where there is no correlation between the high-res period to which the imported flow value relates and the period low-res period of the column in which that flow value now rests is confusing, dangerous, and unnecessary.

    A: We agree that we are bringing flows from high resolution to low resolution timeline for conversion purpose, when there is no correlation between these two timelines. That’s why in Slide 7, 3rd point in the guide clearly says that the imported data is alien to the underlying timeline of active worksheet and hence, to distinguish this imported data, we mark it in italics.

    The alternative approach could be to use “Off-sheet” reference, which is not transparent and are prone to errors, in case the linking in the high resolution timeline changes. Therefore, we generally avoid using inter-sheet linking.

    Q: Imagine if you have to model monthly flows for the first five years and then annual flows for the next 35? Following the proposed methodology would generate 420 extra columns/periods in the high-res time-series for no benefit whatsoever!

    A: A model may have dual timelines, in which case, one will be referred to as Primary timeline and the other as Secondary timeline. Secondary timeline is the one which supports primary timeline in the calculations. In this modelling guide, primary and secondary timelines are Annual and Quarterly respectively. Hence, Quarterly timeline (high resolution timeline) supports the Annual timeline (low resolution timeline) and flows are imported from the Quarterly timeline to feed into the Annual timeline. We advocate that both the primary and secondary timeline sheets should have equal number of columns.

    In this context, let’s look at your example regarding 5 years monthly and 35 years annual timeline. That means in high resolution timeline (monthly), there will be 5 * 12 = 60 columns, and in low resolution timeline (annual), there will be 40 * 1 = 40 columns (35 + 5 = 40).

    Therefore, there will be no extra columns required in monthly timeline, instead there will be 20 extra columns required in the Annual timeline. These 20 extra columns will not affect the model per se, but will definitely serve as a buffer to extend the model timeline in future, if need be.

    Q: Why not just calculate (via a simpler formula than used in the supporting model) the financial year (as “yyyy”) to which the high-res period (Qtrs) belongs (locate this calc. in the high-res time-series adjacent to the original period values), and then use a simple SUMIFS formula in the low-res sheet to return all target flow values matching the low-res FY from the high-res sheet to the low-res sheet, rather than mess around with calculating both high-res and low-res column index numbers, and importing hard-coded values which breaks the dynamic link!? The former is done with just two simple formulae (including one inter-sheet link) instead of five as does the latter! This eliminates the need for both time series to have the same number of columns – there’s no logical reason for this.

    A: Yes, you are correct in saying that SUMIFS/SUMIF function can be applied to solve the timeline conversion with the use of “Financial year ending” of both the timelines. This is a simpler approach when compared with the usage of counters to convert the timelines. We will consider your suggestion and soon revise the handbook to reflect the same.

    Does this answer your queries?

    • Col Delane says:

      Hi Rashim

      Thanks for the response.

      Whilst I accept that there may be some benefit (transparency?) from importing the flow from a source sheet to the destination sheet before using it in further calculations, in this case I think the misaligned timelines that result in the clutter of the additional columns left hanging off the edge of the low-res calculation block does not justify doing so. If you’re going to have an off-sheet link (impossible/impractical to create a decent financial model without inter-sheet links, and in any event, you will have one for your imported flow line anyway once the ‘placeholder’ is updated) then my method limits the number of such links to just one. I guess the outcome depends on your definition of “transparency” and the minimum level thereof required. I don’t see a SUMIFS function with the sum and criteria range on another sheet, and a single criteria, as a “black box” formula and I don’t believe off-sheet references are necessarily any more error prone than any other formula.

      An alternative that is somewhat the reverse of your proposal but simpler would be to create (using financial year values as I described rather than column counters, etc.) a low-res flow on the high-res calculation block (i.e. quarterly values rolled up to annual) and then simply import that flow onto the low-res timeline. This still has misaligned timelines (aagh!), but they’d be on the sheet that needs the most columns (i.e. high-res) so doesn’t create the clutter and distraction of having flow values hanging off the edge of the low-res calculation block.

  2. Col Delane, Perth, Western Australia says:

    I accept that I’ve only had a relatively quick look at the supporting model, but the conversion method proposed seems to me to be an awfully clunky and long-winded way of converting high-res time-series values to low-res.

    I also think importing (as hard-coded values) the high-res (Qtly) flow values into the low-res (Yearly) time-series sheet and pasting them into the low-res time-series where there is no correlation between the high-res period to which the imported flow value relates and the period low-res period of the column in which that flow value now rests is confusing, dangerous, and unnecessary. Imagine if you have to model monthly flows for the first five years and then annual flows for the next 35? Following the proposed methodology would generate 420 extra columns/periods in the high-res time-series for no benefit whatsoever!

    Why not just calculate (via a simpler formula than used in the supporting model) the financial year (as “yyyy”) to which the high-res period (Qtrs) belongs (locate this calc. in the high-res time-series adjacent to the original period values), and then use a simple SUMIFS formula in the low-res sheet to return all target flow values matching the low-res FY from the high-res sheet to the low-res sheet, rather than mess around with calculating both high-res and low-res column index numbers, and importing hard-coded values which breaks the dynamic link!? The former is done with just two simple formulae (including one inter-sheet link) instead of five as does the latter! This eliminates the need for both time series to have the same number of columns – there’s no logical reason for this.

Help make the handbook better