How to Do Payroll Reconciliation for Small Businesses

This blog provides a spreadsheet template for payroll reconciliation. It is designed to help small businesses get in the habit of tracking their paychecks and managing employee taxes. This will ensure that your company remains compliant with new IRS tax laws, which may require more frequent payroll deposits by companies or individuals who make $600-$999 each year based on self-reporting information.

Payroll reconciliation is crucial since it may assist discover mistakes or outstanding things that need to be addressed before they become too large of a problem. To do so, make a list of all payroll-related balance sheet accounts that don’t clear to Payroll reconciliation is important because it can help catch errors or outstanding items that need attention before too much time passes. To do this, you’ll list all payroll-related balance sheet accounts that don’t clear to $0 and research the issues that need to be rectified so all funds go where they should be. We offer a free payroll reconciliation spreadsheet to help make the process easier. and investigate the problems that need to be addressed so that all money end up where they belong.

Note: This is just a partial balance sheet reconciliation; we’re simply looking at payroll-related accounts, principally liabilities (accounts that reflect money you’ve expensed or collected but haven’t paid out yet). It’s easy to mix up a payroll audit with a payroll balance sheet reconciliation.

Step 1: Make a list of the accounts on your payroll balance sheet.

Make a list of the balance sheet accounts you need to reconcile each month before we begin the reconciliation process.

A balance sheet is one of the most important financial statements that a company may make. It depicts the asset, liability, and equity account balances at a single point in time (Dec. 31, 2022, for example), rather than across a period (Jan. 1–Dec. 31, 2022).

Here’s a list of balance sheet items relating to payroll that you may need to reconcile.

• Accrued Payroll is the amount of net payroll that has been earned but not paid out.
• Payable Federal Income Tax Withholdings: Federal income tax withheld from employee paychecks but not paid to the IRS.
• State Income Tax Withholdings Due: State income tax deducted from employee paychecks but not remitted to the appropriate taxing authorities.
• FICA Tax Payable: The unpaid employee and employer halves of Social Security and Medicare taxes; half is deducted from employee paychecks, while the other half is expensed from the firm.
• 401(k) Payable: Employee contributions to their 401(k) accounts (you may construct various accounts if you have numerous retirement account choices); you can also put any contribution matching your company makes into this account and keep the money there until it’s paid out.
• Employee-paid health insurance premiums, including contributions you’ve recorded as a company expenditure but haven’t paid

Consider marking transactions that include both employee and employer money if your accounting system permits it (use the codes EE and ER). This will aid you in the reconciliation process, particularly when investigating inconsistencies.

Keep in mind that the accounts you’ll be reconciling are almost certainly all liability accounts. This indicates that the funds should remain in the account until you are ready to withdraw them. When things don’t clear from your payroll balance sheet accounts each month or quarter, it’s probable that you neglected to pay a bill or that a transaction wasn’t recorded properly.

Step 2: Create Payroll Reconciliation Sheets on a Monthly Basis

Produce copies of the balance sheet for each payroll account, and make a fresh duplicate at the end of each month. The account name should be written on the spreadsheet. For each, you should have a starting and ending balance that corresponds to the GL balance records. Make sure to provide room for goods to be reconciled.

Transactions that aren’t represented accurately or at all in the GL are referred to as reconciling items. Your GL will be out of balance as a result, and it will be unclear how the account’s initial balance migrated to the final amount.

Step 3: Gather Reports From Your Payroll Software & General Ledger

For each payroll-related balance sheet account, you’ll need a monthly transaction report from your GL, ideally one that provides the starting and ending balances for the month. A payroll register or a list of monthly payroll transactions, a payroll tax report, a payroll deduction report, and other reports from your payroll software may be required.

You may need to dive into your payroll cash reports or itemized bills from your benefits providers for more intricate difficulties.

Step 4: Review Payroll Transactions & Reconcile Differences

You may now begin balancing your payroll accounts. The procedure may be simplified if you can download the transactions from your accounting software into a spreadsheet—or if you can copy and paste them into your spreadsheet.

If your account had transactions throughout the month but had a If your account had transactions for the month but has a $0 balance at month-end, you should show this on your reconciliation sheet and verify it’s clear. I would still paste the transaction list in another tab just to have it in your records in the event that you need to take another look at the account balance at the end of the month, you should note this on your reconciliation sheet and double-check that everything is in order. I’d still paste the transaction list into a new tab simply to have it in case you need to look at the account again.

