Chapter 1  Introducing Financial Statements.xls Worksheets

About This Chapter

This chapter introduces the structure of the Financial Statements.xls workbook and its worksheets, and includes the following sections:

F       Overview, page 2, presents an overview of the worksheets' functionality.

F       Financial Statements.xls Worksheets, page 3, describes the different categories into which the various worksheets are sorted, and provides details about each of them.

F       Installing the ChooseSheet.xla Add-in, page 18, describes a downloadable add-in that enables you to easily find and select specific worksheets in the Financial Statements.xls workbook.

F       Sorting Worksheets in Ascending Order, page 21, provides macros that enable you to sort the worksheets to organize them in ascending order in the Financial Statements.xls workbook.

Overview

This chapter introduces the structure of the Financial Statements.xls workbook, and discusses:

F       The name of each worksheet

F       The purpose of each worksheet

F       The descriptive information that can be entered and/or the calculations that can be performed in the worksheets' cells

F       The data flow between the worksheets

F       The relationships between the worksheets

Financial Statements.xls Worksheets

The Financial Statements.xls workbook contains 15 worksheets in four categories. The worksheets in the workbook are as follows:

F       Category 1: General, Parameters and Calculated Worksheets, page 4

v              11 – General Details

v              12 – Worksheets List

v              13 – Parameters and Calculations

v              14 – Months List

v              15 – BS, IS Level

v              16 – Notes List

F       Category 2: Data Worksheets, page 14

v              21 – Trial Balances Data

F       Category 3: Reports Worksheets, page 17

v              31– Balance Sheet

v              41 – Income Statement

v              51 – Cash Flow

v              61 – Notes

F       Category 4: Trial Balance and Adjustments Worksheets, page 17

v              91 – Trial Balance (Original)

v              92 – Trial Balance Adjustments

v              93 – Final Trial Balance

v              94 – Trial Balance Audit

After creating the workbook and its worksheets, you can add more worksheets, as required. Adding prefix numbers while naming these additional worksheets enables you to easily move between the different worksheets to find the one you need, as well as conveniently organize them in ascending order. For more details regarding these capabilities, refer to Installing the ChooseSheet.xla Add-in, page 18, and Sorting Worksheets in Ascending Order, page 21.

Category 1 – General, Parameters and Calculated Worksheets

There are five worksheets in this category. These contain general information, parameters and formulas that return calculation results that are required when preparing primary worksheets with the reports from which the financial statements are composed.

Worksheet 11 – General Details

In worksheet 11 – General Details you define the company name and the ending date of the report.

In the Financial Statements.xls workbook's first worksheet, type the desired company name into cell C2 (as shown below), and define the Name CompanyName to the cell.

Ø     To define a Name to a cell:

1.       Press <Ctrl+F3>.

2.       In the Name in workbook box, type CompanyName and click OK.

3.       Type the formula into cell C2:

=CompanyName

By entering a linked formula to CompanyName in all worksheets used for creating reports, the company name will change automatically.

Ø     To set the report ending date:

F       After you have saved and updated the company name, select the month of the report ending date from the MonthList (the input range inserted to theCombo Box). The worksheets storing the reports are automatically updated based on month selected. For more details, refer to Chapter 4, Balance Sheet, Chapter 5, Income Statement, Chapter 6, Cash Flow and Chapter 7, Notes, which discuss the various financial statements report worksheets.

Defining Names to Paste in the Combo Box Input Range and Cell Link

Ø     To define a Name in the month list:

1.       In worksheet 14 – Months List, follow the later steps to insert the month list into the cells in column A.

2.       Select cell A2 and, in the Name box (located at the left of the Formula Bar), type A109 and press <Enter+Shift>. Cells A1:A109 have been selected.

3.       From the Edit menu, select Fill and then Series. The Series window appears.

4.       In the Type group box, select the Date option button, and in the Date Unit group box, select the Month option button.

5.       Click OK.

