Sage Intacct Tips for a Balanced Balance Sheet

An out-of-balance balance sheet doesn’t have to incite panic and fire drills. Learn three quick tips for troubleshooting a balance sheet or any other Sage Intacct financial report.

Gather reports

The first step in troubleshooting Sage Intacct financial reports is downloading the following to Excel:

• Chart of Accounts
  • Go To: General Ledger > All tab > General Ledger accounts 
    
  • Click Export to Excel
    
  • View the COA exported file
• Top-Level Account Group Hierarchies (Assets Group and Liabilities Group)
  • Go To: Reports > Balance Sheet report > Edit > Rows tab
  • Hover over the top-level account group > click View
  • Click Hierarchy
    
  • Export this report to Excel
  • Repeat these steps for the Liabilities and Net Assets account group to export to Excel
  • Combine the two files so you have a full list of all accounts in your Balance Sheet

Once we have both reports, we’re ready to troubleshoot.

Tip 1: Are any accounts duplicated?

Imagine you followed this blog post and set up all account groups with ranges for your initial report build many years ago. You designated 1200 through 1499 as accounts receivable accounts. There were no accounts in the 1400 range then, but you wanted to include them in the balance sheet.

Fast forward to present day, you add a new account group and account — 1412: grants receivable — in the 1400s. You forget your original accounts receivable account group range and now 1412 is in both AR and grants receivable, making your balance sheet unbalanced. This brings us to tip number one: check for duplicate accounts in your report.

In the combined account group hierarchy report, highlight your Account column > select Conditional Formatting > Highlight Cell Rules > Duplicate Values.

You’ll quickly be able to spot any duplicated accounts highlighted in pink, update the account groups, and have your balance sheet back in balance in no time.

Tip 2: Are all accounts accounted for?

Now imagine you inherited financial reports where ranges were not employed in account groups. Instead, specific accounts were selected and added to ranged. You add that same new account — 1412: grants receivable — but instead of creating a new account group for it, you assume it will be picked up in an existing account group and automatically display on the balance sheet. Instead, the balance sheet is out of balance, which brings us to tip number two: checking all accounts are accounted for in your report.

In the combined account group hierarchy report, we’ll add a formula to column F to pull out our general ledger account numbers so that we can create a VLookup in our chart of accounts to check all accounts are included.

In the farthest column to the right, add the following formula:

Copy the formula all the way down to all rows. In your COA excel file, add the following formula to the farthest column on the right:

Now, look for any #N/A rows. (Use conditional formatting on the column to highlight or just filter the column).

An #N/A row, like row 12 in the example, indicates an account is missing from the financial report and needs to be added to an account group.

Tip 3: Are the calculation methods correct on all account groups?

If accounts are not duplicated, and all account are accounted for in the report, our next troubleshooting step is to check calculation methods on account groups.

When setting up a new account group, it’s easy to overlook the calculation method field as it is pre-populated with a default “for period.” However, for balance sheet account groups, this selection should be “end of period.”

To find any offending account groups, view the account group hierarchy report for the calculated amount column and check all balance sheet account groups are set up with “end of period” and change in net assets/net income with “for period.”

How we can help

Hopefully these three tips have helped resolve your balance sheet woes, but if not, or if you would like additional assistance building custom financial reports or additional training, please reach out to your CLA Sage Intacct team.