Client CRM Spreadsheet Template: Turning a Lead List Into a Forecast

Here are two true statements about the same small business.

Its pipeline is worth $392,150.

Its forecast is $169,392.

The first number is what most owners quote when someone asks how the year is looking. It is the sum of every open deal — the ones being negotiated this week and the ones that came in from a directory listing on Tuesday and have not been spoken to since. The second counts each of those deals at the probability of the stage it is actually sitting in. The gap between them is 57%, and that gap is the whole difference between a plan and a hope.

You cannot get the second number out of an inbox. That is what this page is about.

Every figure below comes from one fully worked sample business — 46 leads, 22 of them still open, 14 won and 10 lost — pre-filled in the tracker described at the end. The dollars are mine and chosen to be typical of a small service business with deals in the $10,000–$25,000 range. The structure is the point.

What One Lead Row Has to Hold

Most lead lists are a name, a company and a phone number, and they are useless within a month. A row that works holds nine things, and seven of them are things you already know:

1. Company and contact — two columns, because you will lose one and keep the other
2. Source — where the lead came from, chosen from a fixed list, never typed free-hand
3. Owner — who is responsible, even if the answer is always you
4. Service line — what they are buying, because your win rate is not the same across all of them
5. Value — your honest estimate, updated when it changes
6. Stage — from a fixed list of five or six, never more
7. Next action — the specific thing you will do
8. Next-action date — the day you will do it

Items 7 and 8 are the two everyone leaves off, and they are the two that make the sheet earn its keep. A deal without a dated next action cannot appear on an overdue list, which means it cannot be chased, which means it quietly expires. In the worked business there are two open deals worth $22,550 with no next action set at all — not late, not stalled, just invisible to every report until someone goes looking row by row.

Everything else, a decent sheet works out for you: days in the current stage, days since last contact, weighted value, the age of the deal in days, and whether any of those has crossed a line you set.

Setting Stage Probabilities (The Only Judgement Call)

A weighted forecast needs one input you have to think about: what percentage of deals at each stage eventually close. Set them once in a settings tab and never fiddle with them mid-quarter, or the forecast becomes a mood ring.

The set used throughout this page:

Two rules keep these honest. Nothing sits at 0% or 100% — a 0% deal should be marked lost and a 100% deal should be marked won, and a stage that means either is a stage you do not need. And the jump from Quoted to Negotiating should be your biggest, because that is where the real qualification happens: people do not negotiate things they are not buying.

If you have a year of history, replace my numbers with yours — count how many deals that ever reached “Quoted” were eventually won, and that fraction is your Quoted probability. If you do not, start with the table above and revisit it in six months. Being roughly right, consistently, beats being precisely wrong once.

The Weighted Forecast

Now the same 22 open deals, counted twice:

Read down the right-hand column and the business looks different than it does from the left. Four deals in Negotiating carry more than half the entire forecast — $88,538 of $169,392 — while the eight deals at the top of the funnel, worth $92,900 between them, contribute $9,265. Lose one negotiation and the quarter moves. Lose all four new leads and it barely registers.

That is the single most useful thing this arithmetic does: it tells you where your attention is actually worth money. Most owners, left to instinct, spend their week on the new leads because new leads feel like progress.

The same weighting also answers when. Sort the open deals by expected close date, apply the same probabilities, and you get a rolling window — in the worked business, $133,438 of the $169,392 is expected inside 30 days, which is a genuinely aggressive near-term book and would tell a real owner to spend next week on prospecting rather than closing. A pipeline total can never tell you that, because a pipeline total has no dates in it.

The full method, including what to do with deals that have no close date: how to calculate a weighted sales pipeline forecast.

The Three Ways a Deal Dies Quietly

Once next-action dates exist, three lists fall out of the sheet on their own, and they are not the same list.

Overdue — the next-action date has passed. Eight of them in the worked business, holding $80,450. The worst is nine days late on a $9,250 deal. Nine days is not a disaster; nine days repeated forty times a year is the difference between a good year and a flat one.

Stalled — nobody has touched the deal in longer than your threshold, whatever stage it is in. Seven of them, $61,100. The biggest is $17,600, untouched for 29 days, and it is sitting in a healthy-looking stage. Stalled is not a stage, it is a behaviour, which is exactly why stage-based reports never surface it.

No next action — the two deals worth $22,550 mentioned above. These cannot be late, because nothing was ever scheduled.

Three different failures wanting three different fixes: make the call, restart the conversation, decide what happens next. A sheet that only reports by stage shows you none of them.

The thresholds, the weekly order of work, and what the re-opening message actually says: the sales follow-up tracker.

What Twenty Closed Deals Tell You

Open deals are guesses. Closed ones are evidence, and four numbers come out of them:

