Handling Rounding on Balance Sheets

If you've spent much time working with financial statements, you'll know that balance sheets don't balance when you round account totals. You always have to force a balance by adding a rounding difference to one of your accounts or account sections.

The next two spreadsheet examples show two ways to ensure that your balance sheets will balance:

• Example 1 shows a simple mechanism that rounds all balances on the statement, then forces the totals of rounded assets to balance rounded liabilities.

• Example 2 produces a more theoretically correct balance by summing all unrounded account balances, then forcing the statement to balance to this sum.

Example 2 makes use of a hidden column of unrounded account data, and so will require a larger spreadsheet and more memory to run.

Example 1: Forcing a Rounded Balance Sheet to Balance

This sample statement rounds and sums all printed figures on each side of the balance sheet, then forces liabilities and owner equity to balance with the assets. Any rounding differences are lumped with owner equity.

The specification for the statement includes a few rows that must be hidden before generating the final report (row 11 and row 14).

The specification listed above performs the following actions:

1. Prints the rounded balances of all asset accounts it retrieves from the general ledger using "=ROUND(FRAMT("BALP"),-3)."

The "-3" indicates the number of digits to the left of the decimal place to which the value is rounded -- in this case, thousands.

Because "D" is specified in column D, each account is printed on a separate line. (This is on row 5.)

2. Totals the rounded balances of all asset accounts. (Row 6.)

3. Prints the rounded balances of all liability accounts it retrieves from the general ledger using the same formula. Again, each account is printed on a separate line. (Row 9.)

4. Totals the rounded balances of all liability accounts. (Row 10.)

5. Retrieves a single balance for equity. This amount is calculated on a hidden row, because the difference between the rounded assets and the rounded liabilities plus equity will be added to the equity section. (Row 11.)

6. Calculates the difference between the balances. (Row 14.)

7. Sums the difference between the balances and the equity amount. (Row 12.)

8. Plugs in the balance for total liabilities and equity. Since this balance must match assets, the specification uses the same total. (Row 13.)

This specification assumes no missing accounts

Note that if any accounts are missing from this statement, it will still balance (because we have forced a balance between the assets and the liabilities plus equity).

Example 2: forcing a balance to the rounded total of unrounded account balances

The following statement example shows another way to set up a rounding mechanism in balance sheets to handle rounding differences.

This method has two main advantages over example 1, above:

• It more accurately reflects an unrounded balance sheet, because the final balance is derived by summing unrounded balances instead of by summing rounded ones.

• It is self-auditing.

In example 1, you are forcing assets to balance liabilities and equity, so even if you leave accounts off the statement, the report will balance.

In example 2, the rounded statement won't balance if you leave out an account.

Before generating the final report, you have to hide column F, as well as rows 6, 9, and 10. (See the final report example, below.)

The rows are hidden with the Format, Row Height command. The column is hidden with the Format, Column Width command.

The specification listed above performs the following actions:

1. Inserts the unrounded balances of all accounts in column F of the spreadsheet, and inserts the rounded balances of all accounts in column G.

2. Calculates a rounded sum of column F (in G8) and column G (in G9) of the spreadsheet.

Note that the rounded total to which we want to balance is the total in G8. The total in G9 contains the cumulative effect of all rounding differences.

3. Calculates the difference between the rounded totals (G8 and G9) in G10 of the spreadsheet.

4. Lumps the difference between the totals into the last range of accounts (in row 7 of the spreadsheet).

By lumping the rounding difference with the last group of figures, we've forced the figures in column G to equal the total in G8.

You must also handle liabilities and equity

You handle the credit side of balance sheet similarly, summing all liabilities with owner equity, then deciding where you want to put the difference between the sums of the rounded and unrounded balances.

The generated statement includes lines and columns that would be hidden on the final report (shown in bold):

Description		Current Balance
Cash	37657.35	38,000
Receivables	89576.73	90,000
	356526.85	357,000
Fixed assets		356,000
Total assets:		484,000
		485,000
		(1,000)