We can use the AVERAGE function to FIND THE MOVING AVERAGE OF A DATA. Calculation of moving averages is useful for financial forecasting. The steps below will walk through the process.
Figure 1- Using Moving Average for Forecasting Sales for Month 8 and 9
Syntax
=AVERAGE(RANGE)
- RANGE refers to the cell references that corresponds to the numbers in each cell
Formula
=AVERAGE(B4,C4)
Setting up the Data
With the data in figure 2, we will make a forecast of sales for the next two months. We will use the moving average for every 2 months to calculate the forecast for month 8 and 9.
- We will enter the Months in Row 3
- We will enter the Sales in Row 4
- Our result for moving average for every 2 months will be returned in Row 5
Figure 2 – Setting up the Data
Calculate the MOVING AVERAGE FOR EVERY 2 MONTHS
- We will click on Cell D5
- We will insert the formula below into Cell D5
=AVERAGE(B4,C4) - We will press the enter key
Figure 3- Moving Average for the First two months
The average value taken is that of Cell B4 and Cell C4. The next average value will be of Cell C4 and Cell D4 and so on.
- We will click on Cell D5 again
- We will double click on the fill handle tool which is the small plus sign you see at the bottom right of Cell D5. Select and drag the formula across the row to Cell J5 to copy the formula to the other cells.
Figure 4- Forecast for Month 8 and 9 by Moving Average
If we were to calculate the moving average for every 3 months with the same data, we will use the formula below:
=AVERAGE(B4:D4)
We will click on Cell E5 and insert the formula rather than D5 when working with 2 months sales or 2-month moving average.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment