How to Create a Sales Forecast in Excel: Step-by-Step Guide with Free Template

Why Excel Sales Forecasting Requires System-Level Data Capture

Excel sales forecasts fail at the input stage, not the formula stage. FORECAST.ETS and FORECAST.LINEAR are only as reliable as the historical series behind them. In most sales organizations, someone assembles that series by hand — from ERP exports, CSVs, and a personal tracker — long before any forecasting function runs ². Excel’s own engineering tolerates this reality only up to a point: its algorithm interpolates missing values and still produces a forecast, but only if fewer than 30% of data points are absent ³. Push past that threshold — which happens routinely when reps update deals late or skip stages entirely — and the model has nothing reliable left to smooth.

This is why data teams recommend a full cleaning pass before every forecast cycle: removing blanks, standardizing date formats, and filling every gap in the timeline so no period goes silently missing ¹. That’s manual triage, repeated every cycle, on data that should have arrived clean in the first place.

Data capture method	What happens to forecast accuracy
Manual spreadsheet entry	Rep logs deals late or selectively; gaps exceed Excel’s 30% tolerance 3
End-of-week batch updates	Stage changes and closes lag reality by days
Event-driven capture (CRM/API)	Pipeline stage changes and closes log the moment they happen

As one CRM vendor puts it bluntly: "Excel templates are good, but manually entering data gets time-consuming and unreliable" as sales operations scale ⁴. A separate template maker reaches the same conclusion from the opposite direction: it tells small teams to keep using a spreadsheet — right up until the team outgrows manual input, at which point it explicitly recommends moving to a CRM ⁵. Neither source describes a training gap. Both describe a structural one: the spreadsheet has no mechanism to capture an event the moment it happens. The forecast always works from a stale, partial picture — no matter how well the analyst writes the formula.

Learn more in our complete guide: What is a Sales Operating System: the loop that transforms results.

Step-by-Step Guide to Creating a Sales Forecast in Excel Using FORECAST.ETS and the Forecast Sheet Tool

Excel’s Forecast Sheet is a built-in wizard, available since Excel 2016, that turns two columns of historical data — dates and sales — into a full forecast with a chart and confidence bands. It runs the FORECAST.ETS algorithm behind the scenes⁶. Set it up like this:

1. Organize your data in two columns: dates in column A, sales amounts in column B. Aim for at least two full seasonal cycles (24 monthly points, for example). This gives Excel enough history to detect a real pattern instead of defaulting to a straight line³.
2. Highlight both columns, then go to Data > Forecast Sheet. Excel plots your history in blue and the prediction in orange, with thinner lines marking the upper and lower confidence bounds⁶.
3. Set your forecast length and confidence interval. The default confidence interval is 95%, meaning Excel expects 95% of future values to land inside that band³.
4. Let Excel detect seasonality automatically, or enter a manual value if the pattern isn’t picked up on its own — 12 for monthly-yearly cycles, 4 for quarterly data³.

Click Create, and Excel inserts a new sheet with a FORECAST.ETS formula already populated in every forecast cell⁶.

How to Interpret and Apply the FORECAST Function (FORECAST.LINEAR) with a Practical Numerical Example

FORECAST.LINEAR is the Excel function that fits a straight best-fit line through your historical sales data and projects it forward. The syntax is =FORECAST.LINEAR(x, known_y's, known_x's), where x is the future period, known_y’s are past sales values, and known_x’s are past periods¹.

Take 12 months of sales ranging from $50,000 to $120,000 with a steady upward trend. Lock the sales range and the period range with F4 so the formula doesn’t shift as you drag it down. Then =FORECAST.LINEAR(13, B2:B13, A2:A13) projects month 13 at roughly $125,000 by extending that best-fit line⁷.

When linear forecasting fits — and when it doesn’t

FORECAST.LINEAR assumes a constant growth rate. It works well for mature pipelines with steady rep output and no cyclical spikes, but it fails the moment your revenue has seasonal peaks. Plotted against real seasonal data, a linear projection shows up as a flat, rising line, while the actual pattern has clear ups and downs. It misses every seasonal swing entirely⁷.

