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

[TABLE="width: 423"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Week[/TD]
[TD]Task Category[/TD]
[TD]Resource[/TD]
[TD]Hours Spent[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat A[/TD]
[TD]James[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat B[/TD]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Steve[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat A[/TD]
[TD]Sarah[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat B[/TD]
[TD]Nick[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Sarah[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat A[/TD]
[TD]James[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat B[/TD]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Steve[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat A[/TD]
[TD]Sarah[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat B[/TD]
[TD]Nick[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Sarah[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat A[/TD]
[TD]James[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat B[/TD]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Steve[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat A[/TD]
[TD]Sarah[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat B[/TD]
[TD]Nick[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Sarah[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Steve[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat A[/TD]
[TD]Sarah[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat B[/TD]
[TD]Nick[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat D[/TD]
[TD]Sarah[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]Task Cat C[/TD]
[TD]Chris[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]Task Cat A[/TD]
[TD]James[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7/4/2014[/TD]
[TD]Task Cat B[/TD]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]