Best Practices for Financial Reporting with Excel (Step 2)

In the last part of this article series, we recommended taking some time to confirm that spreadsheets are the right tool for a specific financial reporting task. 

If you do conclude that a spreadsheet is the best tool for the job, you have to be careful to set youself up for success.  The wide-open nature of Microsoft Excel means there are ample opportunities to "shoot youself in the foot." By following these best practices, you can avoid some of the pitfalls that come with using such a general-purpose tool for your specialized reporting tasks. Let's get started!

Step 2: Minimize Data Sources

Take a minute and list all the reports your finance department must create in a year. At a minimum your list likely includes:

• annual financial statements,
• quarterly statements,
• management discussion & analysis (MD&A),
• financial plan or budget documents,
• actual to budget reports that you present on a monthly or quarterly basis,
• forecasts where you analyze actuals and project how well you will meet budget,
• Tens or hundreds of specific purpose reports. Things like bank covenant reporting or provincial reporting (FIR, LGDE, SOPHI)

The point is, there are a lot of reports and each one needs to present data slightly differently. More detail in one, less in another. One includes actuals only, this other adds budget data. This one shows capital expenditures as expenses, but another one treats them as assets.

Consequently, folks tackle each one of these reports as a discrete task. Consider the example of a finance department who is working to prepare the Q1 financial statements and the accompanying MD&A.

The data from the accounting software (often referred to as the ERP system) is exported to begin building the financial statements. In this case, the Excel file becomes the direct data source for the financial statements.

When the statements are nearly complete, either the spreadsheet file that houses the financial statements is copied or another export from the accounting software is created so that the team can work on the MD&A.  

Now imagine the auditor comes back and requires an adjustment be posted. This change needs to effect both files. Not only must finance make the change twice, but there is also now the need to reconcile the data between the two Excel files to ensure that the Financial Statements and MD&A agree.

Compare this to an approach where the MD&A is built off the same Excel file. Changes made in one file necessarily effect both the statements and the MD&A. Reconcilliation of the two reports becomes much faster and simplier.

While this may seem obvious, we rarely see this practice implemented consistently. In part this is caused by difficulties in sharing an Excel file across multiple users or difficulty in dealing with variations in grouping of data. Whatever the reason, when you consider all the reports that the team tackles, duplication of data sources leads to massive amounts of wasted time regrouping data, adjusting in multiple locations and then reconciling to ensure the values balance across data sources.

The very same only different

This same best practice of minimizing data sources also applies within a single spreadsheet. Take Cash as our example. Typically, cash needs to be presented in two or three places:

1. a summary line on the balance sheet
2. a summary line at the bottom of the Statement of Cash Flow
3. a detailed breakdown in a Cash and Cash Equivalents note

Option 1: Indiscriminate Linking

In this option, the focus is on creating the report as quickly as possible. Consequently, there is little discipline applied to how these values are populated. The result is multiple approaches to getting the same value :

1. The note details are populated by summation of GL accounts.
2. The Statement of Cash Flow value is created by summing the lines above. 
3. Finally, the Balance Sheet value is based on linking to the total of the note. This is done to ensure the note and Balance Sheet values agree.

Option 2: Minimize Data Sources 

This option focuses on long term benefits instead of speed of initial creation. The focus is on standardization and consistency. Wherever possible values are derived the same way:

1. The note details are populated by summation of GL accounts.
2. The Balance Sheet value is also populated by summation of GL accounts.
3. The Statement of Cash Flow value is created by summing the lines above. This variation in approach is necessary to ensure the integrity of the statement.

At first glance these Option 1 and Option 2 may appear equally good. But, having values derived in lots of differing ways can cause numerous complications:

• Secession planning -  If there are a large variety of methods used to populate statements, it can be much more challenging for new staff to use the existing template properly. Less variety = Easier to Learn.
• Quality control - Consider that if all cells on the balance sheet are consistent, it is easier to review formulas to ensure accuracy and completeness. If there is great variability in the linking methods (and therefore the formulas) review gets much more complicated.
• Cross-Linking means 0% or 100% - Notes often contain much higher levels of detail than statements or schedules. As a result, notes regularly take more work and therefore time to complete. If the values on the Balance Sheet are linked from notes, your Balance Sheet will remain out-of-balance and unusable until every last line item detail in the notes is in place.