How to Perform an Account Reconciliation in BlackLine

6-minute read

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, and more visibility, control, and consistency. That’s why thousands of accounting teams use BlackLine to set up their balance sheet recs and other close-related items.

What’s Wrong with Excel for Account Reconciliation?

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. 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. That's a big reason for leveraging BlackLine’s best-practice-based templates. Consistency is good for everyone, especially reviewers, new team members, and auditors.

An Example of an Account Rec with Excel

Let’s use an example of a rec in Excel using a prepaid expense account (see the example below). 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, which is linked to each of the stores individually, which each have 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—it can be tough to manage.

In Excel, of course, 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.

Would you rather watch a video about this topic?

Account Reconciliation with BlackLine

But what if you could leverage BlackLine’s templates to gain more visibility, control, and efficiency? Let’s use an example of the amortizable prepaid template.

Setting Up a Template

Setting up templates in BlackLine is a one-time effort and the template can be changed as needed. 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.

Automatic Certification

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.  Booking that amortization journal entry is another great automation opportunity, by the way.

Scheduled Balances

Across all the templates, you'll be able to feed the GL 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.

Difference Alerts

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.

Controls & Workflows

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.

Importing & Mapping Fields

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.

Back to Our Example

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—we can see store number one 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.

Learn more about how BlackLine Account Reconciliations can help your organization.