2a / How To Edit & Refine The Delegated Hours Spreadsheet - EA/Bookkeeping

Editing The Delegated Hours Spreadsheet

The following steps show you how to edit and refine the data you have entered from the Detailed Time Report into the Delegated Hours Spreadsheet. You must follow this process in order otherwise the data may get skewed and you will not have an accurate outcome when you add the calculations at the bottom.

1. Select all data (Ctrl + A)

2. From the ribbon at the top of the spreadsheet select Top Align and Align Left from the Alignment menu options

3. Select the Project Code column (D), right-click and select Cut. Click on the Billable column (H), right-click and select Insert Cut Cells

4. Rename the Project Code column as Delegating VA and resize the Column Width to 15

5. Select the Task column (now column D). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears

6. Go through and delete all rows where the task IS NOT _Virtual or Tasks from Rochelle ie. Admin; Annual, sick or bereavement leave; Downscaled time (office use only); IT Issues; Team Meetings; Training; Travel to clients; etc are all deleted

7. Once you confirm that only _Virtual or Tasks from Rochelle rows are left, select the Tasks column, right-click and select Delete

8. Select the Notes column (now column D), right-click and select Column Width. Change it from 255 to 40 and click on OK

9. Select the First Name column (H). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears

10. Delete all rows that have the First Name of a NZ team member or NZ contractor - this will leave you with lines for only the Global VA team, who at this time are Ashley, Dee, Jamie, Kai, Nicole and Shiela (EA/Internal), and Ginna and Judy (Design). The Design team will only have their time included if they have done a task that the delegating VA could have done themselves ie is NOT a design task

11. Select the Project column (C). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears

12. Select the Client column (B). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears

13. Delete all the rows for design-only Clients: eg Adrenalin Consultants, Jessica Setu, Rawleighs, etc

14. Delete all the rows that have the following in their Project Name: Internal Marketing or Creative Services

15. Check the notes of the lines leftover and delete any rows that refer to design work - these are tasks that a VA COULD NOT have done themselves eg website updates; logo creation; video editing etc

16. Delete the Billable? column (G)

17. Select the Invoiced? (now G) column, right-click and select Clear Contents. Resize the Column Width to be 6

18. Select the Last Name (I), Roles (J) and Employee? (K) columns, right-click and select Delete

19. Select the Cost Rate (K), Cost Amount (L), Currency (M) and External Reference URL (N) columns, right-click and select Delete

20. Select all the cells underneath Delegating VA (column F) down to the last line of notes. Select Fill Color from the editing menu options, and under Standard Colors pick yellow

21. Select the next free cell at the bottom of the Hours column. Select AutoSum from the editing menu options, then hit enter on the keyboard

The Delegated Hours Spreadsheet should look something like this at this point:

Once you have completed editing the spreadsheet, you are ready to refine it and check the VA assigned to each of the highlighted boxes. This should mostly be pre-populated from the Project Code in Harvest, but we always do a final check to make sure it is accurate.

Use the Client & Staff Register in Airtable to check who the VA for each client is if you are unsure. Note that some clients may have a Head VA as well as a Bookkeeping VA, so often there are multiple timers set up under a client so we easily distinguish whether the task was assigned by the Head VA or by the Bookkeeping VA.

Remember: If you are unsure of who the delegating VA is, ASK - either the head VA for the client, or their Team Leader if they are not available at the time.

Refining The Delegated Hours Spreadsheet

1. Assign a VA to each of the highlighted boxes in the Delegating VA column

2. Go to a previous month's tab and scroll to the bottom of the page

3. Select the cells that have the Month, Delegated and Available, and the list of team member's names (as shown in the pic below) and copy (Ctrl + C)

4. Go back to the tab for the month you are editing

5. Click on the 3rd empty cell down in column A, beneath the data pasted from the Detailed Time Report (this will differ each time depending on the amount of data brought over from that report)

6. Paste the data (Ctrl + V) into that cell

7. Change the name of the month to the one you are editing for

8. Check that the sum at the bottom of the Hours column matches the sum at the bottom of the Delegated column that you brought over from the other tab (as highlighted in the pic below). If it doesn't match, check the formulas in the section you brought over to make sure they include all of the rows above it

9. Once the hours match you are ready to save the spreadsheet and post the numbers to the team.

The bottom section of the Delegated Hours Spreadsheet should now look something like: