Former long-time business officer, recent interim CFO, and founding member of NBOA Jim Pugh shares wisdom he has collected in his decades of service to independent schools.
This is the ninth article in the series. Mapping your school’s trial balance to the audit’s financial statements helps both you and the audit process.
The annual audit is not just another wearying exercise for the business office. It is also one of the most important documents your school produces each year. While it is true that an external firm puts together the final product, the ingredients are your school’s financial information and policies. It is your school’s name which appears on the cover of the booklet of “Financial Statements and Supplementary Information.”
The audited financial statements are the financial information your school provides to financial institutions and foundations. The Board of Trustees uses the audit as an external eye on the school’s operations.
An audit with no surprises enhances the credibility of the business office. This report also provides a detailed record of the prior year of activity. Its information is easily accessed in the future. The archive of audited statements is invaluable for a longitudinal study, or for researching a financial issue which has its roots in the now-hazy past.
Rather than be in the dark until the auditors produce the statements, it is possible for you to anticipate the financial statements soon after you close the fiscal year. By mapping your trial balance to the audit’s statements, you have the opportunity to:
Spot the places which need adjusted entries.
See the impact of adjusting entries as they are made.
Confirm the auditors’ assumptions about which accounts are included in the line items of the statements.
Identify variances and issues to discuss with the auditors.
Think about improvements to the layout of the financial statements which are appropriate for your school.
Have a better understanding of the contents of the final document.
In other words, you will be a more proactive participant in the annual audit process.
This sample Excel file is an example of mapping a trial balance to the Statement of Financial Position, the Statement of Activity, and the Statement of Functional Expenses.
To create such a file for your school, start by asking the auditors for the master Excel spreadsheet with the final account groups, account balances, and statements in last year’s audit. Tell them about the purpose of the request. They will appreciate your assistance in developing the statements.
Add a new sheet in the master spreadsheet you receive from the auditors. Then copy/paste last year’s trial balance into the sheet. In the sheets with the account groups, replace the auditor’s hard-coded values with formulas using the “=sumifs( )” command. Once all of the hard-coded values are replaced with formulas, the statements in the spreadsheet should be the same as the statements in the audit. Use this spreadsheet as the template for the financial statements of the current year and future years. Be sure to add new accounts into the appropriate accounting group each year.
Note: There are examples of the “=sumifs( )” command in three sheets of the aforementioned Excel file (in the tabs “SFP detail,” “SOA detail” and “SFE detail”).
A smooth audit process helps you to shine in the eyes of the auditors, the Board, the Head, and the financial institutions which will later pose questions about the financial statements. Consider applying the power of Excel to this endeavor.
This article originally appeared in NBOA's Net Assets magazine as part of Jim Pugh's Business Office Basics series, we have made minor alterations for an international school context. Sage Consultancy strongly recommends membership of NBOA for international schools as a great resource for school leadership.