Final Friday Fix: November 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 extend last month’s 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). Indeed, we are going to complicate last month’s challenge by adding another dimension: fees.

With management impressed by you solving last month’s problem, imagine that you continue to work in an education establishment, seeking to model forecast fees for the calendar year 2021. There are several terms relevant to your modelling period, similar to our last challenge, but now with fees data collated too:

All we need to do is allocate the fees (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 fees will be allocated to each month? You may rely upon last month’s solution, and 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!