Balance Sheet

The balance sheet, and other components of the firm's financial statements are generated once every period. The figures presented in the the Balance Sheet the actual balances for the statement creation date, while the figures presented at the Profit and Loss statements and Cash Flow reports are the accumulated transactions figures for the report period. Public companies other types of companies must publish full financial statements each quarter. There are many companies ( or other types of entitites ) prepare full financial statements every month, or partial statements ( Profit and Loss statements only) every month, full financial statements every quarter and full annual statements to be used internally, for control and decision making purposes and for tax purposes. This chapter describes step by step techniques on how to create the Balance Sheet using Microsoft Excel workbook. After creating the reports, you can easily display and/or print the Balance Sheet report at the end of each month, end of quarter or end of calander or fiscal year, compare the figures between years, add graphs, PivotTables and more. This chapter will take you step by step through the various techniques Excel program offers that you need to use when creating Financial reports and how to combine between them. Some of the techniques are:
* Combo box. .
* Validation.
* Formulas that returning and summarizing data. Other important subjects including: Balance Sheet ratios, analysis and comparing, formating including rounding to thousands and summaryzing, printing, emailing, page numbering methods, will be described in chapters YYY, XXX.

Sample Annual Balance Sheet, year end December 31

1- Names
For efficient and accurate work and for verification purposes, You should use names for redirectioning in formulas, to attach lists to a combo box and to the cell linked to a combo box and a verification list. In worksheet 4 - Parameters, you defined names for cells (see chapter XX). In worksheet 5 - Months Lists, you defined a name for the months list with balances of trial balances saved in the TB_data worksheet. The names you define should be attached to a combo box, to formulas and to verification lists. Worksheet 4 - Parameters:

Combo Box -2
From the Combo Box in the worksheet, select a reporting month for the financial statements. When you select one of the months from the list, the formulas in the worksheet will sum data from the column in worksheet TB_Data where the selected month data is stored.

Combo Box adding technique
A - Add a combo box to the worksheet
1. Select one of the toolbars.
2. Right-click and select the Forms toolbar from the drop-down menu. 3. Click and copy the Combo Box.
4. Select a cell in the worksheet, click it and select the combo box.

B - Add a list to the combo box and link to a cell 1. Select the combo box and make sure you are in edit mode. 2. Right-click and select Format Control from the drop-down menu. 3. Select the Control category.
4. In the Input Range box: Type the name MonthList. 5. In the Cell Link box: Type the name MonthSelectionNumber. 6. Select the 3-D shading box and click OK. Note
The F3 shortcut used to paste a name to the Cell Link and Input Range boxes is not available in a combo box.

3 - Company Name
Enter the following formula into cell A4: =CompanyName. Select cells A:D and click Merge and Center.

4 - Title of financial statement ending year data Type the formula =ReportYearNumber into cell C8. Type the formula =C8-1 into cell D8. ell ReportYearNumber in the 4-Parameters worksheet contains a calculation of the statement year number. The number is calculated in the cell when the month December 2003 is selected from the combo box. See chapter XXX with a description of formulas calculated in the 4 - Parameters worksheet.

5 Add a list to select balance clause
Selecting from a balance clause (column A in the figure) will enable formulas that you enter into cells in columns C:D to identify the text (balance clause) in a matching cell in column A and return summary results from Level 3 criteria of the month selected from the combo box in the worksheet. The name Level 3 is a column C range in the 8-BS, P&L Level worksheet (see chapter XXX).

Use Validation to add a balance clause lists to cells cells in column A: 1. Select cell A12.
2. Select Validation from Data and select the Settings category. 3. Select list from the Allow.
4. Select the Source box, press F3, select the name Level3 and click OK.

