As you move from column to column, you have to use the value of the previous column compared to the current column. If using percent, it's 100*(Count at New Stage - Count at Prev Stage)/(Count at Prev Stage)
. You have to do that for each column.
You'll then see at which stage your users were having difficulty converting to the next stage. I've seen this report done in a number of different ways and this is just one variation. Some only look at comparing each stage to close-won.
Edit
It sticks in my mind that you may need to use (PrevValue - CurrentValue)/(PrevValue)
. Also, If you want them to sum to 100 percent, then you're barking up the wrong tree. You're looking at a totally different number or percentage here. If the last column of data is from your next to last stage
to closed-won
and you're comparing with it to having began for example, at prospecting
(that column being zero since none convert from a previous value or 100% convert from zero depending on how you view it), you'd need to factor this into account. The last column before your grand total would be (closed-won - next to last stage)/(next to last stage)
.
The Grand Total would need to factor in the total opportunities converted to closed-won, probably by multiplying everything by (closed-won)/(Prospecting)
, assuming every Opportunity begins at Prospecting
. I hope that makes sense. Otherwise, the final number can't be expected to total to 100% unless you somehow normalize your data. I've not run any numbers, but off-hand, I think that's the approach you'd need to take. If what you really want to know is which stage doesn't get converted, then a guage should tell you or any kind of a bar chart. You'll readily see the peak.
Let's see if we can create a simple example:
Opp Records by User
Prospecting | Estimating | Negotiating | Closed-Won | Total
4 | 2 | 2 | 1 | 1
6 | 4 | 3 | 2 | 2
2 | 2 | 2 | 1 | 1
--------------------------------------------------------------------
12 | 8 | 6 | 4 | 30
Stage Conversions:
Prospecting | Estimating | Negotiating | Closed-Won | Total
0 | 66% | 75% | 66% | 207%
Stage Conversions:
Prospecting | Estimating | Negotiating | Closed-Won | Total
0 | 66% | 75% | 66% | 207%
40% | 27% | 20% | 13% | 100%
^-- Factor for Total Records
0 | 18% | 15% | 9% | 42%
If you remove the 12 records in prospecting that account for 40% and don't include that in the total, you get numbers that look more like the following:
Stage Conversions:
Prospecting | Estimating | Negotiating | Closed-Won | Total
0 | 66% | 75% | 66% | 207%
0% | 44% | 33% | 22% | 100%
^-- Factor for Total Records
0 | 29% | 25% | 15% | 69%
As you can see, with this type of report, the numbers just aren't inclined to come out to 100% for you. They also show you that you can manipulate numbers to show pretty much whatever you want them to show. Be careful of how you interpret reports.