# Sales forecast template: build it from CRM fields instead of a spreadsheet

https://salesbond.app/blog/sales-forecast-template-crm-fields/
Published: 2026-10-17 · Updated: 2026-10-26

A sales forecast template for a B2B team needs only five deal fields: amount, close date, stage probability, forecast category and owner. Export those from your CRM instead of retyping them in a spreadsheet, and check the close dates and probabilities before you trust the total.

That is the whole template. The rest of this post covers why the spreadsheet version keeps breaking, what feeds each column, and three checks that separate a number you can say out loud on Monday from a number you hope is right.

## Why a forecast template fails in a spreadsheet

Open any sales forecast template from the first page of Google and you get a grid. Months across the top, products or reps down the side, units times price in the cells. The Fit Small Business collection works that way for most of its templates: annual, monthly, by product, retail, subscription. Its B2B version is the exception. There you enter estimated deal values and the percent chance of closing each one, and the projection comes from the two ( [Fit Small Business: sales forecast templates](https://fitsmallbusiness.com/sales-forecast-template/)). The math is fine. The trouble is where the inputs come from.

In a spreadsheet, they come from a person. Someone copies deal values out of the CRM on Friday, picks a probability that feels right, and types a close date from memory. By the next Friday the CRM has moved and the sheet has not. Two deals slipped a month, one was lost, a new one arrived at proposal stage. Now there are two forecasts, and the planning call turns into an argument about which one is real.

We see this a lot. The grid itself is harmless; the retyping is what hurts. Every field you copy by hand goes stale within a week, and the fields a forecast depends on are exactly the ones that change most often.

So the template we use is a short list of fields that already exist in HubSpot or Pipedrive, an export of those fields, and a few rules for when not to believe the result.

## The five CRM fields a forecast needs

Amount, close date, stage probability, forecast category and owner. Here is what we think each one should hold, and what it shouldn’t.

**Amount.** HubSpot calls it Amount, Pipedrive calls it the deal value. It is the number you would invoice if the deal closed as scoped today. Not the upsell potential, and not the three-year contract value unless three-year value is what you forecast. If the deal has no scope yet, the field stays empty, and we will come back to why that matters. For service work, the number you would invoice depends on whether the deal is [fixed price or time and materials](https://salesbond.app/blog/time-and-materials-vs-fixed-price-deal-amount-zoho-crm/).

**Close date.** HubSpot’s Close date, Pipedrive’s Expected close date ( [HubSpot: default deal properties](https://knowledge.hubspot.com/properties/hubspots-default-deal-properties); [Pipedrive: sorting deals in the pipeline view](https://support.pipedrive.com/en/article/how-are-deals-ordered-in-the-pipeline-view)). This is the date the buyer signs, not the date the rep would like them to. The close date decides which period a deal lands in, so a wrong date is a wrong forecast even when every other field is perfect. What happens when reps keep pushing it is covered in [how to improve forecast accuracy on a small team](https://salesbond.app/blog/sales-forecast-accuracy-small-teams/).

**Stage probability.** Both CRMs attach a percentage to a stage. Whether that percentage means anything on your team is a separate question, and the answer differs by CRM: HubSpot’s defaults and how to replace them are in [HubSpot deal stage probability and your forecast](https://salesbond.app/blog/hubspot-deal-stage-probability-forecast/), and Pipedrive’s two separate probability fields are in [deal probability vs stage probability in Pipedrive](https://salesbond.app/blog/pipedrive-deal-probability-vs-stage-probability/). For the template you need one number per deal, and you need to know which field it came from.

**Forecast category.** Commit, best case, or just pipeline. This is the rep’s judgment about the deal, and it is deliberately a different column from the stage’s judgment. HubSpot has a default Forecast category property with Not forecasted, Pipeline, Best case, Commit and Closed won as values. In Pipedrive, teams usually add it themselves as a single option custom field ( [Pipedrive: custom field types](https://support.pipedrive.com/en/article/what-types-of-custom-fields-are-there)). How to define the buckets so five reps mean the same thing by “commit” is in [commit vs best case for a small team](https://salesbond.app/blog/commit-vs-best-case-forecast/).

**Owner.** The forecast is a list of promises, and each promise has a person you will talk to on Monday.

Everything else in the deal record (stage name, company, next activity, source) is useful context and isn’t needed to compute the number. Keep the export narrow. A forecast with twelve columns gets scrolled, not read.

## A minimal sales forecast template built from a CRM export

The template is one sheet in Excel or Google Sheets with seven columns. Six come straight from the export; one is calculated. Copy this header row into row 1:

```
Deal, Owner, Amount, Close date, Probability, Category, Weighted
```

Filled in, it looks like this:

| Deal | Owner | Amount | Close date | Probability | Category | Weighted |
| --- | --- | --- | --- | --- | --- | --- |
| Northwind renewal | Maria | 24,000 | 2026-11-14 | 80% | Commit | 19,200 |
| Contoso onboarding | Maria | 9,500 | 2026-11-28 | 60% | Best case | 5,700 |
| Fabrikam pilot | Dan | 15,000 | 2026-10-09 | 60% | Best case | 9,000 |
| Tailspin expansion | Dan | 40,000 | 2026-12-19 | 20% | Pipeline | 8,000 |
| Litware rollout | Priya | (empty) | 2026-12-05 | 40% | Pipeline | 0 |

Deal names and numbers are invented for the example. With Amount in column C and Probability in column E, the Weighted cell in row 2 is `=C2*E2`, filled down. Under the table sit three totals:

```
Weighted: =SUM(G2:G500)
Commit: =SUMIF(F2:F500,"Commit",C2:C500)
Best case: =SUMIF(F2:F500,"Commit",C2:C500)+SUMIF(F2:F500,"Best case",C2:C500)
```

For the five rows above that gives 41,900 weighted, 24,000 commit and 48,500 best case. What each total is for comes a little further down.

To fill it, filter open deals to a close date inside the period you forecast (usually the quarter, sometimes the next 12 months), add the five fields as columns, and export. In HubSpot that is a deal view with those properties as columns and Export view ( [HubSpot: export records](https://knowledge.hubspot.com/import-and-export/export-records)); in Pipedrive it is a filtered deal list with the columns set, exported as CSV or Excel ( [Pipedrive: exporting data](https://support.pipedrive.com/en/article/exporting-data-from-pipedrive)). Paste the rows in and the formulas do the rest. It should take less time than reading this section.

Look at the invented example again before moving on. Two rows are already wrong.

![Five CRM deal fields exported as CSV into a seven-column sheet with a calculated Weighted column and three totals](https://salesbond.app/wp-content/uploads/2026/10/sales-forecast-template-crm-fields-2.png)

## Three data checks before you trust the number

The template adds up whatever it is given. Before the total goes into a planning call run three checks on the export. Each one is a sort or a filter.

### Close dates in the past

Sort by Close date, ascending. Any open deal with a date before today is either in the wrong period or should have been marked won or lost already. Fabrikam pilot in the example was due to close on October 9 and is still open on a sheet built mid-October. Its 9,000 sits in this quarter’s weighted total and nobody has decided whether it belongs there. Every row above today’s date needs a decision: new date, closed, or out.

### Probabilities nobody has touched

Filter by stage, then look at the Probability column. If every deal at Proposal shows exactly the stage default, the number is pure stage math. That is fine as long as everyone knows it. It stops being fine when the planning call treats it as the reps’ opinion. If your CRM lets a rep override probability per deal, watch for the opposite pattern: one owner whose overrides are all 90%. That’s optimism with a percent sign.

### Empty or placeholder amounts

An empty Amount is a zero in the Weighted column, so the deal disappears from the total without anyone noticing. Litware rollout above is a real opportunity that contributes nothing. Round placeholder amounts are the quieter version of the same problem: five deals from the same rep at exactly 10,000 were scoped by nobody. Either the deal gets a real number this week or it moves out of the forecast period until it does.

A disclosure, since this is where tooling tends to come up: we build SalesBond, a sales management layer on top of the CRM a team already uses; we started with Pipedrive. It analyses deal and customer data, finds risks such as a close date that has already passed, and explains what needs attention, so the checking doesn’t depend on someone remembering to sort a sheet on Friday. [Check your CRM with SalesBond](https://salesbond.app/) if you would rather read that list than build the filters. The checks themselves need nothing beyond a sort.

## Weighted, commit and best case: which column is which

Weighted is Amount × Probability summed across every open deal in the period, commit is the full Amount of deals marked Commit, and best case is commit plus the full Amount of deals marked Best case. Three totals, three different questions. We would not average them.

The weighted total answers “what does the pipeline math say?” It never asks a person to vouch for anything, which is both it’s strength and its blind spot. Where the stage percentages lie is covered in [how a weighted pipeline forecast works](https://salesbond.app/blog/weighted-pipeline-forecast/).

Commit has no multiplication. A committed deal counts in full because the rep has said they would stake their number on it. This is the figure to report upward.

Best case is a coaching number. The gap between it and commit is where the manager’s week goes.

What if weighted and commit are far apart? That tells you something. Weighted far above commit usually means reps are cautious about committing or the stage percentages are generous. Commit far above weighted means someone is committing deals the stage math does not support, and that is worth a conversation on Monday. Which method to lead with for a team your size is a separate decision; we laid out the options in [sales forecasting methods for a small team](https://salesbond.app/blog/sales-forecasting-methods-small-team/).

## Updating it weekly in 15 minutes

The template only earns its place if the refresh is boring. Ours goes like this.

Re-run the saved CRM view and export it, then paste over last week’s rows. Don’t edit the old sheet by hand. The Weighted formula and the three totals recalculate on their own.

Then the three checks: past close dates, untouched probabilities, empty amounts. Write the names of the deals that fail next to each check.

The only part that takes thought comes next. Compare this week’s commit total with the the one from last week and list what moved: deals in, deals out, deals that changed amount or date. Finish with one sentence under the totals saying what the number is, what it was, and why it changed.

The deals that failed a check and the deals that moved are the agenda for the pipeline review. The sheet is just the record. The conversation is what you came for.

![Five-step weekly loop: export, paste, three checks, what moved, one sentence, ending in the agenda for the pipeline review](https://salesbond.app/wp-content/uploads/2026/10/sales-forecast-template-crm-fields-3.png)

## What the template still cannot tell you

It cannot tell you whether a probability is right. It can show you that nobody changed it. It cannot tell you why a deal slipped, only that the close date moved. And it cannot tell you whether Maria’s commit is honest; it can tell you her commit was 33,500 last week and 24,000 this week, and the rest is a conversation.

Nor can it tell you how accurate your forecast is until you keep the old numbers. That weekly sentence is the start of the record. After a quarter, put the commit totals you reported next to what actually closed, and you will see whether your entry rules are too loose or your reps too shy. How to run that comparison is in [sales forecast accuracy for small teams](https://salesbond.app/blog/sales-forecast-accuracy-small-teams/). Start this Friday: save the deal view, export it once, and keep the sheet.