How to Build a Sales Forecast in Google Sheets

Google Sheets is the default forecasting tool for revenue teams that need several managers working in the same file. It handles the math fine. What it does not do by default is remember anything, and a forecast that has no memory of last week cannot tell you which deals slipped. This build covers the tab structure, the formulas, and the weekly ritual that turns the file from a snapshot into an instrument.

How do you structure the file?

Five tabs, with a hard rule that formulas never touch the import tab. The separation between Import and Calc is the rule that saves the file. CRM exports change without notice. A new field gets added, column order shifts, and every formula pointed directly at the export starts reading the wrong column. Build Calc with lookups keyed on header text, not on column position, and the refresh stops being a weekly hazard.

How do you get CRM data into the sheet?

A scheduled export into the Import tab, then QUERY to pull what Calc needs.

Most CRMs offer either a scheduled report delivery or a connector that writes into a sheet on a timer. Apps Script calling the CRM API on a trigger works too and gives more control over field selection. Any of them is better than a manual export, because manual exports get skipped in the week that matters.

Pull more fields than you think you need, since re-running history is painful. At minimum: opportunity ID, account, segment, owner, stage, amount, close date, created date, and the last modified date on stage, amount, and close date.

That last group is what makes stall detection possible. Meaningful activity means a change in stage, close date, or amount. Logged emails and meeting invites do not indicate a deal is moving.

Which formulas do the work?

QUERY, SUMIFS, and XLOOKUP cover almost everything.

QUERY is the function that makes Google Sheets better than Excel for this job. One QUERY on the Import tab returns a filtered, sorted, grouped slice without a pivot table, and it re-runs automatically when the import refreshes. Use it to build the Calc table from Import rather than copying ranges.

SUMIFS aggregates the weighted values on the Summary tab by period, segment, and owner. COUNTIFS beside it gives deal counts, which you need to compute average deal size and to spot concentration.

XLOOKUP pulls rates from the Rates tab into Calc. Every conversion rate belongs in a cell on Rates, never typed into a formula. When a rate is hardcoded in Calc, updating it means editing hundreds of formulas, which means it never gets updated.

Two adjustments belong in the Calc weighting. Exclude any deal with no change to stage, amount, or close date in twelve months, since more than 10 percent of open pipeline typically sits in that condition and it inflates every coverage number. And apply a realization ratio, because deals close for less than their recorded value. Compare closed-won amounts against what those deals carried a quarter earlier to derive yours. The mechanics of the weighting itself are covered in weighted pipeline.

How do you build the weekly snapshot?

Copy Calc to a new dated tab every Monday before the import refreshes, and never overwrite the previous one.

This is the step that separates a forecast from a report. A single live view tells you what the pipeline looks like now. A series of weekly snapshots tells you what changed, and the change is where the information is.

Three comparisons run off the snapshot series. Close date movement, which surfaces deal slippage. A rep changing a close date is the strongest slippage signal available, and a deal that has already slipped is less likely to close than one that has not, even when it sits in commit. Amount movement, which shows discounting pressure building before it lands in closed-won data. And appearance and disappearance, which shows in-quarter creation and deals being written off.

Automate the copy with a short Apps Script on a Monday trigger. Manual snapshots get skipped in busy weeks, which are exactly the weeks worth recording.

Add a summary row comparing this week's forecast total to last week's, broken into slipped, pulled forward, created, and closed. A flat total hiding equal amounts of slippage and pull-forward is a quarter in trouble, and the net number conceals it completely.

How do you share the file without losing control of it?

Protect Rates and Calc, and give managers one editable column.

Shared forecast sheets break when someone types a number over a formula. Sheet protection on Rates and Calc prevents this without blocking visibility, since protected tabs stay readable.

Give managers an override column and a comment column on Summary. If a manager believes a deal will close above its weighted value, that judgment goes in the override column beside the calculated number rather than replacing it. Keeping both visible is what makes rep and manager bias measurable at the end of the quarter.

When does Google Sheets stop being enough?

When the file cannot answer why a number changed.

Performance is the first symptom. A few thousand deal rows with lookups across them and the file starts recalculating slowly enough to interrupt the work.

The real limit is explanatory. The snapshot series shows that the forecast moved, and comparing tabs manually shows roughly which deals moved it. Neither tells you whether the movement is normal for week six of a quarter, whether this rep slips deals every quarter at this point, or whether the pattern is showing up across the whole segment. Answering those requires history across many quarters and pattern detection across many deals, which is where a spreadsheet stops and a system starts. Tracking forecast accuracy across periods is the check that tells you when you have crossed the line.