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.
