Rolling Up Payroll Data

[Bobby_Gay]

Our employees are paid in multiple ways. First, the amount of Revenue they generate dictates Hourly Pay Rate for the week. We are able to import all revenue transactions from our CRM via .csv Revenue Report files. We have a chart that displays Hourly Pay Rate by Weekly Revenue level in the base as well.

An Airtable form is used to capture timesheet entries from the field on a daily basis. The number of hours entered is applied to the Hourly Pay Rate to generate Straight Time and Overtime pay.

Additionally, we pay bonuses for certain high value items that are sold (commissions are on another rate sheet in the base). Again, we use an Airtable form for field reps to submit bonus items that have been sold.

Finally, in all situations, we are using DateTime functions to identify the Week of the Year for pay periods (Sunday through Saturday). So, all transactions include a Pay Period (Week of the year) in the records.

All of this data is in the base in the following tables:

Revenue

Timesheet Entries

Bonus Entries

My question is how to automatically roll data from these three tables into one combining numbers and dollar amounts to calculate the gross pay in each employee’s paycheck. All transactions in all tables have a Technician name included in the records. Should I do this by creating an automation that updates the table upon creation of new records in Revenue, Timesheet Entries and Bonus Entries?

Suggestions?

[TheTimeSavingCo]

Bobby_Gay:

Should I do this by creating an automation that updates the table upon creation of new records in Revenue, Timesheet Entries and Bonus Entries?

Hi @Bobby_Gay, as you mentioned this is for calculating employee paychecks, it feels like the automation you mentioned would be the way to go about it

I would have a table called “Paychecks” or something that would contain all these paychecks, and in each of the tables that log money to be paid out to the person, I would have a formula field that would combine the person’s name with the Pay Period (Week of the year) value you mentioned, so you’d end up with something like “John Doe - 23 2022” or some such

The automation would paste the above value into the linked field to the Paychecks table where we could do a rollup fields as required, and sum them all, which would give you the total amount you needed to pay that person for that pay period, does that make sense?

[Bobby_Gay]

Thanks Adam! I’ll try this out and let you know how it goes.

[Bobby_Gay]

I have been able to put a table together named Gross Pay that captures information from Airtable forms named: Timesheet Entries, Spiff Entries and 5 Star Review Commissions. I have used multiple automations where, upon the submission of these forms (trigger), a record in the Gross Pay table is created.

Gross Pay is like an umbrella table that accepts records from the other 3 component tables (Time, Spiffs and 5 Star Reviews). So, if there are10 fields in each component table, there are 30 in the Gross Pay table. When a time entry form is received, the 10 Time Entry fields in a new Gross Pay record are entered into the new Gross Pay record. When a Spiff Form is submitted, the 10 fields associated with Spiffs receive entries in the new Gross Pay record, etc.

Here is my challenge. Calculating Straight Time and Overtime pay is a matter of multiplying Hours Worked by an Pay Rate per Hour. Pay Rate per Hour is driven here by the amount of Revenue each employee generates for the week. For every $1,000 in revenue - above $5,000 - in a week, the employee is paid an additional $1 per hour.

Example
Suppose the Base Hourly Rate is $20. The employee is paid $20 per hour until $5,000 in revenue is generated. From that point forward, the employee is paid accordingly:

Weekly Revenue | Hourly Rate
$5,001 to $6,000 | $21
$6,001 to $7,000 | $22
$7,001 to $8,000 | $23, etc.

We have all revenue-related transactions in a Revenue table. So, it’s possible, using grouping by Pay Week and Employee to see how much the revenue transactions total for the week per employee.

All hours worked are located in the Timesheet Entries table. It’s not hard to physically see the amount of weekly revenue the employee had in the Revenue table, and I can see the hours worked, per employee, for the week.

I’m trying to figure out how to, based on the amount of weekly revenue, automatically select the correct Pay Rate per Hour to apply. So, if Jane Doe had revenues of $18,020 for the week, and I have all of Jane’s time entries for that week in the Gross Pay table, how do I get the proper rate applied in the Gross Pay table?

Thanks for any suggestions.

[TheTimeSavingCo · Accepted solution]

Could you use a formula field to find the difference between their weekly revenue and $5000, divide that by 1000, round it to the next lowest whole number, and add that to $20?

[Bobby_Gay]

Thanks Adam. I’ll give that a shot.

[Bobby_Gay]

Boom! That worked great. I was making it more complicated than it needed to be.

Thanks again Adam.