Microsoft Office Tutorials and References
In Depth Information
Financial Statements and Ratios
The Retained Earnings classification on the balance sheet refers to the Ending Retained Earnings
classification on the income statement. Ending Retained Earnings is computed by taking
Beginning Retained Earnings, adding net income (or subtracting net loss), and subtracting
Finally, the balance sheet must be in balance: hence, the name. Total assets must equal total
liabilities and equity. This error-checking formula is used in cell B31 on the balance sheet:
=IF(ABS(B29–B15)>0.01,”Out of Balance”,””)
If the difference between assets and liabilities and equity is more than a penny, an error message
is displayed below the schedule (otherwise the cell appears blank). The ABS function is used to
check for assets being more or less than liabilities and equity. Because the balance sheet is in
balance, the formula returns an empty string.
Common size financial statements
Comparing financial statements from different companies can be difficult. One such difficulty is
comparing companies of different sizes. A small retailer might show $1 million in revenue, but a
multinational retailer might show $1 billion. The sheer scale of the numbers makes it difficult to
compare the health and results of operations of these very different companies.
Common size financial statements summarize accounts relative to a single number. For balance
sheets, all entries are shown relative to total assets. For the income statement, all entries are
shown relative to total sales. Figure 13-13 shows a common size income statement.
Figure 13-13: Entries on a common size income statement are shown relative to revenue.
The formula in cell C4 is
The denominator is absolute with respect to both rows and columns so that when this formula is
copied to other areas of the income statement, it shows the percentage of revenue. To display
only the percentage figures, you can hide column B.