Situation	Right function
Stable, non-seasonal growth	FORECAST.LINEAR
Seasonal peaks and troughs	FORECAST.ETS

Once you have your month 13 projection, compare it against actual results to measure accuracy before trusting the model for budget or headcount decisions.

What Are Alternative Projection Methods: Moving Average, Linear Regression, and Percentage Trend?

Moving average, linear regression, and percentage trend are three Excel-native alternatives to FORECAST.ETS. Each suits a different demand pattern rather than serving as a universal replacement for it. Picking the wrong one on noisy or seasonal data is a common reason forecasts miss — not a sign your team lacks discipline.

Moving Average takes the average of the last N periods (3-month or 12-month) as the next forecast. It’s simple and transparent, and it works best when sales are volatile but show no clear trend². A weighted variant — assigning heavier weights like 3, 2, 1 to the most recent months — reacts faster to recent shifts than a flat average does².

Linear Regression uses =INTERCEPT(known_y's,known_x's) + SLOPE(known_y's,known_x's)*next_period. This gives you more granular control than FORECAST.LINEAR while solving the same trend-line problem⁷.

Percentage Trend assumes a fixed month-over-month growth rate, computed from historical months and projected forward. It’s fast to build for steady-growth pipelines, but risky the moment growth decelerates².

Method	Best for	Fails when
Moving Average	Volatile, trendless data	Strong seasonality exists
Linear Regression	Consistent up/down trend	Trend is cyclical
Percentage Trend	High-growth startups	Growth rate slows
FORECAST.ETS	Seasonal/cyclical demand	Fewer than 2 seasonal cycles of history7

None of these methods fixes the underlying issue: a spreadsheet forecast is only as reliable as the pipeline data feeding it. And that data usually depends on reps typing it in correctly.

How Do You Handle Seasonality and Demand Spikes When Building a Forecast in Excel?

Seasonality means sales rising and falling in a repeating pattern tied to the calendar — not to underlying growth or decline. You detect it by plotting at least 24 months of history and watching for the pattern to repeat at fixed intervals, such as a Q4 spike or an August dip⁶. Demand spikes from one-off promotions or large deals work differently: they’re anomalies, not cycles, and you should handle them separately.

1. Plot 24+ months of sales on a line chart and scan for repeating peaks and valleys at consistent intervals⁶.
2. Feed that range into FORECAST.ETS. It detects seasonality automatically as long as your data covers at least one full cycle, or you can set it manually (12 for monthly/annual cycles, 4 for quarterly)³.
3. For one-time spikes — a flash promotion, a single whale deal — exclude those months from the range or flag them as outliers before re-running the forecast. Unclean data undermines accuracy¹.
4. For a more advanced blend, calculate a seasonality index — average sales in month X divided by average sales overall — and multiply it against your linear trend line².

With less than two years of data, Excel won’t reliably separate a real cycle from a lucky quarter³.

Ready-to-Download Spreadsheet Template with Formulas Explained Cell by Cell

A ready-to-use sales forecast template Excel free download pairs raw historical sales with formula-driven columns that update automatically as you add new rows — no macros required. The structure below mirrors the reference syntax documented for Excel’s forecasting functions¹.

Column	Field	Purpose
A	Date	Chronological timeline, consistent intervals
B	Actual Sales	Historical units or revenue
C	Month #	Numeric index used as the target date/x-value
D	FORECAST.ETS	Seasonality-aware prediction
E	FORECAST.LINEAR	Straight-line trend prediction
F	3-Month Moving Avg	Smooths short-term noise
G	Forecast Error %	Accuracy check
H/I	Confidence Interval Upper/Lower	Range around the ETS forecast

Enter the key formulas in row 2 and copy them down, with the range locked by $:

1. D2: =FORECAST.ETS(C2,B$2:B$25,A$2:A$25,,1) — applies exponential triple smoothing to the historical range⁷.
2. E2: =FORECAST.LINEAR(C2,B$2:B$25,C$2:C$25) — a simple linear trend, useful as a baseline comparison against D⁶.
3. F2: =AVERAGE(B2:B4) — a 3-month moving average; extend the range if your team prefers a longer smoothing window².