6.       Press <Ctrl+1>. The FormatCells window appears.

7.       In Number tab displayed, select Custom and type mmmm dd, yyyy in the Type box.

8.       Click OK.

9.       With cells A1:A109 still selected, press <Ctrl+F3> and type the Name MonthList in the Names in workbook input box.

10.   Click OK.

Ø     To define a Name for the Combo Box linked cell:

F       In the worksheet 13 – Parameters & Calculations, select cell B2 and define the Name MonthSelectionNumber for the cell, as shown below.

Adding a Combo Box 

Ø     To add a Combo Box to the worksheet:

1.       In worksheet 11 – General Details, place the cursor over any toolbar.

2.       Right-click and select Forms.

3.       Click the Combo Box icon, as shown:

The cursor changes into a cross.

4.       Select a cell in the worksheet, and draw the shape of the Combo Box. When you are finished, a Combo Box appears.

Ø     To add a list to the Input Range Combo Box and cell link:

1.       Select the Combo Boxyou just created and make sure you are in edit mode (see the small circles below).

2.       Right-click, and from the shortcut menu, select Format Control.

3.       Select the Control tab, as shown below.

4.       In the InputRangebox, type the previously defined Name, MonthList.

5.       In the Cell Link box, type the defined Name, MonthSelectionNumber.

6.       Select the 3-D shading check box and click OK.

Worksheet 12 – Worksheets List 

Chapter 4, Balance Sheet, Chapter 5, Income Statement, Chapter 6, Cash Flow and Chapter 7, Notes.

F       The formula in cell B3, the Name defined to the cell is ReportsDate:

=INDEX(MonthsList,MonthSelectionNumber)

Thisformula uses two arguments (the third argument is optional), Array and Row Number. The resulting calculation is the date in row 72 in the MonthsList (as shown above).

F      The formula in cell B4, the Name defined to the cell is ReportsMonthNumber:

=MONTH(ReportsDate)

This formula returns the month number from cell B3 (ReportsDate).

F       The formula in cell B5, the Name defined to the cell is ReportsYearNumber:

=YEAR(ReportsDate)

This formula returns the year number from cell B4 (ReportsMonthNumber).

Worksheet 14 – Months List

For details regarding this worksheet, refer to To define a Name in the month list, page 5.

Worksheet 15 – BS, IS Level

This worksheet contains lists of the three summary levels used in the Financial Statements reports, as shown in Figure 1‑2 (the BS in the worksheet name stands for Balance Sheet; the IS for Income Statement):

F       Account Category level (level 1, column A).

F       Account Groups level (level 2, column B).

F       Account Types level (level 3, column C).

Figure 1‑2: Worksheet 15 – BS, IS Level

       Level 1, Category: Assets, Liabilities, Stockholder's Equity and Income Statements.

F       Level 2, Account Groups: Under the Assets section – Current Assets, Property and Equipment (at Cost), Other Assets. Under the Liabilities and Stockholder's Equity section – Current Liabilities, Long-Term Liabilities, Stockholder's Equity.

F       Level 3, Account Types: Under the Current Assets section (as an example) – Cash, Accounts Receivable, Inventories, Other Current Assets and Prepaid Expenses.

Figure 1‑3: Balance Sheet Summary Levels

Figure 1‑4: Defining a Name for a Column

Worksheet 16 – Notes List

This worksheet contains a list of Notes titles, details and formulas that return calculated Note numbers automatically. The worksheets that contain the reports in the Financial Statements.xls workbook contain formulas that return the Note numbers from this worksheet. For more details, refer to Chapter 7, Notes.

Category 2 – Data Worksheets

Figure 1‑5: Worksheet 21 – Trial Balances Data

F       Column A: Account A

F       Column B: Account Name

F       Column C:Account Types, Level 3, described on page 15

F       Column D:Account Groups, Level 2, described on page 16

F       Column E:Category, Level 1, described on page 16

