|
|
Chapter 5Income Statement |
|
About This Chapter |
This chapter explains how to create the Income Statement report using Excel's worksheet, and includes the following sections:
F Overview, page 90, introduces the concept of the Income Statement report.
F Creating an Income Statement Report in an Excel Worksheet, page 91, describes how to create the Income Statement report using Excel's worksheet.
F Adding Unique Items to the Income Statement Report, page 93, describes how to add the two unique items to the Income Statement report.
Overview
The Income Statement report presents the net result of a company's revenues, minus expenses, over a given period of time. In other words, the Income Statement bottom line is an indication of how much profit the company has made during the period.
In this chapter, you will learn how to create a regular Income Statement report using an Excel worksheet. In the second part of this book, Analyzing Financial Statements and Creating Management Financial Reports,you will learn how to:
F Interpret Income Statements.
F Analyze Income Statements.
F Create Income Statements that enable management to make business decisions.
F Generate monthly, quarterly or annual accumulated Income Statements based either on a calendar or on fiscal reporting periods.
F Analyze profit centers.
Creating an Income Statement Report in an Excel Worksheet
Figure 5‑1 displays an example of an annual Income Statement report created in worksheet 41– Income Statement.
Figure 5‑1: Income Statement report
In Chapter 4, Balance Sheet, you learned how to create a Balance Sheet using an Excel worksheet. The Income Statement report is created using similar techniques, including:
F Adding a Combo Box to select the ending-month reporting period.
F Entering the company name in the report's title.
F Using a Validation dropdown list to select an Account Types item (explained in Chapter 1, Introducing Financial Statements.xls Worksheets).
F Automatically calculating Note numbers in the Notes column.
F Calculating the report year's number in the comparison columns (columns C and D in Figure 5‑1, page 91).
F Inserting formulas that return the total balance for each Account Type in the comparison columns (columns C and D below).
F Inserting auditing formulas to summarize the balance at the Account Groups level(for more details regarding the different types of levels, refer to Chapter 1, Introducing Financial Statements.xls Worksheets).
F Using Styles to apply the same format used in the Balance Sheet report (for more details, refer to Chapter 4, Balance Sheet).
Adding Unique Items to the Income Statement Report
The Balance Sheet, as well as many other components of a corporation's Financial Statements, is generated once every period. The figures presented in the Balance Sheet are the actual balances for the statement creation date, while the figures presented in the Income Statement and Cash Flow reports are the accumulated summarized balance figures for the report period.
There are two items that are unique to the Income Statement report compared to the Balance Sheet report:
F The number of the year the reports end.
F The retained earnings.
In this section, you will learn how to add these two items to worksheet 41 – Income Statement.
Entering a Formula to Display the Date the Report Ended
Account Types balances in the Balance Sheet are summarized for
the date it is prepared (shown in cells C7:D8 inFigure 5‑2).
Account Types balances in the Income Statement report are summarized for
the reporting period, which can be monthly, quarterly or annually. The title in
cell A6 (in Figure 5‑2)
includes the reporting period and the creation date, as in this example for the
year ending
Figure 5‑2: Income Statement Ending Period Date
Ø To create and insert the formula that returns the income statement period and ending date:
1. In cell A6, type the following formula:
=IS_YearEndDate
This formula returns the period (that is, the year the report ended) and the report end date (December 31, 2003) from cell B12 in worksheet 13 – Parameters & Calculations (shown below) and places it in cell A6 (IS_YearEndDate) of the Income Statement worksheet.
The formula in cell B12 in 13 – Parameters & Calculations is:
=A12&" "&TEXT(ReportsDate,"mmmm dd, yyyy")
which merges the text in cell A12 with the calculated formatted date in cell B3, as shown:
2. In the Income Statement worksheet, select cells A6:D6 and click
the Merge and Center 
icon.
The report period and end date is displayed, as shown:
Adding the Retained Earnings Balances
Add Retained Earnings balances for previous years to the Income Statement report, as described in this section.
Ø To add previous Retained Earnings balances:
1. Add two new rows (rows 131 and 132, in the figure below) to worksheet 21 – Trial Balances Data.
2. In row A131, type the exact text that should appear in cell A40 of worksheet 41 – Income Statement (Figure 5‑1, page 91).
3. In cell A132, type different text. This is because the formula in cell C40 (of worksheet 41 – Income Statement) will return the retained earnings figure for the beginning of the year, based on the criteria in cell A40 (of worksheet 41 – Income Statement).
4. In cells BM131:BY132, type the Retained Earnings balances. As shown in the figure in step 1(page 96), the balance between the figures in cell BM131 and BM132 is 0, as is the balance between cells BY131 and BY132. This is because the balance for each month must be kept at $0.
The formula in cell C40 in worksheet 41 – Income Statement for the Retained Earnings is:
=SUMIF(TB_DB_Level3,A40,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2))
For more details regarding this formula, refer to Chapter 4, Balance Sheet.