Timesheet & Billable hours calculation summary

[Mahesh5277]

Dear All,

I am working to develop a Timesheet for my team and would like to automate the extraction of the key KPIs on the Summary sheet.

the KPIs i am looking for are as below:
1. Weekly Summary for billable hours Servicewise along with the Team Leads (i.e. how many hours were filled on a respective service by the team lead working on that service)
2. Weekly Graph showing the above summary
3. Weekly Resource Utilization (target vs actual)- again distributed Service wise with associated team lead handling the service
4. Current Vs Previous Week comparison showing billable hours & utilization trend
5. Pie chart showing distribution of the hours expended ( i am trying to figure out, how the hours are utilized- i.e. i have categorized this scenario like - execution, meeting, follow up, review, rework, data study etc.

Given below is a snapshot of the Timesheet. I need help to develop the above KPIs on the 'Summary' sheet.

Thank you for your patience & support.

NOTE: Also, if any expert can guide me to develop 'On-time delivery' formula on this sheet, it would be great (issue is i dont have a defined end date for the tasks)
Also, for Resource Utilization, i need a formula which displays that the Utilization target is met, if the actual hours aare>=Target hours

Mahesh

Project Lead

Date

Week

Service Name

Service Group

Task Details

Task Category

Hours Planned

Hours Target

Hours Actual

Hours Actual

Hours Category

Hours Distribution

Peer Review Required

Peer Review done by

On Time delivery

Client Feedback

Right First Time Delivery

Resource Utilization achieved

Remarks

Lead-1

1-Jan-20​

1​

S-2

CRSF

something 1

WIP

9​

8.55​

6​

70.18%​

Billable

Execution

N

Positive

Lead-2

2-Jan-20​

1​

S-6

AAA

something 2

WIP

9​

8.55​

9​

105.26%​

Billable

Review

N

Lead-1

18-Jan-20​

3​

S-7

CCC

something 3

Deliverable

9​

8.55​

9​

105.26%​

Billable

Execution

Y

Chris

Positive

Lead-6

14-Jan-20​

3​

S-4

EEE

something 4

Capability Development

9​

8.55​

8​

93.57%​

Non-Billable

Capability Dev

N

Lead-7

10-Jan-20​

2​

S-6

CRSF

something 8

Deliverable

9​

8.55​

6​

70.18%​

Billable

Review

Y

Vikas

Lead-2

8-Jan-20​

2​

S-3

BBB

something 9

Milestone

9​

8.55​

5​

58.48%​

Billable

Review

N

 

[GlennUK]

Show a hand-crafted version of the summary based on that data. And tell us all what you've attempted so far.