There’s No One-Size-Fits-All Approach to Reconciliations, exactly how you lay out and prepare your reconciliations will vary by company, industry, software, ledger account, and much more. However, there are best practices that can make the process faster, clearer, and more efficient. Let’s explore how to structure your reconciliations for maximum impact.
Key Elements of a Well-Structured Reconciliation
Standardize the Basics
Start with the essentials that make your Excel file tidy and easy for anyone to pick up and understand: company name, date, account number, preparer, and reviewer. If you’re using a tool like
Easy Month End, these details can be automated, saving you time. For example, the software can populate the company name and date automatically, reducing the risk of errors.
Include a Summary
Place the general ledger balance and the sum of your supporting documentation at the top of the page. This makes it easy to see the reconciliation summary at a glance. For example:
Per GL: $100,000
Per Support: $99,500
Variance: $500 (see details of variance in note 1 below)
This approach ensures that anyone reviewing the reconciliation can quickly understand the key details.
A brief account summary can also be incredibly helpful, especially when training new team members or covering for colleagues. For example:
"This account represents the estimated co-marketing cost for the month. The journal is posted monthly and reversed once the actual amount is determined on the 15th of the following month, based on discussions with Mark from the Marketing team.”
Use Comments Effectively
Comments are your best friend in reconciliations. Use them to explain adjustments, unusual items, or anything else that might not be immediately clear to someone reviewing your work who may not have your intimate background knowledge. For example:
“$500 variance due to an incorrect journal entry, considered immaterial, will be corrected next month.”
This reduces the likelihood of questions during the review process and ensures that future users of the reconciliation can understand the details. Remember, these reconciliations may be reviewed at any time in the future, and not every detail will be remembered - so document everything clearly.
Break Down Complex Accounts
If an account is particularly complex, use multiple Excel tabs and listings to organize the data. For example, if you’re reconciling a deferred revenue account, split the workbook by revenue type, with each tab representing a different deferral item. Then, create a summary tab that brings the totals from each listing into the main reconciliation.
If your reconciliation still feels overly complicated, it might be a sign that you need to create a new GL account. Splitting items into separate accounts can make your life much easier and ensure the reconciliation is clear and easy to follow.
Avoid Hardcoding
Never hardcode numbers into cells with formulas. This not only creates errors when you roll the template forward to the next month (and can’t figure out why nothing balances), but it also makes it harder for reviewers to understand how the support adds up to the total GL balance.
For example, if you hardcode a $375 adjustment, you’ll need to manually update it next month. Instead, use formulas to link to other items or add an additional row that highlights the adjustment and explains why it was made.
Embed Reconciliations in Workbooks
The fastest and easiest way to prepare your reconciliations is to embed them into your month-end workbooks. This can be as simple as adding another tab called ‘Reconciliation’ and including key details such as the account balance and the sum of the supporting documentation.
This approach means your reconciliation is in the same place as all the supporting documentation, eliminating the need to create a separate workbook. For example, for accounts like accrued expenses and prepayments, you can set up your workbook to show a month-by-month view and the YTD balance, which will agree to the closing balance sheet. This essentially combines your reconciliation support and journal calculations into one workbook, saving time and effort.
Leverage Technology
Tools like
Easy Month End can help standardize your reconciliations, making them easier to prepare and review. With features like automated summaries and centralized storage, you’ll save time and reduce errors. For example, the software can automatically generate a summary tab, eliminating the need for manual data entry.
The Bottom Line
A well-structured reconciliation is clear, concise, and easy to follow. By standardizing your approach and leveraging technology, you’ll make the process faster and more efficient. Whether you’re reconciling a simple prepayment or a complex deferred revenue account, these best practices will help you stay organized and ensure your work stands up to scrutiny.