The formulas are color-coded — blue for ETS, green for linear, orange for moving average — so anyone opening the file can trace which method produced which number without reading documentation³. Delete unused columns if your team only needs one method. The sheet still recalculates correctly because each formula references fixed, absolute ranges.

How Do You Validate Forecast Accuracy by Comparing Historical vs. Forecasted Data?

You validate forecast accuracy by comparing forecasted values against actual results month by month. Use Percentage Error (PE) and Mean Absolute Percentage Error (MAPE) as your two core metrics, then confirm the model with a backtest before you let it drive targets.

Step 1 — Calculate Percentage Error per month: PE = (Actual – Forecast) / Actual × 100. A PE in the ±10% range is generally acceptable for sales forecasting. In a worked Excel example, monthly PE swung between 4.2% and 5.6% — well inside that band².

Step 2 — Roll PE into MAPE: Sum the absolute value of each month’s PE, then divide by the number of months. A MAPE under 20% signals a solid forecast. Excel’s Forecast Sheet generates this and related error metrics (MASE, SMAPE, MAE, RMSE) automatically when you check "Include Forecast Statistics"³.

Step 3 — Backtest before trusting the model:

1. Hold out the last 6 months of historical data.
2. Run the forecast using only the earlier months.
3. Compare the model’s predictions for the held-out period against what actually happened. This hindcasting approach is the same technique Excel’s own forecast tools use to gauge prediction accuracy before extending into unknown future periods⁶.
4. If MAPE comes back high, adjust the method or clean the underlying data.

Re-run this validation every quarter. If actuals diverge from forecast by more than 20% for three straight months, that’s a signal to dig into root causes — pipeline changes, market shifts, or gaps in how activity data is captured — rather than patch the spreadsheet again.

Metric	Formula / Source	Acceptable range
PE (monthly)	(Actual–Forecast)/Actual×100	±10%
MAPE	Average of absolute PE	Under 20%
Confidence Interval	Forecast Sheet default	95%7

This is exactly where spreadsheet forecasting hits its ceiling: it can tell you that your numbers are off, but it can’t tell you why a rep’s pipeline stalled or a lead went cold. It never captured the underlying activity in the first place.

What Are Common Mistakes When Building a Sales Forecast in Excel and How Do You Avoid Them?

Most Excel sales forecasts fail for four predictable, fixable reasons — not because Excel is the wrong tool. The pitfalls below sabotage accuracy long before a formula ever runs.

Mistake	Why It Breaks the Forecast	Quick Fix
Insufficient history	FORECAST.ETS needs enough cycles to detect seasonality; fewer than 12 months of data misses patterns and inflates error by 30–40% 1	Build at least 24 monthly data points before trusting seasonal output
Data gaps and manual edits	Missing months, rounded totals, or retroactive edits to prior-month sales quietly bias the whole series	Audit the source before forecasting; capture events automatically instead of editing a spreadsheet after the fact
Ignoring outliers	One large deal or promo spike drags the trend line off course	Exclude one-time events from the forecast range or footnote them as assumptions
Forecasting too far ahead	Excel’s functions lose reliability past 18 months out	Cap forecasts at 6–12 months and refresh monthly as actuals arrive

The common thread: every one of these mistakes starts with how the data got into the spreadsheet, not with the formula itself. When a rep manually re-enters last week’s calls or fudges the numbers on a rushed deal, that’s a data-integrity problem — and no variant of FORECAST.ETS can fix it ¹.

When Should You Migrate from Excel to AI-Powered Forecasting Tools or Integrated CRM?

Migrate away from spreadsheets when three thresholds hit at once: forecast error stays above 25% despite clean data, more than two people touch the same forecast file, and reps must manually re-enter pipeline changes daily. Below those thresholds, Excel is still the right tool.

