Sales funnel is a very common business chart. Here is a simple bar chart based trick you can use to generate a good funnel chart to be included in that project report.
Download the Sales Funnel Chart – Excel Template and learn it by playing with it.
1. Get your sales data ready
The first step is to get your sales data ready for a funnel chart.
For this we take a normal phase-wise sales data and add a dummy series to it. The dummy values tell excel how far each of the sales figures should be moved away from y-axis to get the funnel effect.
The dummy values are calculated using a simple formula like =(some_large_number – funnel_size_at_that_phase)/2, In our case, I have used 150,000 for the large number.
We will also add a total column.
Tell me again, why are we using dummy series?
In order to create the funnel effect, we must move each of the bars in such way that they are all centered vertically. To do this, we take a large number and subtract funnel value from that and divide this by two. How it works ? well, that you can figure out while sipping coffee 😀
2. Create a simple bar chart with the columns Total and Dummy
Once you are done, the bar chart should look like this.
3. Now adjust bar overlap and gap width settings to create a funnel effect
Select the bars by clicking on them, and right click and go to format data-series. In the resulting dialog, go to options and adjust “series overlap” and “gap width” settings in such way that series are completely overlapped and gap is ZERO.
Once you do this, the chart should look like this.
4. Adjust colors and add labels
Select the dummy series and fill it with white color or make it transparent. Add data labels and you have a sparkling funnel chart ready.
Next time use this in your sales presentation to the boss and see everyone having a discussion.
Download the Sales Funnel Chart – Excel Template and learn it by playing with it.
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.