End-of-Period Spreadsheet (Work Sheet)

Accountants often use working papers for analyzing and summarizing data. Such working papers are not a formal part of the accounting records. This is in contrast to the chart of accounts, the journal, and the ledger, which are essential parts of an accounting system. Working papers are usually prepared by using a computer spreadsheet program such as Microsoft’s Excel™.

The end-of-period spreadsheet shown in Exhibit 1 is a working paper used to summarize adjusting entries and their effects on the accounts. As illustrated in the chapter, the financial statements for NetSolutions can be prepared directly from the spreadsheet’s Adjusted Trial Balance columns.

Some accountants prefer to expand the end-of-period spreadsheet shown in Exhibit 1 to include financial statement columns. Exhibits 18 through 22 illustrate the step-by-step process of how to prepare this expanded spreadsheet. As a basis for illustration, NetSolutions is used.

1. Step 1. Enter the Title

The spreadsheet is started by entering the following data:

1. Name of the business: NetSolutions
2. Type of working paper: End-of-Period Spreadsheet
3. The period of time: For the Two Months Ended December 31, 2013

Exhibit 18 shows the preceding data entered for NetSolutions.

2. Step 2. Enter the Unadjusted Trial Balance

Enter the unadjusted trial balance on the spreadsheet. The spreadsheet in Exhibit 18 shows the unadjusted trial balance for NetSolutions at December 31, 2013.

3. Step 3. Enter the Adjustments

The adjustments for NetSolutions from Chapter 3 are entered in the Adjustments col­umns, as shown in Exhibit 19. Cross-referencing (by letters) the debit and credit of each adjustment is useful in reviewing the spreadsheet. It is also helpful for identifying the adjusting entries that need to be recorded in the journal. This cross-referencing process is sometimes referred to as keying the adjustments.

The adjustments are normally entered in the order in which the data are assembled. If the titles of the accounts to be adjusted do not appear in the unadjusted trial bal­ance, the accounts are inserted in their proper order in the Account Title column.

The adjusting entries for NetSolutions that are entered in the Adjustments columns are as follows:

• Supplies. The supplies account has a debit balance of $2,000. The cost of the sup­plies on hand at the end of the period is $760. The supplies expense for December is the difference between the two amounts, or $1,240 ($2,000 – $760). The adjust­ment is entered as (1) $1,240 in the Adjustments Debit column on the same line as Supplies Expense and (2) $1,240 in the Adjustments Credit column on the same line as Supplies.
• Prepaid Insurance. The prepaid insurance account has a debit balance of $2,400. This balance represents the prepayment of insurance for 12 months beginning December 1. Thus, the insurance expense for December is $200 ($2,400 4 12). The adjustment is entered as (1) $200 in the Adjustments Debit column on the same line as Insurance Expense and (2) $200 in the Adjustments Credit column on the same line as Prepaid Insurance.
• Unearned Rent. The unearned rent account has a credit balance of $360. This bal­ance represents the receipt of three months’ rent, beginning with December. Thus, the rent revenue for December is $120 ($360 4 3). The adjustment is entered as
  • $120 in the Adjustments Debit column on the same line as Unearned Rent and
  • $120 in the Adjustments Credit column on the same line as Rent Revenue.
• Accrued Fees. Fees accrued at the end of December but not recorded total $500. This amount is an increase in an asset and an increase in revenue. The adjustment is entered as (1) $500 in the Adjustments Debit column on the same line as Accounts Receivable and (2) $500 in the Adjustments Credit column on the same line as Fees Earned.

• Wages. Wages accrued but not paid at the end of December total $250. This amount is an increase in expenses and an increase in liabilities. The adjustment is entered as
  • $250 in the Adjustments Debit column on the same line as Wages Expense and
  • $250 in the Adjustments Credit column on the same line as Wages Payable.
• Depreciation. Depreciation of the office equipment is $50 for December. The ad­justment is entered as (1) $50 in the Adjustments Debit column on the same line as Depreciation Expense and (2) $50 in the Adjustments Credit column on the same line as Accumulated Depreciation.

After the adjustments have been entered, the Adjustments columns are totaled to verify the equality of the debits and credits. The total of the Debit column must equal the total of the Credit column.

4. Step 4. Enter the Adjusted Trial Balance

