Best Practices for Financial Reporting with Excel (Step 1)

In the first part of this article series, we discussed why spreadsheets can be problematic. In short, spreadsheets are cheap, available and flexible, but they are very high risk!

As finance professionals, how do we improve our processes to mitigate the risk but get all the benefits?

Step 1: Confirm spreadsheets are the right tool

As with anything else, the key is to find the right tool for the job. The simplest way forward is to calculate (accountants love this part) a score. The higher the score, the more you should be looking to find an alternate approach to spreadsheets:

1. Frequency - How many times a year will this tool be used?
2. Users - How many different people will use the tool?
3. Standardization (1-10) - How much change in format is required for each use (see frequency above)
4. Complexity (1-10) - Are we talking about a 10 row spreadsheet or a series of workbooks linked together with hundreds of tabs?
5. Required Accuracy (Percentage) - What percentage of accuracy must be maintained?

Multiplying each of these values results in your Value of Replacement (VR) score.

Consider an example with one of our clients:

• They were being asked to forecast performance 3 times a year.
• They would take data from the General Ledger, export it out into 20 Excel workbooks. Each spreadsheet contained hundreds or thousands of rows.
• They then distributed those spreadsheets to 20 users . Users had to go row-by-row, assess actuals and develop a model for how to forecast the balance to the end of the year
• Finally each user returned their spreadsheet to Finance.
• Finance then combined the spreadsheets back into one workbook and did analysis.
• Ultimately Finance needed to summarize the data and prepare a final report (tables of data and narrative explanations) to council.

3f x 20u x 8s x 9c x 99%r = 4,277

Now what if this work was very simple? 

3f x 20u x 8s x 1c x 99%r = 475

As you can see, the first scenario yields a much higher value for replacement than the second. And by comparing the VR scores of different processes, you can determine which ones to prioritize to yield the most benefit from new tools and automation efforts.

For the others, Excel may be the perfect tool for the job. But that doesn't mean you're done - not all spreadsheets are created equal. Look for our next article "Best Practices for Financial Reporting with Excel - Step 2" where we'll begin discussing ways to mitigate the risks inherent in Excel reporting.