Best Practices for Financial Reporting - Eliminate rounding errors
- Jamie Black
- Excel
- minute(s)Complex reports (Financial statements / Comprehensive Annual Financial Report, Budget Book, etc) often present the same value in multiple locations in multiple ways. For example: on page 5 we show total revenue, on page 100 revenue is broken down by type, and on page 200 revenue is broken down by source. The trouble is, the value on page 5 and the total of the break-downs on pages 100 & 200 must all agree. The most common reporting tools (Word & Excel) do not have good methods to confirm and enforce that agreement (see how CaseWare Working Papers solves this problem here). You're left with a manual, error-prone process. The amount of detail provided in even a moderately complex report means rounding errors likely occur hundreds of times. This requires massive time investment during the reporting process to check & double check & triple check as your proceed through the reporting process. This article will explore just one of the common causes of disagreement between these values and suggest best practices to minimize failures of agreement. Different Rounding Approaches Lead to Disagreement As we have examined in our post Best Practices for Financial Reporting with Excel (Step 2), the optimal method for dealing with these 3 different revenue presentations is by linking back to central data source. We will assume you are following this advice. Let's use the following as our central data source (G/L): Approach 1 On page 5, where we just need total revenue we would enter a formula that adds all of the individual account values and rounds the result. Your page total 5 will show total revenue as $804. Approach 2 But what about on page 100 where we present revenue by type? The problem becomes apparent. When we need to present more detail, we round each individual account value and then add up those rounded values. Your page 100 will show total revenue as $801, which does not agree to the total of $804 presented on page 5. In other words the problem is we use different approaches to rounding: Approach 1 adds raw values and rounds the total Approach 2 rounds raw values and then adds them The Solution This problem may seem trivial: "Just link the values together!" For example, the value on page 5 might be a cell reference formula to the total of the revenue by type on page 100. As we discussed in our post Best Practices for Financial Reporting with Excel (Step 2) this causes other problems and only "solves" this problem in one location. It does not address the total on page 200 or any of the other locations that we provide break downs. A better way to address this problem is to attack the foundation of it; always use a single approach to rounding! We must choose one of the two approaches. Given that we must provide detailed disclosures which must be presented rounded to the nearest dollar in many locations we are forced to select the rounding model used in the detailed break-down schedules everywhere. So the recommendation is: If you must present detailed but also rounded disclosures, round all account values in your report and then add them. Thus the value on page 5 becomes $801 which will agree "per-force" with all other presentations. By following this model you will immediately eliminate all of your "true" rounding issues. In coming articles we will discuss some of the other causes of disagreements.
Many finance departments use spreadsheets where rounding errors are a continual struggle. This article presents best practices to minimize rounding errors.
READ MORE
Best Practices for Financial Reporting with Excel (Step 2)
- Jamie Black
- Excel
- minute(s)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: a summary line on the balance sheet a summary line at the bottom of the Statement of Cash Flow 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 : The note details are populated by summation of GL accounts. The Statement of Cash Flow value is created by summing the lines above. 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: The note details are populated by summation of GL accounts. The Balance Sheet value is also populated by summation of GL accounts. 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. Look for our next article "Best Practices for Financial Reporting with Excel - Step 3" where we'll discuss more ways to mitigate the risks inherent in financial reporting using spreadsheets.
Best Practices for Financial Reporting with Excel Step 2: Minimize Data Sources
READ MORE
Best Practices for Financial Reporting with Excel (Step 1)
- Jamie Black
- Excel
- minute(s)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: Frequency - How many times a year will this tool be used? Users - How many different people will use the tool? Standardization (1-10) - How much change in format is required for each use (see frequency above) Complexity (1-10) - Are we talking about a 10 row spreadsheet or a series of workbooks linked together with hundreds of tabs? 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. Their VR score: 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.
Spreadsheet applications like Excel are incredibly flexible and easy to use tools but if you are not careful, they can kill your career. Step 1 of this process is covered in this article.
READ MORE
Financial Reporting with Spreadsheets - Avoiding the Traps
- Jamie Black
- Excel
- minute(s)Finance professionals around the world use spreadsheets for nearly every purpose imaginable. We have seen finance officers use spreadsheets to prepare their year end financial statements, file their FIR, prepare quarterly variance reports and produce their SEC filings. We know auditors who use Excel to do data analysis, gap detection on cheque numbers, and identification of duplicate payments. Why spreadsheets? While there are certainly a great variety of causes for the ubiquitous use of spreadsheets, we would suggest 3 major reasons: Availability - your organization almost certainly owns a spreadsheet program (likely Excel). If not, Google Sheets, OpenOffice and Libre Office all provide free tools. Nothing gets accountants excited like a no-cost solution. Flexibility & Power- They can be made to do just about anything. We have seen clients with 50 to 100 spreadsheets all daisy-chained together acting as their database. Why? Because they could. Ease of Use - With very little training on the software, nearly anyone can open Excel and quickly create a very sophisticated tool to solve a burning issue. So what's the problem? There are two major issues with Excel and they build off of each other: Spreadsheets are very susceptible to error: "In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists." What we Know about Spreadsheet Errors - Raymond Panko No doubt you have encountered some or all of these mistakes: someone over-typed a calculation or created a formula incorrectly (include too many rows in a Sum for example) or hard-coded a value in a cell that changes often or forgot to update a value after it had changed or rows / columns are cut off in print-ready version Few people perceive how common these errors are: "As noted earlier, when Brown and Gould (Brown & Gould, 1987) gave three spreadsheet development tasks to nine highly experienced spreadsheet developers, all made at least one error, and 63% of the spreadsheets overall had errors. Yet when asked about their confidence in the correctness of their spreadsheets, their median score was "very confident." What we Know about Spreadsheet Errors - Raymond Panko The result all too often is major catastrophe and disaster. If you think I'm exaggerating, consider this article from Fortune.com: Damn Excel! How the 'most important software application of all time' is ruining the world...The popular Microsoft program has been implicated in the financial crisis, Europe’s growth problems, the U.S.’s weak economic recovery, and pretty much everything else....read more So no more spreadsheets? Abolishing all spreadsheets from your organization, while momentarily appealing is just not practical. Just this week in-fact we had a client tell us that unless their requested solution resulted in Excel files, their users would revolt! So if we can't live with 'em and can't live without 'em, what are you to do? In this series of articles we will explore a 4 step approach to Optimizing Excel (and other spreadsheets) as your financial reporting tool: Step 1: Confirm a spreadsheet is the right tool Step 2: Standardize your spreadsheets Step 3: Use variabilization, error-checking & data tagging Step 4: Lock & document your spreadsheets
Excel is incredibly flexible & easy to use tool for financial reporting but if you are not careful, it can turn into a trap
READ MORE
