Chapter 3  Updating the Trial BalancesData Worksheet

About This Chapter

This chapter describes how to add the account balances of the final Trial Balance (refer to Chapter 2, Adjusting the Trial Balance) to the appropriate column in worksheet 21 – Trial Balances Data, and includes the following sections:

F       Overview, page 52, introduces worksheet 21 – Trial Balances Data and describes how to add the account balances of the final Trial Balance to it.

F       Finding and Adding New Account Details , page 53, describes how to find and add new account detail information that appears in the final Trial Balance and is not in worksheet21 – Trial Balances Data.

F       Entering Final Trial Balances to a New Column in Worksheet 21 – Trial Balances Data, page 62, describes how to add the account balances of the new final Trial Balance to a new column in the worksheet.

F       Finding and adding new account name and number information that appear in the final Trial Balance but not in the old Trial Balances stored in worksheet 21 – Trial Balances Data, as described on page 53.

F       Adding the balances of the new Trial Balance to a new column in the worksheet, as described on page 62.

Finding and Adding New Account Details

New accounts are added nearly every month in almost every accounting system. Finding the new account number and name information and adding them to column A of worksheet 21 – Trial Balances Data is essential so that all of the balances of the new Trial Balance flow into the appropriate column in the Trial Balances data worksheet.

To determine if there are any new accounts, and if so, to extract the list of new account names and numbers, you can use one of the following techniques to compare lists:

F       Using Consolidate (without using any formulas), as described on page 54.

F       Using a COUNTIF formula, as described on page 58.

Using the Consolidate Technique to Compare Lists

The Consolidate technique uses formulas to consolidate data. To learn how to use this technique, add two lists of numbers to different columns and then consolidate them. The following example will make this idea clearer.

Figure 3‑1 presents two lists. Column A (the first list) contains a list of account numbers from the old Trial Balance (stored in worksheet 21 – Trial Balances Data). Column D (the second list) contains a list of account numbers from the new Trial Balance. The highlighted cells, D4 and D11 are new account numbers that are not listed in column A.

Figure 3‑1: Adding Two Lists of Numbers

The procedure below describes how to find the two new accounts using the Consolidate technique.

Ø     To add a list number to column adjacent to the account numbers column:

1.       In cells B1 and E1, type text title - List Number (ensure that the column title text is the same).

2.       Copy cell B2 and paste it to cells B2:B12; do the same for cell E2 to cells E2:E12.

3.       Define a Name for the first list by selecting cell A1 in the first list and pressing <Ctrl+Shift+*>.

4.       Press <Ctrl+F3> to open the Define Name window.

5.       In the Names in workbook box,type the Name List1, and then click OK.

6.       Select cell D1in the second list and repeat steps 3 through 5 with the Name List2. Your worksheet should now look like this.

7.       Select cell G1.

8.       From the Data menu, select Consolidate. The Consolidate window appears, as shown.

In the Reference box, press <F3>, select and paste the Name List1 and then click Add to add the name to the All references box.

Select both check boxes in the Use Label in area, and then click OK.

13.   Select cell H2 and click the Sort Ascending  icon. When you are done, your worksheet should look like this (note columns G and H, as well as cells G2 and H2):

The Consolidate technique summarizes the data by comparing and identifying texts in cells the top row and the leftmost column.

In the example presented here, list numbers 1 and 2 were summarized when the account number was identified in both lists. There are two new account numbers in the second list that do not appear in the first list. In the consolidated table, the numeral 2 is displayed next to each account number that does not appear in list 1.

Using the COUNTIF Formula to Compare Lists 

A COUNTIF formula returns a calculation result that contains the number of counted occurrences according to the evaluated criteria.

The formula in cell E2 in Figure 3‑2 returns 0 (a new account number) if it cannot find the criteria (that is, the number in column D) in column A. If the calculated result equals 1, the account number already exists.

Figure 3‑2: COUNTIF Formula

Adding New Account Details to Worksheet 21 – Trial Balances Data

Ø     To add the account numbers to the Trial Balances Data worksheet:

F       Copy the new additional account numbers you find using the technique described in the previous section to worksheet 21 – Trial Balances Data and paste them into column A after the last account number, as shown.

Step 2: Adding the Account Names

Ø     To add the account names to the Trial Balances Data worksheet:

1.       Add the account names to the cells in column B by adding the formula:

=INDEX(TB_data,MATCH(A135,TB_Index,0),2)

F       TB_data: The Name defined for the Trial Balance data in worksheet 21 – Trial Balances Data.

F       TB_Index: The Name defined for the account number column (column A) in worksheet 21 – Trial Balances Data.

2.       Copy and paste the account names as values using Paste Special, Values.

Step 3: Adding the Three Summary Levels of the Balance Sheet and Income Statement

Ø     To add the summary levels to the Trial Balances Data worksheet:

1.       Select cells C134:E134 (as shown in Figure 3‑1, page 54) and paste them into the cells from C135 onward.

The cells in columns C through E contain formulas that return the summary levels from worksheet 15 – BS, IS Level (for more details about this worksheet, refer to Chapter 1, Introducing Financial Statements.xls Worksheets).

2.       Select an Account Type item from the Validation list in every cell from C135 onwards.

Step 4: Sorting the Data Table and Trial Balances

Ø     To sort the data table and trial balances in the Trial Balances Data worksheet:

F       After you have entered all of the new account names and numbers, as well as the formulas that return the financial statement's summary Balance Sheet and Income Statement levels, select a cell in column A and sort the complete table in ascending order by clicking the Sort Ascending  icon.

Entering Final Trial Balances to a New Column in Worksheet 21 – Trial Balances Data

This section discusses how to add the balances of the new final adjusted Trial Balance to a new column in the worksheet.

Ø     To add an account balance to the new month column:

1.       In worksheet 21 – Trial Balances Data, add a SUMIF formula to the new month column (BX) you added to the worksheet, as shown below.

2.       Type the following formula into cell BX2:

 =SUMIF(TB_Index,A2,TB_Adjusted)

3.       Copy the formula and paste it downward into the cells of column BX (as shown above).

Saving the Account Balances of the Trial Balance as Values

The formulas in worksheet 21 – Trial Balances Data are saved until the Financial Statements reports are finalized. They are automatically updated either when a new adjustment is made, or when one or more adjustments in the Trial Balance in worksheet 93 –Final Trial Balance is updated.

To delete the formulas after you have finalized the Financial Statements reports, return to worksheet 21 – Trial Balances Data, copy column BX and then paste it back as values only into the same column.