Table of Contents
Naive forecasting in Excel is a simple forecasting technique that uses the most recent data point as the forecast for all future time periods. It involves using the last observed value as the forecast value for the next period. To use this method in Excel, the user should enter a formula into a cell to calculate the forecasted value for the next time period. This formula can be repeated for each future period to create a forecast. Naive forecasting is a simple and easy method to use for forecasting future values, but it does not take into account any trend or seasonality changes that may occur.
A naive forecast is one in which the forecast for a given period is simply equal to the value observed in the previous period.
For example, suppose we have the following sales of a given product during the first three months of the year:
The forecast for sales in April would simply be equal to the actual sales from the previous month of March:
Although this method is simple, it tends to work surprisingly well in practice.
This tutorial provides a step-by-step example of how to perform naive forecasting in Excel.
Step 1: Enter the Data
First, we’ll enter the sales data for a 12-month period at some imaginary company:
Step 2: Create the Forecasts
Next, we’ll use the following formulas to create naive forecasts for each month:
Step 3: Measure the Accuracy of the Forecasts
Lastly, we need to measure the accuracy of the forecasts. Two common metrics used to measure accuracy include:
- Mean absolute percentage error
- Mean Absolute Deviation
The following image shows how to calculate mean absolute percentage error:
The mean absolute percentage error turns out to be 9.9%.
And the following image shows how to calculate mean absolute deviation:
The mean absolute deviation turns out to be 3.45.
To know if this forecast is useful, we can compare it to other forecasting models and see if the accuracy measurements are better or worse.