How to calculate project profitability using time tracking data: Everything you need to know [Excel tutorial]

If you bill clients based on the time you spend, here’s a pop quiz: which projects make the most money? Or even more importantly: which projects aren’t profitable? If you don’t know the answer to these questions, it’s time to start thinking about project profitability, a measurement of revenue billed versus time and cost expended.

Note that you’ll find additional tutorials, webinars, and more on Beebole’s video resource page.

In addition to this written tutorial, you can also watch the recorded screencast on YouTube.

In this tutorial, you’ll learn how to calculate project profitability in Excel, using time tracking data from your employees. That means that you’ll synthesize three key pieces of information – the time spent, the costs of delivering the product, and the revenue you bill – to understand your profitability.

After you follow this tutorial, you’ll have an easy-to-analyze model to analyze project profitability in Microsoft Excel. With your finished project model, you can ensure that every project is profitable – and optimize your rates if they aren’t.

What is project profitability analysis?

Let’s have a look at project profitability analysis and how it’s related to project cost management.

What is project profitability analysis?

Project profitability analysis is an evaluation of the anticipated or actual profits generated from a project. It involves examining revenues, costs, and other factors to determine whether the project delivers adequate financial returns.

Project profitability analysis and project cost management are closely intertwined in project management, as they both directly impact a project’s financial health.

What is project cost management?

Project cost management involves planning, estimating, budgeting, and controlling costs so that the project can be completed within an approved budget. It focuses on effective allocation and control of resources to avoid cost overruns and ensure that every dollar spent contributes to the project’s objectives. It also involves budget vs actuals analysis to track cost performance, identify variances, and implement corrective actions to keep costs within budget.

The relationship between these two is that successful cost management can lead to better profitability. By effectively estimating and controlling project costs, managers can minimize unnecessary expenditures, reducing the cost base against which profitability is measured.

On the other hand, profitability analysis can inform cost management. By understanding which activities generate the most profit, project managers can make informed decisions on where to allocate or cut resources. Thus, combining robust cost management practices with insightful profitability analysis can lead to better financial performance and more successful projects.

What are the 4 measures of project profitability?

Project profitability is often evaluated using four primary measures:

1. Net profit margin: This is the percentage of profit a project makes after all costs and expenses are subtracted from the revenue. It gives a clear idea of the overall profitability of the project after accounting for all costs, including direct and indirect costs, overheads, taxes, and more.
2. Return on investment (ROI): This metric evaluates the profitability of a project in relation to its total cost. It’s calculated by subtracting the cost of the project from the net profit, then dividing the result by the cost of the project. A higher ROI implies a more profitable project.
3. Payback period: This refers to the time it takes for the project to recoup its initial investment. It’s a critical measure for cash-flow sensitive businesses or projects with high upfront costs.
4. Net present value (NPV): NPV is a measure that evaluates a project’s profitability by considering the time value of money. It compares the present value of cash inflows with the present value of cash outflows. If the NPV is positive, the project is considered profitable as the present value of its benefits exceeds the present value of its costs.

All these measures provide valuable insights into a project’s profitability, but none should be used in isolation. A combination of these measures can give a holistic view of a project’s financial viability.

Project profitability analysis: The resources you need

Before we jump in, make sure to download the finished project profitability dashboard to help you better follow each step below.

FREE EXCEL DASHBOARD

Get the finished project profitability dashboard here, and follow the tutorial below.

Get my copy!

The fastest alternative: Choose a tool that does it for you

While knowing how to calculate project profitability in Excel is great, it’s even better when a tool is automated to do all of the heavy lifting for you. With Beebole’s incredibly easy-to-customize settings, all you need to do is define your billing rates and costs in order to run your very own project profitability report, like the one shown below. No spreadsheets necessary. Of course, if you’d like to amp things up and analyze your timesheet data in Excel, our Excel add-in makes that an absolute breeze, too.

Capture your time tracking data

If you’re not using a time management suite like Beebole, you’ll need to rely on spreadsheets to gather and organize this vital information. The first data we need to consider is project time tracking data. Remember, with Beebole you can automatically capture and organize this information right inside the tool itself. We’ll use time data for two main purposes:

• Multiplying time tracking data by the hourly costs helps you understand each project’s cost.
• It’s also helpful to understand the relative amounts of time involved with each project. After all, it’s helpful to know if your niche projects (fewer hours involved) are highly profitable so that you can target your future marketing efforts.

In Excel, start by setting up a data table that includes employee hours. Each row should represent a billable period (like the month), the project name, the employee name, and the hours spent.

Here’s an important consideration: make sure that you capture your time tracking data at the same time interval as the rest of your data. For example, if you’re looking at monthly billings by client, ensure that your time data is monthly as well. If you have more detailed billing data (like weekly), then it’s okay to capture your time weekly as well.

