A really good tutorial can be found here on how to create “clustered-stacked column charts”. It works very well in Excel 2007 also. The result looks something like this:
Clustered-stacked column chart
It took me a while to find how to accomplish this, I googled different variations of “Excel 2007 series values” minus, negative, stacked, “excel 2007 axis scale”, “same secondary scale”, “negative bar color”. I even attempted scripting it, but with no luck.
This chart is used to illustrate why a company is not necessarily profitable, despite that it looks that way first. The profits exceed the costs at a first look (above the zero), but adding costs for personnell and operating expenses, the actual result is negative, as seen above where the multi-colored bar (expense types) is longer than the blue (income).
I’m a little confused. It looks like both income and cost span the Y=0 axis. A clearer approach might be like a waterfall, where the income starts at zero and extends upward, then the costs start at the top of the income bar and extend downward.
The customer wanted the visualization to be done this way (so he could explain to his customers), but thank you for your input! :)