Revenue forecasting with funnel data: how to move beyond guesswork in your forecast

Revenue forecasting comes from two pieces of information your operation already has: the conversion rate of each funnel stage and the average time a lead takes to move through it. With those two, the projection becomes a calculation. Without them, it becomes the sum of the salespeople's expectations, which is the forecast that misses badly every quarter.

The good news is that the calculation is simple. The hard part, and the one this article treats with the most care, is making sure the numbers that go into it are reliable. That is why most texts about forecasting disappoint: they teach the formula, which is the easy part, and do not say what to do when the CRM does not store the information the formula requires.

The calculation, step by step

All the values below are used only as an example.

Step 1: gather the conversion rate of each stage. Look at the last six months and calculate the passage from one stage to the next. Say your operation has: from lead to contact made, 60%; from contact made to meeting, 40%; from meeting to proposal, 50%; from proposal to sale, 30%.

Step 2: multiply the rates to find the end-to-end conversion. In the example, 60% × 40% × 50% × 30% gives 3.6%. Of every 100 leads that come in, almost 4 become customers.

Step 3: apply it to what is in the funnel today. If there are 500 new leads at the top, the projection is 18 sales. Multiplying by the average ticket, say R$ 8 thousand, you arrive at R$ 144 thousand.

Step 4: spread it over time using the average cycle. If the average cycle is 60 days, that revenue is not next month's, it is the month after. This is the step that almost every improvised forecast forgets, and it is what makes the forecast match or not match the finance calendar.

Step 5: add what is already in progress. Each open deal enters with its value multiplied by the remaining conversion of the stage it is in. A R$ 20 thousand proposal, with 30% conversion at that stage, enters as R$ 6 thousand in the forecast.

Note that steps 3 and 5 answer different things. The third projects what today's lead capture will generate down the road, and serves the marketing conversation. The fifth projects what the sales team should close in the short term, and serves the sales conversation. Adding the two into the same number, without separating the horizon of each, is the mistake that makes the forecast look good and arrive wrong.

This funnel-based method is the same one described in reference materials on sales forecasting. What changes from one operation to another is not the formula. It is the quality of the data that feeds each step.

Resource · In PortuguesePrompt Guide for Marketing and Sales70 prompts with questions about marketing, sales and behavior to ask your data.Download the guide

Four things that break the forecast in practice

Here is what usually explains the difference between forecast and actual, and none of them is solved by tinkering with the spreadsheet.

Stage without a change date. If the CRM records which stage the deal is in, but not when it got there, you cannot calculate the average cycle or the time per stage. Without the average cycle, the forecast may get the total right and the month wrong, which in practice is getting it wrong.

Duplicate leads inflating the top. The same person came in through the form and through WhatsApp, became two records, and the top of the funnel looks bigger than it is. The conversion rate calculated on that base comes out artificially low, and the forecast comes in lower than reality.

Stalled deal without an updated status. The proposal was sent in May, the customer disappeared, and the deal remains open in the CRM because nobody marked it as lost. It enters the calculation every month, as if it were going to close, and is the main culprit behind optimistic forecasts.

Rate calculated over too short a period. In a long-cycle operation, three months of history is a small sample. Two atypical sales change the whole rate. Use six to twelve months and recalculate every six months.

How to reduce these four problems

The first two are about data structure: they require that the date of each stage change be recorded and that the same contact be resolved as a single person. We cover this in the article on the unified marketing and sales funnel.

The last two are about routine. It is worth agreeing with sales on an inactivity period after which the deal is marked as lost, and reviewing the base periodically. The data hygiene routines cover this kind of cleanup.

A segment of "deals stalled for more than X days" solves the third problem almost on its own, because it turns the cleanup into a list the team opens every week instead of a semiannual push. Laiki's segments let you build that cut by combining stage, time and behavior, and it stays up to date without anyone filtering a spreadsheet.

What to expect in terms of accuracy

A note on expectations, so the number does not become a promise. A forecast made on organized data is wrong, and wrong by less. It is valuable for the trend and the comparison: forecast against actual, month by month, shows whether the model is calibrated.

If the difference is large and always in the same direction, the problem is an outdated rate or an inflated pipeline. If the difference swings both ways, it is low volume, and the projection needs a longer window.

It is worth recording forecast and actual in some fixed place, month by month. Without this history, each review starts from zero and nobody notices that the model has been wrong in the same direction for six months.

Revenue forecasting improves when the base improves, not when the spreadsheet gets more elaborate. A sophisticated model on dirty data keeps being wrong, just with more decimal places.

Frequently asked questions

What is the difference between revenue forecasting and a target?

The forecast is what should happen if the current pace holds. The target is what the company decided to pursue. When the two become the same thing, the forecast stops being a management tool and becomes an exercise in optimism.

How often should I review the forecast?

Monthly for the number, and every six months for the conversion rates. Reviewing the rate every week makes the model chase noise.

Do I need a CRM to forecast revenue?

You need stage records with dates, and that normally lives in the CRM. The tool matters less than the discipline of recording: a pipeline kept up to date in a spreadsheet forecasts better than an expensive CRM with blank fields.