The adjusted trial balance is entered by combining the adjustments with the unad­justed balances for each account. The adjusted amounts are then extended to the Adjusted Trial Balance columns, as shown in Exhibit 20.

To illustrate, the cash amount of $2,065 is extended to the Adjusted Trial Balance Debit column since no adjustments affected Cash. Accounts Receivable has an initial bal­ance of $2,220 and a debit adjustment of $500. Thus, $2,720 ($2,220 + $500) is entered in the Adjusted Trial Balance Debit column for Accounts Receivable. The same process continues until all account balances are extended to the Adjusted Trial Balance columns.

After the accounts and adjustments have been extended, the Adjusted Trial Bal­ance columns are totaled to verify the equality of debits and credits. The total of the Debit column must equal the total of the Credit column.

5. Step 5. Extend the Accounts to the Income Statement and Balance Sheet Columns

The adjusted trial balance amounts are extended to the Income Statement and Balance Sheet columns. The amounts for revenues and expenses are extended to the Income Statement column. The amounts for assets, liabilities, capital stock, and dividends are extended to the Balance Sheet columns.^[1]

The first account listed in the Adjusted Trial Balance columns is Cash with a debit balance of $2,065. Cash is an asset, is listed on the balance sheet, and has a debit balance. Therefore, $2,065 is extended to the Balance Sheet Debit column. The Fees Earned balance of $16,840 is extended to the Income Statement Credit column. The same process continues until all account balances have been extended to the proper columns, as shown in Exhibit 21.

6. Step 6. Total the Income Statement and Balance Sheet Columns, Compute the Net Income or Net Loss, and Complete the Spreadsheet

After the account balances are extended to the Income Statement and Balance Sheet columns, each of the columns is totaled. The difference between the two Income Statement column totals is the amount of the net income or the net loss for the pe­riod. This difference (net income or net loss) will also be the difference between the two Balance Sheet column totals.

If the Income Statement Credit column total (total revenue) is greater than the In­come Statement Debit column total (total expenses), the difference is the net income. If the Income Statement Debit column total is greater than the Income Statement Credit column total, the difference is a net loss.

As shown in Exhibit 22, the total of the Income Statement Credit column is $16,960, and the total of the Income Statement Debit column is $9,855. Thus, the net income for NetSolutions is $7,105 as shown below.

The amount of the net income, $7,105, is entered in the Income Statement Debit column and the Balance Sheet Credit column. Net income is also entered in the Ac­count Title column. Entering the net income of $7,105 in the Balance Sheet Credit column has the effect of transferring the net balance of the revenue and expense accounts to the retained earnings account.

If there was a net loss instead of net income, the amount of the net loss would be entered in the Income Statement Credit column and the Balance Sheet Debit column. Net loss would also be entered in the Account Title column.

After the net income or net loss is entered on the spreadsheet, the Income Statement and Balance Sheet columns are totaled. The totals of the two Income Statement columns must now be equal. The totals of the two Balance Sheet columns must also be equal.

7. Preparing the Financial Statements from the Spreadsheet

The spreadsheet can be used to prepare the income statement, the retained earnings statement, and the balance sheet shown in Exhibit 2. The income statement is nor­mally prepared directly from the spreadsheet. The expenses are listed in the income statement in Exhibit 2 in order of size, beginning with the larger items. Miscellaneous expense is the last item, regardless of its amount.

The first item normally presented on the retained earnings statement is the bal­ance of the retained earnings account at the beginning of the period. This amount along with the net income (or net loss) and the dividends amount shown in the spreadsheet, are used to determine the ending retained earnings account balance.

The balance sheet can be prepared directly from the spreadsheet columns except for the ending balance of retained earnings. The ending balance of retained earnings is taken from the retained earnings statement.

When a spreadsheet is used, the adjusting and closing entries are normally not journalized or posted until after the spreadsheet and financial statements have been prepared. The data for the adjusting entries are taken from the Adjustments columns of the spreadsheet. The data for the first two closing entries are taken from the Income Statement columns of the spreadsheet. The amount for the third closing entry is the net income or net loss appearing at the bottom of the spreadsheet. The amount for the fourth closing entry is the dividends account balance that appears in the Balance Sheet Debit column of the spreadsheet.

Source: Warren Carl S., Reeve James M., Duchac Jonathan (2013), Corporate Financial Accounting, South-Western College Pub; 12th edition.