Resource allocation and utilization

[Mayfarm]

Hi,

I have been asked on a high level to show my resource utilization for my department. The tool has to be used by others than myself.

We do not want to register hours etc., but just track allocation on a quarterly basis by inputting the percentage each resource is allocated to what activities.

The data input table is as below. So as illustrated an employee can be allocated more than 100%.

Name	Activity type	Task	Quarter	Allocation
Jim	Project	Win10 project	Q1	0.5
Jim	Operations	Desktop	Q1	0.7
Jim	Project	Sharepoint Upgrade	Q1	0.5
Allan	Operations	Desktop	Q1	0.7

<tbody>
</tbody>

My plan was to show it in a bar chart (Using Pivot table-chart), that could then illustrate the utlilization of employees. However, if he/she is not (like Allan) 100% allocated we need to be able to see his Free time. Of course the easy way would be to add another row for each employee and manage it manually, but I was hoping Pivot could somehow help me with it. Any ideas/suggestions?

Thanks

Best,
Peter

 

[oldbrewer]

Name	Activity type	Task	Quarter	Allocation
Jim	Project	Win10 project	Q1	0.5
Jim	Operations	Desktop	Q1	0.7
Jim	Project	Sharepoint Upgrade	Q1	0.5
Allan	Operations	Desktop	Q1	0.7
							freetime
					jim	1.7
					allan	0.7	0.3
					a simple sumproduct table

<colgroup><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>