How to build a sales forecast in a spreadsheet
A spreadsheet forecast is where most teams start, and it can be perfectly adequate at small scale. The structure matters more than the tool: deals, amounts, probabilities, close dates, and a weighted rollup.
The catch is maintenance. A spreadsheet is only as current as the last manual update, so it drifts from reality between refreshes, which is why teams eventually move forecasting into their CRM.
- Deal x probability The weighted column
- By close date Group into periods
- Goes stale The manual trap
Short answer
Build a sales forecast in a spreadsheet by listing each open deal with columns for amount, stage, win probability, and close date, adding a weighted-value column (amount times probability), then summing by period and comparing to quota. Add a rep-commit category and a historical adjustment. It works, but manual spreadsheets go stale fast.
Step by step
List deals with key columns
One row per open deal, with columns for deal name, amount, stage, win probability, expected close date, and forecast category (commit, best case, pipeline).
Add a weighted-value column
Compute weighted value as amount times win probability. This risk-adjusts each deal into an expected contribution.
Group and sum by period
Filter or group deals by close-date period, then sum both raw and weighted values. Compare the totals to that period's quota.
Adjust and sanity-check
Layer in rep commit as a floor and adjust for your historical win rate and slippage. Reconcile the weighted total against a top-down coverage estimate.
Common mistakes
Forecasting from raw amounts without probability, which turns the spreadsheet into a best-case list. Always include the weighted column and reconcile against your real win rate.
Letting the sheet go stale. A forecast built on last month's stages and close dates is fiction. If you keep forecasting in a spreadsheet, schedule a disciplined refresh, and move to your CRM once the manual burden outweighs the flexibility.
How Ardovo handles it
Ardovo replaces the spreadsheet with a live forecast that weights pipeline, rolls up rep commits, and scores accuracy automatically, always current. Rook flags commits with no supporting activity and reconciles bottom-up and top-down views, so you never rebuild the sheet again.
Frequently asked questions
How do you build a sales forecast in a spreadsheet?
List each open deal with columns for amount, stage, win probability, and close date, add a weighted-value column (amount times probability), sum by period, and compare to quota. Layer in rep commit and a historical adjustment.
Is a spreadsheet forecast good enough?
At small scale, yes, if you weight deals by probability and refresh it disciplined. The problem is that manual spreadsheets go stale between updates, so teams move forecasting into their CRM as the deal volume and manual burden grow.
Why weight deals by probability in the forecast?
Because raw pipeline treats a new deal and a near-closed deal as equally likely, overstating what will close. Multiplying each amount by its win probability produces a realistic expected value to compare against quota.