After you type your time tracking data into individual rows, let’s convert it into an Excel Data Table. Click on one of the rows, then choose Insert > Table. Tick the My Table has headers box, and press OK.

One step that will come in handy later is naming this data table. Click on any cell in the table, then go to the Design tab on the ribbon. Give the Table Name a name, such as Hours.

This process is much easier when you use a tool to track and monitor time spent. Don’t rely on clumsy paper records or your team’s memory to track time. Check out a tool like Beebole to help your team log and capture their work time spent.

Capture your billing data

After you’ve logged the hours spent on each project, it’s time to move onto our second table: billing data. This step is very similar to the first one. Put each month, project name, and amount billed to the client on its own row. Remember that the data is shown at the same interval (monthly for our example) as the hours we’ve already tracked.

Let’s convert this table into a data table as well. Once again, click on one of the rows, then choose Insert > Table. Tick the My Table has headers box, and press OK.

Let’s also name the table with the same field on the Design tab. Let’s call this table Billing. Press enter, and you’re ready to move onto the next step.

Capture your cost data

We’ve locked in two out of the three parts of project profitability: the time spent, and the revenue we receive from our clients. Now, we need to add cost data to our workbook so that we can understand the full comparison of revenue versus cost.

Since we’ve already captured the hours each employee spends on the time, it’s best to square this data with hourly cost rates for employees.

Make sure that you consider the “fully loaded cost” of each employee. That means calculating their costs including extra expenses like insurance, benefits, and other costs. Otherwise, you’ll overstate profitability by not capturing the full employee cost. In this case, let’s enter each employee’s name and their cost per hour in the table. Enter each employee on row alongside their hourly cost. Then, make sure to name the table as we did in the prior steps.

It’s a great idea to work with your HR or finance team to calculate an hourly rate for each employee. They can help you make assumptions about working hours and all cost factors involved.

Keep in mind that your business includes costs other than “people costs.” That might include the expense of operating an office, employees who aren’t billable, (like HR and accounting) and other fixed costs that occur regardless of projects. These are real costs that need to be managed, but it’s outside the scope of project profitability.

How to bring all your data together

So far, you’ve built the tables that feed our profitability analysis. It’s time to take that data and combine it. With all of the tables added to a data model and several formulas, it’s easy to analyze project profitability.

Load the project profitability data

We’re going to use Power Query and PowerPivot in Excel to combine data and create our project profitability analysis. This helps us take data, transform it, and keep up-to-date in an easy model. Let’s learn how.

For each of the three tables, let’s load them to the data model. Let’s start with the time tracking table. Click on a row in the table, then click on Data > Get & Transform Data > From Table & Range.

On each table, simply click on the Close & Load button, then choose Close & Load To. Choose Only Create Connection, and Add this data to the Data Model. Then press OK.

Remember, you’ll need to repeat this for each of the tables we’ve already created: the billings, the costs, and the hours spent. This step gets all of your data ready to work with in our profitability dashboard.

Merge the project profitability data

Now, it’s time to join up the tables. By merging tables, we’ll take our three datasets and bring them together into one data model. Let’s open the Data > Queries & Connections menu. You’ll see the three tables that we’ve loaded so far. Double click on the table that corresponds to your billing data, the table with the month, project, and billable revenue.

Prepare the hours data

To get everything ready, we need to double up our Hours tab. That’s because we need to calculate each employee’s hours as a percent of the total on the project. Right-click on the Hours query and choose Duplicate.

On this table, we need to sum up the hours for each project, irrespective of employee. We’ll right click on the Employee column and choose Remove Columns on the menu.

Then, choose Group By on the menu. Let’s group by Month, then Project. We’ll create Summed Hours by choosing Sum for Hours as you see in the screenshot below. This is designed to condense this table into an easy sum of the hours by project and month.

You’re left with the hours by month and project. Let’s merge in the employee hours, then compare those hours to the total to find the percentage. Click on Merge Queries, then choose your other Hours table.

Click on the Merge Queries button. Then, choose the hours table where you’ve loaded your employee hours. On the merge window, click on the Month column, then hold control on your keyboard and click on the Project column in the top table. This says that we need to match data up to both factors.

Now, in the bottom table, click on the same columns (in the same sequence) and press OK. Click on the double arrow to select Employee and Hours. Then, press OK.

You’ll see each billing line of hours in the report. We need to add a custom column to calculate hours as a percent. Click on Add Column, Custom Column. Type in the formula as shown below, then press OK.

Finally, we’ll highlight our new column and transform it into a percent with the Data Type > Percentage option. When you’re done, use the Close & Load icon and load as a Connection, with Add to the Data Model.

That’s it! We’ve calculated hours as a percent, and we’ve laid most of the groundwork for the rest of our calculations.

