An Employee Payroll Calculations Based On Their Timesheets

Having to calculate employee payroll can be tricky, especially when you have to take into account paid and unpaid types of time off your employees take. Teamdeck – the resource management software – allows you to create a payroll calculator in the report section so that you can automate your work a little bit.

You may want to calculate payroll to check your bottom line or to track the project’s profitability. Unexpectedly, payroll can affect employee retention, too.

To calculate payroll, you will use the following data available in the app:
– time spent working (timesheets)
– time off (divided into paid and unpaid if necessary)

You will need to provide:
– hourly rates of your employees

Create your report

You can create a new report from scratch (by clicking create custom) but I suggest using a template called “Payroll from timesheets”. Name it and select an appropriate date range.

If you’re using the template, you’ll see two charts. The first one: “Timesheets time off”, provides you with a visual overview of your team’s time: estimated bookings, actual timesheets, and vacations (thanks to leave management system features).

We will make the calculations in the second chart, Payroll. Unless you want to learn how to create this table yourself, you can skip the rest of step 1.

If you want to create your payroll table from scratch, here’s an instruction on how to do that:
– create a custom (add custom) report, name it and select the date range,
– click on add table,
– select grouping from your table’s menu and create a structure People -> Projects, not the other way round,
– select metrics from your table’s menu and display Timesheets: Time and Vacations: Time,
– create a custom value column by clicking on add custom in metrics: name it “Hourly rate”, pick Currency as your data format and click save and apply,
– create a custom calculation field by clicking on add custom in metrics: name it “Payroll”, pick Currency as your data format, and type in the following formula before you click save and apply:

(Timesheets: Time + Vacations: Time) / 60 * Custom values: Hourly rate

Fill out the hourly rate of your employees

The column called Hourly rate is empty so you have to fill it out by typing in your employees’ rates.

Click on a cell to start editing it. If you have several people with the same rate you can click on the green square in the lower right corner of your cell and drag the value up & down.

As you’re filling out the Hourly rate field, the Payroll column is being calculated automatically.

This is an instruction for calculating employee payroll when you pay for the logged hours as well as for time off. What if you’re paying just for the time spent working? Or if your employees are taking paid and unpaid vacations? Teamdeck’s online calendar planner allows you to cover both cases.

When you’re compensating your team members only for the time they spent working, you don’t need to take vacation days into account. Change your Payroll formula to:

Timesheets: Time / 60 * Custom values: Hourly rate

Take paid and unpaid time off into account

Many companies allow their employees to take different types of time off. If it happens to be the case at your company, calculating employee payroll can be more complicated since you have to subtract the time spent on unpaid vacations. Here’s how to configure your Teamdeck account so that this calculation happens automatically. First of all, you need to make sure that you have defined your organization’s types of paid and unpaid time off.

How to define different types of time off?
You can edit it in Settings -> Advanced (only people with owner access can see this tab) -> Vacation. There you can see a list of vacation reasons. A dollar sign signifies that a paid time off type. When this is set up, you can proceed to customize your report.

What you need to do is filter your report so that the unpaid vacations are not displayed. Click on the filters icon in the upper menu of your table and add the filter. Now, pick Vacation reasons: Name from the drop-down list. The last step is to type in the unpaid vacation types and click apply. If anyone from your team had taken any unpaid time off, the number of hours in their column will change as a result. Automatically, the payroll will be changed as well.

What if you have fixed monthly salaries?

Fixed monthly salaries are usually very straightforward to calculate. It can be a little more challenging, however, to determine the payroll if your employees are allowed to take unpaid time off which has to be subtracted from their regular salary.

Our capacity planning tool allows you to calculate this formula. Start by creating a custom value column for your employees’ monthly rate.

Do it by clicking on add custom in metrics (upper menu of your Payroll table): name it “Monthly rate”, pick Currency as your data format, and click save and apply.

Now you have to fill this column out:

Now it’s time to check if you need to subtract any time off from the monthly rate.
In order to see the unpaid vacation hours you can use the filtering option.

Don’t know how to mark paid and unpaid types of time off in Teamdeck? Find an instruction above, in the “How to define different types of time off?” section.

Click on the filters icon in the upper menu of your table and add the filter. Now, pick Vacation reasons: Name from the drop-down list. The last step is to type in the unpaid vacation types and click apply.

As a result, the Vacations column will only display the unpaid time off that you should subtract from the overall monthly rate.

Now it’s time to change the Payroll formula to reflect this:

Custom values: Monthly Rate – ((Custom values: Monthly Rate / (Availability: Overall / 60)) * Vacations: Time / 60)

Note that this formula can be further simplified if you have a fixed hourly rate for your employees:

Custom values: Monthly Rate – (Custom values: Hourly Rate * Vacations: Time / 60)

When the hourly rate is not defined we have to determine it ourselves. Since there might be a different number of working hours in each month you need to calculate it dynamically based on the Availability metric (which excludes holidays/weekends for full-time employees and takes into account the part-time availability of other team members).

Calculate employee payroll based on their bookings

Some companies calculate employee payroll based not on the hours spent working but on the hours employees were booked for. In that case, you would determine their salary using Teamdeck’s resource scheduling tool. Change the Payroll formula to:

Bookings: Time / 60 * Custom values: Hourly rate

There you have it – an automatic payroll list you can use every month.

Do you want to share your payroll list? You can share it with other managers or even people from external companies (e.g. your bookkeepers).