How to Forecast Sales in Excel Without Breaking the Model

Excel is a fine place to forecast sales. The math is not the hard part, and the functions have been stable for a decade. What breaks Excel forecasts is structure: formulas pointed at volatile exports, assumptions hardcoded inside deal rows, and trend functions applied to data they were never designed for. This guide covers the functions worth using and the build order that keeps the model stable through a quarter.

How do you forecast sales in Excel?

Build two layers, then reconcile them against each other.

Layer one is the run-rate baseline. It answers what revenue you would produce if the business continued behaving the way it has behaved, calculated from historical actuals with a seasonality adjustment. Layer two is the pipeline layer, calculated from open opportunities weighted by conversion rates you derived from your own closed-won history.

The two layers should land within a reasonable distance of each other. When the pipeline layer sits far above the baseline, either something real changed in demand generation or the pipeline is carrying deals that will not convert at the rates you assumed. That gap is the most useful diagnostic the model produces, and a single-layer forecast never surfaces it.

Keep each layer on its own tab. Reconcile on a third.

Which Excel functions belong in a sales forecast?

Five do almost everything. FORECAST.ETS is the function most teams reach for and most often misuse. It requires a consistent time series with enough history for the algorithm to find a seasonal pattern. Two years of monthly revenue is the practical minimum. Feed it eighteen months and it will still return a number, and that number will be a straight line wearing a seasonal costume.

Never run a trend function on pipeline values. Pipeline is a snapshot of a stock, not a flow, and its week-to-week movement reflects data entry as much as demand.

How do you build the pipeline layer with SUMIFS?

One SUMIFS per period and segment combination, multiplied by a rate pulled from a lookup.

Structure the deal tab with one row per open opportunity and columns for segment, stage, amount, and close date. Then build a grid on the pipeline tab with periods across the top and segments down the side. Each cell sums the amounts matching that period and segment.

Split further by stage if your stage conversion rates differ materially, which they usually do in enterprise segments. The formula pattern stays the same, the criteria list just gets longer.

Then apply the rate. Use XLOOKUP against a rates tab keyed on segment and stage rather than typing percentages into the formula. Hardcoded rates are the single most common reason a spreadsheet forecast cannot be audited six weeks later.

Two adjustments belong in this layer. First, exclude any deal whose stage, amount, or close date has not changed in twelve months, whatever the CRM says about its stage. Across our customers, more than 10 percent of open pipeline sits in that condition. Second, apply a value realization ratio, because deals close for less than their recorded amount. Compare closed-won amounts against what those same deals carried a quarter earlier and use the resulting ratio. A win rate applied to inflated amounts produces an inflated forecast even when the rate itself is correct.

How do you build the run-rate baseline?

FORECAST.LINEAR over trailing revenue, then a seasonal index applied on top.

Pull at least eight quarters of closed revenue by month into a clean two-column range: date and amount. Run FORECAST.LINEAR against that range to project forward. This gives you the trend without any pipeline input, which is exactly what you want as an independent check.

Then adjust for seasonality manually rather than relying on FORECAST.ETS, because the manual version is inspectable. Divide each month's revenue by the average monthly revenue for its year, then average each calendar month's ratio across the years you have. That gives twelve index values. Multiply the linear projection by the matching index.

The pattern in B2B SaaS is consistent enough to sanity-check your indices against. Q2 and Q4 run stronger than Q1 and Q3. Month three of a quarter runs stronger than months one and two. If your calculated indices contradict that shape, check whether a single large deal is distorting a month.

For the broader methodology behind the baseline, see how to forecast revenue.

What breaks Excel sales forecasts?

Formulas that reference a paste-over export tab.

CRM exports change. A new field gets added, a column order shifts, and every VLOOKUP with a hardcoded column index starts returning the wrong field silently. Errors that throw #N/A are the lucky case, because you see them. The dangerous case is a formula that now points at a different column of numbers and returns a plausible total.

Three habits prevent most of it. Paste exports to a dedicated raw tab and never calculate on that tab. Use XLOOKUP or INDEX with MATCH keyed on header text rather than position. Add a check row that recomputes total pipeline two different ways and flags a difference.

The other structural failure is aging assumptions. A model built in January carries January's win rates and January's cycle lengths through the year. When a new competitor creates pricing pressure and average deal size falls, or when buying decisions slow and cycles lengthen, the spreadsheet keeps producing confident numbers from assumptions that stopped being true. Nothing in the file tells you this happened. Rebuild the rates tab from trailing twelve-month actuals every quarter, and track your own forecast accuracy by period so the drift becomes visible.

Excel will do the arithmetic correctly forever. Keeping the inputs current is the work, and it is manual work that does not get easier as the deal table grows.