Table of Contents
Purpose
Steps
Purpose
This article provides a step-by-step guide for the payroll reconciliation process between Aspire and your payroll service provider. These steps will also apply to QuickBooks Online users as well as other payroll systems.
Check Before Reconciliation
If you are reading this article, you should have already:
Record Hours From Weekly Time Review
You will need to first get total hours and total OT hours from Weekly Time Review to reconcile Regular Hours, OT Hours, and Hours in the Hours Pivot Table you will create later.
Let's start by identifying the Payroll period and recording hour amounts within Weekly Time Review.
From Scheduling, click Weekly Time Review.
Adjust the date at the top to the first day of the payroll period you need to reconcile.
Group your list by Contact FullName.
If you don't have the Advanced Group Header available, you can turn this on.
To reconcile, record the following values:
Total Hours in Weekly Time Review
OT hours in Weekly Time Review
Subtract Total Hours - OT hours in Weekly Time Review. Record this value. This should equal
This is because Total Hours - OT Hours = Regular Hours!
Field Mapping for Weekly Time Review Reconciliation
Weekly Time Review Field | Hours Pivot Report for Payroll Reconciliation Field |
Total Hours | Hours |
OT Hours | OT Hours |
(Total Hours - OT Hours) | Regular Hours |
Weekly Hours Example
Pay Period: 4/13/20 - 4/19/20 | Total hours: | 180 | OT hours: | 15 |
Total Hours - OT Hours = Regular Hours
180 - 15 = 165 Regular Hours
Bi-Weekly Hours Example
If you process payroll bi-weekly, repeat and record the above process to get the second week's totals. Add these together. Then, subtract the summed totals to get your bi-weekly regular hours.
First Week: 4/13/20 - 4/19/20 | Total hours: | 180 | OT hours: | 15 |
Second Week: 4/20/20 - 4/26/20 | Total hours: | 193 | OT hours: | 13 |
Summed Totals of Both Weeks | Sum of Total Hours: | 373 | Sum of OT Hours: | 28 |
Total Hours - OT Hours = Regular Hours
373 - 28 = 345 Regular Hours
Record Labor Dollars From the End of Month Report
We will need the Total Gross Earnings, which in Aspire is the sum of Base Labor Cost and the Overtime Labor Cost available in the End of Month Report.
Click on Reports in the blue side menu, and then click on the End of Month Report.
Select the Labor tab and change the date range to Custom – Between and adjust the date range to match the pay period you are reconciling.
Scroll to the bottom of the report and record the totals for Base Labor Cost and Overtime Labor Cost.
To reconcile, record from the End of Month Report:
Total Base Labor Cost in the End of Month Report
Total Overtime Labor Cost in the End of Month Report
Add these values together. This value will be you Total Labor Cost No Burden within your Hours Pivot Report for Payroll reconciliation report.
This is because Total Base Labor Cost + Total Overtime Labor Cost = Total Labor Cost No Burden (which is also considered the Total Gross Cost)
Field Mapping for End of Month Report
End of Month Report Field | Hours Pivot Report for Payroll Reconciliation Field |
Total Hours | Base Cost |
Total Overtime Labor Cost | OT Premium |
Total Base Labor Cost + Total Overtime Labor Cost
(This value is your Total Gross Cost) | Total Labor Cost No Burden |
Total Gross Cost Example
Base Labor Cost: $11,207.00
Overtime Labor Cost: $2,392.00
+_____________________________________
Total Gross Cost: $13,599.00
Create an Hours Pivot Report for Payroll Reconciliation
To create a report that includes payroll dates, hours, and gross pay in an exportable format, you'll create an Hours Pivot Report called Hours Pivot Report for Payroll Reconciliation. These values will be reconciled with the values you have recorded in Weekly Time Review and the End of Month Report.
📌 Note: You will only need to create this Hours Pivot Report for Payroll Reconciliation your first time reconciling. You will be able to save it and use it in future reconciliations. Just make sure your filters are accurate!
The data is combined into a exportable pivot report so you can reconcile your Gross Payroll with QuickBooks Desktop.
To start creating the Hours Pivot Report for Payroll Reconciliation:
Click on Reports in the blue side menu. Then, Standard Reports.
Select Hours Pivot Table.
A separate tab will appear. It's now time to create the Hours Pivot Report for Payroll Reconciliation from the Default View.
Add the filter fields to build your pivot report:
Work Date Year - Select the year for your pay period.
Work Date Month - Select the month or months for your pay period.
Work Date - Select only the days that are included in your pay period. Make sure that all the days are checked in the list and keep dates unchecked that should not be included in the total.
Status - Add the Status of the hours and select the Exported checkbox.
Pay Schedule - You may need to use this filter to exclude certain pay schedules.
Add the following fields to your Data Header Column: Regular Hours, OT Hours, Hours, Base Cost, OT Premium, Total Labor Cost No Burden.
Set your Row Display Headers to include Employee (Last, First), Division, Service Name, Base Rate, and Pay Code.
When your filters are set, this is what the report will look like.
We'll now review these values shown on the Hours Pivot Report for Payroll Reconciliation and compare them to our recorded values from the Weekly Time Review and End of Report. (See mapping below)
⚠️ Before saving or exporting the pivot report, you can filter out employees (like salaried employees) that have gross pay will not be entered into your Accrued Payroll Holding account in your accounting system.
You would filter out these employees if you choose to enter their gross pay as a direct or an indirect expense to a division.
If this is the case, remember when closing the month, you cannot use the Labor tab on the End of Month Report for the labor journal entry into your accounting system. If you need to get payroll journal entry numbers from Aspire, instead of using your payroll service reports, you will need to create a pivot report in Aspire and exclude these same employees or payroll schedule to determine the value of the journal entry.
Field Mapping for Reconciliation
Weekly Time Review Field | Hours Pivot Report for Payroll Reconciliation Field |
Total Hours | Hours |
OT Hours | OT Hours |
(Total Hours - OT Hours) | Regular Hours |
End of Month Report Field | Hours Pivot Report for Payroll Reconciliation Field |
Total Hours | Base Cost |
Total Overtime Labor Cost | OT Premium |
Total Base Labor Cost + Total Overtime Labor Cost
(This value is your Total Gross Cost) | Total Labor Cost No Burden |
Saving the Hours Pivot Report for Payroll Reconciliation
If you have created this report for the first time, use the Save As option so you can use this report and its filters again the next time you reconcile.
📌 Note: The Hours Pivot Report for Payroll Reconciliation will only need to be created once after your first processed payroll. For payroll processed afterwards, you will only need to select the list to export.
Creating the Reconciliation Spreadsheet
To create the reconciliation spreadsheet to reconcile your Payroll Service values, you will export the Hours Pivot Report for Payroll Reconciliation saved above.
Select the Hours Pivot Report for Payroll Reconciliation you saved.
Export the Hours Pivot Report for Payroll Reconciliation report to Excel by hovering over the Save icon and select Export to Excel.
Open the exported file. This is the Reconciliation Spreadsheet that you will use to move forward with the reconciliation process. You'll need to edit the exported Excel file you created from the Hours Pivot Table for Payroll Reconciliation. We call this the Reconciliation Spreadsheet.
To start, add the following columns to your exported Excel file:
Add a Payroll Service column.
This will be used later and will have the Payroll Service Gross Value filled in.
Add a Difference column to the right of the Payroll Service column. This will show the difference between Aspire Payroll Gross and your Payroll Service Gross.
Create a formula within the Difference column to subtract the Payroll Service column from the Total Labor No Burden column. ( = Total Labor No Burden - Payroll Service)
Copy the formula down to all below cells including the Grand Total line.
Add a Notes column to the right of your Difference column.
This is to add notes if there is a difference between your payroll numbers and the numbers from Aspire.
Add a Division column to the right of your Notes column.
This field will be used later.
Then, you can move forward with entering payroll values into your Reconciliation Spreadsheet.
Entering the Payroll Service Values into the Reconciliation Spreadsheet
Exporting a Payroll Service Invoice Summary
To reconcile between your payroll system and Aspire, you will first need to get the total gross dollar amount for each direct labor employee during the specific pay period you would like to review. Each payroll system may be different, but you will need a report that will provide:
A date range limited to the specific payroll period you would like to reconcile
All direct, hourly labor employees who were paid during the paid period
Each employee's total gross dollar amount (Regular Hours + Overtime)
🧠 Remember, in order to reconcile, you must export the payroll report for the pay period from your payroll service.
Take the Total Gross Amount for each employee from the report provided by your payroll service, and whose time is in Aspire, and enter the values into the Payroll Service column of the Reconciliation Spreadsheet as shown below.
Your differences between Aspire and your payroll Service's amount will calculate based on the formula you created.
Add information to the Notes column explaining any difference on the reports.
In the Division column, enter the division where the employee works, to help when creating a partial journal entry.
Investigate any difference in amounts between Aspire and your payroll service.
For example, Employee 3 has a difference between the Aspire Total and the total from the payroll system.
The reason is because Employee 3's Base Rate does not match the rate in the payroll service system.
For differences, make adjustments accordingly. This could include making corrections in Aspire or creating a journal entry in your accounting system to ensure Aspire payroll matches your payroll service summary for the same period.