Table of Contents
Purpose
This article discusses how to compare your Aspire weekly or bi-weekly payroll gross earnings amount with gross earnings in QuickBooks Desktop for all direct labor employees that are listed as your employees in Aspire.
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.
Creating the Reconciliation Spreadsheet
To create the reconciliation spreadsheet to reconcile Quickbooks Desktop values, you will export the Hours Pivot Report for Payroll Reconciliation report saved above.
Select the Hours Pivot Report for Payroll Reconciliation report 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 compare Aspire to your QuickBooks Payroll Summary.
You can now edit the exported Hours Pivot Table Excel report to reconcile amounts with Quickbooks Desktop.
In this section, we will verify all employee hours from the Aspire pay codes tie to the QuickBooks Payroll Summary.
To start,
On the spreadsheet, insert 2 columns, which are highlighted in yellow.
Enter the heading QuickBooks Total Gross Earnings to the right of the Total Labor Cost No Burden column (In this example, Column L).
Enter the heading Difference to the right of QuickBooks Total Gross Pay (In this example, Column M).
Enter a formula in the range of cells within the Difference column to calculate the difference between Total Labor Cost No Burden and QuickBooks Total Gross Pay (In this example, =K7-L7. We'll then auto-fill the formula in the rest of the needed cells.)
Review the Accrued Payroll Holding Account in QuickBooks Desktop and Reconcile
In order to add the Total Labor Cost from QuickBooks to the Reconciliation Spreadsheet, we will have to get the Gross Total for each employee from the QuickBooks Payroll Summary.
Go into QuickBooks, open the reports section and select the Employees & Payroll, Payroll Summary.
Adjust the date to the pay date for the payroll period.
Verify all employee hours related to Aspire pay codes are tied to the QuickBooks Payroll Summary. See example below with the A-Hourly pay code.
(The Pay Codes for QuickBooks Desktop could include A-Hourly, A-Overtime, A-Holiday, or A-PTO)
Record the total gross earnings within the specific Aspire pay codes. You will add those totals into your Reconciliation Spreadsheet.
On the Reconciliation Spreadsheet, enter the QuickBooks Gross Earnings by employee on each line (Column L in this example)
Review and reconcile any differences.
📌 Note: During this step, you need to use the Balance Sheet Standard Report to view the transactions in the Accrued Payroll Holding account in QuickBooks Desktop. Do not use the Chart of Accounts Report because it will not total the employee gross earnings correctly.
Once payroll has been processed in QuickBooks Desktop, the Accrued Payroll Holding will show the paycheck gross earnings for each employee.
⚠️ If you choose not to accrue payroll, these costs would appear in your COGS (Cost of Goods Sold) Labor expense accounts.
The End of Month Labor journal entry for the pay period you're reviewing will zero out the Accrued Payroll Holding account and move it to the COGS Labor account by division (or class). Accrued Payroll Holding has a $0.00 balance after the journal entry is completed for the check issue date.
The next step in the reconciliation process would be to create your journal entry. Click below to read the article about expensing payroll for QuickBooks Desktop.