Chapter 11

Balance Sheet Five-year Comparison Reports

About This Chapter

In this chapter, you will learn how to use the reports already created in the previous chapters to create the Balance Sheet five-year comparison report, and includes the following sections:

F Overview, page 190, presents the purpose of and need for creating a five-year Balance Sheet comparison report, to enables you to better see the long-term business activities.

F Five-year Balance Sheet Comparison Report, page 191, describes the five-year Balance Sheet comparison report, as well as how to round the numbers to the thousands and troubleshoot calculation errors due to the rounding.

Overview

In Chapter 4, Balance Sheet, you learned how to create a Balance Sheet in the Financial Statements.xls workbook.

In addition to regular Financial Statements reports, you need to create and present comparable financial reports in different forms for management purposes, for fundraising from potential investors and for decision makers.

In this chapter, you will learn how to create a comparable Balance Sheet report to compare five years of business activity results, and how to present the figures rounded to the thousands.

Five-year Balance Sheet Comparison Report

After you have finished creating the five-year Balance Sheet comparison report, it will look like Figure 11‑1:

Figure 11‑1: Five-year Balance Sheet Comparison Report

Creating a Five-year Balance Sheet Comparison Report

The first step in creating the five-year Balance Sheet comparison report is to copy worksheet 31 – Balance Sheet, change the worksheet name and then add the needed formulas to three additional new columns.

Ø To create a five-year Balance Sheet comparison report:

1. Select worksheet 31 – Balance Sheet tab, and drag it to a new location while pressing <Ctrl>.

2. Double-click the sheet tab and change the worksheet name to 32 – Five-year Balance Sheet.

3. Add the three additional year's number titles to cells E8:G8 by typing the following formula to cell E8:

=D8-1

and then copying and pasting it to cells F8:G8.

NOTE:

For more details regarding the structure of the Balance Sheet report years' number titles, refer to Chapter 4, Balance Sheet.

4. Add formulas to cells in columns E: G (from cell D12) that return the summaries for the Account Type (Level 3) in column A from the appropriate month column in worksheet 21 – Trial Balances.

5. Select cell D12, then select the formula in the formula bar and press <Ctrl+C>.

6. Click either the Cancel or Enter sign in the formula bar.

7. Select cell E12 and press <Ctrl+V>, select cell F12 and press <Ctrl+V>, and then select cell G12 and press <Ctrl+V>.

8. Select cell E12, and in the last argument of the Offset formula, change the number from 12 to 24.

9. In the formula in cell F12, change the number from 12 to 36.

10. In the formula in cell G12, change the number from 12 to 48.

11. Copy cells E12:G12 and paste the formulas to all appropriate cells in columns E:G.

The MonthSelectionNumber is reduced by 12 in each formula in the cells from column C to column G (starting from cell C12, as shown in step 4). This parameter is the Namedefined inworksheet 13 – Parameters & Calculationsfor the Combo Box linked cell,as explained in Chapter 1, Introducing Financial Statements.xls Worksheets.

The formula in cell C12 is:

=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2))

The formula in cell D12 is:

=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2-12))

The formula in cell E12 is:

=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2-24))

As can be seen in Figure 11‑1, page 191, the third argument of the OFFSET function (the column number argument) is reduced by 12 (that is, twelve months or one year) in each year formula. As a result, the SUMIF formula summarizes from the appropriate column number in worksheet 21 – Trial Balances Data based on the criteria (the Account Type in column A)

Presenting the Figures in the Five-year Balance Sheet Report in Thousands

It may be difficult to read the figures side-by-side in the five-year Balance Sheet comparison report if the numbers are not rounded to the thousands. In this section you will learn how to easily round the numbers to the thousands, as well as how to correctly present the totals of the presented rounded numbers.

It is not convenient to use a ROUND formula to round numbers to the thousands, as it adds one more formula to the formulas already in the cells. It also makes auditing more complicated, uses more memory and expands the workbook size.

Instead, you should use the Custom Format techniqueto round numbers to the thousands.

Ø To format the numbers in column C:G:

1. In the five-year Balance Sheet report shown in Figure 11‑1, page 191, select the cells in columns C: G from cell C12 onwards.

2. Press <Ctrl+1>, select the Number tab, and then select the Custom category.

3. In the Type box, type:

#, ##0, [Red] (#, ##0);- ;

4. Click OK.

The # and 0 characters both display the digits entered into the cell. The difference between them, however, is that # does not display an irrelevant zero. For example, the zero-cents place in the figure 1112.50 will be displayed only if you type 0 (not #) in format's cents place. In other words, if the format is #,##0.00, the number displayed in the cell will be 1,112.50; if it is #,##0.0#, the result displayed is 1,112.5.

To round numbers to the thousands using the Custom Format technique, simply eliminate the # or 0 signs after the comma that separates the thousands from the hundreds, tens and ones, as shown:

#,###, ;[Red]-#,###,

The left side of the formatting number up to the ; sign, presents the positive numbers, while the right side of the formatting number from the ; [Red]-#,###, presents the negative numbers.

Problem: The Summary of the Presented Numbers Is Unequal

It may sometimes occur that the summary of the displayed rounded numbers is incorrect. For example, the formula in cell C16 returns 2,514 (as shown below). However, if you add the numbers up, the result is actually 2,515.

Figure 11‑2: SUM Formula Returns Error Result

This error happens because the SUM formula calculates and returns the totals as they have been entered into the cells, not how they were formatted.

For example, the numbers 1653,558 and 13,552, shown in cells C13 and C15 of the five-year comparison report on page 193, will be rounded up and displayed as 1,654 and 14, as shown in cells C13 and C15 in
Figure 11‑2, accordingly, and the total of 2,514,407 will be displayed as 2,514 after it is rounded to the thousands.

Applying a new format to cell C16 does not change a calculation already performed or cause the formula in cell C16 to run a new calculation. In other words, the formula is returned to the numbers in the cells, and not the numbers as displayed.

To solve this problem, you should use the Array Formula technique to return the totals of the rounded displayed numbers.

Ø To make the SUM formula return the correct total of the displayed numbers:

1. Type the following formula into cell C16:

=SUM(ROUND(C12:C15,-3))

2. Ignore the #VALUE error, and select the cell.

3. Press <F2>.

4. Press <Ctrl+Shift+Enter> to call an Array Formula. The correct number will be shown.

This formula returns the correct displayed numbers for two reasons:

F A ROUND formula plus the use of the rounded Custom Format to the thousands.

F An Array Formula.

Each of these reasons will be discussed in the following sections.

ROUND Formula and Rounding to the Thousands

The ROUND formula here is:

=Round(C12:C15,-3)

In the second argument of the ROUND formula, the value is -3, which rounds the number in cell C12 (as an example) from 301124.065 to 301000.

The -3 in the second argument changes the three digits (hundreds, tens and ones) to zeros. The number format in the cell (which is #,###,) does not present the three digits after the comma , which are three zeros (000).

The final result from using both the ROUND formula and rounding to the thousands displays 301 in the cell, as shown.

Figure 11‑3: ROUND Formula Arguments

An Array Formula

Pressing <Ctrl+Shift+Enter> while the cell is in edit mode (by pressing <F2>) executes a macro that opens temporary cells in the memory to which the rounded numbers in the formula range are stored. The formula then returns the summary of the rounded numbers from the stored numbers and not the summary from the numbers displayed in the worksheet cells.