How Do You Build a Weighted Pipeline Forecast in a Spreadsheet?

How do you build a weighted pipeline forecast when your CRM report is not enough?

Export your open deals into a spreadsheet, add a probability for each deal stage, multiply each deal's amount by its probability, and sum the results by expected close month. That weighted total is your forecast. The whole build takes about an hour in Google Sheets or Excel, and it teaches you more than any dashboard.

This tutorial is written for one specific reader: a founder or first revenue hire at a small B2B company with fewer than a hundred open deals, a CRM like HubSpot or Pipedrive, and a board or investor asking how much revenue will close this quarter. You do not need a forecasting tool. You need a clear sheet and honest stage definitions.

I build GTM reporting for B2B teams, and I often start here even when the CRM has built-in forecasting. A spreadsheet forces you to see every assumption. Once the numbers make sense in a sheet, moving them into the CRM is easy.

What is a weighted pipeline forecast?

A weighted pipeline forecast estimates future revenue by multiplying each open deal's value by the probability that it will close, based on its stage. A ten thousand dollar deal at a stage with a forty percent close rate counts as four thousand dollars. Adding those weighted values gives a realistic expected total.

The idea is simple. Raw pipeline overstates what you will close, because many deals die. A weighted forecast discounts each deal by how far along it is. Early stage deals count for little. Deals with a signed proposal count for a lot.

It is not a promise. It is an expected value, which means it will be wrong deal by deal and roughly right in total, if your probabilities are honest. That is why the stage probabilities matter more than any formula in this tutorial.

What data do you need to export from your CRM?

Export every open deal with five fields: deal name, amount, current stage, expected close date, and owner. Add the date the deal entered its current stage if your CRM tracks it. Export to CSV, then import it into a new Google Sheet or Excel workbook as a tab called Deals. Keep the raw export untouched.

Most CRMs, including HubSpot, Pipedrive, and Salesforce, let you filter a deals view and export it, though the exact menu differs by tool and plan. Check your CRM's official documentation if you cannot find the export option.

Before going further, scan the export for obvious problems. Deals with no amount, close dates in the past, or deals stuck in a stage for months will distort the forecast. Fix them in the CRM rather than in the sheet, so the next export is clean.

Keeping the raw export on its own tab matters. When someone questions a number next month, you can show exactly what the CRM said on the day you built the forecast.

How do you set the probability for each stage?

Use your own history if you have it: for each stage, count how many deals that reached it eventually closed won, and divide by the total that reached it. If you have too little history, start with conservative estimates, write them down, and replace them with real conversion rates once you have a few months of data.

Create a second tab called Stages. In the first column, list each deal stage exactly as it is spelled in your CRM. In the second column, enter the probability as a decimal, such as 0.1 for ten percent. Keep the spelling identical, because the lookup in the next step depends on it.

Be honest here. Founders tend to assign high probabilities to stages that feel advanced, like demo completed. If your history says only a small share of demoed deals close, the forecast should say so too.

Probabilities only mean something when stages have clear entry and exit rules. If one rep moves deals to proposal sent after a friendly call and another waits for a written proposal, the same stage hides two very different odds. My guide on how to define deal stage exit criteria covers how to fix that.

How do you calculate the weighted amount for each deal?

Add a Probability column to the Deals tab that looks up each deal's stage in the Stages tab, then add a Weighted column that multiplies the amount by that probability. In Google Sheets or Excel, a VLOOKUP or XLOOKUP pulls the probability, and a simple multiplication gives the weighted value for each row.

In the Deals tab, add a new column called Probability. In its first row, write a formula that looks up the stage cell in the Stages tab and returns the matching probability. VLOOKUP works in both Google Sheets and Excel. XLOOKUP is cleaner if your version supports it. Fill the formula down for every deal.

Next, add a column called Weighted. In its first row, multiply the amount cell by the probability cell, then fill it down. Each row now shows how much that deal contributes to the forecast.

Check for errors before moving on. A lookup error usually means a stage name in the export does not exactly match the Stages tab. Fix the spelling in the Stages tab and the error will disappear.

How do you group the forecast by month or quarter?

Add a Close Month column that converts each deal's expected close date into a month label, then build a summary with SUMIF or a pivot table that totals the Weighted column by month. The result is a simple table showing weighted expected revenue for each upcoming month and quarter.

In the Deals tab, add a column called Close Month. Use a formula that turns the close date into a year and month label, such as the TEXT function with a year-month format. Fill it down.

Create a third tab called Forecast. List the next six months in the first column using the same label format. In the second column, use SUMIF to add up the Weighted column wherever Close Month matches that row's month. In a third column, use SUMIF again on the raw Amount column, so you can see weighted and unweighted totals side by side.

A pivot table does the same job with fewer formulas, if you prefer. Put Close Month in rows and the sum of Weighted in values. Both approaches work. I prefer formulas for a forecast that other people will read, because they are easier to audit.

How do you check whether the forecast is realistic?

Compare the forecast against three things: your actual closed revenue in recent months, the number of deals expected to close each month, and your target. If the forecast is far above anything you have ever closed, your probabilities or close dates are too optimistic. Adjust the inputs, not the formulas.

A quick sanity check is to count deals per month as well as dollars. If one month holds a single large deal and nothing else, the forecast for that month is fragile. If one deal slips, the month collapses.

Close dates are the other common problem. Many teams leave close dates on their default values or push every deal to the end of the quarter. Ask each deal owner to confirm close dates before you share the forecast.

Finally, compare the total weighted pipeline to your target. If weighted pipeline for the quarter is well below target, you have a pipeline problem, not a forecasting problem. My piece on what a pipeline coverage number actually means explains how to read that gap.

How do you keep the forecast up to date each week?

Refresh it on a fixed day each week. Export the open deals again, paste the new data into the Deals tab, and let the formulas recalculate. Save a copy of each week's Forecast tab, or paste its values into a history tab, so you can see how the forecast changed over time.

The history is the most valuable part. A forecast that drops every week as the quarter goes on tells you that deals are slipping or dying. A forecast that holds steady tells you your probabilities are working.

If the weekly export becomes tedious, automate it later. A scheduled export or a simple integration with Zapier or Make can drop fresh deal data into the sheet. Build the manual version first, though, so you understand every number before you automate it.

When should you move beyond a spreadsheet forecast?

Move beyond the spreadsheet when your deal count grows past what one person can review weekly, when several reps need their own forecasts, or when you need forecast categories like commit and best case. At that point, your CRM's forecasting features or a dedicated tool will save time. The logic you built here still applies.

The spreadsheet's job is to make your assumptions visible while the business is small. Once your stage probabilities are grounded in real history and your team trusts the numbers, moving that logic into the CRM is a configuration task rather than a debate.

Some teams keep a spreadsheet alongside the CRM for years, because it is the easiest place to test a new assumption. That is fine too, as long as everyone knows which number is the official one.

What should you do next?

Block an hour this week. Export your open deals, set honest stage probabilities, build the Probability and Weighted columns, and summarize by month. Share the result with whoever asks you for a forecast, along with the stage probabilities you used, and refresh it every week on the same day.

You will finish with a forecast you can defend, line by line, which is worth more than a polished dashboard nobody can explain.

If you want help building the stage definitions, CRM fields, or automation that keep a forecast like this honest, reach out at pravinkumar.co. Let's chat.