Chapter 4Balance Sheet |
| About This Chapter |
This chapter provides step-by-step instructions for creating a Balance Sheetreport, which is the major report in the Financial Statement, and includes the following sections:
F Overview, page 66, introduces the Balance Sheet report, and describes the techniques used to create it in an Excel worksheet.
F Creating a Balance Sheet Report in anExcel Worksheet, page 68, provides step-by-step instructions for creating the Balance Sheet reportusing an Excel worksheet.
F Presenting Two Different Periods, page 85, describes how to add a Combo Box to the worksheet to enable comparing different periods.
F Auditing the Balance Sheet Figures, page 87, describes how to audit and balance the figures in the completed Balance Sheet.
F Using a Combo Box.
F Using Validation.
F Using formulas that return and summarize data.
Balance Sheet formatting (including rounding to the thousands), printing, e‑mailing, page-numbering methods, and so on, are described in subsequent chapters. Other important subjects, such as the common Balance Sheet and ratio analysis are discussed in the second part of this book, Analyzing Financial Statements and Creating Management Financial Reports.
Figure 4‑1: Balance Sheet for the Year Ending December 31
Creating a Balance SheetReport in an Excel Worksheet
The following sections provide step-by-step instructions for creating a Balance Sheet in worksheet 31 Balance Sheet.
Step 1: Defining Names
In order to ensure efficient and accurate work, you should use Namesas range references in the worksheet. This is especially helpful for re‑directioning in formulas, attaching lists to Combo Boxes and Validation, and for linking cells to a Combo Box.
In worksheet 13 Parameters & Calculations (described in Chapter
1, Introducing Financial Statements.xls Worksheets and shown in
Figure 4‑2), you defined Names for cells used for a cell link
to a Combo Box, and for cells that contain formulas that calculate
the reporting date, month and year numbers. In worksheet 14 Months Lists,
you defined a Name for the MonthsList. Inworksheet 21 Trial
Balances Data,you defined Names to TB_data, and so on.
Figure 4‑2: Worksheet 13 Parameters & Calculations
Step 2: Adding a Combo Box
The Combo Box enables you to select the reporting month for the Financial Statements. When you select one of the months from Combo Box monthslist, the formulas in the worksheet summarize the data from the column in worksheet 21 Trial Balance Data where the Trial Balances data are stored.
Ψ To add a Combo Box to Worksheet 31 Balance Sheet:
1. Select worksheet 11 General Details.
2. Select and copy the Combo Box.
3. Select worksheet 31 Balance Sheet and paste the Combo Box.
For more details on how to add a Combo Box, refer to Chapter 1, Introducing Financial Statements.xls Worksheets.
Step 3: Entering the Company Name
Add the company name to the Balance Sheet report.
Ψ To add the company name into the Balance Sheet report:
1. Type the following formula into cell A4:
=CompanyName
2. Select cells A4:D4 and click the Merge and Center icon, as shown below.
Step 4: Entering the Month Title and End-of-Year Numbers
Ψ To enter the month title and end-of-year numbers:
1. Type the following formula into cell C7:
=ReportsDate
Select cells C7:C8 and click the Merge and Center icon.
2. Type the following formula into cell C8:
=ReportYearNumber
3. Type the following formula into cell D8:
=C8-1
Your worksheet should look like this:
The Names ReportDate and ReportYearNumber are in worksheet 13 Parameters & Calculations (for more details refer to Chapter 1, Introducing Financial Statements.xls Worksheets).
Step 5: Formatting the Summary Levels
F Level 1, Category: Assets, Liabilities, Stockholder Equity.
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 4‑3: Balance Sheet Summary Levels
A professional Financial Statements report should keep the formatting of all reports, including Balance Sheet, Income Statement, and Cash Flow, identical. This is done by saving each Style as a separate and permanent Style. You can format cells in every report by selecting cells within any report and then selecting the desired Style from the Style Box, as explained in the following sections.
Ψ To create a Custom Format and save as a Style:
1. Right-click one of the toolbars and select Customize. The Customize window appears.
2. Select the Commands tab, as shown below.
3. Select Format from the Categories box, and drag the Style Box icon to the formatting toolbar.
4. Click Close. The Style Box is now located on the Formatting toolbar.
5. Select cell A12 (as shown in Figure 4‑3, page 71).
6. Use the Font Size Box icon on the Formatting toolbar to set the font size to 10. Ensure that it is not bolded or italicized.
7. From the Format menu, select Style.
8. In the Style Box, type Level1 and then click OK. The Level 1 style has now been set.
9. Select cell A13 and repeat step 6.
10. Increase the indentation by two characters by either:
Double-clicking the Increase Indent icon,
OR
Pressing <Ctrl+1> to open the Format Cells window, and selecting the Alignment tab. In the Indent spin box, type 2.
11. Repeat steps 7 and 8 for Level2. The Level 2 style has now been set.
12. Select cell A17 and repeat step 6. In this case, however, click the Bold icon on the toolbar to bold the text.
13. Repeat step 10, except indent the text four characters (that is, click the Increase Indent icon four times or type 4 in the Indent spin box).
14. Repeat steps 7 and 8 for Level3. The Level 3style has now been set.
15. Select the Style box icon and ensure that all three new styles (Level1, Level2 and Level3) have been added to it.
Ψ To add and modify a style:
1. From the Format menu, select Style.
2. In the Style window, select a style from the Style Name box.
3. To add a new Style, type the Style Name into the Style Name box.
4. Click Add.
5. In the Format Cells window, change to the desired format and click OK.
6. To modify a style, choose the Style from Style Name, and click Modify.
7. In the Format Cells window, change to the desired format and click OK.
Step 6: Choosing Account Type Items from a List Using Validation
Selecting an Account Type item from a list of Account Types using Validation (column A in Figure 4‑4) enables formulas entered into cells in columns C and D to identify the text (Account Type item Level 3)andreturns the summary results from the Level 3 criteria (column C in worksheet 15 BS, IS Level, as described in Chapter 1, Introducing Financial Statements.xls Worksheets) for the month selected from the Combo Box in the worksheet 31 Balance Sheet.
Figure 4‑4: Choosing an Account Types Item from a List Using Validation
The Name Level 3 is a column C range in worksheet 15 BS, IS Level. For more details, refer to Chapter 1, Introducing Financial Statements.xls Worksheets.
Add the list of Account Types to the Validation technique for easier selection of an Account Type item.
Ψ To add the Account Types list to the Validation source box:
1. Select cell A12.
2. From the Data menu, select Validation. The Data Validation window appears.
3. Select the Settings tab, and select List from the Allow Box.
4. In the Source box, press <F3>, select the Name Level3, and click OK.
5. Paste the cell containing the Validation list to all of the cells in column A from which the Account Type items will be selected.
Step 7: Entering Formulas That Return Summary Balances for Account Types (Level 3)
The formula in cell C12 (and in the cells of column C that parallel an Account Type item in column A) is:
The formula in cell D12 (and in the cells of column D that parallel an Account Type item in column A) is:
This SUMIF formula summarizes the data from the December month columns for the years 2002 and 2003 from worksheet 21 Trial Balances Data. The criterion the SUMIF formula uses is the Account Type item in column A (in worksheet 31 Balance Sheet). The SUMIF formula has three arguments:
F First argument: The range to evaluate according to the criteria entered in the second argument of the SUMIF formula.
F Second argument: The criterion is the Account Type item chosen from the Level 3 list using the Validation technique.
F Third argument: The column from which the data will be summarized. The summary will be performed from the month column number selected in the Combo Box adjusted by the OFFSET formula. The OFFSET formula enables diversion to the column of the selected month from the base column (column C). The following section explains how the OFFSET formula diverts to the proper column.
How the OFFSET Formula Operates
Column 77 is the column number for December 2003 in worksheet 21 Trial Balances Data, as shown in Figure 4‑8 (to change the header title text to numbers, refer to page 82).
When December 2003 is selected from the Combo Box list (Month List), the month number in that list is 72 (this is calculated by determining the number of months between January 1978 to December 2003 6 years * 12 months = 72), the linked cell to the Combo Box in worksheet13 Parameters & Calculationsreceives the value of 72,andthe cell linked Name is MonthSelectionNumber.
In worksheet 21 - Trial Balances Data, column 3 is the base column where the SUMIF formula evaluates for the criteria in the second argument of the SUMIF formula. In this case, it is the difference between 77 and 3 (74).
In the third argument, the Sum_range should be 74 columns distant from the base column. The OFFSET formula returns the result of 74 and causes the SUMIF formula to summarize the figures from the December 2003 Trial Balance column.
Figure 4‑9: Offset Function Arguments Window
Step 8: Automatically Updating the Note Numbers
Worksheet 61 Notes contains a Notes table that you can update, add or delete (for more details, refer to Chapter 7, Notes).
The formula in the cells in column B in worksheet 31 Balance Sheet returns an updated Note number and prevents a situation where an incorrect Note number is displayed in the Balance Sheet.
The formula in cell B12 (and all cells from B12 to the last cell of the Balance Sheet report) is:
=IF(ISERROR(INDEX(NotesTable,MATCH(A12,NotesFSItem,0),1))=TRUE,"",
INDEX(NotesTable,MATCH(A12,NotesFSItem,0),1))
Figure 4‑10: Formula Returning the Note Number
This formula returns an updated Note number from worksheet 61 Notes, as shown below (for more details, refer to Chapter 7, Notes).
Figure 4‑11: Worksheet 61 Notes
Notice that the function:
To present two periods by choosing each period, add a second Combo Box to the worksheet.
Figure 4‑12 shows two Combo Boxes at the top of each column.
Figure 4‑12: Presenting Two Different Periods
Ψ To add a second Combo Box:
1. Copy worksheet 31 Balance Sheet by selecting the worksheet's tab and dragging it to a different location while pressing <Ctrl>.
2. Select the Combo Box and press <Ctrl+C> to copy.
3. Select cell D2 and press <Ctrl+V> to paste.
4. Select the new Combo Box, and from the shortcut menu, select Format Control, and then the Control tab.
5. In the Cell Link box, type MonthSelectionNumber.
6. Select worksheet 13 Parameters & Calculations and define the Name MonthSelectionNumber to cell B10.
7. Select the new worksheet you copied in step 1, and change the formulas in cells in column D to:
=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,CompareMonthNumberBS+2))
Notice that, in the SUMIF formula's third argument, the cell address within the Offset formula that links to the second Combo Box is CompareMonthNumberBS.
Auditing the Balance Sheet Figures
It is essential that the figures in the Balance Sheet be thoroughly checked before it can be presented or printed. This can be done by adding formulas that summarize the balances according to Level 2, Account Groups.
Figure 4‑13: Auditing the Balance Sheet
The formula in cell F16 is:
=SUMIF(TB_DB_Level2,A11,OFFSET(TB_DB_Level2,0,MonthSelectionNumber+1))
This formula summarizes the totals in the December 2003 column of worksheet 21 Trial Balances Data based on the Current Assets criteria (cell A11 inFigure 4‑13) in column B Level 2.