The pipeline says you beat target. Will you?
Six live pursuits, each with a fee and a chance of winning. The weighted pipeline number everybody reports is the average of sixty-four possible years, and it is one of the few numbers on the sheet that can never actually happen.
Marketing Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Will the New Business Pipeline Deliver?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
The answer
- Miss the $350K target
- 51% for a pipeline $22,750 above it
- Median year books
- $330,000 P10 $90,000 · P90 $715,000
- Nothing lands at all
- 7% of simulated years
- Weighted pipeline
- $372,750 an average of 64 futures the year can never book
Six live pursuits, from a $90,000 university campaign at better than even odds to a $420,000 utility rebrand at 15%. Multiply each fee by its chance, add them up, and you get $372,750, which is the number on the new business slide and comfortably above the $350,000 target. Each pursuit is either won or lost, so each one is a two-point distribution: a zero with the odds of losing and a one with the odds of winning, wired into its own Won cell.
Click Run and three things come back that the weighted number cannot show you. First, the odds. The booked fee misses the $350,000 target in about 51 of every 100 runs. A forecast sitting $22,750 above target is a coin flip. Second, the shape. The median year books $330,000, the tenth percentile books $90,000 and the ninetieth books $715,000, with a standard deviation of about $250,000 on a $372,750 forecast.
There are exactly sixty possible booked totals in this pipeline and $372,750 is not one of them. The weighted pipeline is an average of sixty-four futures and cannot itself occur, which is why the simulated mean comes back at $372,750 exactly and tells you nothing you did not already know. Third, the tail nobody plans for: about 7 in 100 years book nothing at all.
Now the second run, and it is the one that changes how the pipeline meeting goes. The utility rebrand contributes $63,000 of the $372,750, which is 17% of the forecast, and it lands fewer than one year in six. Delete that row and rerun: the weighted pipeline falls to $309,750 and the chance of missing the target rises from about 0.51 to about 0.60.
Nine points of miss probability is what the long shot is genuinely worth, and it is a lot less than the slide implies, because a pursuit you win one year in six is mostly a way of making a forecast look bigger. What the model cannot tell you is timing. It books a win as if the whole fee arrives this year, and in practice a pursuit won in November is a small number this year and a big one next.
It also treats the six as independent, which is wrong if three of them go to the same procurement panel, or if losing the first one damages the credentials for the rest. To make it yours, replace the six rows with your own live list, set each win chance in the Risk Analysis panel from your own record rather than from the account director, and put your real target into the target line. The chance-of-winning column on the sheet is documentation only, so edit it to match whatever you set in the panel.
The model
It arrives on a tab called Template: Will the Pipeline Deliver, carrying these columns:
- Fee if won ($)
- Chance of winning
- Won (1 = yes)
- Fee booked ($)
with the model computed beside the data:
| Total fee if every pursuit landed ($) | 1,355,000 |
| Weighted pipeline, the number in the forecast ($) | 372,750 |
| Fee actually booked ($) | 1,355,000 |
| Pursuits won (count) | 6 |
| Misses the target (1 = yes) | 0 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Can you hold the cost per customer you promised?Will the Campaign Hit the Cost Per Acquisition?
- What would have to change for this channel to pay?What Conversion Rate Makes This Channel Work?
- The winner got more opens. Did it get more clicks?Which Subject Line Won?
- Is a pitch you probably lose still worth entering?Should We Pitch for This Account?
- Does the price cut earn back the margin it gives away?Will the Promotion Pay For Itself?
- How many invitations does sixty replies take?Pull the People to Survey
Every model like this one, and the method behind them: Monte Carlo simulation.