Paste the cell containing a verification list to all of the cells in column A from which the balance clause will be selected. 6 - Add formulas to return summary of balance clauses The formula in cell C12 and in cells of column C matching the balance clauses in column A: =SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+3((

The formula is in cell D12 and in cells of column D matching the balance clauses in column A. =SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+3-12((

A Sumif formula summarises the data from the December month columns for the years 2002 and 2003 according to the balance clause selected from the verification list in column A. A sumif formula uses three arguments:
1. First argument - range for the lookup of cells containing the criteria (balance clause selected in column A). The range is column C in the data table which is the balance clause column. 2. Second argument - criteria, the balance clause selected from a verification list in column A. 3. Third argument - the column from which the data will be summarized. The summary will be performed from the column of the month selected in the combo box. An Offset formula enables divertion to the column of the selected month from the base column (column C, see a description of how the Offset formula calculates column numbers).

The data sheet, see top leftmost corner (name box) the name of the column TB_DB_Level3.

Third argument - Offset formula
See in the figure the December 2003 column. The worksheet column number is 78.

The MonthList (Combo Box list) from which the financial statements month is selected (December 2003). The month number in the list is 72 (calculated by the number of months in the list starting from January 1978, to December 2003, 6 years * 12 months). When you select December 2003 from the combo box, the cell linked to the combo box MonthSelectionNumber receives the value 72. An Offset formula (see third formula argument in the figure) diverts the address of the column from which the data will be summarized, 75 columns from the reference column (column C) in the data sheet (see first column in the Sumif formula). The first data column in the worksheet (column 7 - January 1978), trial balance data, is 3 columns from column C, therefore you should add 3 to the value 72. According to this calculation, the Offset formula diverts the column address 75 columns to the right of the Reference column (column C). The calculated result in the matching formula in column D of the BalanceSheet worksheet (third box of the Offset formula) is 63 (5 years * 12 + 3) and the sum will be returned from the December 2002 column.

Note
For verification purposes, while working in the worksheet, temporarily turn the letters of the column title lines (A, B, C...) into numbers. Select Tools>Options>General, select R1C1 Reference Style and click OK.

7 - Notes Numbers
The formula in cell B12 and all cells from B12 to the last cell matching Total Liabilities... : =IF(ISERROR(INDEX(NotesTable,MATCH(A12,NotesFSItem,0),1))=TRUE,"",

INDEX(NotesTable,MATCH(A12,NotesFSItem,0),1))

This formula returns the requested note number from the Notes worksheet (see chapter XXX). Notice that the function appears twice in the formula: =INDEX(NotesTable,MATCH(A12,NotesFSItem,0)) A note is sometimes added to a subclause in financial statements, and sometimes it is added to a main clause such as Property and Equipment (at ' Cost). Since only some of the texts in column A have notes, a verification formula is required to prevent errors from being displayed in the cell. An ISERROR formula returns TRUE if the calculation returns an error or FALSE when the calucaltion returns a note number. If the calculation result returns an error, an empty cell will be displayed. Notes worksheet
In the NOTES worksheet containing note details, you can update, add or delete notes. Every modification resulted from adding or deleting a note will change all or some of the note numbers. The formula in column B cells of the Balance Sheet worksheet returns an updated note number for the Balance Sheet worksheet and prevents a situation where an incorrect note number is displayed in the worksheet (See chapter XXX Notes).

Reporting by fiscal year, end of month/quarter report and comparison number for previous end of year

See the two combo boxes in the figure. From each combo box you can select the reported months agains the comparison numbers.

Adding a ComboBox in the comparison number columns Add a ComboBox to cell D3. See the Combo Box adding technique at the beginning of the chapter. Link the combo box to a cell other than the one to which the combo box in column C3 is linked. The combo box is linked to a cell named CompareMonthNumberBS in the 4-Parameters worksheet.

Comparison report between balances of different years The formulas in cells in columns containing a comparison number between years are an exact copy of the formula typed into cell C12 with the number of substracted columns. See, for example, the formula in column E12: =SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+3-24)) When calculating column number for diversion in an Offset formula, the number 24 (24 months) has been substracted. The resusult is a data summary received from column December 2001.