Solving the Problem of Rounding in Spreadsheets
Rounded arithmetic is the traditional way of presenting this-and-previous-year accounts. In such presentations there is always at least one point of correspondence between the end of the previous year (rounded) and the start of the current year (unrounded). Accountants like to see consistency at these points, as well knowing that their arithmetic is correct.
Currently available spreadsheets offer 2 methods: rounding to the nearest unit before arithmetic is performed in which case the arithmetic will be correct but the total may be wrong; or rounding after the arithmetic has been performed in which case the arithmetic may be wrong but the total will be correct. For example, given the sum
1.3 + 1.4 = 2.7
rounding before adding gives
1 + 1 = 2 total wrong but arithmetic OK
whilst adding before rounding gives
1 + 1 = 3 total OK but arithmetic wrong
The traditional practice in accounting is to allow the components
of a sum to be rounded either up or down (the error is now up to
twice that of rounding to the nearest unit) but to constrain the
total to be rounded to the nearest unit. Thus, in the example above
accountants would allow either of
2 + 1 = 3 total OK and arithmetic OK
but would prefer the first. Implicit in this is the need for whole units to remain invariant. (This is not shown in the example above.)
The algorithm for vectors of any length is linear in time and can be applied at any unit: e.g. $1, $1,000, $1,000,000, etc. It was known in the data processing community of the 1960s as Round Pound Arithmetic (RPA), but there appears to be nothing on this method in the literature and no attempt to implement it in spreadsheets. The algorithm is easily proved correct for vectors of any length.
Accounts are rarely as simple as mere vectors. A component of one
vector may itself be the sum of another vector. This is the
structure known as a tree. The algorithm has been adapted for
arbitrary trees and formally proved it correct. The value at the
root is the value which is rounded to the nearest unit. The
algorithm is linear with the number of nodes in the tree.
A method based on constraint satisfaction with tabu list has been used to round arbitrary acyclic directed graphs according to the RPA constraints, with all "roots" rounded to the nearest unit. Using a test generator, it has been tested extensively on cross-tabulations in 2 and 3 dimensions (where every node is constrained to sum into to 2 or 3 separate totals), on arbitrary graphs, and on graphs representing sets of intersecting trees, these being the structure of financial statements.
Two very small graphs have been found (one of 4 nodes and one of 5 nodes) to which it is impossible to apply RPA. These are likely to be too small to be of interest to anyone using a computer. Fortunately, the more nodes there are in the graph, the more solutions are possible.
A few cases of 2 by 2 by 2 cross-tabulations have been observed in which the present algorithm fails although a solution within RPA constraints exists. Here, the search for a solution cycles because the tabu list is short (only 8 nodes) and the interconnectivity high. However, because the number of nodes is very small (28) they may be easily and very quickly solved by enumeration. The same problem has been noticed in some intersecting trees with a very high density of integers and consequently little room for maneuvering. None of these cases is likely to crop up in even modest use of spreadsheets. But even in these few cases where the algorithm does fail, it confesses and the number of anomalies which remain is no greater, and often less, than there would be with traditional methods. Further research into these cases may yield improvements in this area.
A demonstration program consisting of a simple type of
spreadsheet has been written. Values may be rounded according to RPA
constraints as well as by the two traditional methods. The method
has also been built into a simple accounting program which is in
regular use by a local organisation.
A general description of the method as applied to cross-tabulations was published in Software Practice and Experience, Vol. 26, No. 3, March 1996.