Roughly the 3rd week of every month, we do strategic planning for clients. This is where we look at the data from the previous three months to determine what's been working and not working. 

The SEO and Paid Media Team Leads will look over rankings, traffic, inquiries, and conversion percentages. Graphics and Content will look at workflow, productivity, and what content they think clients would benefit from moving into the next month.

We then have a half day meeting to go over and discuss how to distribute projects and hours across projects and initiatives. The goal is always conneting patients to quality care. If we think spending more time on SEO vs paid media or content vs graphics, then we make those decisions and move client hours around accordingly.

The Monthly Planning Sheet

The monthly planning sheet, found here, is our main guide for tracking and discussion. We do a new one for each month, but use the same template.

We then go through each client and decide what to produce or where to focus the team's efforts.

Some things are always a part of a client's workflow, like social media posts or newsletters. Others, we may change up every month. A Project Manager or VP will lead the discusssion. The below is for their reference and you do not need to prep the sheet unless you are leading the conversation for that month.

Step 1: Copying the Sheet

• Navigate to the Operations folder on the shared drive

• Open the “Projects & Planning” and then the “Hours and Planning” folders

• Within this folder, find the “Client Hours Report” template sheet - direct link

• Right-click on the thumbnail for the file and then click “Make a Copy”

• Open your new copy and rename it according to the standard naming convention “Client Hours Report - Month Year”

Step 2: Prepping the Sheet

• Navigate to the “Hours Sheet - Total Look”

• Adjust cell A1 to the current month

• Verify the budgets in column B are correct

• Double-check that the “weeks in a month” area in cell N1 is correct

• Friendly reminder: a week is part of the month with the “majority of the days”

• For example: if a week has November 28th - December 4th, two days of the workweek are November while 3 days are December therefore that week is technically part of the month of December for planning purposes - this is most important for content planning.

• Navigate to the “Hours Sheet - Department”

• Adjust cell A1 to the current month

• Adjust cell L4, the “weeks in the month” cell to match the weeks in the month from the prior sheet

• Verify the “Total Hours by Department Across All Clients” table is correct

• Double-check that the formulas in the G column are “Number of Employees on the Team” * 40 hours per week * “Weeks Per Month” (L4) + Managers time dedicated to clients (varies per department)

• Review column H to double-check the formula reads “GXX * 0.8”

• Copy and paste the “Planned for the Month” column from the Monthly Planning Sheet.

• Update the “Website Build Hours” to match the Monthly Planning Sheet

• Update the “Hours Allocated for Meetings” to be accurate for teams

• The formula is: 1 hour per meeting (unless otherwise determined by a team lead) * “Number of team members” * “Weeks Per Month” (L4)

• Update the “Total Hours Contracted & Planned” table with the contracted hours from the Monthly Planning Sheet 

• Only adjust cell F36 in this table, the rest is pre-made formulas

• Repeat this for the “Removing Paid from the Equation” table, only adjusting F43

• Go through column B for each client and department to list the planned hours

• This is easily copied and pasted from the Monthly Planning Sheet by navigating to each client’s planning sheet in cells J31-J35

Step 3: Using the Sheet

• Once per week, update the MTD hours in column C with Hive Tracking

• Open Hive and this sheet

• Navigate to “Time-tracking” in Hive

• Click on the small “spreadsheet” icon that is found to the left of “Custom” in the Time-tracking toolbar area (it reads “Reporting” when you hover over it)

• When you open reporting, you will need to specify dates and what you want to be displayed

• Friendly tip: select only “Hours” in the display area, specify the time period as the week you are tracking.

• Find the hours spent per team

• To find the hours spent per team, you will need to be familiar with the team members for each department 

• (It may be worth creating a spreadsheet or list for this yourself as right now Hive will not sort by the team here), you will then find out how many hours were spent by the client by each team member by selecting the drop-down arrow to the left of the client name in the chart. 

• You will see a display like the one below. From this display, you will be moving the numbers to the spreadsheet

• It is easiest to do this if you create a formula and then manually type in the amounts for each team and team member.

Do this by typing = XX + XX …. 

