Rounding of numbers for presentation on financial statements has long been a pain point for Excel users. Financial statements are often presented rounded to the nearest whole dollar and, while this sounds rather benign, it can lead to frustrating discrepancies and financial statements that don’t add up. This problem is not unique to Excel and is an inherent mathematical phenomenon – the sum of the rounded figures will often not equal the rounded sum of the un-rounded figures. This is a result of the cumulative differences between the original figure to be rounded and the resulting rounded figure.
To illustrate the point, using Excel’s ROUND() function (or simply reformatting the cell to omit cents) $1.02 would be rounded down to $1.00 using standard rounding procedures, as would $1.49; yet the sum of these two cells, being $2.51 would round up to $3.00. That’s right, according to Excel, 1+1 can equal 3!
In this case, we want the total to be rounded to 3 because that is what the actual total should round to following normal rounding conventions. In a financial statement, the summations absolutely must equal the sum of the integers above them in order for the statement to balance, but we generally also want the totals presented to match the un-rounded figures underlying the data presented on the statements.
In order for the integer portions of all of the values being summed to equal the correct rounded total of the un-rounded figures, we need to add/subtract the rounding errors back into the figures before they are rounded.
The numbers in column C are simply copied from column B and are formatted so that cents are omitted. Excel will automatically perform standard rounding on the numbers when formatted this way. This looks great, but in fact, if you add up the numbers in column C, they do not equal $47,523 – they equal $47,524! We want it to equal $47,523 because that is the true rounded total. I know, $1 may not be material in most cases and you could just plug this somewhere – but with this simple trick, you can evenly spread the difference throughout all numbers so that you end up with the most accurate representation mathematically possible and all of your schedules will be comparable, with or without cents.
To do this, pick a cell that will store an adjustment value and put a 0 in that cell for now. Next, create a column that rounds the sum of the Debit column and your adjustment cell to zero decimal places (Don’t forget to lock the cell reference on the adjustment cell by pressing F4 after typing/clicking on cell B13):
Now, we want the sum of the adjusted debit column to equal $47,523 – we will use a tool in Excel called Goal Seek. In Excel 2016, Goal Seek is found under Data > Forecast > What-If Analysis > Goal Seek (see your version’s help documentation if you are using a different version of Excel). Set the first criteria in the Goal Seek dialogue (“Set cell”) to the cell containing the sum of the Adjusted Debit Column (E11 in this example). In the second criteria box (“To value”) input the rounded total that Excel should present on the financial statements – $47,523. Set the third criteria (“By changing cell”) to the cell containing the adjustment figure (B13). Click OK:
Excel will now look for a value for the adjustment that, when added to every value in the Adjusted Debits column, will round such that the sum will now equal the correctly rounded figure. In this case, all that was needed was to subtract one penny from every figure in order to result in correct rounding of the total.
Column F can now be used in the financial statements because all numbers are reasonably rounded and the total equals the actual sum of the numbers presented in column F.
This example is fairly basic and the cumulative rounding errors in larger financial statements will often be much less straight forward to correct, so Goal Seek provides us with an excellent way to easily achieve accurate financial statements when presented in rounded formats.
Written by Dan Langlois, CPA, MSA, Senior Accountant