The balance sheet is looking pretty good at this point, but there is one glaring problem: the Liabilities & Owners Equity section lists accounts for revenues and expenses. As discussed earlier, this has to do with the company’s net income and we need to handle it appropriately. To summarize the earlier section, if a company transfers the net income into retained earnings with a year-end adjusting entry, there is no need to show net income. If it doesn’t do this, you should summarize the net income as a single line item.

In the sample financial database provided with the Crystal Reports software, there are no year-end adjusting entries. Thus, we have to summarize the net income as a single amount on the balance sheet and display it within the Liabilities & Owners Equity section.

If your accounting system does close-out the net income balance into retained earnings, you should exclude all revenue and expense accounts from your report. This can be done in the record selection formula by only selecting records from the appropriate account types. For example, the records selection formula for this database would be:

{Account_Type.Account Type} IN [“Asset”, “Equity”, “Liability”]

If you recall from the discussion of the {@Amount} formula modification, the expense accounts didn’t have their amounts switched according to the rules in Table A-1. This is because we are going to add the expenses together with the revenues so that they are summarized into a single Net Income sub-total. This lets the balances for the two account types offset each other.

To create the Net Income balance on the report, we are going to list it as if it were an individual account with its own sub-total. I’m going to include it within the Retained Earnings heading since they are related. This requires modifying the account heading values so that they all say Retained Earnings (thus including them in that group). For the account number and account name fields, we want them to return a single number and name so that they get consolidated into one line on the balance sheet. This requires modifying four formulas.

37. Right-click on the formula {@Account Heading Number} and select Edit Formula. Change the formula to the following:

if {Account_Heading.Account Heading Number}>="4000" Then
"3500"
else
{Account_Heading.Account Heading Number}

The account heading numbers 4000 and greater all represent revenue and expense accounts. The same applies for the account numbers. This formula takes those accounts and gives them the number 3500. This reclassifies them as retained earnings. We also need to do the same for the account heading names since the names are displayed on the report.

38. Right-click on the formula {@Account Heading Name} and select Edit Formula. Change the formula to the following:

if {Account_Heading.Account Heading Number}>="4000" then
"Retained Earnings"
else
{Account_Heading.Account Heading Name}

39. Let’s modify the account details so that all the accounts are consolidated into a single account called Net Income. Right-click on the formula {@Account Number} and select Edit Formula. Change the formula to the following:

if {Account.Account Number}>="4000" then
"4000"
else
{Account.Account Number}

This changes all revenue and expense accounts to have the same account number of 4000.

40. Right-click on the formula {@Account Name} and select Edit Formula. Change the formula to the following:

if {Account.Account Number}>="4000" then
"Net Income"
else
{Account.Account Name}

This changes the account names of all revenue and expense accounts to be Net Income. Now that the account number and account names are all the same, the total net income balance will appear as a single line item within the Retained Earnings section.