How to Perform an Account Reconciliation in BlackLine

Companies come to BlackLine because they have a capacity problem—too much work, and not nearly enough time to do it. They're looking for efficiency and automation, plus more visibility, control, and consistency. That’s why thousands of accounting teams use account reconciliation software like BlackLine to set up their balance sheet recs and other close-related items.

Don’t get us wrong—we’re big fans of Excel! Many BlackLine employees were F&A professionals in their prior careers, and they’ve written thousands of Excel macros over the years and used spreadsheets to tackle all kinds of processes. But Excel has its limits, especially around control and visibility. The truth is: we’re not trying to eliminate Excel—we want to reduce the risk of errors and reduce repetitive, manual effort.

With Excel processes, if you ask 10 accountants about their approach to the same rec, you’ll probably get 10 slightly different answers. This is a big reason for leveraging BlackLine’s best-practice-based templates: consistency is good for everyone, especially reviewers, new team members, and auditors.

Another issue is when reconciliations are performed in Excel and stored in shared folders, there is a lack of visibility into the real-time status of reconciliation activities.

Let’s take a real-world look at an account rec in Excel versus BlackLine.

Let’s use an example of a rec in Excel using a prepaid expense account. Imagine you have a retail business that has multiple stores, and you’re working on the prepaid property taxes account. In Excel, you’ll probably have a summary sheet that is linked to each of the stores individually, each of which has dates and calculations that are broken down by category. There are sheets for all the other stores. The more stores you add, the more complex it gets and the bigger the file grows, making it tough to manage.

In Excel, you could link some things together, and you can set up calculations, which could update the calculations based on what period you’re working in. But most people don’t go to that level of effort.

One of the options you have in BlackLine is to attach your Excel file. You can add a description that says, ‘see Excel file for calculations.’ Another way would be to summarize each of the stores—store one, two, and three, and the balances. You’ll still come up to that same balance comparing to the GL.

So we’ve seen what that rec would look like in Excel. But what if you could leverage BlackLine’s templates to gain more visibility, control, and efficiency?

Let’s start with visibility. Dynamic dashboards offer real-time insights on key closing tasks to manage the close and mitigate risks. Redirect your efforts to the most critical items, like past due reconciliations.

You can use filters and saved views to quickly access information like status, account number, description, unidentified differences, preparer, and risk. These will help you monitor reconciliation progress and gain instant visibility into unidentified differences to focus time and effort, remove bottlenecks, and identify efficiencies.

Now, let’s continue with the prepaid expense reconciliation.

Setting up templates in BlackLine is a one-time effort, and the template can be changed as required. If you need to, this template allows you to add or import details for items that should be amortized. The schedule is automatically built, and it will automatically roll the balance forward each month.

Assuming you've booked the amortization expense to the GL, a rec could automatically be certified by BlackLine when the schedule balance matches the GL within a tolerable threshold that you determine. Automatically booking that amortization journal entry is another great automation opportunity that you can achieve with BlackLine’s Journal Entry.

Across all the templates, you'll be able to feed the general ledger balance in on a scheduled basis—every 10 minutes, every hour, twice a day. It’s controlled, so you don't have to tie it out, which saves a lot of time. If something changes, BlackLine will alert you that the balance changed and decertify it so you can take a closer look.

BlackLine will also calculate this difference in terms of what has not yet been supported in the GL balance. That difference can have a tolerable threshold of $0, $50, $100—whatever your organization is comfortable with. Most companies use a risk-based approach or use the same global amount. You're not able to certify unless it's within that, so you’re adding control to the process.

When you certify your rec, supporting items, attachments, and comments will be locked so nobody can edit them. That's a huge control feature. And once you certify, BlackLine will automatically send the rec to the next person in the workflow.

The entire balance is unidentified because we haven't added anything yet.

You can add items individually, or if you already have details in Excel (often in a GL detail report), you can import these items. BlackLine will parse your file and split out the different sheets, so you can select the right one.

Here's a GL detail report—the yellow fields are what the GL provided. We've added the green fields to extract the exact details and format to upload into BlackLine.

The fields at the top are mapped to what BlackLine needs. The system does a pretty good job of this, but you’ll want to spot check to be sure. After you've imported it once, the system will remember what you’ve imported previously. Then you can simply spot check the items you want to import and click them individually, or use the box at the top that checks them all. Then you can import them all in a matter of seconds, and you've got everything in your rec.

In this example, there’s a difference of $2,820.10. To review the differences, we can summarize by store number and compare to what's in Excel, where we can see the store number is off.

If we look at the Excel file, we can see that someone is amortizing over 11 months, when it should have been 12. People do a lot of copying and pasting because it's easy in Excel, but it can lead to unintentional errors. Excel isn’t built to alert you to these errors, but BlackLine is, and it will let you know when there's an issue.

This example is not a major error, but it shows how easy it is to make mistakes in Excel. Unless you're reviewing every formula every month, there's risk that errors will go undetected. BlackLine’s goal is to reduce that risk and refocus your manual effort to more valuable tasks.

BlackLine’s Account Reconciliations solution is flexible to meet your business's unique needs, it’s easy to set up, and accounting teams of all sizes will find value, save time, and gain control.