Final Friday Fix: August 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!

Can you believe this is the 50^th Final Friday Fix Challenge? We have been made it this far! Given we have hit such a milestone, it seems appropriate to consider a time series challenge this month.

The Challenge

When building a financial model, time series are important, and most people know how to build them when each column represents a year, a month or any time period with at least a day in length (if you don’t, it is highly recommended that you take our Financial Modelling course). Here is such an example:

But what do you do when you want a time period that is less than a day, with dynamic blocks of hours, like the one below:

When you change the inputs, the time series automatically updates:

This month’s challenge is to create a formula that will propagate the illustrated Date and Hour rows, when the number of days and the number of blocks per day may be varied.

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