How to Do Naive Forecast in Excel

Building a complex statistical model to predict the future can feel overwhelming, but what if your first step could be as simple as looking at your last month's numbers? That's the core idea behind a naive forecast, one of the most straightforward yet surprisingly useful methods for predicting future performance. This article will show you exactly how to calculate and visualize a naive forecast in Excel, providing a solid baseline for all your prediction efforts.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is a Naive Forecast?

A naive forecast is the simplest time-series forecasting technique. The rule is incredibly basic: the forecast for next month is simply the actual value from this month. For example, if you had 10,000 website visitors in January, the naive forecast for February would be 10,000 visitors. If February’s actual number ends up being 11,500, your naive forecast for March then becomes 11,500.

It gets its name because it’s "naive" - it assumes nothing about trends, seasonality, or any other underlying factors that might influence your data. The only thing that matters is the most recent data point.

While it may sound overly simple, this method is powerful for two key reasons:

• It’s fast. You can calculate it in seconds without any special statistical software or knowledge.
• It's a fantastic benchmark. Any complex, sophisticated forecasting model you build should be able to outperform this simple one. If your fancy new model can't beat "just use last month's value," you know there's a problem with the model, not the naive approach.

When a Naive Forecast Works Best

The naive method is surprisingly effective for data that is relatively stable and doesn't exhibit a strong trend or clear seasonality. This behavior is often called a "random walk," where the value just meanders without a predictable pattern.

Use a naive forecast when you need:

• A quick and easy estimate with minimal effort.
• A baseline to compare more advanced forecasting models against.
• Predictions for the very near term (e.g., just one period ahead) where drastic changes are unlikely.

When to Avoid It

This method breaks down when your data has obvious patterns. You should avoid a naive forecast if your data shows:

• A strong trend: If your sales are consistently growing by 10% each month, blindly forecasting last month's value will always be too low.
• Clear seasonality: If you run an e-commerce store that peaks every December, forecasting January sales based on December's peak will give you a wildly inflated and inaccurate number.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Setting Up Your Data for Forecasting in Excel

Before you can forecast, your data needs to be organized properly. The best way to set this up in Excel is with a simple two-column table:

1. Column A: Your time period (e.g., Date, Month, Quarter).
2. Column B: The metric you want to forecast (e.g., Sales, Users, Revenue).

It's vital that your data is sorted in chronological order, from oldest to newest. Otherwise, your forecast will be referring to the wrong periods. Here’s a sample of what your sales data might look like:

Step-by-Step: Creating a Basic Naive Forecast

Let's use the sample sales data to build our forecast. The process is simple and only takes a few clicks.

Step 1: Add a "Forecast" Column

In the column next to your actual data (Column C), create a header called "Naive Forecast". This is where your predictions will live.

Step 2: Enter the First Forecast Formula

Your first forecast always corresponds to the second period of your data. This is because you need the first period's actual value to make a prediction.

So, in cell C3 (next to Feb-23’s sales data), you’ll enter a formula that references the cell containing Jan-23’s actual sales (B2):

=B2

When you press Enter, cell C3 should now show 1,200, which was the value from B2.

Step 3: Drag the Formula Down

You don't need to type the formula for every row. Click on cell C3, and you'll see a small square in the bottom-right corner. This is called the "fill handle." Double-click it or click and drag it down to the last row of your actual data.

Excel will automatically adjust the formula for each row. Cell C4 will become =B3, cell C5 will become =B4, and so on. Your table now shows the forecast for each following month.

Step 4: Forecast Into the Future

The real goal is to predict what happens next. Simply extend your forecast by dragging the fill handle down one more row into the future. For example, if your last data point is in December, drag the formula down to the January row. This cell will reference the actual value from December, giving you your one-step-ahead forecast.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Advanced: How to Create a Seasonal Naive Forecast

What if your business has strong seasonality? For an e-commerce gift shop, comparing January's traffic to December's peak is useless. Instead, you need to compare this January to last January.

This is where the seasonal naive forecast comes in. The rule here is: the forecast for any period is the actual value from the same period in the previous cycle.

• For monthly seasonality, the cycle is 12 periods. The forecast for March 2024 is the actual from March 2023.
• For weekly seasonality in daily data, the cycle is 7 periods. The forecast for Tuesday is the actual from last Tuesday.

Let's build a seasonal naive forecast for monthly data covering two years.

Step 1: Set Up Multiple Years of Data

To use this method, you need at least one full cycle of historical data. For monthly patterns, that means at least 13 months of data to start forecasting.

Step 2: Enter the Seasonal Formula

Let's say your data starts in cell B2 (January 2022). The first period you can forecast is January 2023, which is 12 rows down in B14. Your forecast for Jan 2023 (in cell C14) should reference the actual sales from Jan 2022 (cell B2):

=B2

Once you've entered the formula, use the fill handle to drag it down for the rest of your data. The formula in C15 will automatically become =B3, forecasting Feb 2023 based on the actual from Feb 2022. This simple shift captures the seasonal pattern without any complex math.

Visualize Your Forecast with an Excel Chart

Numbers in a table are useful, but a visual chart makes it much easier to see how your forecast performs against reality. Creating one is simple.

1. Select your data: Highlight all three columns, including the headers (e.g., Month, Units Sold, and Naive Forecast).
2. Insert a Line Chart: Go to the Insert tab on the Ribbon, find the Charts section, and select the first Line chart option.

Excel will generate a chart showing your actuals and forecast plotted over time. You’ll immediately notice that the forecast line perfectly mirrors the actuals line but is shifted one period to the right. This visual powerfully communicates how the naive method works - it's always chasing last period's truth.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Measuring Forecast Accuracy

A forecast is only useful if you know how good (or bad) it is. You can measure this with a simple error metric called Mean Absolute Error (MAE), which tells you the average error of your forecast in either direction.

Step 1: Calculate the Error

In a new column (Column D), calculate the difference between the actual value and your forecast for each period. Label the header "Error". The formula for D3 would be:

=B3-C3

Step 2: Find the Absolute Error

Negative errors can cancel out positive ones, giving a misleading impression of accuracy. To fix this, create another column (Column E) called "Absolute Error" and use the ABS() function to turn every error value into a positive number:

=ABS(D3)

Drag both formulas down for all rows where you have a forecast.

Step 3: Calculate the Mean Absolute Error (MAE)

Finally, in an empty cell, use the AVERAGE() function to find the average of the "Absolute Error" column. This is your MAE:

=AVERAGE(E3:E13)

If your MAE is 85, it means that, on average, your naive forecast was off by 85 units each month. This one number now becomes the benchmark to beat. If your next, more complex forecast model has an MAE of 50, you know you're making progress.

Final Thoughts

Mastering the naive forecast in Excel gives you a quick, reliable starting point for any predictive task. It establishes an essential performance benchmark, ensuring that any time you invest in more complex methods actually yields better, more accurate results.

While Excel is fantastic for this kind of focused analysis, the real-world challenge is often getting all your data - from Google Analytics, Shopify, Facebook Ads, and your CRM - into one place to begin with. We built Graphed to solve exactly this problem. By connecting your sources one time, you can ask plain-English questions like, "Create a dashboard showing my Shopify revenue vs. Facebook ad spend," and get a live, updating report in seconds, freeing you from manual data exports and spreadsheet hassles for good.