At the end of each period, after making adjusting entries organizations close their books. A valuable tool useful to help summarize and move data from the trial balance to the financial statements is a work sheet.

A work sheet has columns debit and credit for each of the following:

o Trial Balance
o Adjustments
o Adjusted Trial Balance
o Income Statement
o Balance Sheet.

Accounts’ unadjusted ending balances are entered under the heading of Trial Balance. These amounts come directly from the ledgers accounts. Accounts are usually grouped in the same order as they appear in the ledger: Assets, Liabilities, Owners’ Equity, Revenue, and Expenses.

Next, adjustments are reflected under Adjustments. Obviously the double entry process ensures that total debits are equal to total credits. Usual adjustments are: Prepaid expenses, depreciation, amortization, and unearned revenue.

The Adjusted Trial Balance is the result of combining the trial balance unadjusted ending balances with the adjustments.

The following columns within the work sheet are the Income Statement (Dr. and Cr.) and the Balance Sheet (Dr. and Cr.)

The process to arrive to these columns’ balances is as follows:

Move over accounts from the Adjusted Trial Balance depending on their nature, keeping in mind that debits on the Adjusted Trial Balance remain debits and credits remain credits: The assets, liabilities, and owners’ accounts go to the Balance Sheet Column. Conversely, move revenue and expense accounts to the Income Statement column.

Important: The difference between revenues and expenses determines the net income or net loss for the period. This amount will balance both columns: the Income Statement and the Balance Sheet. Note that if its balance is credit on the Income Statement it will be Credit on the Balance Sheet.
Creating an IF function that calculates the 10-column accounting worksheet for financial statements takes a careful design to ensure accuracy on the carried over amounts. This is the scenario: Accounts are listed on the far left. The first two columns hold the debit and credit balance for the 'Trial Balance'. The next two hold the debit and credit balance for the 'Adjusting Entries'. The next two after that are the Debit and Credit columns for the 'Adjusted Trial Balance'.

Creating a formula using the function IF that computes the Adjusted Trial Balance figures from the Adjusting entries, which adds a normal credit to a credit adjusting entry, subtracting a credit from a debit and returning with a positive number.

In the adjusted debit column
=IF(I7-J7+L7-N7>0,+I7-J7+L7-N7,0)

Where:
The I column is the unadjusted trial balance debit column

The J column is the unadjusted trial balance credit column

The L column is the debit adjustment column

The N column is the Credit adjustment column

The O column is the adjusted debit column

And the P column is the adjusted credit column.

The formula above goes in the O column (the adjusted debit column)

This formula spells out:

If the unadjusted debit column (I7), minus the unadjusted credit column (J7), + the adjustment debits L7, minus the adjustment credits (N7,) is greater than zero, then use the resulting balance in the debit column, otherwise put in a space or blank cell.

Properly setting up the Excel spreadsheet ensures that numbers transferred over to the income statement and the balance sheet are accurate. Accurate financial statements are the bases for a sound decision-making process within organizations.