Sales Cycle Chart

[Guest]

I need to create a Stacked Bar Chart showing a client's progress by month through our Sales Cycle. Ideally this would be
Pivot Chart. The x-axis should show Jan '03, Feb '03.; the y-axis showing any given account (or a few accounts); and the data would be the stage in the Sales Cycle (initial contact,..., closed deal). I have 7 stages of my Sales Cycle: Initial Contact, Suspect, Pipeline, Forecast, Verbal, Won and Close; each stage has a corresponding date in which the client entered that stage. Obviously, each client has an Initial Contact date; however, as the Sales Cycle progresses, there are fewer and fewer clients in each stage. So, for example, ABC Co has initial contact 01.01.03, moves to Suspect at 03.01.03, to Pipeline at 09.01.03 and is still at that stage today; XYZ Co. has initial contact at 04.01.03, moves to Suspect at 06.01.03, to Pipeline at 08.01.03, to Forecast 09.01.03, to Verbal 10.01.03, to Won 11.01.03 and Closes 01.01.04. We have 100 or so clients in several vertical markets, all having different dates associated with the Sales Cycle stages they have progressed through. I need a way to chart this information looking at either a few accounts at a time or by vertical market.

Any help I can get w/ this is greatly appreciated!!

 

[Jon Peltier]

Kim -

If it's a stacked bar you want, then you plot the Initial Contact as a
date, and the rest as a number of days since the previous item. You
could use a pivot chart, but they aren't very flexible, so I would just
use a regular chart, and I wouldn't bother to pivot it, I'd use a
filter. I'd put the data into these columns:

Plot Market Company Initial Suspect Pipeline Forecast Verbal etc

where Plot is either 0 or 1 (or False or True), for whether to plot that
company, Market is the vertical market, Initial is the initial contact
date, and the other columns are the days as discussed before (other
columns further to the right may hold the dates and these columns have
formulas that subtract them). Make a stacked bar chart with all the data
(I know it's crowded), then set up an autofilter, and filter either on
1s in the Plot column, or Show All there and filter on vertical market.
The hidden rows are by default not charted, so you control the chart by
the autofilter.

- Jon