Anytime you start with = then Google sheets will start a formula for you

• Note: the sheet is set to round to the nearest whole number so when you put in the decimal points within Google Sheets, it will automatically make it a whole number, this is helpful for the charts in the spreadsheet.

Planning - Monthly Planning Sheet

Step 1: Copying the Sheet

• Navigate to the Operations folder on the shared drive

• Open the “Projects & Planning” and then the “Hours and Planning” folders

• Within this folder, find the “Monthly Planning” template sheet - direct link

• Right-click on the thumbnail for the file and then click “Make a Copy”

• Open your new copy and rename it according to the standard naming convention “Month Year - Monthly Planning Sheet”

Step 2: Prepping the Sheet

• Open the sheet and verify you are on the “Total Hours by Dept”

• Double-check that the “weeks in a month” area in cell F17 is correct

• Friendly reminder: a week is part of the month with the “majority of the days”

• For example: if a week has November 28th - December 4th, two days of the workweek are November while 3 days are December therefore that week is technically part of the month of December for planning purposes - this is most important for content planning.

• Verify the “Total Hours by Department Across All Clients” table is correct

• Double-check that the formulas in the C column are “Number of Employees on the Team” * 40 hours per week * “Weeks Per Month” (L4) + Managers time dedicated to clients (varies per department)

• Review column D to double-check the formula reads “CXX * 0.8”

• You will copy and paste the “Planned for the Month” column from the monthly planning sheet for use in the Client Hours Report sheet (Hours Sheet - Department cells I12:I16).

• Do not touch the E column of the table unless the cell reads “#REF”, this means that a sheet or cell used in the formula is missing. 

• In the event this happens, verify that you have not deleted any client sheets and if you have, look at the formula and remove the piece of the equation that uses the missing sheet. 

• Do not touch the “Total Hours Contracted & Planned” unless the cells read “#REF” then do the step above. You will use these numbers in the Client Hours Report.

• Add any upcoming website build projects in the “Website Build Hours”, you only need to add the number of projects in the pipeline in column C, column D will auto-populate hours for you based on our company averages.

Step 3: Prepping the Client Sheets for Next Month’s Planning

• Each client has the same setup in their sheet

• Prior to the planning meeting the “Hours Breakdown”, “Notes for the Month” and “Suggestions of Projects…” will need to be updated.

• Update the “Hours Breakdown” table

• Verify the “Hours Budgeted by Contract” cell J3

• Open the Client Hours Reports for the last 2 months as well as the current month and add their client hours together to find the Hours spent MTD, cell J4

• Again, Do this with a formula = XX + XX + ….

• This allows the formula to work that is in cell J5. This formula is: (Contract Hours * 3) - Time spent in the last 3 MTD

• The line in orange, cell row 6 is calculated with the table to the left and it will update as you check off boxes during the planning meeting

• Verify the “Notes for Month” and the “Suggestions” boxes are either empty or up to date with the Department Head suggestions.

• Once you are finished doing this for all clients, send it to department heads and leadership so they can update the sheet in prep for the meeting. 

Step 4: Using the Sheet

• First, review the time available per team with the team in the meeting

• Second, review the time spend current MTD with the team

• Move into planning each client from largest “Hours to be Spent” to smallest

• Friendly tip: you can click and drag sheets into any order you’d like at the bottom of the Google Sheet, drag them into the order in which you want to plan them in the meeting

• Allow the team leads to discuss what each client needs, check the boxes in the D column next to tasks determined to be done in the upcoming month. The sheet will use its formulas to update the relevant cells. 

• Keep an eye on the time planned for the coming month in cell J6, do not go over the budget by contract (unless the client was under planned or underspent in past months), attempt to get as close as you can to the time in cell J5

• The formulas will also update the “Hours by Department” chart, this is most helpful for copying into the “Hours Sheet - Department” sheet in the Client Hours Report Google Sheet for each client in column B

Meeting Follow-up: Action Needed

Following the meeting, each Department Head will then need to put their respective action cards into Hive. See the video training below as an overview of how to do that as well as ensure your projects are moving along in order to meet deadlines. 

Finally, here is the link to the Project Management folder.