Monday Morning Mulling: August 2020 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Welcome to the 50^th Monday Morning Mulling. On this special occasion, it seems timely to have a time series challenge.

The Challenge

When building a financial model, time series are important. Most people know how to build a time series when each column represents a year, a quarter, a month, etc. (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 in hours, say, like the one below? This was our Friday challenge.

The requirement was that when you change the inputs, the time series automatically updates:

To aid understanding of our suggested solution, please feel free to refer to the associated Excel file.

Suggested Solution

First, we will prepare our ‘Daily Time Series’ sheet. In cell J6, we use the TODAY function to get today’s date, then format it as a text ‘Today’ and give it the range name Today in the Name Box. We will also need a few inputs, being the number of days in the time series, the number of blocks of hours per day and the number of hours per day (which should be a constant, rather than a variable, assuming you are a resident of Earth). These input cells are given a corresponding name in the Name Box, viz.

Having all our inputs ready, we will calculate the Hour series by using the MOD and >SEQUENCE functions.

The MOD function, MOD(number, divisor), returns the remainder after the number (first argument) is divided by the divisor (second argument).  The result has the same sign as the divisor.  Here, the MOD function is used to set up the hour block number e.g. 0 / 6 / 12 / 18, if we are breaking the day into quarters.

The SEQUENCE function, SEQUENCE(rows, [columns], [start], [step]), allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.  For example, SEQUENCE(4) will generate the numbers 1, 2, 3 and 4 down a column, whereas SEQUENCE(1,4) will generate the same sequence across a row – which is what we want.  In this case, we require a time series which automatically propagates across a row with a width of Number_of_days*Blocks_per_day, i.e. 4*3 = 12 columns, in our example above.  We will nest this SEQUENCE formula inside the MOD formula to get the width of the series:

In the first part of the formula, we want to get the block separations in a day unit, hence, we need to divide the numerator by Blocks_per_day:

The series is now not starting from zero but ending at zero, while we want it to count from zero.  The trick here is we will subtract one (1) in the numerator part to bring it back to the normal zero starting series.  If we have a series that starts from one (1), e.g. counting the month number in a quarter, just add one (1) after the final close bracket.

We need to multiply the series with the number of hours per day to get the correct hour block:

With the Hour series ready, we can calculate the Date series.  The Date will be filled only at the zero-hour marks.  Similarly, we use the SEQUENCE function to work out the actual date series; and we use the TEXT function to keep the correct date formatting:

It’s true this solution requires dynamic arrays to work, but as we hit our 50^th challenge, we thought we should look to the future of Excel rather than remain in the past.

Until next month!

The Final Friday Fix will return on Friday 25 September with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.