A Guide to Setting Up BaseNYC's % Complete Document
Learn how to effectively manage project finances using the % Complete document at Base Design New York.
By Jake Post
In this guide, we'll learn how to effectively setup the % complete document that is used in BaseNYC to manage project finances at an individual project level. This document is crucial for tracking project progress and ensuring financial alignment with the project's budget and timeline. We'll explore how to access the template via Google Sheets, set up the project using the proposal and project burn data, and manage the financial aspects throughout the project. An accurate initial setup of the % Complete for a project will provide you with a deep understanding of the project finances, and make it easy for you to make adjustments throughout the lifespan of a project.
TERMINOLOGY
Phase & Description→Pulled directly from the project's proposal, this describes what each phase of the project is about in a couple of words.
Department→To help us understand our business, we track revenue separately for the different types of work that we do (Brand Strategy, Brand Design, Digi Design, Digi Production). Each phase of a project needs to be associated with one department, based on the type of work that is being executed.
Revenue→Also referred to as Turnover, this is the dollar amount at which we are selling our services to the client, typically broken up by phase.
Target Margin→Sometimes referred to as budget, this represents the Gross Margin we are aiming to achieve for the project, which is defined with the Revenue/Budget and the Costs associated for us to complete the work. (Budget minus Costs = Profit. Profit divided by Budget = Gross Margin %). The Target Margin should be established prior to the project beginning, and should be aligned upon with the Director of Operations and the Project Team.
Burn→When generating a proposal for a client, we use a burn to understand what the project's budget should be. Based on the proposal's scope of work, we build a workflow with the associated internal and external team (costs). This tool helps us understand different scenarios, and ultimately sets the budget and target margin for the project.
Internal Costs→The costs associated with Base's internal team members on a project, based on an hourly rate provided by the Director of Operations.
External Costs→The costs associated with freelance collaborators who are not part of Base's internal team.
Revenue %→In the % complete, this number represents the % of work that has been completed for a particular phase and defines the amount of the budget that is applied to each month. It should be based on a the costs accumulated in each month along with projected costs. Ultimately, the objective is to maintain the Target Margin across each month of the project. Across each phase, this needs to total to 100% to maintain accurate numbers.
Phase & Description→Pulled directly from the project's proposal, this describes what each phase of the project is about in a couple of words.
Department→To help us understand our business, we track revenue separately for the different types of work that we do (Brand Strategy, Brand Design, Digi Design, Digi Production). Each phase of a project needs to be associated with one department, based on the type of work that is being executed.
Revenue→Also referred to as Turnover, this is the dollar amount at which we are selling our services to the client, typically broken up by phase.
Target Margin→Sometimes referred to as budget, this represents the Gross Margin we are aiming to achieve for the project, which is defined with the Revenue/Budget and the Costs associated for us to complete the work. (Budget minus Costs = Profit. Profit divided by Budget = Gross Margin %). The Target Margin should be established prior to the project beginning, and should be aligned upon with the Director of Operations and the Project Team.
Burn→When generating a proposal for a client, we use a burn to understand what the project's budget should be. Based on the proposal's scope of work, we build a workflow with the associated internal and external team (costs). This tool helps us understand different scenarios, and ultimately sets the budget and target margin for the project.
Internal Costs→The costs associated with Base's internal team members on a project, based on an hourly rate provided by the Director of Operations.
External Costs→The costs associated with freelance collaborators who are not part of Base's internal team.
Revenue %→In the % complete, this number represents the % of work that has been completed for a particular phase and defines the amount of the budget that is applied to each month. It should be based on a the costs accumulated in each month along with projected costs. Ultimately, the objective is to maintain the Target Margin across each month of the project. Across each phase, this needs to total to 100% to maintain accurate numbers.
1
ACCESSING THE % COMPLETE TEMPLATE / STEP 1: Open your web browser and to to sheets.google.com and hit Enter
2
ACCESSING THE % COMPLETE TEMPLATE / STEP 2: Click on "Template gallery"
3
ACCESSING THE % COMPLETE TEMPLATE / STEP 3: Click on the % complete template. This will open a new draft file of the percent complete document saved in your drafts folder.
This document contains four tabs: Project Setup, Percent Complete, Invoicing, and Instructions.
The most important sections are the project setup, percent complete, and invoicing tabs. The instructions tab provides extra details on usage. In the project setup tab, input the proposal information into the document. There are four key columns: phase, description, department, and designation. The phase and description should usually match the proposal's language. The department column indicates which department the phase belongs to.
4
PROJECT SETUP TAB / ACCESS: Click on the "Project Setup" tab if you are not already there.
5
PHASE & DESCRIPTION: This is the tab where you plugin the most basic information about the project. This information should be pulled directly from the proposal for the project.
You begin by entering each phase of the project, as per the proposal. For this example, we will use the default phases that are part of the template.
You begin by entering each phase of the project, as per the proposal. For this example, we will use the default phases that are part of the template.
6
DEPARTMENT: Once you have entered each phase description, you will need to select which department that phase's revenue is tied to: Strategy, Brand Design, Digital Design, or Production.
This is an important step to ensure that the project finances accurately reflect the type of work that is being done. If there is ever a question about which department a phase should be tied to, reach out to the Director of Operations to discuss.
Each phase has a drop down menu for you to select the appropriate department. The "TBD" option is there for phases that are yet to be defined, but should only be used as a temporary placeholder.
This is an important step to ensure that the project finances accurately reflect the type of work that is being done. If there is ever a question about which department a phase should be tied to, reach out to the Director of Operations to discuss.
Each phase has a drop down menu for you to select the appropriate department. The "TBD" option is there for phases that are yet to be defined, but should only be used as a temporary placeholder.
7
REVENUE: Lastly, you have the budget column for the dollars associated with each phase. This information can be pulled from the project proposal. The final row includes a total that sums the amounts from each phase.
8
TARGET MARGIN: Lastly, you have the "Target Margin". This number represents the target Gross Margin for the project.
The "Target Margin" is a number that should be aligned upon with the Managing Director, Director of Operations and project team at the outset of the project. It is derived from the project burn which is completed prior to setting up the % Complete.
The "Target Margin" is a number that should be aligned upon with the Managing Director, Director of Operations and project team at the outset of the project. It is derived from the project burn which is completed prior to setting up the % Complete.
9
Now that you've completed the "Project Setup" tab, click on the "% Complete" tab.
% COMPLETE TAB
The "% Complete" tab may look overwhelming at first, but don't be intimidated! It has a number of automations built in to help keep things simple.
This sheet pulls in the following data automatically from the "Project Setup" tab: Phase Description, Department by Phase, Budget by Phase, and Target Margin.
IMPORTANT NOTE: The sheet is built through a number of automated formulas that, if altered accidentally, can create inaccuracies. Be careful when making changes to not alter any existing formulas.
10
The sheet is broken up into collapsible sections for each phase of the project, with totals included at the bottom.
11
PROJECT START DATE / STEP 1: Let's start by opening Phase 1: Discover & Immersion by clicking the + symbol next to it. The first step is to ensure the project's starting month is accurate.
Click on cell D3 that says "Jan23".
Click on cell D3 that says "Jan23".
12
PROJECT START DATE / STEP 2: And enter the date of the month when the project will begin in the formula bar.
One of the great features is that after changing this initial field, the remaining months automatically update throughout the rest of the sheet.
One of the great features is that after changing this initial field, the remaining months automatically update throughout the rest of the sheet.
SETTING UP THE FINANCES
This is where things begin to get a bit more complicated. 😉 The first step is to enter all of the project costs, based on the PROJECT BURN.
13
INTERNAL COSTS: Begin with the INTERNAL COSTS for PHASE 1. Enter the amount for each month.
14
EXTERNAL COSTS: Then, if applicable, enter the EXTERNAL COSTS for PHASE 1. Focus on one EXTERNAL COST at a time. First enter a description of the EXTERNAL COST in column C, then add in their cost according to the invoicing schedule agreed upon in their LETTER OF AGREEMENT.
As a rule, the EXTERNAL COSTS should match the invoicing schedule that is included In their LETTER OF AGREEMENT. By way of example, if the invoicing schedule is broken into two (2) payments of $10k, for a total of $20k, there should be two entries of $10k reflected in the % COMPLETE. However, there may be some instances where that is not possible because their payments are split across multiple phases. In these cases, apply the appropriate amount in each phase, but do not distribute a single invoice across multiple months in one phase.
15
APPLY COSTS TO ALL PHASES: Do the same exercise across all subsequent phases, so that the entirety of the project's costs are incorporated in the % COMPLETE. When you are done, cell AN135 should match the total costs reflected in the PROJECT BURN.
16
REVENUE %: Once you are certain that all of the project costs have been entered, it is time to enter the REVENUE % across each phase.
To determine the appropriate REVENUE %, you need to consider three things:
1: The costs reflected in each month in your % COMPLETE
2: The projected duration of the PHASE
3: The TARGET MARGIN
In this example, PHASE 1: DISCOVERY & IMMERSION is projected to take place over the course of two months. This is reflected by the fact that there are only costs in both the month of May25 & Jun25. Therefore, we need to distribute the revenue across both months.
We will begin with the month of May25, and input a percentage that meets or exceeds our TARGET MARGIN of 58%. In this case, a REVENUE % of 75% gives us a MARGIN of 60%, which is what we will use in this case.
To determine the appropriate REVENUE %, you need to consider three things:
1: The costs reflected in each month in your % COMPLETE
2: The projected duration of the PHASE
3: The TARGET MARGIN
In this example, PHASE 1: DISCOVERY & IMMERSION is projected to take place over the course of two months. This is reflected by the fact that there are only costs in both the month of May25 & Jun25. Therefore, we need to distribute the revenue across both months.
We will begin with the month of May25, and input a percentage that meets or exceeds our TARGET MARGIN of 58%. In this case, a REVENUE % of 75% gives us a MARGIN of 60%, which is what we will use in this case.
If the MARGIN does not meet or exceed the TARGET MARGIN for the project, the MARGIN cell will turn red to indicate that the the REVENUE % is too low.
In some cases, this may mean that the project's costs are exceeding what has been planned, which should be flagged to the project team and Director of Operations so that we can align on an adapted plan for the project.
In some cases, this may mean that the project's costs are exceeding what has been planned, which should be flagged to the project team and Director of Operations so that we can align on an adapted plan for the project.
17
REVENUE %: We then need to figure out what REVENUE % needs to be added for the remaining months. In this phase, we only anticipate the duration to be two (2) months. Therefore we can apply the remaining revenue to Jun25, which would be 25%, making the total REVENUE % for the month 100%.
18
REVENUE %: If you scroll to the right, you can check to make sure that your math is accurate. The REVENUE % row should equal 100%, the REVENUE row should reflect the total dollars for that phase, and the MARGIN row should match or surpass the TARGET MARGIN % assigned to the project.
If the REVENUE % row does not equal 100% or the BUDGET row does not match the appropriate $ amount, the cells will be red to flag that there is a mistake somewhere.
If the MARGIN row is less than the TARGET MARGIN designated for the project, it will also turn red.
If the MARGIN row is less than the TARGET MARGIN designated for the project, it will also turn red.
19
REVENUE %: Let's look at a slightly more complicated phase. We can see from the project costs that we already entered that our costs are distributed across three (3) months.
We will begin by applying a percentage to the first month (Jun25), with the objective of achieving a MARGIN that is equal to or greater than our TARGET MARGIN. In this case, 48% is close.
We will begin by applying a percentage to the first month (Jun25), with the objective of achieving a MARGIN that is equal to or greater than our TARGET MARGIN. In this case, 48% is close.
20
REVENUE %: We then move to the next month, and complete the same exercise. In this case, 46% for the REVENUE % gives us a MARGIN of 58%, inline with our TARGET MARGIN.
21
REVENUE %: After entering the REVENUE % for the first two months of the phase (Jun25 + Jul25), we are left with 16% that we can apply to Aug25. We can see that with our anticipated costs, we are left with a robust margin of 84% in Aug25.
It is a best practice to push extra margin to the end of the phase. This creates some buffer in case there are any curveballs or delays in the project.
22
REVENUE %: After entering the REVENUE % for the entirety of the phase, and confirming that the MARGIN is on track for each month, you can scroll to the far right to double check to make sure that your REVENUE is correct (100%) and that the MARGIN for the phase is on track.
23
REVENUE %: You should then complete this process for each phase of the project, applying the REVENUE % across each phase. Click on "$ 4,500"Click here
24
FINAL CHECK: Once you have finished distributing the REVENUE across each phase using the REVENUE % row, you can find an overview of the projects finances by month, across all phases, by scrolling to the bottom of the page.
We can see that in each month, across all phases of the project, we are exceeding our TARGET MARGIN of 58%, and achieving a TOTAL MARGIN of 66.40% across the whole project.
We can see that in each month, across all phases of the project, we are exceeding our TARGET MARGIN of 58%, and achieving a TOTAL MARGIN of 66.40% across the whole project.
Since the initial setup is based off the PROJECT BURN, the TOTAL MARGIN should be much closer to the TARGET MARGIN that was agreed upon at the outset. If this is the case, you should double check your COSTS throughout the project to make sure that you didn't miss anything.
In a case where you didn't miss anything, the TARGET MARGIN should be revisited with the project team, Director of Operations and Managing Director.
In a case where you didn't miss anything, the TARGET MARGIN should be revisited with the project team, Director of Operations and Managing Director.
Congratulations! You've made it through the % Complete Setup Guide. For additional information on how to use this throughout a project, please see the % Complete Updates Guide (TK).