Table of Contents
Naive forecasting in Excel is a simple and straightforward method for predicting future values based on past data. The step-by-step process for implementing this method involves the following steps:
1. Collect and organize the historical data: The first step is to gather and arrange the data in a tabular format in Excel. This data should include the time period and corresponding values.
2. Create a line graph: Once the data is organized, create a line graph to visualize the trend and any potential patterns.
3. Calculate the average: Next, calculate the average of the historical data using the AVERAGE function in Excel.
4. Create a new column: In a new column, enter the average value for each time period.
5. Extend the trendline: Extend the trendline for the new column to predict future values.
6. Forecast the future values: Based on the extended trendline, input the predicted values for the future periods in a new column.
7. Compare with actual data: Finally, compare the predicted values with the actual values to evaluate the accuracy of the forecast.
This step-by-step process can be repeated for different time periods to make multiple predictions. Naive forecasting in Excel is a quick and easy way to generate basic forecasts, but it should be used with caution as it does not take into account any external factors or potential changes in the trend.
Naive Forecasting in Excel: Step-by-Step Example
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.