F       Columns F and onwards: Monthly Trial Balance, described on page 17

Column C: Account Types, Level 3

Ø     To use a Validation technique to select an Account Type from the Level 3 list:

1.       Select column C.

2.       From the Data menu, select Validation, and then select the Settings tab.

3.       From the Allow dropdown list, select List.

4.       In the Source box, press <F3>, paste the Name Level3 and then click OK.

Column D: Account Groups, Level 2

=INDEX(BS_Level,MATCH(C2,Level3,0),2)

Figure 1‑6: Account Groups, Level 2

Column E: Category, Level 1

The formula in column E:

=INDEX(BS_Level,MATCH(C2,Level3,0)0.1)

This formula returns the Category summary for the Account Types, Level 3in column C from column A in worksheet 15 – BS, IS Level.

Figure 1‑7: Category, Level 1

Columns F and Onwards: Monthly Trial Balance

Ø     To enter the month names into the cells in row 1 from column F and onwards:

1.       Select and copy the MonthsList list in worksheet 14 – Months List.

2.       Select cell F1, right-click and select Paste Special from the shortcut menu.

3.       Change the paste direction from vertical to horizontal by selecting the Transpose check box and then clicking OK.

4.       While range F1 and onwards is selected, change the Dates format by pressing <Ctrl+1>, selecting the Number tab, and then selecting Custom.

5.       In the Type box, type the format: mmmm yyyy and click OK.

Category 3 – Reports Worksheets

F       Worksheet 31– Balance Sheet, as described in Chapter 4, Balance Sheet.

F       Worksheet 41– Income Statement, as described in Chapter 5, Income Statement.

F       Worksheet 51 – Cash Flow, as described in Chapter 6, Cash Flow.

F       Worksheet 61 – Notes, as described in Chapter 7, Notes.

Category 4 – Trial Balance and Adjustments Worksheets

This category has four worksheets. For more details, refer to Chapter 2, Adjusting the Trial Balance.

Installing the ChooseSheet.xla Add-in

In a workbook containing multiple worksheets, moving between worksheets to select the one you need is not a convenient task. There are two ways of searching for the worksheet you need:

F       Press <Ctrl+Page Down>or <Ctrl+Page Up> to move to the next or previous worksheet.

F       Right-click one of the arrows in the worksheet's horizontal scroll bars (in the leftmost lower corner) and select a worksheet from the displayed menu.

Selecting a worksheet from the worksheets menu is useful only if there are less than 10 to 15 worksheets in the workbook.

The ChooseSheet.xla add-in enables you to easily select a specific worksheet from a list of sorted worksheets in ascending order.

Figure 1‑8: Choose Sheet Window

Figure 1‑8 displays the Choose Sheet window, which contains the list of worksheets. In the background, you can see the worksheets list in worksheet 12 – Worksheet List. When the add-in has been installed, an icon is added to the regular toolbar, which can be clicked to display the Choose Sheet window.

Ø     To install the add-in:

1.       Go to www.exceltip.com and download the add-in from the Financial Statements.xls page.

2.       Save the downloaded ChooseSheet.xla file in any folder.

3.       From the Tools menu, select Add-Ins, and click Browse.

4.       Locate and select the ChooseSheet.xla file.

5.       Click OK.

6.       In the Add-ins available box, select the ChooseSheet.xla check box.

7.       Click OK.

Sorting Worksheets in Ascending Order

When you have finished adding worksheets to the Financial Statements.xls workbook, you should arrange them in ascending order.

Type the macro code below into a regular module in the Visual Basic Editor. You can then either:

F       Add a command button to execute the macro from worksheet 11–General Details,

OR

F       Add the macro to a Custom Menu.

For more details, refer to Chapter 8, Customizing the Financial Statements.xls Workbook and Presenting Information.

You can also add a macro that both sorts the worksheets in ascending order and updates worksheet 12 – Worksheet List. This macro runs the macro above, as well as the macro presented on page 9.