ABCDE
1
2
3
Overview
4
- This template contains dummy data for illustrative purposes.
5
- Make a copy of this Google Sheet to start tracking and analyzing your team's projects and needs.
6
- Blue font color indicates a numeric input
7
8
Tabs
9
- Cover: Note down any additional details (e.g. last date of update, clients, major projects) in case you need to revisit your planning in a few months
10
- Project Detail: Track all your projects across all the teams/clients, input wage rates for your freelancers and hours needed for each project, evaluate $ savings with full-time conversion
11
- Timeline Allocation: Allocate your expenses across the years. In this template; this is done by each quarter (e.g. Project B for Team A will happen across Q1 and Q2)
12
- Budget: View total costs by month across the next year, evaluate which team / client is the highest cost to assist with invoicing/pricing as an example
13
14
15
16
Step-by-step
17
Project Detail Tab
18
(1) Input all the contracted roles + projected roles that you need for all your projects in column C.
19
(2) Input pay by hour for each role in column G. This rate will automatically populate in the project breakdown below.
20
(3) Input forecasted annual salary if the contracted role were to be hired full-time in column I.
21
 - If you envision having 2 senior brand designers, for example, then you would put the annual salary x 2.
22
(4) Start mapping out all your projects by team. Let's say Team A is your internal growth marketing team. After discussion with the team, you forecast that they will have brand and design needs for their ads, website, virtual events, SEO initiatives, and outdoor campaigns. With each corresponding project, you allocate certain contractor roles and note how many hours they will spend on it across the project duration (column I)
23
 - When inputting the hours in column I, this is summed up in the initial rows, so you can get the total # of hours they worked in the year across all projects and clients
24
(5) Denote which quarters / time frame each project will occur in column B.
25
(6) Repeat this process for each of your teams. For example, if team B was your product marketing team, their projects will differ from growth marketing and may require brand/design help on product launches, content, customer videos, etc. You may copy and paste Team C and D as a starting point when adding additional teams/rows to the sheet.
26
(7) When all the projects are in, unhide columns D and E. These are key identifiers that the next two tabs will depend on for summarizing the costs for each team and each project.
27
 - Column D: Make sure the values for the TEAM is populated here. Input the team name in the yellow input cell (e.g. D21, D84, etc.) and the rest should populate for the team.
28
 - Column E: Make sure the values for the PROJECT is populated here. Input the project name in the yellow input cell (e.g. E21, E39, etc.) and the rest should populate for the team.
29
 - Hide these columns again once done.
30
Check
31
 - Double check that the totals within each team are adding all the relevant rows. E.g. check formula within row 80 (column I and J) to check if Team A's hours and costs are all included.
32
 - Double check Column D and Column E have the right key identified values so that the hours and expenses are summed properly for the following two tabs.
33
34
Timeline Allocation Tab
35
(1) Copy and paste all your projects across all teams in column C. A quick way to do this is to copy the entire Column C on the Project Detail Tab in a separate tab >> Remove duplicates >> Paste in the projects in the Timeline Allocation tab.
36
(2) Fill out column E through H within the yellow input cells on when each project will happen throughout the year. For example, you may anticipate that there will be work for growth ads that happen evenly throughout the year, so you would give an even 25% allocation to Q1, Q2, Q3, and Q4.
37
38
Budget Tab
39
- Populate Column A with all your teams. The expenses will automatically populate based on the allocations you noted in the previous tab.
40
- Note: Since this template is done on a quarterly basis, the expenses are split evenly within each quarter (divided by 3 for each month).
41
42
43
44
45