Best Excel Chart to show the lifecycle of a customer in the company database systems

[applesandpears]

Hi all!

I am a bit stuck. I have been working on a reconciliation project to find out where all our customers are for a certain product in 6 different systems from:

1. Sales team will put customers on as signed
2. Operation team will put in their system if its live or in production
3. 2nd Operation team will put in thier system if its production and the next two levels
4. Another team will enter when they will get their sims for the product
5. Another team will then put in their system when the product is delivered to the customer
6. Finally the last system will tell us if its been billed.

what i have done is I have I have done a v look up against the customer names and then brought back all the different phases and dates for the above steps - so now have one excel sheet with Customers on the left and different columns with the above status.

What I am stuggling is how to show in a chart and what chart? None seem to me to be able to show the process easily?

The whole lifecycle of the chart from 1-6 as above
To show the variances in the different systems eg if its showing in production in one system why is it not production in the system before? etc

I hope this is clear

I would love to hear some expert opinions and inupt

 

[Gerald Higgins]

Maybe you need some kind of Gantt chart ?
This isn't one of the built in Excel chart types, but it can be replicated using a horizontal stacked bar chart.

 

[applesandpears]

Hi Gerald,

How can this be replicated with Bar charts? I know excel very well but struggle with charts outside the box!
I could send you the excel file also?

 

[Gerald Higgins]

Please don't send me the file

At a very basic level, let's say you have two tasks that you want to plot on a Gantt chart.

Let's say one starts on 1st January and lasts for a week, and the other starts on 20th January and lasts for 2 weeks.

You need source data something like this

TASK...............START..........DURATION
A..................1/1/2013..........7
B.................20/1/2013.........14

Create a horizontal stacked bar chart, with 2 data series, one for START and one for DURATION.
Make the series for START have no colour, no border line, so that it's invisible.

This can then be adapted to be much more complex if you like, such as

TASK............START 1..........DURATION 1........START 2.........DURATION 2.......START 3....
A..................1/1/2013..........7.........................3....................14......................7........
B.................20/1/2013.........14........................0...................7........................7........

and so on.

 

[applesandpears]

hi Gerald,

Thank you.

The issue is that not all the processes will have dates and some overlap each other? How would I display that in the chart? Can this be done by putting the data in a pivot table?

Thabnks

 

[Gerald Higgins]

I'm assuming you know what a Gantt chart is, and I'm guessing a Gantt chart is appropriate for you, but maybe it's not, you'll have to decide that yourself.
If you DO want a Gantt chart, then pretty much by definition you have to apply dates.
You might not know all the dates for certain yet, in which case you can estimate dates, or leave items off the chart altogether, or put them only on the axis, without being plotted with bars.

In terms of overlapping processes, it would probably be best to show them on separate rows. It is possible to overlap them directly in Excel, but this can be complicated.

Another option - don't use Excel at all, use a tool designed for creating Gantt charts such as MS Project.