All Resources

The Quagmire Challenge: Convert Quarterly Forecasts to Monthly Forecasts (February 2017)

The Question

Create a single formula that can perform the quarterly to monthly transformation so that it simply needs to be copied to the right for any number of years in the forecast.

Figure 1 – Quarter to Monthly Example

The Solution

Since this is Excel – there is always more than one way to solve a problem, but each of the winners had the following elements in their solutions:

  • Simple: Minimal work/formula length, no matrix formulas, and no VBA code.
  • Flexible: Formulas were built anticipating that someone would add columns in the future on either side of the data. Good modelers always anticipate that future users will always make unexpected changes to the worksheet.
  • Expandable: The formula was built so that it would work indefinitely as it was copied to the right as new quarters were added.

You will recall we asked for a formula that took quarterly figures and translated them into monthly figures. The idea was to have a single formula that could be copied indefinitely. Again, here is the layout of the problem:

The formula can be thought of as having three functioning parts:

  1. Retrieve the quarterly revenue number. The formulas we received used INDEX, OFFSET, HLOOKUP, and INDIRECT successfully. OFFSET has the advantage of having no set lookup range that needs to grow for additional quarters but we used INDEX in our sample solution and only semi-locked the range so that it grew automatically as the formula was copied.
  2. Track the number of months measured. The challenge here is that fixed ranges and fixed adjustments to formulas (e.g. +1) can read the wrong number if columns are added or deleted to the left of the data (thereby throwing off the interval being counted). The most robust solutions used COUNT, COUNTA (if counting the months), COLUMN and COLUMNS to keep track of how far the formula had been copied in a way that was unaffected by the addition or deletion of columns.
  3. Match each month to the correct quarter. The lookup function must only select the next quarter every three monthly periods which means the count of the month must be modified as a step function that changes months 1, 2, and 3 to a 1, months 4, 5, and 6 to a 2. Dividing the current period by 3 (since three months per quarter) and the use of any of the following functions will work to calculate the correct quarter. Notice that ROUNDUP and CEILING match the quarter without any needed adjustments.

Using the INDEX, ROUNDUP and COLUMNS functions, here is one example formula that will work in all cells:

=INDEX($B$3:B3,1,ROUNDUP((COLUMNS($B$9:B9))/3,0))/3