Manual data entry is the core failure point. As sales operations scale, typing pipeline updates into a sheet becomes "time-consuming and unreliable," and that friction introduces exactly the kind of error that compounds into forecast drift⁴. Once a team outgrows a single owner, some template guidance is explicit: move to a CRM "when a team grows too large or when manually inputting data into a sheet becomes burdensome"⁵.

	Excel	AI-powered / CRM-integrated forecasting
Cost	Free, fully transparent formulas	Paid, but automates data capture
Data entry	Manual, rep-dependent	Captured from system events in real time
Version control	Prone to conflicting copies	Single source of truth
Best use	Scenario modeling, what-if analysis	Operational, baseline forecasting

The practical answer isn’t either/or. Keep Excel for strategic scenario testing, and route day-to-day pipeline data into a system that captures events automatically. That’s the exact gap Play2sell SalesOS Leads closes: it routes leads and captures activity without waiting on manual entry.

How Do You Use Sales Forecasting to Set Targets and Commercial Budgets?

Sales forecasting only becomes actionable once you translate the statistical output into quota and budget decisions — not before. Treat the point estimate (50th percentile) from your forecast as the baseline budget assumption. Use the upper confidence bound (95th percentile, Excel’s default) as the stretch target for aggressive planning ³.

Turning Forecast Bands Into Targets

Forecast Output	Business Use
Point estimate (50th percentile)	Baseline budget, headcount planning
Upper bound (95th percentile)	Stretch quota, incentive ceiling
Seasonality pattern	Pre-stage campaigns before the spike quarter

If your model flags a seasonal pattern — say a strong Q4 — that signal should trigger marketing and pipeline-generation work a full quarter earlier. Forecast templates built around monthly or quarterly cycles exist precisely to expose these patterns before they hit revenue ⁸.

Quota-setting should follow the same logic. A forecast is one of the core inputs used to build an expense budget and staffing plan, so growth assumptions from the model — not last year’s number plus a guess — should set next year’s target ⁹.

Once targets exist, governance matters as much as the number itself. This is where commission and bonus structures need to trace back to the same forecast assumptions the revenue leadership team already approved — the same discipline Play2sell SalesOS Pay applies when it turns quota attainment into auditable payout splits instead of a spreadsheet argument.

Review cadence closes the loop. Bring forecast versus actual into a monthly revenue sync, and treat any gap above roughly 15% as a signal to open the pipeline, not just the spreadsheet — a large gap between projected and actual revenue is the clearest sign it’s time to change course ¹⁰.

1. Pull point estimate and upper bound from the Forecast Sheet output.
2. Set baseline budget to the point estimate.
3. Set stretch quota to the upper bound.
4. Flag seasonal quarters and shift campaign timing one quarter earlier.
5. Review actual-versus-forecast monthly; investigate root cause if variance exceeds ~15%.

FAQ: Common Questions About Excel Sales Forecasting

Gaps create blind spots that neither FORECAST.ETS nor FORECAST.LINEAR was built to absorb gracefully — both functions assume a timeline with consistent intervals between data points³. Excel can interpolate up to 30% of missing data automatically, filling gaps as a weighted average of neighboring points, but the forecast degrades beyond that threshold³. Your best move is to fix the data source first — standardize date formats and fill every period, even with zero sales — before you trust any output¹.

What’s the difference between FORECAST.ETS and FORECAST.LINEAR?

FORECAST.LINEAR fits a single straight line through your history and ignores cyclical swings entirely⁶. FORECAST.ETS (exponential triple smoothing) detects trend and seasonality together. That’s why it tracked a hotel chain’s summer peaks and winter dips, where the linear version flattened them into a straight line⁶.

Function	Best for	Limitation
FORECAST.LINEAR	Steady, non-seasonal growth	Misses cycles entirely6
FORECAST.ETS	Seasonal or cyclical demand	Needs 2+ full seasonal cycles of history to detect a pattern3

Do I need forecasting software, or is Excel enough?