Add hours data to billings data

Now that we’ve prepared most of our hourly data, let’s join it back to our billings. Let’s double click on the Billable query. Then, click on the Merge Queries option. On the bottom dropdown, choose the table from the last step (likely called “Hours (2)” if you’ve followed closely).

Again, we need to show Excel how to match up our data. Click on Month and Project while holding Control on your keyboard in the top table. Then, repeat the same step in the second table with the bottom preview window.

Now, click the double arrow to expand the column. Leave three columns checked: the employee name, the hours, and the percent of hours. You’re starting to see the power of all of this Excel work: joining up three separate tables to one cohesive model.

Let’s make one more join. Click on Merge Queries again, and this time, point to the costs table. This time, we need to add the hourly cost for each employee so that we can multiply it times the hours.

Choose the Employee Name in each table so that Excel matches everything up. Then, expand the column and add only the Employment Cost Per Hour column.

One more custom column to create: click on Add Column > Custom Column. Write the formula you see below to multiply the hours times the hourly rate to get to the total employment cost by employee / month / project.

Last up, let’s divide out the revenue based on the hourly efforts. We need to multiply that hourly share of rates times the billable revenue in order to see how much revenue is assigned to each employee, based on their share of the billings.

Here’s the formula we’ll use:

=[#"Hours (2).Percent of Hours"]*[Billable] 

Take a second to admire your work. With these Excel steps, we have a data model with everything in one place. We started with billings, added hours by employee, then layered in the costs. Keep reading to become a master project analyst.

Add your profitability calculations

With our data model in hand, we can write a few formulas that show project profitability. Let’s click on the Power Pivot > Manage button to open the data model. This looks like a separate spreadsheet and it’s the data that feeds our dashboard.

Make sure that you’re working on the Billable tab, then type these formulas into the bottom section of the spreadsheet, below your data. Each of these formulas gives us a way to analyze project profitability.

• Total Revenue:=CALCULATE(SUM(Billable[Employee Generated Revenue]))
• Total Hours:=CALCULATE(SUM(Billable[Hours (2).Hours.Hours]))
• Hourly Rate:=[Total Revenue]/[Total Hours]
• Total Costs:=CALCULATE(SUM(Billable[Employment Cost]))
• Project Margin:=[Total Revenue]-[Total Costs]
• Project Margin Percent:=[Project Margin]/[Total Revenue]

Let’s click on PivotTable on the menu, and choose New Worksheet. This places a table that hooks into the data model into our spreadsheet, and we’re ready to begin the analysis.

Note: if you gave your tables different names at any point in this tutorial, you’ll need to adapt your formulas to match those names.

How to analyze project profitability in dashboards

So far, we’ve spent a lot of time setting up our data and writing formulas to analyze profitability. Let’s build a dashboard to turn this into a visual analysis.

In the screenshot below, I’m using fields in the pivot table to create a dashboard. I’ve placed the month on each row, followed by each employee. Then, I’ve put numeric fields in the values to show the metrics.

With this view showing, here are things to notice and questions to ask to analyze your profitability:

• Are any projects unprofitable? Ensure each project has a positive margin percent.
• Are any employees unprofitable? Ensure each employee has a positive margin percent.
• Which projects are the highest and lowest in terms of profit percent? Compare the relative profitability percent of each project.

Remember, the dashboard serves as a tool to help you explore your curiosity. It’s up to you to decide how to analyze the data.

Here are other ideas for pivot tables that may be useful:

• Which employees have the highest profit margin? Compare their total billings generated to their costs and use the Project Margin Percent field to review.
• Which projects have the highest and lowest margins? We’ve basically built this, but simplify by pulling out the employee field on each row.
• What’s your overall agency profit by month? Place month in each column, then show the project margin below that.

Here’s my favorite part of what we’ve built: it’s easy to update. Simply fill in the tables with new information each time you get new data, then choose Data > Refresh All. Your tables will automatically update and populate in the same pivots you’ve already built.

Now that you’ve analyzed your project profitability, it’s time to take action. Make sure to consider this project profitability as you tailor your marketing and sales efforts. Also, consider revising your billing rates in projects that are lower in profitability.

And don’t forget that all of this can be done with a few simple clicks right in Beebole—no spreadsheets necessary!

Watch the tutorial

Don’t miss the tutorial screencast where we show you exactly how to calculate project profitability in Excel.

Facebook

Twitter

Linkedin

Whatsapp

Reddit

Telegram

Andrew Childress

Andrew Childress is a former FPA & CFO with a passion for simplifying, understanding, and analyzing financial data. He founded the FP&A consulting firm Beyond a Number to show others that great financial planning really starts once you go beyond the spreadsheets and numbers, and truly understand the data in front of you.