To create a fiveyear Balance Sheet comparison report

1. Add new worksheet by pressing <Shift+F11>, and then rename it to 32 — Five-year Balance Sheet.

2. Select worksheet 31 — Balance Sheet and copy columns A:D.

3. Select worksheet 32 — Five-year Balance Sheet, and press <Enter> to paste the columns.

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

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


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

Chapter 11

Follow steps 6 through 12 below and add formulas to cells in columns E: G (from cell E12) that return the summaries for the Account Type items (Level 3) in column A from the appropriate month column in worksheet 21 — Trial Balances Data.

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

7. Click either Cancel or Enter in the formula bar.

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

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

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

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

12. 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 D to column G (starting from cell D12, as shown in step 4). This parameter is the Name defined in worksheet 13 — Parameters & Calculations for the Combo Box linked cell, as explained on page 6, 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 on 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 in column A, which are Account Type items.


The range Names pasted into the formulas above are defined on page 63; Chapter 3, Updating the Trial Balances Data Worksheet.


Chapter 11

0 0

Post a comment