MS Excel: How to create a modified stacked bar chart for consumption data, with roll-over?

Question

I am trying to create a visual representation of Service Hours for a customer.

The customer contracts with us for 1+ years. When they purchase a service package, they buy a number of hours that they can "spend" to work with our Professional Services, Solutions Architecture, etc teams. Those hours are allocated annually and unused hours roll over.

Example:

Customer under a 3 year contract purchases 180 hours, 60 hours per year. Last year they billed 43 of 60 hours, rolling over 17. This year they have billed 37 hours, consuming the 17 roll-over hours and 20 hours from the current year. 30 hours have been scoped for outstanding projects this year and 30 hours have been scoped for projects planned for next year.

The simplest descriptor of what I'm trying to accomplish is a stacked waterfall chart. Part stacked bar (X of Y hours consumed) and part waterfall (Year 1, 2, 3 etc as components of the total purchase).

The excel workbook I've developed for this 3-year contract looks like this.

I would like the resulting visualization to look like this: Service Hours Consumption Report

I haven't been able to accomplish this via stacked bar charts, even deleting/hiding bars, but its possible that I might need to restructure my data and it would work that way. I'm hoping this forum can provide some guidance, so thank you in advance.

Context for comments/solutions: Data with columns added

Answer 1

As with almost anything, Excel can chart it, but it relies on the data being correctly organized. In your example, you have 3 bar charts (one per project year) so you'll need 3 columns of data. You also have 6 data series (even if you don't think you do), so you'll need 6 data rows. Here's how I organized your data

and the resulting chart

The left gaps on years 2 and 3 are from the prior used row and are formatted with no fill/no border.

If you want the used bar to be split between carry-over used and used in the assigned year, you'll need two rows. If this isn't important, it's probably easier to have a single column, but that's dependent upon your project requirements (it's clearly possible).

Also, there seems to be a redundancy/artificial distinction between roll-over and unused-they could be combined (again, unless your project requirements need them separated).

Here's two more versions of the same data with slightly varied designation of rollover and unused. The main advantage is that there's fewer data series to manage, but you lose some fidelity as to what the specific data categories are.