chart with multiple data analysis

[peter vh]

I want to create a chart to analyse multiple date selections:
the X-axis should plot the time
the Y-axis should plot the volume

in the chart itself I want to see the forecasted volume per product, but
split out in actual sales and prospected sales.
thus per product (A, B, C, etc) I would like to see a bar per month,
representing the total forecasted volume.

this bar should be divided into 2 data fields (actual sales and prospected
sales)

does anyone know how to do this? Or is it even possible? I have been trying,
but cannot get it right. it feels like the data I have in the excel sheet
should be sorted or represented differently, but don't know how.

thanks in advance!

 

[Del Cotter]

I want to create a chart to analyse multiple date selections:
in the chart itself I want to see the forecasted volume per product, but
split out in actual sales and prospected sales.

There's quite a large literature on choosing the best information
graphics for a particular task. Often the best choice depends on the
details of the data, not just the layout, but fortuitous features of the
actual values (whether one is much greater than the others, etc.)

How many products are there?

It occurs to me that one technique you might find useful is the *small
multiple*, many small copies of the same chart, each showing a single
product, plus perhaps another small chart showing the total volume. The
idea of making them small is so that the reader's eye can take them all
in in a single glance: separate pages or screens is no good, and even
the whole of one page can be too big. Little graphs can be surprisingly
effective.

Which of several ways to choose to do this depends on your skills. Are
you comfortable lining multiple graphs up using the Drawing tools like
sizing and alignment? Then that's one way to do it. Alternatively you
can design a "panel chart" in Excel that is one chart with several
artificial lines and scales drawn in. (Google "Excel panel chart")

thus per product (A, B, C, etc) I would like to see a bar per month,
representing the total forecasted volume. this bar should be divided
into 2 data fields (actual sales and prospected
sales)

Is one always less than the other? Then you can use stacked bars. But if
actual might be either more than prospected or less, then lines might be
better.

Color choice can help a lot with either sort of arrangement. Because you
have n times 2 series, you can have the actuals for A, B, C be red,
blue, purple, green etc., and the prospected can be the same colors but
a different shade: pale red, pale blue etc.

 

[peter vh]

thank you Del.

I've created 7 small charts with stacked bars (1 per product). it does the
trick (giving an overview on the relation between prospect and actual sales),
but graphically it is not that great.

I will investigate the panel chart option to check if that might enhance the
visual image.