Chapter 2 | |
| About This Chapter |
This chapter introduces techniques to add or update adjustments to the account balances of the General Ledger Trial Balance, and prepare a final adjusted Trial Balance to be used for preparing the financial statements. It includes the following sections:
F Overview, page 24, introduces the needs to update the General Ledger Trial Balance in the Excel worksheet.
F Worksheet 91 – Trial Balance (Original), page 25, describes this worksheet, and presents step‑by‑step instructions for using it.
F Worksheet 92 – Trial Balance Adjustments, page 29, describes this worksheet, and provides information about adding new adjustments.
F Worksheet 93 – Final Trial Balance, page 39, describes this worksheet, and provides two ways of consolidating the Trial Balance account figures with the adjustment account figures.
F Worksheet 94 – Trial Balance Audit, page 49, describes this worksheet, and discusses how to summarize the data in it.
The Financial Statements.xls workbook contains four worksheets that deal with original Trial Balance, add /update Adjustments for a final adjusted Trial Balance:
F Worksheet 91 – Trial Balance (Original), described on page 25.
F Worksheet 92 – Trial Balance Adjustments, described on page 29.
F Worksheet 93 – Final Trial Balance, described on page 39.
F Worksheet 94 – Trial Balance Audit, described on page 49.
Worksheet 91 – Trial Balance (Original)
Step 1: Entering and Saving the Trial Balance in an Excel Worksheet
Ø To enter and save a trial balance:
1. Select worksheet 91 – Trial Balance.
2. Import the Trial Balance from the General Ledger application or type it directly into the worksheet, as shown below. When you have finished, save the Financial Statements.xls workbook.
F The table has a single title row with a text title at the top of each column.
F There are no empty rows between groups and/or subtotals.
F There are no totals at the bottom.
This structure has a number of advantages. A Trial Balance table entered into the worksheet's cells in list form enables extensive and efficient use of the various techniques offered by Excel, such as Sorting, Filtering, Subtotaling, Consolidating, creating PivotTable reports and easily using Lookup & Reference, and SUMIF formulas.
Ø To define the Names of columns A through D:
1. Select column A.
2. Press <Ctrl+F3> to open the Define Name window.
3. In the Names in workbook box,type the Name TB_Index and click OK.
4. Repeat the above steps and define the Names of columns B through D, as follows:
F Column B: TB_AccountName
F Column C: TB_Debit
F Column D: TB_Credit
Ø To define the Name for row 1:
F Select row 1 in the worksheet, and follow the steps in the previous procedure to define the Name TB_Row1 for the row.
Ø To define a dynamic range name for the Trial Balance list:
1. Select a cell in the data area of worksheet 91-Trial Balance, and press <Ctrl+*> (the * on the numeric keypad) or <Ctrl+Shift+*>.
2. Press <Ctrl+F3> to open the Define Name window, as shown.
3. In the Names in workbook box, type the Name TB_Table.
4. In the Refers to box,type the following formula:
5. Click OK. The range Name TB_Table automatically updates its size without needing to be manually updated.
F Adjustments are entered in columns A through E.
F Column F is an empty column, so there is a space between columns E and G.
F Column G is the Details column.
F Column H is an empty column, so there is a space between columns G and I.
F Columns I through L contain the audit formulas that return the balances for each adjustment number (to summarize the adjustments based on column C).
The following sections present step-by-step instructions for using the 92 - Trial Balance Adjustments worksheet.
Step 1: Entering Adjustments
F Cell A1: Account Number
F Cell B1: Account Name
F Cell C1: Adjustment Number
F Cell D1: Debit
F Cell E1: Credit
F Cell G1: Details
Column A – Account Number
Column A contains the account numbers, which are validated by the Validation technique before they are entered into a cell.
Ø To validate an account number:
1. Select column A.
2. From the Data menu, select Validation,and then select the Settings tab.
3. From the Allow box, select List.
4. In the Source box, press <F3>, and paste the Name TB_Index.
5. Ensure that both the Ignore blank and In-cell dropdown check boxes are selected, as shown below.
6. Select the Input Message tab.
7. In the Title box, type Adjustments.
8. In the Input Message text box, add an explanation.
9. Select the Error Alert tab and set the alert and control level from the Style dropdown list.
10. Add a title for the alert in the Title box and an error explanation in the Error message text box.
11. Click OK.
|
|
TIP: You can quickly select an item from a Validation dropdown list in a cell by selecting the cell and pressing <Alt+Down Arrow>. |
Column B – Account Name
=IF(ISERROR(INDEX(TB_Table,MATCH(A2,TB_Index,0),2)=TRUE),"", INDEX(TB_Table,MATCH(A2,TB_Index,0),2))
The formula is composed of two parts:
F An INDEX formula that returns the account name from column B in worksheet 91 – Trial Balance:
=INDEX(TB_Table,MATCH(A2,TB_Index,0),2)
F An IF formula with a nested ISERROR formula to eliminate error messages. The formula's Logical Text box checks for errors by calculating an INDEX formula. If the calculation returns an error, an empty cell is displayed.
When adding new adjustments, you will need to also add a new account to the original Trial Balance.
Ø To add a new account name and number to the original Trial Balance:
1. Select worksheet 91 – Trial Balance.
2. Select the first empty cell at the end of the account list in column A, and type the account number
3. In the corresponding cell in column B, type the name of the new account.
4. Sort the Trial Balance table by selecting a cell in column A
and clicking the Sort Ascending
icon on the standard toolbar.
Step 2: Defining Names
F Column A: Adj_AccountNumber
F Column B: Adj_AccountName
F Column C: Adj_Number
F Column D: Adj_Debit
F Column E: Adj_Credit
F Row 1: Adj_Row1
Refer to Step 2: Defining Names, page 26, for the naming procedure.
Ø To define a dynamic range name for the Adjustment table:
1. Select a cell in the Adjustments table, and press <Ctrl+Shift+*>.
2. Press <Ctrl+F3> to open the Define Name window.
3. In the Names in workbook box, type the Name Adj_Table.
4. In the Refers to box,type the following formula:
=OFFSET('92 - Trial Balance Adjustments'!F1,0,0,COUNTA(Adj_AccountNumber),Adj_Row1)
For an explanation of this formula, refer to To define a dynamic range name for the Trial Balance list, page 27.
Step 3: Auditing the Adjustment Balances
F Adding a table containing formulas that sum the balances of the adjustments, as described on page 35.
F Handling each unbalanced adjustment separately, as described on page 37.
F Using Conditional Formattings to color the unbalanced adjustments, as described on page 37.
Finding Unbalanced Adjustments
In columns I through L, you add formulas that calculate the totals of each adjustment number and find any unbalanced adjustment number.
Ø To find unbalanced adjustment numbers:
1. Type the list of adjustment numbers into column I (as shown in Figure 2‑2).
2. Type the following SUMIF formulas into columns J and K:
F Cell J2:
=SUMIF(Adj_Number,I2,Adj_Debit)
F Cell K2:
=SUMIF(Adj_Number,I2,Adj_Credit)
The adjustment number in column I is the criteria used by the SUMIF formulas to sum the adjustments.
3. Type a formula calculating the difference between columns J and K into column L. The formula should return zero for each adjustment number.
You can now use this column to find unbalanced adjustments, sort the list or filter it, as shown below.
Correcting and Updating Unbalanced Adjustments
After you have found the unbalanced adjustments, you can use Excel's AutoFilter to make it easy to correct or update each one separately.
Ø To correct and update unbalanced adjustments:
1. Select cell C1.
2. From the Data menu, select Filter and then AutoFilter.
3. Filter the list based on your unbalanced adjustment number criteria, as shown.
Coloring Rows with Unbalanced Adjustments
You can use Conditional Formatting to color cells containing unbalanced adjustments, as described below.
Ø To color rows with unbalanced adjustments:
1. Select columns A:E.
2. From the Format menu,select Conditional Formatting.
3. From Condition 1, select Formula ls and type the following formula:
=SUMIF(Adj_Number,$C1,Adj_Credit)-SUMIF(Adj_Number,$C1,Adj_Debit)<>0)
4. Click Format and then select the Patterns tab.
5. Select a color and then click OK. Your screen should now look like this:
6. Click OK again to save the Conditional Formatting.
Each cell in the range (columns A through H) will check the result of the formula calculation in the parallel cell in the same column. When it does not equal zero, the cell pattern will be colored.
Worksheet 93 – Final Trial Balance
F Using a SUMIF formula, as describedbelow.
F Using the Consolidate technique, as described on page 40.
Using a SUMIF Formula to Summarize Trial Balance Figures with Adjustment Figures
The SUMIF formula summarizes data based on certain criteria. In this example, the criterion is the account number.
Ø To summarize data using the SUMIF formula:
1. Select worksheet 91 – Trial Balance.
2. Select cell F2 and type the following formula:
=SUMIF(TB_Index,A2,TB_Debit)+SUMIF(Adj_AccountNumber,A2,Adj_Debit)
3. Select cell G2 and type the following formula:
=SUMIF(TB_Index,A2,TB_Credit)+SUMIF(Adj_AccountNumber,A2,Adj_Credit)
4. Select cell H2 and type the following formula:
=G2-F2
5. Copy the formulas in cells F2:H2 and paste them into cells in columns F:H.
Using the Consolidate Technique to Summarize Trial Balance Figures with Adjustment Figures
Ø To consolidate without source links:
1. Select worksheet 91 – Trial Balance.
2. Select cell A1, and press <Ctrl+Shift+*>.
3. Press <Ctrl+F3> to open the Define Name window.
4. In the Names in workbook box, type the Name TB_data, and click OK.
5. Open worksheet 92 – Trial Balance Adjustments, and repeat steps 2 through 4, typing the Name Adj_data in the Names in workbook box.
6. Open worksheet 93 – Final Trial Balance, and select cell A1.
7. From the Data menu, select Consolidate.
8. In the Consolidate window that appears, select Sum from the Function list box.
9. In the Reference box, press <F3>, select and paste the Name TB_data and then click Add.
10. Repeat step 9 for the adjustments table, naming it Adj_data. The Consolidate window should now look like this:
11. Ensure that Top row and Left column check boxes in the Use labels in group box are selected, and that the Create links to source data check box is deselected.
12. Click OK.
13. Select cell C2 and click the Sort Ascending icon. The worksheet will be sorted automatically
by adjustment number, as shown below.
Ø To add a formula that returns the account balance after consolidating the figures:
F Type the formula into cell G2:
and copy the formula to cells in column G.
Figure 2‑3: Using the Consolidate Technique
The inner area of the table is the area of merged data. Note that the text is missing from column B (Account Name). This is because the Consolidate technique uses functions that add figures via cross-checking identical text that appears in the top row and leftmost columns of the lists. Functions cannot consolidate text, however.
F Switch columns A and B by moving column B (the account name) to column A, and vice versa.
F In the Adj_data table, delete the account number column (do not forget to copy column B and paste it back as values).
The account number column has now been moved to the data area.
When using the Consolidate technique, account numbers are consolidated and the returned result contains non-existing account numbers (for example, account number 1011 plus 1011 becomes 2022). The cell in column B with the account number in the first list (TB_data) is transferred to the consolidated table as is.
Columns A through D in Figure 2‑4 show the results of this switch between column A and B (represented in the TB_data list). Columns F through I appear after deleting the account number column (represented in the TB_Adj list).
Figure 2‑4: TB_Data List After Switching Columns A and B
Figure 2‑5: Final Trial Balance
The consolidation of the two lists results in worksheet 93 – Final Trial Balance and a formula in column F that returns the final balances.
Consolidating Data with Links to the Source Data
In the previous section, you learned how to consolidate data without creating links to the source data. This has the advantage of being more convenient and having fewer formulas; however, it has the following disadvantages as well:
F Adding new adjustments after you have completed the consolidation does not update the consolidated table.
F Account balances cannot by analyzed.
Consolidating and linking to the source data enables you to do both of these. Nevertheless, you will still have to run the Consolidate technique to add new adjustments to the worksheet. For this reason, it is recommended that you link to the source data and perform the Consolidate technique every time you add new records to the source lists.
Ø To perform the Consolidate technique after adding new records:
1. Delete the data in worksheet 93 – Final Trial Balance.
2. Select cell A1.
3. From the Data menu, select Consolidate.
| NOTE: The Consolidate techniquepreserves the Nameslists in the Consolidate window, so you do not have to paste it again. |
4. Select the Create links to source data check box and click OK. The data is now consolidated using the Subtotal technique.
Adding an Account Balance Calculation Column to Worksheet in Subtotal Mode
Ø To add an account balance calculation column to the worksheet in Subtotal mode:
1. While the table is in Subtotal mode (as explained in the previous section), enter the formula:
2. Select the formula and press <Ctrl+C> to copy it.
3. Select cells from cell H2 downward, and press <F5>. The Go To window appears.
4. Click Special. The Go To Special window appears.
5. Select the Visible cells only option buttonand click OK.
6. Press <Enter> to paste the formula into the visible cells in the range.
7. Select the Level 2 summary in the subtotal levels (on the left side of the worksheet) and open the invisible rows. The worksheet should look like this:
Coloring the Subtotal Summary Rows
The procedure below describes how to color the Subtotal summary rows..
Ø To color the Subtotal summary rows:
1. Make sure that the Subtotal position is at Level 2 (and all cells are visible).
2. Select cell A1, press <Ctrl+Shift+*> and select the current region.
3. From the Format menu,select Conditional Formatting.
4. From Condition 1, select Formula ls and type the following formula:
=$A1<>0
5. Click Format and then select the Patterns tab.
6. Select a color and then click OK. Your screen should now look like this:
|
|
TIP: You can use both Consolidate techniques (with and without source-data linking) in different worksheets and enjoy the advantages of both. |
Worksheet 94 – Trial Balance Audit
F Summarize the account balances of the original Trial Balance before new Adjustments were added.
F Summarize the new Adjustments to the accounts.
F Calculate the final adjusted balance accounts of the Trial Balance.
Figure 2‑6: Worksheet 94 – Trial Balance Audit
Use the SUMIF formulato calculatethe Profit & Loss balance (refer to rows 6 and 7 in the figure above) based on the Trial Balance before and after new adjustments were added. This formula summarizes the figures by finding all the account numbers that are greater than 4000 (account numbers 4000 to 4999 represent sales and other income, while account numbers greater than 5000 are used for expenses).