Forecasting
Calculating an Average Annual Growth Rate in MetrixND
After finishing my forecast, I like to present forecast average annual growth rates. These results are calculated by converting the monthly forecast into annual values using a Transformation table with an annual periodicity. The formulas used in the annual Transformation table are shown below.
In these equations, the “Res” variable sums the monthly residential sales forecast into annual sales using the “SumAcross” function. The “Res_GrowthRate” variable calculates the year-to-year growth rates using the “PercentLag” function and the newly created “Res” variable. The results shown below are annual sales and growth rates.
While year-to-year numbers are valuable, most forecasters like to communicate average annual growth rates. In this case, what is the average annual growth rate for the forecast? To calculate this, I added two new variables in the annual Transformation table, “SumRange” and “AvgAnnualGrowthRate” as shown below.
The “SumRange” variable isolates the forecast period, which, in this example, begins in 2015. The variable uses the “IF-THEN-ELSE-ENDIF” function to remove any values which are not defined in the IF condition. In this case, all the values prior to 2015 are removed as shown below.
The “AvgAnnualGrowthRate” variable takes the “SumRange” results and obtains the average to present the average annual growth in the forecast period. The key component of this variable is the “GetDStat” function. This function obtains the variable average (mean) from the DStat tab. The IF condition is used to place the annual average growth rate in the year 2020 row.
Typically, the average annual growth rates are calculated in a spreadsheet. Forecasters copy and paste results into a spreadsheet where annual averages are easily calculated. However, using the two variables described above allows for the dynamic calculation of results, removing the need to copy and paste into the spreadsheet.
In these equations, the “Res” variable sums the monthly residential sales forecast into annual sales using the “SumAcross” function. The “Res_GrowthRate” variable calculates the year-to-year growth rates using the “PercentLag” function and the newly created “Res” variable. The results shown below are annual sales and growth rates.
While year-to-year numbers are valuable, most forecasters like to communicate average annual growth rates. In this case, what is the average annual growth rate for the forecast? To calculate this, I added two new variables in the annual Transformation table, “SumRange” and “AvgAnnualGrowthRate” as shown below.
The “SumRange” variable isolates the forecast period, which, in this example, begins in 2015. The variable uses the “IF-THEN-ELSE-ENDIF” function to remove any values which are not defined in the IF condition. In this case, all the values prior to 2015 are removed as shown below.
The “AvgAnnualGrowthRate” variable takes the “SumRange” results and obtains the average to present the average annual growth in the forecast period. The key component of this variable is the “GetDStat” function. This function obtains the variable average (mean) from the DStat tab. The IF condition is used to place the annual average growth rate in the year 2020 row.
Typically, the average annual growth rates are calculated in a spreadsheet. Forecasters copy and paste results into a spreadsheet where annual averages are easily calculated. However, using the two variables described above allows for the dynamic calculation of results, removing the need to copy and paste into the spreadsheet.