When it comes to forecast and previsional, one very basic approach which can do the trick is to use the CAGR, Compound Annual Growth Rate. CAGR is an important concept when it comes to make a sales forecast.
The CAGR formula on Excel
CAGR fomula on Excel is pretty straightforward to compute. Imagine that you have the following table and want to understand what has been the average growth rate observed between all these years:
| Period | Year | Value |
| 0 | 2015 | 100 |
| 1 | 2016 | 150 |
| 2 | 2017 | 210 |
| 3 | 2018 | 360 |
| 4 | 2019 | 380 |
| 5 | 2020 | 399 |
You have 6 values and 5 periods in the example above with as you can see different year over year growth rate.
To get the CAGR between 2015 and 2020, enter the following formula in Excel:
= (399/100)^(1/5) - 1 = 31.8% on average.
The theoretical formula to use on Excel is : = (end value / start value)^(1 / number of period) - 1
Why using the CAGR?
Using the Compound annual growth rate is good in the following use cases:
You need to understand the long trend growth rate. When you have multiple period and year to cover with different year over year or month over month growth rates, you might want just to assess the long term growth rate. What has been the average growth rate observed between the first period and the very last one. Using CAGR can help understanding the underlying trend.
You don't have much other information to build a forecast. Sometimes, you need to build quickly a forecast, predict what the sales activity will be. A quick back of the envelope calculation. You might not have a thousands of data points at your disposal to start forecasting. Using the CAGR is then a good way to perform a quick forecast.
The variation between the years are not huge, meaning the plotted growth curve is pretty linear - hence using the CAGR is fairly accurate. If one year, the growth observed is +30%, then you have +4%, then +16% and -8%, using a CAGR might not be the best approach. The volatility observed between two growth periods is too high for the CAGR to be a good predictive tool. Having such variations in the growth rate means that either the dataset you're trying to forecast is too small to get a pattern emerged or that there are a few of very business activity drivers behind your performance, which are hard by essence to predict.
You don't have much time to invest to deepen the forecast analysis and the CAGR will just be fine for your to use something quickly.
You don't need to use a statistical approach to buid a forecast. Sometimes, you don't need to use a bazooka to hunt bees. A simple reliable method will be ok.