About Rounding on Balance Sheets

If you have spent much time working with financial statements, you know that balance sheets do not 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, and 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, and 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, and 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).

This specification 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. Displays the balance for total liabilities and equity. Since this balance must match assets, the specification uses the same total. (Row 13.)

Note: 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 the previous example:

1. It more accurately reflects an unrounded balance sheet, because the final balance is derived by summing unrounded balances, instead of by summing rounded ones.
2. 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 will not balance if you leave out an account.

Before generating the final report, you must hide column F, as well as rows 6, 9, and 10. (See the final report example, below.) For information about hiding columns and rows, see About Formatting Financial Statements.

This specification 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: 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. Adds the difference between the totals to the last range of accounts (in row 7 of the spreadsheet).

By adding the rounding difference to the last group of figures, we have forced the figures in column G to equal the total in G8.

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

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

Rows and Columns Hidden on Report

Description		Current Balance
Cash	37,657.35	38,000
Receivables	89,576.73	90,000
	356,526.85	357,000
Fixed assets		356,000
Total assets:		484,000
		485,000
		(1,000)