ElyxAI

Sales Forecast Template in Excel

A sales forecast template in Excel is a layout you build: actuals in one table, assumptions on their own sheet, formulas apart from both, and a short output. For a stable run rate, =AVERAGE(B2:B4) projects the next month from the last three, and a trend uses =FORECAST.LINEAR(A14,B2:B13,A2:A13).

ThomasCoget
15 min
Non classé
Sales Forecast Template in Excel

The workbook layout

A sales forecast template in Excel is four areas, not a file you download. Keep actuals in one table, assumptions on their own sheet, formulas in a calculation sheet, and a short output for the review. Do not type projections into the actuals table.

Actuals need one row per period, with the date, the amount, and whatever you slice by, such as product, region, or owner. Pipeline, if you use it, is a second table: opportunity, amount, close date, and probability stored as 40% or 0.4, not as the whole number 40 next to the amounts.

Run rate and a moving average

When recent months are stable, the next month is the average of the last ones. If B2:B4 are the last three months, =AVERAGE(B2:B4) is the run rate.

A moving average uses the same formula and slides forward. In the row for the next month, average the three months before it, then copy the formula down as new actuals arrive. It smooths a noisy month. It misses a seasonal spike.

A straight trend

When sales trend up or down, use FORECAST.LINEAR. If A2:A13 are period numbers 1 to 12 and B2:B13 are sales, the next period is =FORECAST.LINEAR(A14,B2:B13,A2:A13), with 13 in A14. The first argument is the period you want. The second is the sales. The third is the period numbers.

Weighted pipeline

For open deals, weighted value is amount times probability. If C2 is the amount and D2 is the probability (40% or 0.4, not 40), =C2*D2 is the weighted amount. =SUM(E2:E50) is the pipeline total. A probability stored as 40 needs /100, or the forecast is 100 times too large.

A simple driver model

When you do not have a long history, build the month from drivers. Leads in B2, close rate in C2, average deal in D2: =B2*C2*D2. Put the rate and the average on the assumptions sheet so a change is visible in the review.

Direct answers

How do I make a sales forecast template in Excel?

Use four areas: a table of actual sales, an assumptions sheet, a calculation sheet, and a one-page output. Do not type forecasts into the actuals table.

What formula forecasts next month from recent sales?

=AVERAGE(B2:B4) when B2:B4 are the last three months. Copy a moving window down the column as new actuals arrive. It smooths one odd month and misses a seasonal spike.

How do I forecast a trend with FORECAST.LINEAR?

Put period numbers in A2:A13 and sales in B2:B13. The next period, with 13 in A14, is =FORECAST.LINEAR(A14,B2:B13,A2:A13). The first argument is the period you want.

How do I weight a sales pipeline?

Weighted value is amount times probability. If D2 is 40% or 0.4, =C2*D2 is the weighted amount. A probability stored as 40 needs to be divided by 100, or the total is 100 times too large.

Ask Elyx in Excel: "Build next month’s forecast from the actuals in column B." Elyx does it in the workbook. 7-day trial, no card.

Start the 7-day trial