Arrays to Calculate Consistent Increases in Rate Spreads
Every year at work we go through a planning cycle for the following year. During that time we build an annual and five year plan. After those plans are approved and submitted, we revisit the annual plan and spread those results across each month of the year. We then do a second submission of monthly results.
In going through that process, I've struggled to explain why the following calculation doesn't work . . .
In the example below we're given the total planned 2010 volume (in the yellow cell) that we need to spread. The spread needs to be based on a consistent or even growth in the rate per work day. We have history for volume in 2009 and workdays for 2009 and 2010 as well. Below is a screenshot of what we know.
On the surface this seems to be a simple algebra problem. Knowing the total planned volume for 2010 and having 2009 as a base, the spread should be an easy calculation using the 6-month average volume per workday.
Using this logic, the calculation above now looks like this:
In the range J12:J14, a rate per workday has been calculated for both years. The change in the rate is applied to each month (range D12:I14). Finally, the 2010 rate per workday (range D13:I13) is multipled by the workdays (range D9:I9) to get the volume for each month (range D5:I5).
All of this seems to make sense until you sum the range D5:I5. Instead of getting a total volume of 85,265, the actual total volume is 83,345. The total volume is overstated by 80 units, or 0.09%.
To correct this error, instead of using the increase in the total volume per workday and then applying that increase to each month, an array formula can be used. Referencing the screenshot below:
the following array formula is entered into the green shaded range D13:I13 (using CNTL - SHIFT - ENTER at the same time to enter the array formula):
{=J5 / SUM((D4:I4)/(D8:I8)*(D9:I9)) * (D12:I12)}
Now when you sum the range D5:I5 the total volume ties to 85,265. However, the monthly change in the rate is now 4.77% which does not agree with the 6-month average change of 4.87%. The difference is due to how the array formula evaluates each individual month and then totals the results.
