Help in producing employee capacity pie-chart

[stusam]

Hi.

I'd like guidance on creating a data sheet with linked pie-charts that reflect employee capacity, to help me see at a glance who has/hasn't capacity to take on further projects and who is at maximum capacity. I'll try to break down my requirements:

• Each project would be assigned a point value between 0.5 and 3, with the former being a small project and the latter being a large project.
• The maximum capacity would be a point total of 9.
• There should also be the option to add/subtract points where there are mitigating factors affecting the size of the project.
• Ideally, I would like the pie chart to change from green, to amber to red depending on how close to capacity each employee is.

I have some basic grasp of formulas but would probably need most things spelling out. I've mocked up and attached a very rough prototype to to try to support my dodgy explanation.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Douglas Quaid
2	Project title	Points	Additions	Deductions	Total
3	Rocket science 101	3		0.5	2.5
4	Baking for beginners	1	1		2
5	Social media management	2		0.5	1.5
6					6
7
8	Alex Murphy
9	Project title	Points	Additions	Deductions	Total
10	Brain surgery for dummies	3	0.5		3.5
11	Productivity	1	2		3
12	Advanced marketing	0.5			0.5
13	Product design	2			2
14					9
15
16	Kyle Reece
17	Project title	Points	Additions	Deductions	Total
18	Innovation in design	3		1	2
19	Film production	1			1
20					3
21
22
Sheet1

Cell Formulas
Range	Formula
E3:E5,E10:E13	E3	=B3+C3-D3
E6	E6	=E3+E4+E5
E14	E14	=E10+E11+E12+E13
E20	E20	=E18+E19

Thanks for any guidance anyone can provide.

Stuart.