• Win rate, two ways. On decided deals — 14 won against 10 lost — the worked business runs 58.3%. By value, $273,000 won against $155,800 lost, it runs 63.7%. The second being higher says the deals it loses are its smaller ones, which is a good sign. If yours runs the other way you are losing the big ones, and that is a pricing or a seniority problem rather than a volume one.
• Average won deal: $19,500. This is the number that turns a revenue target into an activity target. A $200,000 year needs about 10 wins, and at a 58% win rate that is 18 decided deals.
• Average days to close: 50.9, fastest 13, slowest 69. The spread matters more than the average: it tells you how far ahead you have to fill the funnel, and a deal at 90 days in a 50-day business is not “nearly there,” it is a different animal.
• Quote decision time: 15 days on average, with nine quotes currently waiting. Anything past double that is a no that has not been said out loud, and it belongs on a follow-up list rather than in a forecast.

Where the Deals Came From

The last column on the lead row — source — costs nothing to fill in and pays for the whole exercise. Put the spend for each channel next to the wins it produced and the picture is rarely what people expect. In the worked business, referral produced $101,200 of revenue on zero spend, one paid channel won deals at $156 each and another at $5,100 each, and the overall cost per win across $22,440 of spend was $1,603.

But cost per win on its own is a trap, and the worked numbers show why: two paid channels there cost almost exactly the same per win — $5,040 and $5,100 — and returned $41,200 and $14,100. Same price, three times the outcome.

The full channel table and the two ratios to read it with: lead source ROI, and which channel actually wins you business.

Why the Lost Ones Were Lost

Ten lost deals, $155,800 of value, and one field on each: why. The top reason in the worked business is “price too high,” carrying $51,400 — a third of everything lost. That is a number worth having before your next quote, and it is unavailable to anyone who marks deals lost and deletes the row.

The trap is ranking loss reasons by count rather than by the money behind them. Four small deals lost on timing and one large one lost to a competitor are not equally interesting, and a count says they are.

How to write loss reasons that are usable, and what “price too high” usually means instead: win/loss analysis for a small business.

Setting It Up, In Order

1. Fix your stages and your probabilities — five or six stages, one probability each, in a settings tab. Fifteen minutes, and every other calculation reads from it.
2. Fix your source list. Ten options in a dropdown. Free-typed sources produce “referral,” “Referral,” “ref” and “word of mouth” as four separate channels, and the ROI tab becomes unreadable.
3. Enter every open deal you currently have, including the embarrassing old ones. Value, stage, next action, next-action date. This is the only genuinely tedious part and it takes about an hour.
4. Set a stall threshold — 14 or 21 days for most businesses — and let the sheet flag anything past it.
5. Book twenty minutes a week. Overdue list, then stalled list, then no-action list. In that order, because that is descending order of how close the money is.

After one quarter you will have a real win rate, a real average deal, a real time-to-close and a channel table. After two, the forecast starts being right often enough to hire against.

The Thing Worth Remembering

A pipeline total is a measure of activity. It goes up when you are busy and it goes up when you are being ignored, and it cannot tell the two apart.

A weighted forecast is a measure of expectation. It only moves when a deal moves stage, which means it only moves when something real happened.

Set the odds against your stages once, put a dated next action on every open row, and the difference between $392,150 and $169,392 stops being a philosophical question and starts being a list of eight calls to make on Monday.

The rest of this series

• Weighted sales pipeline forecast: how to calculate it — the formula, a worked 22-deal pipeline, the 30/60/90 window and what to do with deals that have no close date.
• Sales follow-up tracker: overdue, stalled and the deals nobody owns — three lists, three fixes, the thresholds that generate them and the weekly order of work.
• Lead source ROI: which channel actually wins you business — a nine-channel table, cost per win against revenue per dollar, and why the two disagree.
• Win/loss analysis: why you lose deals and what to do about it — ranking loss reasons by money rather than count, and the five reasons worth having on a dropdown.

Featured on ReadySheetGo

Client CRM, Sales Pipeline & Lead Tracker — $16.99

Thirteen linked tabs and 7,217 working formulas — the business above, pre-filled across 46 leads with 22 still open, so you can see it running before you type anything.

Leads is the master list every other tab reads from: company, contact, source, owner, service line, value, stage, next action and next-action date on one row, with days in stage and days since contact calculated. Pipeline totals open value by stage, by owner and by service line, then applies the probability you set against each stage to return the weighted figure. Forecast projects by expected close month across rolling 30, 60 and 90-day windows and six months ahead, then sets it against the twelve months that actually closed.

Follow-Ups counts down to every next action and then counts up past it, building the overdue list worst-first, the stalled list biggest-first, and a separate count of open deals with no next action set at all. Then Quotes with an expiry clock and a quote win rate; Clients with lifetime value and a dormant list of people who already paid you once; Activity returning touches per win and meetings booked; Win-Loss ranking loss reasons by the revenue behind them; Source ROI dividing channel spend by wins to return a true cost per win; plus Dashboard, Settings and Lists.

Row checks catch a lost deal with no reason, a deal with no owner, a close date before the created date and a stage that is not on your list. Colour-coded inputs and dropdown validation throughout.

Works in Excel and Google Sheets. No macros, no add-ons.

Get the Client CRM, Sales Pipeline & Lead Tracker →