If you have a debt at the end of the month, go through each debit and credit to see which ones did not fully clear. When working with a large number of transactions, I’ve found that deleting the debits and credits that net to If there is a balance at month-end, you’ll need to sift through each debit and credit to find those that did not clear completely. When dealing with numerous transactions, I always found it helpful to delete the debits and credits that net to $0, so it’s easy to see what’s remaining. makes it easier to identify what’s left.

If your company is tiny and you just have a few transactions, this procedure will be simple and you may not need to go through it. In any case, I suggest keeping a separate tab that displays all transactions, whether they have cleared or not.

For example, in the case below, employee health insurance premiums totaled $2,500 ($500 each weekly payroll, for five weeks) and were collected during weekly payroll runs and recorded to the liability account.

We can also see that the corporation paid the insurance company $2,350 in December to meet the insurance cost. The problem is that even after paying the $2,350 insurance payment, the account still has a $150 credit from employee premiums.

Before coming up with a remedy to the problem and clearing the account, you’ll need to figure out what’s causing it. Jane Doe’s insurance payment was debited for the whole month, but she was dismissed on the 16th and is entitled a $150 reimbursement.

Step 5: Resolve Payroll Reconciliation Issues

Items that need to be reconciled might have a wide range of requirements. In the case of our health insurance premium, the first step would be to examine the health insurance invoice. Is it broken down by employee? Examine your paycheck deduction report for the month as well. Examine the employee paychecks from which you withheld $500 each month, or $250 every pay period. Are the staff still employed by the firm? Do they appear on the invoice?

You may need to reverse the $500 transaction and repay an employee, work with the insurance provider to see whether the invoice needs to be rectified, or keep the money to pay on next month’s payment, depending on the answers to these questions.

You may also come across the following elements throughout your reconciliation:

• The payroll system cancelled a check, but the GL did not (having payroll software that is coupled with your GL may avoid this problem since it automates the procedure).
• Your benefits provider sent you a bill that wasn’t what you expected; maybe an employee quit and you withheld their payment for the following month. The charge will still appear on the invoice if you did not tell the firm. If you accept it, this might result in an extra, unneeded expenditure.
• Employees who departed before their 401(k) contributions vested would have the monies put aside and would still appear in the liabilities account if not yet paid, but they would have to be reversed to reconcile.
• Charges that should have been paid by employee deductions were expensed.

When it comes to payroll reconciliations, don’t be hesitant to go deep when it’s needed. Call suppliers, double-check payroll information with employers, go through your bank and tax records, and so on. Pay attention to payroll compliance regulations as well; if you only pay taxes quarterly or yearly, you’ll notice that some of your tax obligation accounts are often in the black. Keep track of the deadlines and make sure the charges are paid on time.

Payroll Reconciliations: How to Make It Easier

To end things off, here are a few pointers to help you simplify your payroll reconciliations.

• Reconcile the payroll balance sheet accounts on a regular basis—at least monthly.
• Add specific tags or labels to transactions to help you recognize them faster, such as those involving cash from workers vs employers or those involving a range of transactions, such as if the health insurance payable account contains dental, vision, and medical premiums.
• Set up your payroll reconciliation sheets with account names and numbers, dates, and transaction descriptions so they’re simple to follow.
• In a separate tab inside your reconciliation, enter records of all year-to-date transactions.
• Add a column for notes so you can keep track of important details like when a charge should be cleared.
• Separate tabs may be added for supporting facts such as bills, emails, and other material that might assist clarify any outstanding things.
• When conducting payroll, make the transactions as simple as possible.

Conclusion

A payroll reconciliation is a report that is created from the many payroll transactions that pass through your balance sheet accounts. Small companies might benefit from doing a monthly payroll reconciliation to verify that their payroll transactions are moving properly through their accounting systems.

It’s not a bad idea to understand how to reconcile payroll as a small company owner. Even if you employ a bookkeeper to handle the reconciliation for you, you should have a general idea of what to anticipate so you can spot any discrepancies. If the expectations and actual payments do not match completely, the discrepancies should be resolved as soon as possible.

Frequently Asked Questions

How do I keep track of payroll in Excel?

You can track your payroll using a simple table.

How do you do ledger reconciliation in Excel?

In order to perform a ledger reconciliation in Excel, you can use the Goal Seek function. This will search for a particular value and returns any cells that contain this number. For example, if your goal is $5000, then it would return all of the values where $5000 was shown on the spreadsheet so that you could edit those rows accordingly.