Accounting

5 Strategies for Streamlining Reconciliations in Excel – By Popular Demand

Apr 17, 2017 Katrin Kitzman

We’ve heard repeatedly from many of you that you like our webinars. We try to make them educational and share practical, hands-on accounting knowledge.  Among our most popular ones continues to be “5 Strategies for Streamlining Reconciliations in Excel” by our co-founder and CEO, Mike Whitmire. Mike is a CPA and has worked both for the Big Four and in-house and has a wealth of accounting experience. He also loves to share his ideas and suggestions on how accounting departments can streamline their month-end and quarter-end close process.

I’m going to cover the highlights here, but feel free to check out the playback sometime when have an hour to spare.

Okay, first things first — Here at FloQast, we love Excel and that’s why we built our solution around it. Despite all the haters out there, it remains one of the best, most powerful tools that accounting teams use. In case you need a refresher, we covered a handful of Excel’s benefits in the webinar:

  • It’s easy for organizing, massaging and manipulating data
  • Has lots of functions designed for accounting and financial modeling
  • Has the ability to manage a large cache of data
  • It’s the common language between accounting, audit and tax
  • There are 30 years of development behind the product

We admit that Excel has its weaknesses too, mostly around the lack of controls, collaboration and project management. We’ll try to address those shortcomings in these strategies.

Speaking of strategies, let’s dive into them. Now, these might seem deceptively simple, but you should know that that’s by design! Accounting is hard enough, let’s try to keep the process easy.

  • Assess the trial balance for completeness — This might be something that you take for granted, but to know that all of your balance sheet accounts are being considered is essential to an accurate close. Each team will have to identify the key accounts for the appropriate period and should think about these key questions to ask when assessing completeness:
    • Are you tieing out the trial balance at the end of each month?
    • How are you currently tieing out each account?
    • How is each reconciliation structured?
    • Why is the reconciliation structured that way?

After answering these questions, your team can properly measure the completeness of your balance sheet for each close.

  • Standardize templates with documentation — Consistency is crucial to streamlining any process on a team and the close is no different. Standardizing your reconciliation templates will accomplish a lot of efficiencies including:
    • Team alignment
    • A consistent, streamlined preparer process
    • Gives your team some role flexibility
    • An accelerated review process
    • Reduced errors
    • It’s simpler for your auditors

If you’ve struggled to create templates for reconciliations, not to worry, you can download some our examples and customize them for your specific needs.

  • Organize documentation in a logical manner — We get to see how our clients work together and have concluded that all happy accounting teams are alike, while all unhappy teams are unhappy in their own way. Well, mostly anyway.

We advise those not-so happy teams on some best practices for organizing their documentation and the most common approach looks like this:

Picture1

There may be variations on this setup, depending on your entities, business units, processes and accounts, but for the most part, this is what we recommend for organizing your file structure. If you’re on a very small team, organizing the documentation by individual might make more sense, but as the team grows and the business starts to experience turnover, a more static structure like the one above will serve better.

  • Tie out trial balance — How is your team doing this? We’ve noticed three main ways that accounting departments are tieing out their trial balance:
    • Old: Printed tie-out binder — This the manual, tick and tie with red pens. If you’re feeling ironic, you might find this interesting, but the nostalgia wears out fast.
    • Now: Consolidated excel workbook — Lots of teams have moved their trial balance tie-out to Excel, which is an improvement over the ol’ tick & tie above, but if the number of accounts in your balance sheet is growing, this will get unwieldy fast.
    • Future: Dedicated software — This is the future of the tie-out and our personal favorite. We’re biased, of course, but it will save teams tons of time when many aspects of your close are automated and communication is streamlined.
  • Centralize reviews — Do all of your reviews happen in one place? No, of course not. Reviewers ask questions throughout the day and in many different ways, which can lead to all sorts of problems. By centralizing your review process you can eliminate all kinds of problems, like:
    • Inconsistent formats of reconciliations
    • Disparate systems, data and processes
    • Lost clarifications in emails.
    • Communication barriers (e.g. global operations)
    • Lost process knowledge with employee turnover

And here are the ideas for solutions:

    • Standardize checklists and reconciliations — By creating forms with consistent formats, layouts and parameters, teams will get accustomed to the structure. This will shorten the time to complete each task and will reduce the learning curve for new team members.
    • Team alignment on process — Similar to the standardized checklists and recs, setting expectations up front will keep your team all on the same page. Periodic refreshers when new processes or team members come up will help keep your close focused without losing a step.
    • Cloud systems allow for integration — One way to help with standardization and team alignment is to use tools that allow for easy collaboration. Cloud-based solutions like Google Docs or Box provide access to anyone and permit real-time updates from any team member. No lost work, no multiple versions, no emailing. It’ll change the way you think about teamwork.
    • Dedicated solutions emerge for niche functions — When you’re ready to supercharge your close, you’ll start thinking about tools that can help you do all of the above. That’s where FloQast comes in!

That’s it! Nothing to it, right? Hopefully these strategies help streamline your Excel reconciliations. We’ll publish more posts in the future to address some of these in detail but for now, go pick up our reconciliation templates and when you get a moment, listen to Mike walk through each of these points if you need help getting started.