|
|
Chapter 12Income Statement Five‑year and Quarterly Comparison Reports |
|
About This Chapter |
In this chapter, you will learn how to use the Income Statement report already created in Chapter 5, Income Statement to create the Income Statement five-year comparison report and quarterly comparison reports, and includes the following sections:
F Overview, page 202, presents the purpose of and need for creating an Income Statement report that compares results from five years of business activities and compares results over the last four quarters.
F Five-year Income Statement Comparison Reports, page 203, describes the creation of a five-year Income Statement report.
F Quarterly Income Statements, page 207, describes the creation of comparison quarterly Income Statements, as well as how to deal with the calendar or fiscal year end reporting period.
F Creating Charts Easily and Quickly,page 219, describes how to quickly create charts to display the comparison balances of the Income Statement report.
Overview
In Chapter 5, Income Statement, you learned how to create an Income Statement report in the Financial Statements.xls workbook.
In addition to regular Income Statement reports, you need to create and present Income Statement reports in different forms for analysis and management purposes, for fundraising from potential investors and for decision makers.
In this chapter, you will learn how to create different comparable Income Statement reports to compare the results between years and quarters of business activity results.
Five-year Income Statement Comparison Reports
After you have finished creating the five-year Income Statement comparison report, it will look like Figure 12‑1:
Figure 12‑1: Five-year Income Statement Comparison Report
Creating a Fiveyear Income Statement Comparison Report
The first step in creating the five-year Income Statement comparison report is to copy worksheet 41 Income Statement, change the worksheet name and then add the needed formulas to three additional new columns.
Ψ To create a five-year Income Statement comparison report:
1. Select worksheet 41 Income Statement tab, and drag it to a new location while pressing <Ctrl>.
2. Double-click the sheet tab and change the worksheet name to 42 Five-year Income Statement.
3. Add the three additional years' 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 Income Statement report years' number titles, refer to Chapter 4, Balance Sheet. |
4. Add formulas to cells in columns E: G 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 D10, 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 E10 and press <Ctrl+V>,select cell F10 and press <Ctrl+V> and then select cell G10 and press <Ctrl+V>.
8. Select cell E10, and in the last argument of the OFFSET formula, change the number from 12 to 24.
9. In the formula in cell F10, change the number from 12 to 36.
10. In the formula in cell G10, change the number from 12 to 48.
11. Copy cells E10:G10 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 C10, 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 C10 is:
=SUMIF(TB_DB_Level3,A10,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2))
The formula in cell G10 is:
=SUMIF(TB_DB_Level3,A10,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2-48))
As can be seen in Figure 12‑1, page 203, the third argument of the OFFSET function (the column number argument) is reduced by 12 (that is, twelve months or one year) in each column. As a result, the SUMIF formula summarizes based on the criteria (the Account Type in column A) from the appropriate column number in worksheet 21 Trial Balances Data.
Presenting the Figures in the Five-year Balance Sheet Report in Thousands
To present the figures in the five-year Income Statement report in thousands, refer to Chapter 11, Balance Sheet Five-year Comparison Reports.
Quarterly Income Statements
The Income Statement shown in Figure 12‑1, page 203 displays the current results of the last four quarters (columns B:E), and the summarized data of the last four quarters (columns F:I).
The cells in row 2 of the worksheet contain the quarter number based on the calendar year reporting period, as explained in the following sections.
Figure 12‑2: Quarterly comparison Income Statement
Calculating the Quarter Number Based on the Reporting Period (Calendar or Fiscal Year)
Use worksheet 42 Five-year Income Statement, created in the last section, to create an Income Statement report that compares balances for the four last quarters.
Copy this worksheet by selecting the worksheet tab and dragging it to a new location while pressing the <Ctrl> key. Then change the worksheet name to 43 Four Quarters Income Statement.
Start creating the Income Statement that compares the balances between four quarters by entering formulas that calculate the quarter numbers.
In row 2 of Figure 12‑3, three out of four quarters are from the same calendar year (2003) and the last quarter (the fourth quarter) is from the previous year (2002). To display the quarterly balances only, you should calculate the difference between the accumulated balances of two consecutives quarters, except when the quarter is first quarter of the year.
This calculation of the quarter number in row 2 will help to calculate the differences between two consecutive quarters from the current year or from a previous year, as explained below.
Figure 12‑3: Formula for Calculating the Quarter Number
The formula in cell B2 is:
=INDEX(MonthsTable,MonthSelectionNumber+1,5)
Two definedNames were pasted into the INDEX formulas in row 2, MonthTable and MonthSelectionNumber,as explained in the next section.
Quarter Number Calculation Worksheet
In worksheet 5 Months Lists, the Name MonthTable was defined for columns A:E (as shown in Figure 12‑4:). The Name was pasted into the first argument of the INDEX formula, as shown in Figure 12‑3.
Figure 12‑4: Worksheet 5 Months List
Cells from B2 onwards contain the month list range. The Name defined for the range is MonthsList, which is the list entered in the Combo Box input box.
When selecting the end month period, September 2003 from the Combo Box MonthsList, the linked cell MonthSelectionNumber receives the number 69 (this is the month number, starting from January 1998).
In the INDEX formula's second argument, the row number of the selected months is MonthSelectionNumber+1 (the sequence numbers in column A start from cell A2).
In the INDEX formula's third argument, the column number is column 5 (column E in the worksheet in Figure 12‑4).
Figure 12‑5: Index Formula that Returns the Quarter Number
Columns C:EFigure 12‑4 contain formulas that calculate the quarter numbers based on either a calendar or fiscal year reporting method:
F Cell C2 contains a formula that calculates the calendar quarter number:
=INT((MONTH(B2)-1)/3)+1
F Cell D2 contains a formula that calculates the fiscal quarter number, based on the month the fiscal year ended, as explained in the next section.
=MOD(CEILING(22+MONTH(B2)-FiscalYearMonthNumber-1,3)/3,4)+1
F Cell E2 contains an IF formula that returns calculation results from cell C2 or D2 depending on the reporting year end period (either calendar or fiscal), as explained in the next section.
=IF(CalendarYear=True,C2,D2)
Selecting the Calendar or Fiscal Year Reporting Period
Add a Check Box to worksheet 11 General Details to choose the year‑end reporting period, either calendar year or fiscal year, and add a Scroll Box to select the month year-end number (if the reporting year period is fiscal year), as shown in Figure 12‑6.
Figure 12‑6: Selecting the Calendar or Fiscal Year Reporting Period
Ψ To add a Check Box:
1. Select one of the toolbars, right-click and then select the Forms toolbarfrom the shortcut menu.
2. Select cell K6, press <Ctrl+F3> and type CalendarYear into the Name in workbook box.
3. Click OK.
4. Select the Check Box object, as shown.
5. Draw a small square anywhere in the worksheet. A check box appears.
6. Select the Check Box, right-click and select the Format Control from the shortcut menu.
7. Select the Control tab.
8. In the Cell link box, type CalendarYear, as shown.
9. Click OK.
10. Press <Esc> to cancel edit mode.
11. Mark the Check Box.
The value in cell K6 is True when the Check Box is marked.
Ψ To add a Scroll Bar:
1. Follow steps 1 through 3 in the previous procedure for cell C8, with the Name FiscalYearMonthNumber in the Name in workbook box.
2. Select the Scroll Bar object, as shown.
3. Draw a small square in cell D8. The Scroll Bar object appears.
4. Select the Scroll Bar, right-click and select Format Control from the shortcut menu.
5. Select the Control tab.
6. Select 1 for the Minimum value and 12 for the Maximum value, as shown.
7. In the Cell link box, type $K$7.
8. Click OK.
9. Press <Esc> to cancel the edit mode.
10. Type the following formula into cell C8 (FiscalYearMonthNumber):
=IF(K6=TRUE,12,K7)
The formula will either return 12 if the reporting year-end period is based on the calendar year end, or the number from cell K7 chosen by using the Scroll Bar.
|
|
TIP: To automatically fit the size of an object (Combo Box, Check Box or Scroll Bar) to the cell, press the <Alt> button while sizing the corner of the object to the cell borders. |
Automatically Modifying the Quarter Column Titles
Select the worksheet in which you created the comparable Quarterly Income Statements, as shown in Figure 12‑7. The cells in row 10 from B10 onwards (that is, the ending date of each quarter) contain an INDEX formula with two arguments. When choosing the reporting month from MonthsList using the Combo Box, the formulas return the end date of each quarter.
Figure 12‑7: Quarterly Comparison Income Statement
The formula in cell B10 is:
=INDEX(MonthsList,MonthSelectionNumber)
The formula in cell C10 is:
=INDEX(MonthsList,MonthSelectionNumber-3)
In worksheet 21 Trial Balance Data, the Trial Balances are stored on a monthly basis, one column for each month. The distance between the quarters is three columns wide. In the INDEX formula's second argument, the number in the MonthSelectionNumber cell is reduced by 3 (that is, in cell D10 type -6 instead of 3 and in cell E10 type -9 instead of 3).
Copy the formulas from cells B10:E10 and paste them to cells F10:I10.
Summing the Quarterly Balances
Columns F:K contain formulas that return the accumulated balances for the Account Types in column A from the worksheet 21 Trial Balance Data. Column K contains the balances from the fifth quarter.
Figure 12‑8: Summing Current and Summed Quarter Data
The formula in column F12 (the last quarter) is:
=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+3))
The formula in K12 (the fifth quarter) is:
=SUMIF(TB_DB_Level3,A12,OFFSET(TB_DB_Level3,0,MonthSelectionNumber-9))
The formulas in columns B:E return the net balances for each quarter.
The formula in cell B12 is:
=IF($B$2=1,F12,F12-G12)
The formula in cell D12 is:
=IF($B$2=1,I12,I12-K12)
The IF formula's first argument checks the quarter number. If it is 1, then in the second argument there is no need to subtract the accumulated balances from the previous accumulated balances quarter.
Figure 12‑9: Using Trace Precedents to Audit Formula
Quarterly Income Statement Examples
Figure 12‑10 shows the results of the last four quarters' activity and the annual results.
Figure 12‑10: Example 1 Results of the Last Four Quarters' Activity and Annual Results
Figure 12‑11 shows the results of the last four quarters' activity, analyzed by percentage (sales = 100%) and annual results.
Figure 12‑11: Example 2 Results of the Last Four Quarters' Activity, Analyzed by Percentage (Sales = 100%)
Creating Charts Easily and Quickly
After you have created a number of Income Statement reports that display the comparison balances for five years, or compare sales revenue and net income for the last four quarters, you may want to present the balances using Charts that provide the reader with a powerful tool for analyzing the figures.
Excel offers an excellent shortcut key (<F11>) that enables you to create Charts in a matter of seconds.
Ψ To create Charts with the shortcut key:
1. Select worksheet 42 Five-year Income Statement.
2. Select non-adjacent cells by:
F Select the Sales figures by selecting cells A12:G12.
F Press <Ctrl> and select the Total Cost of Goods Sold by selecting cells A19:G19.
F Continue pressing <Ctrl> and select the Net Income by selecting cells A39:G39.
Your worksheet should now look like this:
3. Press <F11>. A new Chart is created in a Chart worksheet:
The same technique can be used to create a Chart that compares the balances in the Quarterly comparison reports you have created, for example, a Chart that compares almost every subtotal item in the Quarterly Income Statement, as shown below.