Excel works fine for a lean pipeline, but manual entry becomes the bottleneck as volume grows. Spreadsheet-based forecasting is explicitly flagged as time-consuming and error-prone once a sales team scales past what one person can maintain by hand⁴. That’s the same ceiling we see across sales orgs generally: a spreadsheet or a CRM field only reflects what a rep manually typed, and reps don’t get paid to type². Play2sell SalesOS Leads closes that gap by capturing pipeline events automatically from integrations instead of requiring manual logging, so forecast inputs stay current without depending on rep discipline.

How often should I update my Excel forecast?

Monthly, right after you close the books for the prior period. Most companies build an initial annual forecast, then revise it monthly or quarterly as new actuals arrive⁹. Quarterly-only updates miss mid-quarter pivots; daily updates just amplify noise.

What if my historical data has outliers, like one big one-off deal?

Flag the outlier, note why it happened, and run two versions of FORECAST.ETS — one including it, one excluding it — then use the excluded version as your working baseline¹. Outliers are explicitly called out as a step in any serious data-cleaning checklist before forecasting¹.

Where can I get a free sales forecast template for Excel?

Several vendors publish free sales forecast templates for Excel and Google Sheets, including simple one-year layouts, 12-month seasonal trackers, and multi-year projection sheets⁸. Vertex42, for instance, offers a free 3-year template that calculates unit sales, growth rate, margin, and gross profit across products¹¹. These are a solid starting point — just remember they still depend on someone typing the numbers in correctly every month.

Automate Your Sales Data for Better Forecasts: Next Steps with Play2sell SalesOS

Excel forecasts are only as accurate as the data feeding them — and for most sales teams, that data goes stale before anyone even enters it. If pipeline stage changes, deal closures, and lead assignments depend on a rep manually updating a CRM, your FORECAST.ETS formula is modeling last week’s business, not this week’s².

That’s a systemic issue, not a discipline problem. Every template in this guide assumes the underlying stage, probability, and close-date fields are current: Vertex42’s 3-year model, Smartsheet’s 12-month tracker, Salesflare’s funnel sheet^11⁸⁴. When they’re not, the forecast function stays precise, but the input is fiction.

The Play2sell SalesOS Leads module addresses the root cause. It captures pipeline events — lead received, stage moved, deal closed — directly from CRM integration, without requiring a rep to type anything. It also routes leads by seller performance, which cuts the friction that stalls pipeline movement in the first place. That means your Excel forecast (or whatever BI tool you feed it into) reflects pipeline truth in days, not weeks.

Concrete next step: audit your current pipeline lag. How long does it take from "lead received" to "stage recorded" in your CRM? If it’s over 48 hours, your forecast is already running on stale data. Request a 15-minute walkthrough of how Play2sell Leads automates that capture and prepares clean data for forecasting.

## Sources

1. https://www.finaleinventory.com/blog/guides/forecasting-in-excel — https://www.finaleinventory.com/blog/guides/forecasting-in-excel ↩
2. https://usmsupplychain.com/sales-forecast-in-excel-step-by-step-for-planners — https://usmsupplychain.com/sales-forecast-in-excel-step-by-step-for-planners ↩
3. Create a forecast in Excel for Windows — https://support.microsoft.com/en-us/excel/create-a-forecast-in-excel-for-windows ↩
4. Free Sales Forecast Template: Excel & Google Sheets | Salesflare — https://salesflare.com/templates/sales-forecast-template ↩
5. Free Sales Forecast Template by Salesflare — https://www.youtube.com/watch?v=5I84SVP7u7k ↩
6. Forecasting in Excel Made SIMPLE (include seasonality & make predictions) — https://www.youtube.com/watch?v=j22tLUQQDh4 ↩
7. Forecasting in Excel: MUST-KNOW for Any Analyst — https://www.youtube.com/watch?v=6rqhMnOgQnU ↩
8. Free Sales Forecast Templates — https://www.smartsheet.com/content/sales-forecasting-templates ↩
9. Free Sales Forecast Template — https://www.liveplan.com/downloads/free-sales-forecast-template ↩
10. The 5 sales forecasting spreadsheets you really need — https://zapier.com/blog/sales-forecasting-spreadsheet ↩
11. Sales Forecast Template for Excel — https://www.vertex42.com/ExcelTemplates/sales-forecast.html ↩