A business will frequently want to know whether its Profits, Sales etc are growing or declining. This can be a difficult question to answer when results are variable and sometimes even a good chart doesn’t help. Additionally, it is useful to based a forecast on quantified understanding of the growth of the item that you are interested in.
In modelling terminology one way to quantify growth is using current trends. I’ll cover other methods in another article on another day. The rest on this blog focuses on calculating a trend, particularly where results are variable.
Moving Average
One of the most common ways to smooth out variable results is to use a moving average. For example if you have monthly results then the average will be typically based on the last 12 months i.e. if your latest result are for February 2017 then you take the average of Mar 16 to Feb 17 results (in January the average would have been based on Feb 16 to Jan 17). NB taking a full year has the advantage of not being subject to seasonal variations.
However, there is often a desire to place more emphasis on the more recent results. In this case a weighted moving average should be used. Commonly the months are weighted as follows:
To get the weighted average you take the following steps
- Multiply Sales for Mar-16 by 1, sales for Apr-16 by 2, May-16 by 3 through to Feb-17 by 12.
- Add the above together and divide by 78 (the sum of the numbers 1 to 12
A shorthand way of achieving the same result is to use the sum product formula. If your sales values are in cells C3:N3 and your weightings are in cells C6:N6 then using the sumproduct formula you would enter Sumproduct(C3:N3,$C$6:$N$6)/78. NB the reference to the weightings needs to be an absolute reference incl the $ signs so that you can copy the formula to subsequent months. An full example is shown below:
One thing to not3: if the data is extremely variable then I would normally create a moving average first and then weight the moving average using the Sumproduct() technique explained above. One slight drawback is that it requires nearly 2 years worth of data as the first weighted number would be the moving average for Mar-16 and to create this as a 12 month moving average would be Apr-15!
Growth
Both the Moving average and the weighted average (WAv) can be used to determine the current growth trend (which can then be used for forecast purposes. There are two options (i) straight line growth and (ii) exponential growth.
Straight line growth is calculated as the difference between the WAv for the current month and the WAv for 12 months ago. This is then divided by 12 (the number of months). The formula is written as =+(AD39-AD27)/(AB39-AB27) and the result is in units or £. A result of 1.25 means that sales grow by 1.25 units each month. Therefore, to forecast we would add 1.25 units each month starting from the latest moving average (or WAv).
Exponential growth is calculated as the WAv for the current month divided by the WAv 12 for months ago multiplied by the power 1/12 (as we have had 12 months of growth. The formula is written as ‘=(AD39/AD26)^(1/12)-1 and the result is expressed as a percentage. A result of 0.88% means that on average sales increase by 0.88% each month. Therefore, to forecast we would add 0.88% units each month starting from the latest moving average (or WAv).
An example:
Excel Monkey 
Leave a Reply