Chapter 7  Notes

About This Chapter

This chapter explains the Notes worksheet, and includes the following sections:

F       Overview, page 108, introduces the concept of Notes in the Financial Statements reports.

F       Creating the Notes Worksheet, page 109, describes the steps needed to create and structure the Notes worksheet, as well as how to add or update Notes.

Overview

Financial Statements reports contain the Table of Contents, the Auditor Report, Balance Sheet, Income Statement, Cash Flow and Notes. Notes are an integral part of Financial Statements reports, as they contain essential and important information describing the company's business activities, as well as details of the major subjects in the Balance Sheet, Income Statement and Cash Flow reports.

A number of tasks must be carried out when preparing Notes reports using Excel worksheets and linking their numbers to other Financial Statements reports:

F       The Notes numbers in column B in the Balance Sheet, Income Statement and Cash Flow reportsmust be automatically updated.

F       The total figures in the Notes detailed pages must be equal to the figures that appear in these three major reports.

Creating the Notes Worksheet

Step 1:Defining a Name for the Notes Worksheet

The first step in creating the Notes worksheet is to define a Name for the worksheet.

Ø     To define a Name for the Notes worksheet:

1.       Select any cell in worksheet 16 – NotesListandpress <Ctrl+A> to select all the cells in the worksheet.

2.       Press <Ctrl+F3>.

3.       In the Names in workbook box, type NotesTable.

4.       Click OK.

Step 2:Structuring the Notes Worksheet

Ø     To enter the formula to Column A – Note Number:

F       Type the following formula into cell A2:

=ROWS($A$2:A2(

This formula returns the number of rows between the first address ($A$2) with absolute reference mode and the second address (A2) with relative reference mode. Copy the formula to all the cells in column A, as necessary.

The formula in cell A11, for example,

=ROWS($A$2:A11)

returns the number 10, which is the number of rows between cell A2 and cell A11.

When adding or deleting a row or rows, this formula enables the calculation result returned to always be in an automatic continuously ascending mode.

Ø     To define a Name to Column B – Note Title:

1.       Type the Note Title in the cells in column B (this may be changed at a later date, if required).

2.       Select column B, and press <Ctrl+F3>.

3.       In the Names in workbook field, type NoteTitle.

4.       Click OK.

Ø     To concatenate columns A and B in Column C – Note Number and Title:

1.       Type the following formula into cell C2:

="Note "&" "& A2 &" - "& B2

This formula concatenates the text Note with the number returned from the formula in cell A2 and the text in cell B2. For example, the returned result for the first Note is:

Note 1 – Summary of Significant Accounting Policies

2.       Copy the formula to the cells in columnC. When deleting or adding a Note, all Notes will be updated with the new calculation numbers returned from the formulas in column A.

Ø     To define Column D – Balance Sheet, Income Statement, Cash Flow:

F       Column D contains the Account Types level (as described in Worksheet 15 – BS, IS Level in Chapter1, Introducing Financial Statements.xls Worksheets) used in the three major reports: Balance Sheet, Income Statement and Cash Flow.

Refer to Chapter 4, Balance Sheet for details regarding how to automatically update the Note number in the Notes column. This is done via a formula that returns the Note number related to the Account Type level in column A in the Balance Sheet, Income Statement and Cash Flow worksheets.

Step 3:Adding a Note to Financial Statements

Ø     To add a Note:

1.       Select worksheet 31 – Balance Sheet and copy the first three rows.

2.       Add a new worksheet by pressing <Shift+F11>.

3.       Change the worksheet name to 61 – Notes.

4.       Select cell A1 and paste the first three rows, as shown in step 11.

5.       Select cell E11.

6.       From the Data menu, choose Validation.

7.       Select the Settings tab.

8.       In the Allow box, select List.

9.       Select the Source box, press <F3> and paste the Name NoteTitle.

11.   Press <Alt+Down Arrow> to open the Notes list, as shown below.

12.   Type the following formula into cell A11, as shown below.

 =INDEX(NotesTable,MATCH(E11,NoteTitle,0),3)

The formula returns the Note number combined with the Note title from column C in worksheet 16 – Notes List.

13.   Select the cells in column A (for example, cells A13:A18 in the figure below)

14.   Add the list Tb_Table_AccountName to the cells using Validation (to add a list to Validation, refer to steps 6 through 10), which is the list of Account Names from column B in worksheet 21 – Trial Balances Data.

15.   Type the following formula in cell B13:

=SUMIF(Tb_Table_AccoutName,A13,OFFSET(Tb_Table_AccoutName,0,MonthSelectionNumber+3-12))

Step 4:Auditing the Totals

Figure 7‑2: Auditing the Notes Figures