Guest post by David Churchward [Twitter]

Pipeline Funnel Chart in Excel PowerPivot

Pipeline Funnel Chart

For those of you waiting on the explanation of the SUMX / SUMMARIZE measure in GANTT POST 2, I have to put you on hold for a while longer.  Sorry.  In truth, I’m working through some performance aspects with that measure.

For now, I thought I would take the opportunity to expose the Pipeline Funnel Chart.  It’s really quite straight forward but it’s incredible how few people know that it’s available to everyone, doesn’t require any real manipulation and YES it’s a standard PowerPivot chart!

For those of you that want to dig straight in, YOU CAN DOWNLOAD THE WORKBOOK HERE

What is the Pipeline Funnel Chart

I have to be honest here.  For me, Pipeline funnel charts are marginally more useful than pie charts.  I don’t mean to knock them because they’re kind of pretty, but in terms of true business insight, I just can’t see it!  That said, a proper CRM dashboard seems incomplete without them.

Where funnel charts have one up on pie charts is that there is a degree of perspective.  You can put pipeline funnels side by side and get some idea of scale as below.

Side by side CRM Funnel Charts in Excel PowerPivot

But the reality is…… IT’S A STACKED BAR GRAPH!

The idea is that you expose the value of all opportunities that you have in your CRM database, categorised by which stage they are at through the sales lifecycle.  Naturally, any company will lose opportunities or certain opportunities will simple come to nothing.  As a result, the lifecycle sees opportunities filtered down to real opportunities that ultimately become orders.

How It’s Done

I won’t labour the point here because everyone out there will pick this up in no time.

The Dataset

For the purposes of this example, I’ve kept the dataset simple.  However, remember that this IS a chart type that will work with PowerPivot data so knock yourselves out with any PowerPivot data set that makes sense.

CRM Pipeline Funnel in Excel Dataset

First of all, you’ll notice the slightly ghastly colour – this is the theme thing I’m coming on to a bit later!

My core dataset simply has 3 columns.  The Type column could be substituted with “Sales Stage” or something like that.  This is supposed to be an assessment of how far through the process an opportunity is.  For example, “Prospecting” is obviously a very early stage whereas Proposal and Negotiation are stages that suggest that the opportunity is probably about due to release “through” the funnel and hopefully become an order!

I link Type through to a separate table called Order as shown below.  On reflection, this was a bad name because an Order can result from the sales process.  What I mean is the order in which the Type is to be displayed on our chart.

Pipeline Funnel Order By Table

Pipeline Funnel Data Structure

The key to this is the Order field.  This needs to be ascending based on the first stage in the sales process through to the last.

In the PowerPivot Window, select the Order table and then select Home > Sort By Column.

Excel PowerPivot Sort By Column on CRM Pipeline Funnel Chart

With this done, we can be sure that the Type  field on our Order table will be displayed on our pivot in ascending order of the Order field.

Construct a Simple Pivot

With the dataset in place, we construct the most simple of PivotTables.

Pipeline Funnel PivotTable

You’ll notice that I have Type on columns from the Order table.  A simple [Sum of Value] measure supplies the values.

Now the Chart Bit

From your pivot table, select Insert > Column (from the charts options)

Create Pipeline Funnel Stack Cone Chart in Excel PowerPivot

Select the Stacked Cone Stacked Cone Excel Chart Typechart type (or Pyramid if you like).

Stacked Cone for Pipeline Funnel Chart in Excel

It seems a bit “upside down”.  That’s easily remedied.  Click anywhere on your chart and, from the Chart Options, select Layout > Axis > Primary Vertical Axis > More Primary Vertical Axis Options.

Select the Option Values in reverse order

Stacked Cone Step 2

And, there you have it.  A simple Pipeline Funnel report.

The only problem is the 3D effect.  It’s a bit much for me as standard.  To remedy this, right click the chart and select 3-D Rotation.

Stacked Cone CRM Pipeline Funnel Chart 3D Rotation

Adjust the X and Y rotation settings.  I tend to use 5 degrees for both.

Stacked Cone Step 3 3-D Rotation Settings

Apply some formatting of gridlines, apply a style (PivotChart Tools > Design) and the job is done!

Do you need PowerPivot for this?

The simple answer is no.  You can go ahead and create this as a normal pivot chart.  However, there’s a couple of things to consider:

  1. PowerPivot will control the ordering of your sales stages which, normally, aren’t alphabetical
  2. I’ve used a simple Sum of Value measure, but you’re likely to want to create something a bit more advanced against a whole list of opportunities from your CRM system.
  3. Your chart becomes fully sliceable and will work in context with the rest of your charts and reports.

Spice It Up with some Theme Adjustments

I wanted to throw this in because I didn’t realise how many people didn’t realise that it was there.  In Excel, you can adjust your theme and open up a huge range of ghastly and quite brilliant colours in equal volume.

The reason that I raise this here is because colour is somewhat important in the Pipeline Funnel chart.  If you don’t get enough colour diversity, everything starts to merge together.

Change Your Theme

To investigate some new themes, go to Page Layout and select the Themes drop down.

Excel 2010 Themes

There’s a load of great colour schemes.  I used the Oriel theme in this workbook because the different colours are quite distinct.  I don’t make a habit of using this one as it’s a bit “pink” for me,  but I’m trying to get used to it now that I’ve got a little baby girl!

Check out the Waveform theme on our Pipeline Funnel!

Ultimate CRM Pipeline Funnel Chart in Excel PowerPivot

Just a quick word of caution – if you change your theme, that change applies to the whole workbook, not just the chart you’re working on.  As a result, you could find that changes occur to charts you’ve already finished so please be careful.  The other downside is that your type fonts will also change.  In my opinion, some of the best colour schemes have the worst type fonts associated to them!  Go ahead and experiment, but it’s worth doing this work as up front before you get too far down the production process.

In Conclusion

This is without a doubt the least intense post that I’ve written.  And yet, looking back, it’s the most attractive!  Maybe I jumped to conclusions too quickly on the pipeline funnel.  At the end of the day it’s not going to give you a huge amount of insight, but it looks pretty good so go ahead and use it.  End users will thank you for it!