Team's Productivity

This is under Lance to advise worksheet in the report google sheet. To get the data to this tab, you need to update first the excel spreadsheet here. If you are doing this on July then you will need to update the file name: 20240701EOMJUNE_SS Time Analysis_2024-25. File names are examples for April, May & June below. 

1. Open the file.

2. Make sure that you are in the correct Worksheet. See sample below:

3. Sort the data first in the file. You need to highlight all the data above the Company Target as show in the picture below to sort it.

4. Go to Data in the excel file menu bar above

5. Click Custom Sort and follow the picture below and OK:

6. Go to Client Unders & Overs.

7. Go to Monthly Service Plans.

8. Check the Virtual Hours Remaining for each client and look for any hours that is 1hr and more that is highlighted in Green only. For example the picture below shows Absolute Upholstery Wgtn Limited and the Virtual Hours Remaining is -4.32.

Note: Do not touch any client that has a comment in the line in it and that says 'Roll unused time'

9. Scroll to the right to see the data: Portion ($), Virtual Hours Used, & Spent ($).

10. Highlight orange for the Spent ($) column for each client that is mentioned in step #8.

11. Divide the data in the Portion ($) by Virtual Hours Used. So it is column O/P. The result for example shows like this: O/P = 120.8955223 only copy the last 7 decimal digits.

12. Go to the data that you sort in step #3 and search the client in there. Paste the result in column N. 

13. Go to column O and add this formula, multiply the: column N*G

Please see sample screenshot below:

14. Go back to the Client Unders & Overs. You will see that the computation you made is correct if the Virtual Hours Remaining is 0. And the Portion ($) & Spent ($) columns are now match the amounts. Please see sample screenshot below: 

Note: Please do the same process to all clients as mentioned in step#8 except for Fixed Fee Projects & Flexi Plan Client Usage.

Part 2: Filling up the Savvy Monthly Stats excel spreadsheet

15. Open the Savvy Monthly Stats excel spreadsheet. To get the data in there, go back to the excel spreadsheet file that you have just updated which the above ^ one. 

16. Open the file again. fFle name: 20240701EOMJUNE_SS Time Analysis_2024-25.

17. Go to Company Target section of the file as show in the picture below:

18. To get the #1 data which is Each Employee Total Productivity including delegated hours in the Savvy Monthly Stats excel spreadsheet. Go to "Billable % (w delegated)" - which is in column T and record the date ask for each employee. 

Please follow the step in #18 for the rest:

• Each Billable Percent - VA Global Team Members Productivity - Go to "Billable % (paid)" -  column Q

• Each Billable Percent - VA NZ Team Members Productivity - Go to "Billable % (paid)" -  column Q

• Each Billable Percent - Creative Global Team Members Productivity - Go to "Billable % (paid)" -  column Q

• Average Billable $ Rate Across Teams - Go to "Average Billable Rate "- column X

• Total monthly revenue from contractors in $ - Go to Total Billed - column Y

• Total monthly profit from contractors in $ - Go to Column AA and put this formula: Total Billed minus Actual cost -  column  Y-AJ

• Total Creative Revenue - Go to Total Billed - column  Y

Enter all the data gather into the Savvy Monthly Stats excel spreadsheet.

19. Copy & paste the date to each respective graph in Lance to advise worksheet tab in here.

20. Update the Format Cells to General for all the data entered in the Savvy Monthly Stats excel spreadsheet.