Financial Statements and Ratios

Many companies use Excel to evaluate their financial health and report financial results. Financial statements and financial ratios are two types of analyses a company can use to accomplish those goals. Excel is well suited for financial statements because its grid interface allows for easy adjustment of columns. Ratios are simply financial calculations-something Excel was designed for.

Basic Financial Statements

Financial statements summarize the financial transactions of a business. The two primary financial statements are the balance sheet and the income statement:

• The balance sheet reports the state of a company at a particular moment in time. It shows

  • Assets: What the company owns

  • Liabilities: What the company owes

  • Equity: What the company is worth

• The income statement summarizes the transactions of a company over a certain period of time, such as a month, quarter, or year

  A typical income statement reports the sales, costs, and net income (or loss) of the company.

CONVERTING TRIAL BALANCES

Most accounting software will produce financial statements for you. However, many of those applications do not give you the flexibility and formatting options that you have in Excel. One way to produce your own financial statements is to export the trial balance from your accounting software package and use Excel to summarize the transactions for you. Figure 13-10 shows part of a trial balance, which lists all the accounts and their balances.

Figure 13-10: A trial balance lists all accounts and balances.

Figure 13-11 shows a balance sheet that summarizes the balance sheet accounts from the trial balance.

Figure 13-11: A balance sheet summarizes certain accounts.

The class column of the trial balance is used to classify that account on the balance sheet or income statement. The formula in cell B4 on the balance sheet is

 =SUMIF(Class,A4,Balance) 

On the CD 

The file  financial statements.xlsx contains all the examples in this chapter and can be found on the companion CD-ROM.

For all the accounts on the trial balance whose class equals Cash, their total is summed here. The formula is repeated for every financial statement classification on both the balance sheet and income statement. For classifications that typically have a credit balance-such as liabilities, equity, and revenue-the formula starts with a negative sign. The formula for Accounts Payable, cell B18, is

 =-SUMIF(Class,A18,Balance) 

The account that ties the balance sheet and income statement together is Retained Earnings. Figure 13-12 shows an income statement that includes a statement of retained earning at the bottom.

Figure 13-12: The income statement can include a statement of retained earnings.

The Retained Earning 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 dividends.

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","Balanced") 

If the difference between assets and liabilities and equity is more than a penny, an error message is displayed below the schedule. 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 is 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

 =B4/$B$4 

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.

Ratio Analysis

Financial ratios are calculations that are derived from the financial statements and other financial data to measure various aspects of a company. They can be compared with other companies or to industry standards. This section demonstrates how to calculate several financial ratios. See Figure 13-14.

Figure 13-14: Various financial ratio calculations.

LIQUIDITY RATIOS

Liquidity ratios measure a company's ability to pay its bills in the short term.

Caution 

Poor liquidity ratios may indicate that the company has a high cost of financing or is on the verge of bankruptcy.

Net Working Capital is computed by subtracting current liabilities from current assets:

 =Total_Current_Assets-Total_Current_Liabilities 

Current assets are turned into cash within one accounting period (usually one year). Current liabilities are debts that will be paid within one period. A positive number here indicates that the company has enough assets to pay for its short-term liabilities.

The Current Ratio is a similar measure that divides current assets by current liabilities:

 =Total_Current_Assets/Total_Current_Liabilities 

When this ratio is greater than 1:1, it's the same as when Net Working Capital is positive.

The final liquidity ratio is the Quick Ratio. Although the Current Ratio includes assets, such as inventory and accounts receivable that will be converted into cash in a short time, the Quick Ratio includes only cash and assets that can be converted into cash immediately.

 =(Cash+Marketable_Securities)/Total_Current_Liabilities 

A Quick Ratio greater than 1:1 indicates that the company can pay all its short-term liabilities right now.

Tip 

The custom number format

 0.00":1"_) 

can be used to format the result of the Current Ratio and Quick Ratio.

ASSET USE RATIOS

Asset use ratios measure how efficiently a company is using its assets: that is, how quickly the company is turning its assets back into cash. The accounts Receivable Turnover ratio divides sales by average accounts receivable:

 =Revenue/((Account_Receivable+LastYear_Accounts_Receivable)/2) 

Accounts Receivable Turnover is then used to compute the Average Collection Period:

 =365/Accounts_receivable_turnover 

The Average Collection Period is generally compared against the company's credit terms. If the company allows 30 days for its customers to pay and the Average Collection Period is greater than 30 days, it can indicate a problem with the company's credit policies or collection efforts.

The efficiency with which the company uses its inventory can be similarly computed. Inventory Turnover divides cost of sales by average inventory:

 =Cost_of_Goods_Sold/((Inventory+LastYear_Inventory)/2) 

The Average Age of Inventory tells how many days inventory is in stock before it is sold:

 =365/Inventory_turnover 

By adding the Average Collection Period to the Average Age of Inventory, the total days to convert inventory into cash can be computed. This is the Operating Cycle and is computed as follows:

 =Average_collection_period+Average_age_of_inventory 

SOLVENCY RATIOS

Whereas liquidity ratios compute a company's ability to pay short-term debt, solvency ratios compute its ability to pay long-term debt. The Debt Ratio compares total assets with total liabilities:

 =Total_Assets/(Total_Current_Liabilities+Long_Term_Debt) 

The Debt-to-Equity Ratio divides total liabilities by total equity. It's used to determine whether a company is primarily equity financed or debt financed:

 =(Total_Current_Liabilities+Long_Term_Debt)/ (Common_Stock+Additional_Paid_in_Capital+Retained_Earnings) 

The Times Interest Earned Ratio computes how many times a company's profit would cover its interest expense:

 =(Net_Income__Loss+Interest_Expense)/Interest_Expense 

PROFITABILITY RATIOS

As you might guess, profitability ratios measure how much profit a company makes. Gross Profit Margin and Net Profit Margin can be seen on the earlier common size financial statements because they are both ratios computed relative to sales. The formulas for Gross Profit Margin and Net Profit Margin are

 =Gross_Margin/Revenue =Net_Income__Loss/Revenue 

The Return on Assets computes how well a company uses its assets to produce profits:

 =Net_Income__Loss/((Total_Assets+LastYear_Total_Assets)/2) 

The Return on Equity computes how well the owners' investments are performing:

 =Net_Income__Loss/((Total_Equity+LastYear_Total_Equity)/2)