Open four sheets in Financial Statements.xls Workbook:
* Sheet name: 20 - Trial Balance.
* Sheet name: 21 - Adjustments.
* Sheet name: 22 - Cosolidated Trial Balance.
* Sheet name: 23 - Adjustments Summary & Audit.
Sheet "20 - Trial Balance"
1- Entering and organizing the Trial Balance in Excel worksheet
Choose "20 - Trial Balance" worksheet.
Import the Trial Balance from the General Ledger application or type it into the worksheet (see figure above).
Structure of a Trial Balance data table:
* This table has a single title row with a text title at the top of each column.
* There are no empty rows between groups and/or subtotals.
* There are no totals at the bottom.
Advantages:
A Trial Balance table inserted into Excel worksheet cells in a list structure enables extensive and efficient use of techniques Excel offers: Sorting, filtering, subtotaling, consolidating, creating pivottable and easily using Lookups and Sumif formulas.
2 - Define Names
Define names to Columns A:D
Name defined to Column A: TB_Index Name defined to Column B: TB_AccountName Name defined to Column C: TB_Debit Name defined to Column D: TB_Credit
The tecnique to define a Name to column:
1. Select column A.
2. Press Ctrl+F3.
3. Type the name TB_Index in the Names in workbook: box and click OK.
Repeat the steps and define Names for 3 additional columns according to the Names specified above.
Define Name to row1
Select row 1 in the worksheet and define the name TB_Row1 for the row.
Define Name to Trial Balance list
1. Select a cell in the data area of the Trial Balance and press Ctrl+*.
2. Press Ctrl+F3 in the Define Names window. In the Names in workbook box, type the name TB_Table. In the Refers to: box enter the following formula -
=OFFSET('20 - Trial Balance'!$A$1,0,0,COUNTA(TB_Index),COUNTA(TB_Row1))
and click OK.
Explanation:
Define a Name that refers to a formula ( not actual cells address ) will automaticaly updating the list size.
The Offset formula in the Refers to box, returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
The CountA formula ( nested in the Offset formula at the Refers to box ) returns the number of not empty cells in the coulmn A and in row 1( the second nested formula).
3- Audit
When finishing entering the Trial Balance into the worksheet, be sure to check the balance of Trial Balances total.
Select the worksheet "23 - Summary & Audit", read more later at the chapter.
Sheet "21 - Adjustments"
The techniques described at this chapter will allows you to add unlimited number of adjustments ( limited to 65336 rows in one worksheet).
Worksheet structure:
* Adjustments entered in columns A:E
* Column F, empty column (space between columns).
* Column G, notes and details column.
* Column H, empty column, (space between columns).
* Columns L:I, audit formulas returnes the balances of the new adjustments.
1 - Entering adjustments
Columns A:H
Enter the new adjustments to columns A:E.
Column titles:
1. Cell A1 - Account Number.
2. Cell B1 - Account Name.
3. Cell C1 - Adjustments Number.
4. Cells D1, E1 - Debit, Credit.
5. Column F - empty.
6. Column G - Details.
Column A - Account number
Enter the account number in column A.
Use the Validation technique to validate the account number before entering the account number into column A cells.
The technique:
1. Select column A.
2. Select Validation from the data and select the Settings tab.
3. Select list from the Allow box.
4. In the Source box, press F3 and select and paste the Name TB_Index.
5. Make sure that both Ignore blank and In-cell dropdown boxes are checked.
6. Add an input message, select the Input Message tab. In Title, type Adjustments. In Input Message, add a Explanation.
7. Select the Error Alert tab and set the alert and control level from Style drop down list. Add a title in Title box and an error Explanation in Error message box.
8. Click OK.
Tip - Quickly choose from drop down list
Select a cell in a column under a list already in cells or a list is in cells when using the Validation technique and press Alt+Down arrow, the selected drop down list will be open.
Column B - Account name in Adjustments worksheet
Column B contains a formula that returns the account name after entering the account number to a cell in column A.
The account name should be returned as a verification ensuring that the account number entered into a cell in column A is correct.
The formula:
=IF(ISERROR(INDEX(TB_Table,MATCH(A2,TB_Index,0),2)=TRUE),"",
INDEX(TB_Table,MATCH(A2,TB_Index,0),2))
This formula is composed of two parts:
an INDEX formula returning the account name from column B in the "20-Trial Balance" worksheet:
=INDEX(TB_Table,MATCH(A2,TB_Index,0),2(
After inserting the formula into cell B2, copy it across the cells in column B as required.
This eliminate errors messages, add an IF formula with nested ISERROR formula . The Logical Text box of the formula checks for an error by calculating an INDEX formula. If the calculation returns an error, an empty cell is displayed.
Adding new/additional account name and number
While adding Adjustments, you'd want to add new account name and number, which does not appear in the Trial Balance account list.
1. Select the 20-Trial Balance worksheet. Select the first empty cell at the end of the account list in column A.
2. Type the account number. In the corresponding cell in column B, type the name of the new account.
3. Sort the Trial Balance table, select a cell in column A and click the Sort Ascending icon in the Standard toolbar ( the first toolbar).
2 - Defining names in an Adjustments worksheet
Define names for the Adjustments table and to columns B:E:
(The technique to define a name described at the beginning of this chapter).
* Column A - Define the name Adj_AccountNumber.
* Column B - Define the name Adj_AccountName.
* Column C - Define the name Adj_Number.
* Column D - Define the name Adj_Debit.
* Column E - Define the name Adj_Credit.
Define Name to Row1
Define a name for row 1 in the worksheet: Adj_Row1.
Define Name for Adjustments list:
* Select a cell in the Adjustments table and press Ctrl+*.
* Press Ctrl+F3 in the Define Names window. In the Names in workbook box, type the name Adj_Table. In the Refers to: box enter the following formula:
=OFFSET('21 - Adjustments'!$A$1,0,0,COUNTA(Adj_AccountNumber),Adj_Row1)
Explanation:
See explanation to the formula in the Refers to box in defining the name TB_Table in the 20 - Trial Balance worksheet.
3- Audit of the adjustments balances
After adding adjustments to the 21 - Adjustments worksheet, you should check if the adjustments are balanced.
To find the unbalanced adjustments numbers, or update and correct the unbalanced adjustments, add formulas to the worksheet and use additional techniques:
* Add a table containing formulas used to audit and check the balance of the adjustments.
* Handle each unbalanced adjustments separately.
* Use Conditional Formattings to color the unbalanced adjustments.
Find the unbalanced adjustments numbers
In columns I:L, add a formulas to calculate the totals of each adjusments entry and find the unbalanced adjustments number.
1. Enter to column I the adjustments numbers.
2. Enter to columns J and K SUMIF formulas. The criteria used by the SUMIF formula to sum the adjustments is the adjustments number in column I.
3. The formula in cell J2 of column J:
4. =SUMIF(Adj_Number,I2,Adj_Debit)
5. The formula in cell L2 of column L:
6. =SUMIF(Adj_Number,I2,Adj_Credit(
7. Enter to column L formula to calculate the difference between columns J and K, the formula should return zero for each adjusment entry, to watch the unbalanced adjustments sort the list or filter the list.
Correcting and updating unbalanced adjustments After finding the unbalanced adjustments numbers, use automatic filtering in the adjustments list and correct / update every unbalanced adjustment separately. Select cell C1. From Data select Filter, AutoFilter. Filter, according to an unbalanced adjustment number criteria.
Coloring rows with unbalanced adjustments
Used Conditional Foematting to color cells containing unbalanced adjustments.
1. Select column A:E.
2. From Format select Conditional Formatting.
3. In Condition 1, select Formula ls and type the following formula into the box:
=SUMIF(Adj_Number,$C1,Adj_Credit)-SUMIF(Adj_Number,$C1,Adj_Debit)<>0)
4. Select color template from Format... and click OK.
Explanation
Each cell in range ( columns ) A:H check the result of the formula calculation, when the calculation result not equal to zero the cell patterns colored with the color you picked.
4 - Adjusting the Trial Balances
Consolidate of Trial Balances figures with the adjustments figures.
This chapter describes two consolidated techniques:
* SUMIF formula.
* Consolidate.
Each technique has advantages and disadvantages.
Use the Sumif formula to summarize Trial Balances figures with the adjustments figures
The SUMIF formula is specified to summarize data according to a certain criteria.
In the example describes, the criateria is account number.
1. Select "20 - Trial Balance" sheet.
2. Select cell F2 and enter the following formula:
=SUMIF(TB_Index,A2,TB_Debit)+SUMIF(Adj_AccountNumber,A2,Adj_Debit)
3. Select cell G2 and enter the following formula:
=SUMIF(TB_Index,A2,TB_Credit)+SUMIF(Adj_AccountNumber,A2,Adj_Credit)
4. Select cell H2 and enter the following formula:
=G2-F2
5. Copy the formulas in cells F2:H2 and paste them to cells in columns F:H.
Consolidate
Consolidate without links to source data
Using the Consolidate technique is recommended. One of the important advantages in using it over a SUMIF formula is the possibility to of adding links to the source data for drill down to look at the last entry / adjustments.
The technique:
1. Select 20-Trial Balance worksheet, select cell A1, press Ctrl+*, press Ctrl+F3, type the name TB_DATA and click OK.
2. Select the 21-Adjustments worksheet, select cell A1, press Ctrl+*, press Ctrl+F3, type the name Adj_DATA and click OK.
3. Select the 22-Consolidate Trial Balance worksheet, select cell A1. From Data select Consolidate.
4. At the Consolidate window, in Function choose Sum.
5. Select the Reference box, press F3, select and paste the name TB_data and click Add.
6. Add the adjustments table Adj_data using the same technique, press F3, select the name Adj_data, click OK and click Add (see figure).
7. Checked the boxes Use labels from top row and Right column.
8. Click OK.
9. Select cell C2 and click the icon Sort ascending.
See figure for the result.
Explanation:
The Consolidate technique merges data from lists with similar structure.
The Consolidate technique - summing the data by cross checking the text in top row and in the left most column.
For example, the Consolidate techniqe sum the figures at the Debit columns for the index account number 1011.
Add a formula that returns the account balance after cosoliadting the figures. Enter the formula =E2-F2 to cell G2 and copy the formula across column G.
Problem:
The text (account name) is missing, see the column B at the figure above.
Explanation:
The Consolidate technique use function to add the figures by cross checking identical text appearing in both lists in top row and leftmost column, functions cannot consolidate text.
The inner area of the table is the area of merged data.
Solution:
1. Change columns order in the lists before using consolidate.switch between columns A and B, move column B containing the account name to column A and account number column to column B.
2. In the Adj_data table, delete the account number column ( don't forget to copy column B and paste it back as values).
The account number column has been moved to the data area. When the from the two lists Consolidate, account numbers will be consolidate and the returned result will contain non-existing account numbers ( account number 1011 + 1011 becomes 2022 ). The column B with the account number in the first list ( TB_data) will be transferred to the consolidated table as is.
See figure:
See columns A:D after switching between column A and B ( represent the list TB_data ), columns F:I appears after deleting the account number column ( represent the list TB_Adj.)
The result, see the figure:
Consolidate the two lists in new structure and adding a formula in column F that returns the final balances calculation.
Consolidate with links to source data
When using the Consolidate technique without selecting the Create Links to Source Data box, the data is not linked to the data source tables.
Advantage
* More convenient, less formulas.
Disadvantages
* Adding new adjustments after consolidating does not update the sums in the consolidate table.
* It is not possible to analyze the account balance.
The solution
Do Consolidate every time you add new records to the source lists.
Select cell A1. From the Data menu, select Consolidate and click OK.
Note:
Consolidate preserves the lists Names in the window Consolidate. You do not have to paste lists Names.
Consolidate by checking the box Create links to source data has advantages and disadvantages.
Advantages
* Automatically update the Consolidated table.
* Analzing the new balance is easy.
Disadvantage
* You have to perform re Consolidate to add a new adjustments.
The technique
1. Delete the data in 22-Consolidate Trial Balance sheet.
2. Select cell A1. Select Consolidate from Data.
3. Check Create Links to Source data box and click OK.
The result: data is consolidating by the Subtotal technique.
Adding an account balance calculation column
1. Type the formula =F6-G6 into cell H2, select the formula and press Ctrl+C to copy it.
2. Select cells from cell H2 downward.
3. Press F5, click Special...
4. In the Go To Special window, select the Visible cells only and click OK.
5. Press Enter to paste the formula into the visible cells in the range.
6. Press the level 2 summary in subtotal levels (left side of the worksheet) and open the invisible rows.
Adding color to the Subtotal summary rows 1. Be sure the Subtotal position is in Level2 ( all the cells are visible ), Select cell A1, press Ctrl+* and select the active area. 2. From Format, select Conditional Formatting. In Condition 1, select Formula ls and enter the formula =$A1<>0 into the formula box: 3. Click Format..., select a color from Patterns and click OK twice.
Tip,
Use both Consolidate techniques and gain the advantages of the two
Use the two Consolidate options techniques, without links to source data and with links to source data. Perform each in a different worksheet.
Sheet "23 - Adjustments Summary & Audit"
Audit, Audit and Audit is the name of this game.
The most important formulas you must enter are formulas that summarize figures before adding new adjustments, summarize the new adjustments and calculate the balances.
See the figure below with formulas in the 23-Adjustment Summary & Audit worksheet.
Use the Sumif to calculate Profit & Loss balance according to the Trial Balance before adding new adjustments and after. The criteria the Sumif use to sum the figers is all the accounts numbers greate than 4000, ( 4000 - 4999 represents Sales and other Income, accounts numbers above 5000 uses for Expenses ).