Guided project · 6 steps Cellwise Plus
Weighted pipeline and quota gap in Excel
Weight every deal by stage, roll it up by rep and see who will miss quota.
You will use: XLOOKUP, SUMIFS, MAX, COUNTIF
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Deal | Rep | Stage | Amount | Probability | Weighted | Stage | Probability | Rep | Quota | ||
| 2 | D-4001 | Ana | Won | 14000 | Prospect | 0.1 | Ana | 50000 | ||||
| 3 | D-4002 | Raj | Proposal | 22000 | Demo | 0.3 | Raj | 45000 | ||||
| 4 | D-4003 | Ana | Negotiation | 18000 | Proposal | 0.5 | ||||||
| 5 | D-4004 | Raj | Lost | 9500 | Negotiation | 0.75 | Win rate | |||||
| 6 | D-4005 | Ana | Demo | 26000 | Won | 1 | ||||||
| 7 | D-4006 | Raj | Won | 11200 | Lost | 0 | ||||||
| 8 | D-4007 | Raj | Negotiation | 16500 | ||||||||
| 9 | D-4008 | Ana | Lost | 6000 |
Build it step by step
Step 1
Look up each deal's win probability from the stage table in H:I. Write it in E2, fill to E9.
Step 2
Weighted value is amount times probability. Write it in F2, fill to F9.
Step 3
How much has each rep won? Use the rep name in K2. Write it in M2, fill to M3.
Step 4
Weighted value of each rep's deals that are not yet won, in N2. Fill to N3.
Step 5
Gap to quota: quota minus won minus weighted open, never below 0. Write it in O2, fill to O3.
Step 6
Team win rate: won deals divided by closed deals (won plus lost). Write it in L5.