Project Utilization Chart in Excel

[gkines7]

I have a software team that is being assigned too many tasks/projects per week, but we're having a tough time communicating this to upper management so we can help throttle the workload demands. I'd like to do a utilization chart, but had some questions:

To set up, we're only measuring 4 variables against some standard average per week. The variables are Week; Task Category (A, B, C or D); Resource (person); and Hours Spent. I would attach a sample Excel table, but can't find how to attach files here since I'm new. However, I pasted sample data below for reference. The bar chart (or something similar) would have each bar representing a resource and the sections of the bar colorized according to Task Category A, B, C, D. The Y-axis would be time in hours. Then the Standard Average might be a line going across each individual bar.

In addition to the help I needed to create the chart, I wasn't sure how to come up with that standard average. Should I use a weekly average of the person's time the year or month/week before? In this example, every individual resource would have a different Standard Avg., so I don't know if that would be ideal. Or is it better practice to just use portion of an 8 hr. work day (say 6.5 hrs.) since regardless of project/task or priority, we'd want to know what's keeping our team members at work beyond 8 hrs. per day (or 40 hrs. per week)?

Thanks in advance for any help.

GK

Week	Task Category	Resource	Hours Spent
7/4/2014	Task Cat A	James	7
6/20/2014	Task Cat B	John	2
6/27/2014	Task Cat C	Chris	0.5
7/4/2014	Task Cat D	Steve	0.5
7/4/2014	Task Cat A	Sarah	1
7/4/2014	Task Cat B	Nick	0.5
6/20/2014	Task Cat D	Sarah	4
6/27/2014	Task Cat C	Chris	4
7/4/2014	Task Cat A	James	7
6/20/2014	Task Cat B	John	2
6/27/2014	Task Cat C	Chris	0.5
6/20/2014	Task Cat D	Steve	0.5
6/27/2014	Task Cat A	Sarah	1
6/20/2014	Task Cat B	Nick	0.5
6/20/2014	Task Cat D	Sarah	4
6/27/2014	Task Cat C	Chris	4
7/4/2014	Task Cat A	James	7
7/4/2014	Task Cat B	John	2
6/20/2014	Task Cat C	Chris	0.5
6/27/2014	Task Cat D	Steve	0.5
7/4/2014	Task Cat A	Sarah	1
7/4/2014	Task Cat B	Nick	0.5
6/20/2014	Task Cat D	Sarah	4
6/27/2014	Task Cat D	Steve	0.5
6/20/2014	Task Cat A	Sarah	1
6/27/2014	Task Cat B	Nick	0.5
7/4/2014	Task Cat D	Sarah	4
6/20/2014	Task Cat C	Chris	4
6/27/2014	Task Cat A	James	7
7/4/2014	Task Cat B	John	2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>