10 / Reset The Time Analysis Spreadsheet

The following changes need to be made every month in the Template tab of the Time Analysis Spreadsheet, once invoicing has been fully completed for the month before.

Remove Any Clients That Are Leaving/Exiting

Find the clients line in the Time Analysis Spreadsheet > Template tab
Clear the following cells:

Client Name
Plan (Hours)
Plan ($)
Virtual Rate
On-Site Rate
Carry Over

Important Note: NEVER DELETE A ROW. The spreadsheets are full of formulas that will be affected if you delete a row/s. Instead we simply clear the cells, and copy and paste remaining rows around to fill in any gaps left.

Remove Any Clients That Have Gone From Service Plan To Flexi Plan

Find the clients line in the Time Analysis Spreadsheet > Template tab
Clear the following cells:

Client Name
Plan (Hours)
Plan ($)
Virtual Rate
On-Site Rate
Carry Over

Add Any New Service Plan Clients

Create an empty row for the new service plan client in the Time Analysis Spreadsheet > Template tab. To do this you may need to copy and paste multiple other rows around so that the list remains in alphabetical order
In the applicable cells, enter the following details for the client:

Client Name
Plan ($)
Virtual Rate
On-Site Rate
Carry Over (if applicable - this is at the discretion of the General Manager, and isn't usually used before the client starts, but is often used to carry over time from one month to the next after the client has started)

Add Invoiced Figures From The Month Into The Template Tab

In the Time Analysis Spreadsheet > Template tab, open the sheet for the month you have just invoiced
In the Client Unders & Overs section, scroll across to the Invoiced and Balance columns at the far right - AG and AH

3. In the Invoiced column, enter a zero into the cells of clients:

That are under hours for the month (these lines will show as green to the left of this column - refer pic below); or
Whose over hours are going to be rolled into the next month (a note with be added to the Carry Over column for these clients)

NOTE: There will already be some figures in this column from when you were creating the invoices for clients who were over their plan hours in Step 7 of the EOM invoicing process.

4. Once each line has a figure in it (either $0.00 or from an invoice total), select all of the cells in the Balance column

5. Right click on the mouse and select Copy from the drop-down

Steps4 & 5:

6. Go to the Template tab and select the first cell of the Balance from last month column - column V

7. Right click on the mouse and under Paste Options select Values (123 clipboard symbol). It is important to select this option so it will paste the values only and not affect any of the formulas that are in the template

Note: Make sure to check that the values you have brought over are on the correct client line, because if there have been new clients added to the Template tab, the values may transfer over onto an incorrect line. Move them around by either copy and pasting as per steps 6 & 7, or by manually keying in the amounts as required to ensure they match appropriately

Steps6 & 7:

Clear Old Notes In The 'Plus Approved Hours' Column

In the Time Analysis Spreadsheet > Template tab, in the PLUS Approved hours column, go through each cell of the Service Plans ($) clients section and clear any notes that do not apply to the new month

Update The 'Carry Over' Column

In the Time Analysis Spreadsheet > Template tab, in the Carry Over column, hover over any cells that have a 'Y' in them to show the note.

If a note says being carried over between particular months and still applies, move to the next cell with a 'Y' in it
If a note says only being carried over between particular months and no longer applies, then you can delete the note and the 'Y' from the cell
If there is a note in a cell with no 'Y' in it, but says pre-approved to XX hours if required, leave it as is. If the client is consistently using the pre-approved hours, then this may need to be brought up with the Account Manager, and the client's plan may need to be increased

Google Sites

Report abuse