A sales forecast is an estimate of how much you will sell in the coming weeks, months or quarters. It helps you plan stock, staff, cash and targets. Excel is a good tool for it, because it can apply simple, well-known methods to your past sales in a few formulas. This guide shows three methods: a fixed growth rate, a moving average and a straight-line trend with FORECAST.LINEAR. For each one you will see the formula, a worked example with numbers you can check by hand, and the strengths and weaknesses of the method. It also shows how to compare the methods, measure the error and draw a chart. A forecast is never exact, but a simple, clear method is far better than a guess.
Updated November 2026
In this guide
- Start with clean data
- Example data
- Method 1: fixed growth rate
- Method 2: moving average
- Method 3: FORECAST.LINEAR
- Compare the three methods
- Measure the forecast error
- Use FORECAST.ETS for seasonal data
- Create a chart of the forecast
- Turn the forecast into a plan
- Best, likely and worst case
- Common mistakes
- Practice problems
- Quick recap
Start with clean data
A forecast is only as good as the history behind it. Put the dates in column A and the sales in column B, one row per month, with no gaps. Use real dates, such as the first day of each month, and make sure the sales are numbers. Remove or explain unusual months, such as a one-off large order or a closure. Try to have at least 12 months of data, and ideally 24, so that a seasonal pattern can show up. Our remove duplicates guide helps to clean a list before you start.
Example data
Use six months of sales: January 100, February 110, March 120, April 130, May 140 and June 150, all in units. The sales rise by 10 units every month, so the pattern is easy to see and the results are easy to check. The total is 750 units and the average is 125 units. We will forecast July, August and September with each method and compare the answers. A simple pattern like this makes it clear how each method works.
Method 1: fixed growth rate
This method assumes that sales grow by the same percentage each month. First find the growth rate from the past. Between January and June, sales grew from 100 to 150 over 5 months. The monthly rate is =(150/100)^(1/5)-1, which is about 8.45 percent. The forecast for July is 150 times 1.0845, which is about 162.7. August is 162.7 times 1.0845, about 176.4, and September is about 191.3. This method works well for a business that is growing at a steady percentage, such as a new product, but it can overstate the future if growth is slowing.
Method 2: moving average
A moving average forecasts the next month as the average of the last few months. With a three-month window, the forecast for July is the average of April, May and June, which is 130 plus 140 plus 150 divided by 3, which equals 140. In Excel, use =AVERAGE(B5:B7) for rows 5 to 7 and fill it down. This method smooths random ups and downs, and it is good for stable sales without a trend. When the sales are rising, as in our example, the moving average lags behind, so 140 is lower than the latest month of 150. See our average calculator for a quick check.
Method 3: FORECAST.LINEAR
FORECAST.LINEAR fits a straight line through the past data and extends it. The syntax is =FORECAST.LINEAR(x, known_y's, known_x's). Put the dates in A2:A7 and the sales in B2:B7. For July, use =FORECAST.LINEAR(DATE(2027,7,1),$B$2:$B$7,$A$2:$A$7). Because our sales rise by exactly 10 units per month, the line fits perfectly and the forecast is 160 for July, 170 for August and 180 for September. For data that does not have a perfect line, the formula finds the line that best fits. The older function FORECAST gives the same answer.
Compare the three methods
For July, the growth rate gives 162.7, the moving average gives 140 and the straight line gives 160. For our data, which rises by a constant 10 units, the straight line is the most natural, because sales grow by a fixed amount, not a fixed percentage. The growth method is a little high, because a 10-unit rise is a smaller percentage each month. The moving average is the lowest, because it ignores the trend. Choose the method that matches how your sales behave: steady amount, steady percentage or no trend.
Measure the forecast error
To test a method, hide the last few months, forecast them and compare. Use the first five months to forecast June. The straight line gives 150, the actual figure, so the error is 0. The three-month moving average of February to April, which is 110, 120 and 130, gives 120 for May, and the actual figure is 140, so the error is 20, or about 14 percent. A simple measure is the absolute percentage error, =ABS(actual-forecast)/actual. The average of these values over several months is called MAPE, and a smaller value means a better method.
Use FORECAST.ETS for seasonal data
If your sales have a seasonal pattern, such as higher sales every December, the straight line is not enough. Excel has FORECAST.ETS, which looks for a repeating pattern in the data. The syntax is =FORECAST.ETS(target_date, values, timeline). It needs a regular timeline, such as one row per month, and works best with at least two full cycles of data, so 24 months for a yearly pattern. You can also use Data, Forecast Sheet, which builds a chart and a table of forecasts with a confidence range in a few clicks.
Create a chart of the forecast
Select the dates and the sales, including the forecast months, and insert a line chart. Use a different color or a dashed line for the forecast part, so readers can tell it from the actual sales. To do that, keep the actual sales in one column and the forecast in another, and plot both series. Add a clear title, such as Monthly sales and forecast, and label the axes. A chart makes it easy to see whether the forecast follows the trend. Our pie chart guide explains chart basics, although a line chart suits trends better.
Turn the forecast into a plan
Once you have a forecast, use it. Multiply the units by the price to get a revenue forecast. If the price is 20, the straight-line forecast for July is 160 times 20, which is 3,200. Use the forecast to set targets, order stock and plan cash. Review it every month: add the actual result, compare it with the forecast and adjust the method if the error is large. Keep a note of the assumptions, such as a planned price change or a promotion, so that you can explain the numbers later.
Best, likely and worst case
One number can give a false sense of certainty, so build three cases. The likely case uses your chosen method, for example 160 units for July. The best case adds a margin, such as 10 percent, which gives 176. The worst case subtracts the same margin, which gives 144. Put the three values in a small table and share all three with the people who plan stock and cash. If the worst case still covers your costs, the plan is safe. If it does not, you know early that you need a backup, such as a promotion or a cost cut.
Common mistakes
The most common mistake is to trust one method without checking it. Another is to forecast too far ahead, since the error grows with the horizon. A third is to ignore seasonality. A fourth is to include one-off events in the history, which distorts the trend. A fifth is to use text dates, so FORECAST.LINEAR returns an error. A sixth is to treat the forecast as a promise instead of an estimate. Show a range, such as a low, a likely and a high case, so that decision makers can see the uncertainty.
Practice problems
Try these. Problem 1: sales were 200, 220 and 240 in three months, so the three-month average is 220. Problem 2: if sales grew from 100 to 121 over two months at a steady rate, the monthly rate is 10 percent, since 100 times 1.1 times 1.1 equals 121. Problem 3: use FORECAST.LINEAR for the data in this guide and confirm that August is 170. Problem 4: multiply the August forecast by a price of 20 to get a revenue of 3,400. Problem 5: build a chart of actual and forecast sales.
Quick recap
Clean the history and put the dates and the sales in two columns. Use a growth rate when sales rise by a steady percentage, a moving average when sales are flat, and FORECAST.LINEAR when they rise by a steady amount. Use FORECAST.ETS or the Forecast Sheet for seasonal data. Test each method on past months, measure the error and choose the one with the smallest error. Show the forecast in a chart, convert it to revenue, and review it every month against the actual results.
Frequently asked questions
How do I make a sales forecast in Excel?
Put dates and sales in two columns and use FORECAST.LINEAR, a growth rate or a moving average.
What is FORECAST.LINEAR?
It predicts a value on a straight line fitted through your past data.
How many months of data do I need?
At least 12, and ideally 24 if sales are seasonal.
What is the difference between FORECAST and FORECAST.LINEAR?
They give the same result; FORECAST.LINEAR is the newer name.
How do I measure forecast accuracy?
Compare the forecast with the actual figures using the absolute percentage error.


