Build a Simple Sales Forecast in Excel

A sales forecast in Excel is an estimate of future sales built from past sales. The simplest version takes the average monthly growth and applies it to the latest month, and it needs only AVERAGE, ROUND and a percent change column. This guide is for sales staff, small business owners and analysts who need a quick, explainable forecast, and it shows three methods on the same data so you can compare them.

What you need before you forecast#

You need a clean history: one row per period, the period in one column and sales in the next. Monthly data is the most common. This guide uses six months:

Check the history before you use it. A missing month, a one-off bulk order or a month with a price change will pull every method off course. More history is better: six months is enough to practise, but two years lets you see seasonal patterns.

Method 1: Forecast with average growth#

This method assumes sales keep growing at their recent average rate. First, work out each month's growth with the percent change formula. In C3:

=(B3-B2)/B2

Fill it down to C7. February grew 5.0% on January, March 4.0% on February, and so on. The percent change formula guide explains why you divide by the earlier month.

Then forecast July in B8:

=ROUND(B7*(1+AVERAGE(C3:C7)),0)

• AVERAGE(C3:C7) is the average monthly growth: about 4.57%. See the AVERAGE function guide.
• 1+ turns growth into a multiplier: 1.0457.
• B7* applies it to June's 15,000.
• ROUND(...,0) gives a whole number. See the ROUND function guide.

The result is 15,685. For August, apply the same growth to July's forecast.

A small refinement: the plain average of growth rates is slightly different from the compound rate. =(B7/B2)^(1/5)-1 gives the compound monthly growth, about 4.56%, explained in the CAGR formula guide. On this data both give 15,685.

Method 2: Forecast with a moving average#

A moving average forecasts next month as the average of the last few months. For a 3-month version, in B8:

=ROUND(AVERAGE(B5:B7),0)

The result is 14,400, the average of April, May and June. That is lower than June itself, because a moving average always lags behind a rising trend. It works best when sales are flat but bounce around from month to month, because it smooths out the noise. The moving average guide shows how to build one down a whole column.

Method 3: Forecast a trend line with FORECAST.LINEAR#

FORECAST.LINEAR fits a straight line through past sales and reads off the next point. It needs numbers for the periods, so number the months 1 to 6 in column A (or add a helper column):

=FORECAST.LINEAR(7,B2:B7,A2:A7)

• 7 is the period to forecast (July).
• B2:B7 are the known sales (the y-values).
• A2:A7 are the known periods 1 to 6 (the x-values).

The result is about 15,573. The line adds roughly 597 a month, the average slope of the six months. FORECAST.LINEAR needs Excel 2016 or later; the older FORECAST function takes the same arguments and gives the same answer. TREND does the same job and can return several future months at once.

For seasonal data, Excel 2016 and later on Windows also has Data > Forecast Sheet, which builds a forecast table and chart with the FORECAST.ETS function. It needs a longer, regular history to find the pattern.

Keep assumptions in cells, not in formulas#

A forecast is only as good as its assumptions, so make them easy to see and change. Instead of typing 4.57% into every formula, put the growth rate in one cell, say F1, and point every month at it:

=ROUND(B7*(1+$F$1),0)

The $ signs lock F1 when you fill the formula along the months. Now you can type 3% or 6% in F1 and watch the whole forecast change. That is the start of a best case and worst case plan. The same layout drives a cash flow forecast, where sales are the first line.

How to forecast several months ahead#

To forecast more than one month, chain each month to the one before. With the average growth in F1 (type =AVERAGE(C3:C7) there), put this in B8 for July:

=ROUND(B7*(1+$F$1),0)

Fill it down to B13 for July to December. Each month grows from the previous forecast, so the relative B7 becomes B8, B9 and so on, while $F$1 stays fixed. July returns 15,685, and every later month is about 4.6% higher than the one before.

Keep the forecast months visually separate from actual results, for example with a different fill colour or a "Forecast" label in column C. When a real month arrives, type the actual figure over the forecast so the next months build on real data. The further ahead you forecast, the less certain the numbers are, so treat months five and six as a rough guide rather than a target.

How to check which forecast is best#

Test each method on months you already know. Forecast May from January to April, compare with the real 14,300, and do the same for June. The method with the smallest errors on your data is the one to trust.

Two simple error checks:

• Error: =Forecast-Actual. A positive number means you forecast too high.
• Percentage error: =ABS(Forecast-Actual)/Actual. Average it over several months for a single score.

Then plot actual and forecast sales on one line chart. A gap that keeps growing tells you the method is missing the trend. Update the forecast every month as real results arrive.

Practise it#

Every forecast here is built from three skills: percent change, averages and locked references. Practise percent change in a live spreadsheet, then practise AVERAGE, MIN and MAX and practise absolute references with $ signs. Each lesson checks your formula as you type. Then try the forecast exercise on this page.