Final Friday Fix: October 2020 Challenge

On the final Friday of each month, we’re going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you’re playing for bragging rights only!

This month, we consider a common time series problem.

The Challenge

Most models are not constructed on a daily basis. Data and calculations are consolidated on a weekly, monthly, quarterly or annual basis. Sometimes this causes problems – such as in the following example.

One such issue is when data straddles two or more consolidation periods, and this is something we have looked at before (e.g. Revising Forecasts). It can get worse though: what if we have two “straddles” are in the same period, or some of the data relates to dates which are not to be included in the analysis? That’s what this month’s challenge is all about.

Imagine you work in an education establishment, seeking to model forecast data for the calendar year 2021. There are four terms relevant to your modelling period:

All we need to do is allocate the number of term days (including weekends and public holidays) to each month of 2021, i.e.

The challenge is “simply” this: can you construct a calculation such that the correct number of days will be allocated to each month? You may assume the terms will be in chronological order, they will not overlap, there will never be more than two terms associated with any given month, and there will never be two start dates or two end dates in the same month (these assumptions are why there are checks, although the later requirements, admittedly, is not tested in our file or Monday’s solution). We even supply a starter file for you!

Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!