Data Collection and Presentation

Abstract

The collection, organization, and presentation of data are basic background material for learning descriptive and inferential statistics and their applications. In this chapter, we first discuss sources of data and methods of collecting them. Then we explore in detail the presentation of data in tables and graphs. Finally, we use both accounting and financial data to show how the statistical techniques discussed in this chapter can be used to analyze the financial condition of a firm and to analyze the recent deterioration of the financial health of the US banking industry. In addition, we use a pie chart to examine how Congress voted on the Gulf Resolution in 1991.

Appendices

Appendix 1: Using Microsoft Excel to Draw Graphs

This appendix explains how to use Microsoft Excel to draw graphs. Eight steps are involved: entering Microsoft Excel, preparing the data, and drawing the graph(s):

• Stage 1: Start Excel and enter data for Johnson & Johnson and Merck as shown in Fig. 2.9.

  Fig. 2.9

  

  Rates of return for JNJ and MRK

• Stage 2: Select the data to be graphed as shown in Fig. 2.10.

  Fig. 2.10

  

  Data to be graphed

• Stage 3: From the Insert Menu, choose Line on charts option as shown in Fig. 2.11 to get the 2-D line chart as shown in Fig. 2.12.

  Fig. 2.11

  

  Select graph option

• Stage 4: Choose the Select Data on Chart Tools as shown in Fig. 2.12.

  Fig. 2.12

  

  Step 1 of Chart Tools

• Stage 5: Choose Year and press Remove as shown in Fig. 2.13. Then press Edit to select axis labels as shown in Fig. 2.14.

  Fig. 2.13

  

  Step 2 of Chart Tools

• Stage 6: Select Cell A2 to Cell A22 in Axis label range as shown in Fig. 2.14 and press OK.

  Fig. 2.14

  

  Step 3 of Chart Tools

• Stage 7: Press OK on Select Data Source again, and then we can get the chart as shown in Fig. 2.15. Press Move Chart on Chart Tools, select New sheet, and press OK. The finished chart is shown in Fig. 2.16.

  Fig. 2.15

  

  Step 4 of Chart Tools

  Fig. 2.16

  

  Line Charts of rates of return for JNJ and MRK

Microsoft Excel has strong charting features. With more work, the chart in Fig. 2.16 can look like the chart in Fig. 2.17.

Appendix 2: Stock Rates of Return and Market Rates of Return

Table 2.3 presents data on earnings per share (EPS), dividends per share (DPS), and price per share (PPS) for Johnson & Johnson, Merck, and the S&P 500 during the period 1988–2009. Table 2.4 shows rates of return for Johnson & Johnson, Merck, and the S&P 500, calculated from the data in Table 2.3.

The formula for calculating the rate of return, R _jt , on the jth individual stock in period t is

$$ {R_{jt }}=\frac{{{P_{jt }}-{P_{jt-1 }}+{d_{jt }}}}{{{P_{jt-1 }}}} $$

(2.1)

where P _jt represents price per share for the jth stock in period t and d _jt represents dividends per share for the jth stock. The market rate of return, R _mt , in period t is

$$ \frac{{{\rm S}{{\rm P}_t}-{\rm S}{{\rm P}_{t-1 }}}}{{{\rm S}{{\rm P}_{t-1 }}}} $$

(2.2)

where SP _t represents the S&P 500 in period t.

The rate of return on an individual stock can be rewritten as

$$ {R_{jt }}=\frac{{({P_{jt }}-{P_{jt+1 }})}}{{{P_{jt-1 }}}}+\frac{{{d_{jt }}}}{{{P_{jt-1 }}}}=\mathrm{ Capital}\;\mathrm{ gain}\;\mathrm{ yield}+\mathrm{ dividend}\;\mathrm{ yield} $$

(2.3)

Table 2.3 EPS, DPS, and PPS for Johnson & Johnson, Merck, and the S&P 500

The first term of the rewritten equation is the capital gain yield (in percent); the second is the dividend yield (also in percent). As an example, let us calculate the rate of return for Johnson & Johnson in 2009. From Table 2.3, we know that PPS₀₈ = $59.83, PPS₀₉ = $64.41, and DPS₀₉ = $1.91. Thus, the rate of return for Johnson & Johnson in 2009 equals

$$ {R_{JNJ09}}=\frac{64.41-59.83+1.91 }{59.83 }=.1085. $$

As another example, from Table 2.3, we know that the S&P 500 was 1220.04 and 948.05 in 2008 and 2009, respectively. Thus, the market rate of return in 2008 equaled

$$ {R_{{\rm M}08 }}=\frac{948.05-1220.04 }{1220.04 }=-0.2229 $$

Figure 2.5 in the text compares the rates of return for Johnson & Johnson, Merck, and the S&P 500 over time, as discussed in Sect. 2.5 of this chapter.

Table 2.4 Rates of return for Johnson & Johnson and Merck stock and the S&P 500

Appendix 3: Financial Statements and Financial Ratio Analysis

3.1 Review of Balance Sheets and Income Statements

Accounting concepts are used to understand a firm’s financial condition. We will discuss two basic sources of accounting information: the balance sheet, which reveals the assets, liabilities, and owners’ (stockholders’) equity of a firm at a point in time, and the income statement, which shows the firm’s profit or loss over a given period of time. Assets, which are things that the firm owns, can be classified as current, fixed, or “other” assets. Current assets consist of cash and of property that can be turned into cash quickly. Examples of current assets include cash, stocks, bonds, inventory, and accounts receivable (cash that customers owe the firm). Fixed assets are not easily convertible into cash; they include land, machinery, and buildings. Fixed assets are generally valued at their historical value (purchase price) minus depreciation, not at their current market value. Other assets include intangibles such as goodwill, trademarks, patents, copyrights, and leases.

Liabilities, which are debts of the firm, are divided into current and long-term debts. Current debts come due within 1 year, whereas long-term debts are due in more than 1 year. Current debts include accounts payable (unpaid bills), notes payable, debts on agreements, accrued expenses, expenses incurred but not yet paid, and taxes payable. Examples of long-term liabilities include mortgages, payable corporate bonds, and capitalized leases.

Stockholders’ equity makes up the second part of the liabilities section of a balance sheet. It consists of funds invested by shareholders plus retained earnings. The net worth of the firm is calculated by subtracting total liabilities from total assets.

Whereas a balance sheet looks at the firm at a point in time, the income statement, as we noted earlier, evaluates the firm over a period of time. The end product of the income statement is the profit or loss, which is calculated by taking the sales for a period and subtracting the cost of goods sold and such expenses as research and development, interest, and selling, general, and administrative expenses.

Table 2.5 presents JNJ’s balance sheet for 2004–2009. Total assets are divided into current and fixed assets. Again, the current assets are those assets that can be converted into cash in a year or less; fixed assets such as land cannot be turned quickly into cash. The liabilities section is separated into liabilities and stockholders’ equity. GM’s liabilities include long-term debt, current liabilities such as accounts payable, and deferred taxes. Stockholders’ equity consists of common stock and paid-in surplus, preferred stock, retained earnings, and other adjustments. Note that total assets equal the sum of total liabilities and equity.

Table 2.5 Johnson & Johnson corporation balance sheet ($ million)

Table 2.6 Income statement for Johnson & Johnson corporation ($ milIion)

Table 2.6 displays JNJ’s income statement for both 2004–2009, showing the firm’s profit after expenses are subtracted from revenues. To determine gross profits, the costs of goods sold are subtracted from net sales. Operating income is then calculated by deducting selling and administrative expenses, debt amortization, and depreciation. Earnings before interest and taxes (EBIT) are next obtained by adding other income to operating income, and interest is subtracted to get earnings before taxes (EBT). Finally, net income is obtained by subtracting the provision for income taxes and adding earnings in unconsolidated subsidiaries and associates. Both earnings per share and dividends per share also are reported.

3.2 Financial Ratio Analysis

To help them analyze balance sheets and income statements, financial managers construct various financial ratios. There are five basic types of these ratios: leverage ratios, activity ratios, liquidity ratios, profitability ratios, and market value ratios. Let us use Merck (MRK) and Johnson & Johnson data to calculate a number of these ratios and discuss their significance.

Table 2.7 shows how financial ratios are derived from the 2009 balance sheet and income statement. The information for the current ratio comes from the assets side of the balance sheet. The ratio for JNJ is 1.82, which means that 1 dollar in current liabilities is matched by 1.82 dollars in current assets. To calculate the inventory turnover ratio, we use cost of goods sold from the income statement and inventories from the current assets in the balance sheet. The resulting ratio (3.561) reveals how often the average value of goods in inventory was sold in 2009.

The total debt to total assets ratio is derived from the balance sheet; it indicates that about 45.1 % of JNJ’s assets are financed by debt. Data to calculate the net profit margin come from the income statement. JNJ’s profit margin is.198, that is, about 20 cents out of every dollar of sales is profit (net income). ROI (return on investment) is more accurately described as return on total assets; it is calculated from information in both the income statement and the balance sheet. The resulting figure for JNJ is 12.9 %.

The price/earnings (P/E) ratio is calculated by taking the price per share divided by the earnings per share (EPS). Although the price per share does not appear in the balance sheet or the income statement, it can be found in stock reports in newspapers. The EPS is then found by dividing net income by the number of common shares. (The ratio cannot be calculated if the firm experienced losses.) The P/E ratio for JNJ is 14.474.

Table 2.7 Financial and market ratio calculations for JNJ, 2009 (dollars amounts in millions)

Finally, the payout ratio is calculated by dividing the price of the stock by the dividends per share (DPS). DPS is the value of dividends paid out divided by the number of shares of common stock. This ratio reveals that JNJ paid out about 0.434 % of its earnings in dividends.

The seven ratios discussed in Table 2.7 for both Johnson & Johnson and Merck during 2004–2009 are presented in Table 2.8 following the method discussed in Appendix 2. Line charts in terms of data presented in Table 2.8 are presented in Figs. 2.17, 2.18, 2.19, 2.20, 2.21, 2.22 and 2.23. By using these seven graphs, we now compare the financial ratios of JNJ to those of Merck.

Table 2.8 Seven key financial ratios for JNJ and Merck

As mentioned above, there are five basic types of financial ratios. Liquidity ratios measure how quickly or effectively the firm can obtain cash. If the bulk of a firm’s assets are fixed (such as land), the firm may not be able to obtain enough cash to finance its operations. The current ratio, defined as current assets divided by current liabilities, is used to gage the firm’s ability to meet current obligations. If the firm’s current assets do not significantly exceed current liabilities, the firm may not be able to pay current bills, because although current assets are expected to generate cash within 1 year, current liabilities are expected to use cash within that same 1-year period. In Fig. 2.17, this ratio is graphed for both Johnson & Johnson and Merck for the years 1990–2009. As the figure reveals, JNJ had a higher current ratio almost the entire time with the exception of 1998 when its current ratio was1.364, while Merck’s was 1.685.

Leverage ratios measure how much of the firm’s operation is financed by debt. Although some debt is expected, too much debt can be a sign of trouble. One indicator of how much debt the firm has incurred is the ratio of total debt to total assets, which measures the percentage of total assets financed by debt. Figure 2.18 shows that Johnson & Johnson had a greater share of its assets financed by debt than did Merck over most of the 1977–2009 periods. This fact is not necessarily a reason for concern unless Johnson & Johnson’s leverage ratio was too high in absolute terms. The general trend shows that both firms increased their debt during the period, particularly from 1985 to 1998, and that a sharp increase occurred from 1987 to 1992.

Fig. 2.17

Current ratio for JNJ and MRK

Fig. 2.18

Total debt to total assets ratio for JNJ and MRK

Activity ratios measure how efficiently the firm is using its assets. Figure 2.19 graphs the inventory turnover ratio for each firm; it is found by dividing cost of goods sold by average inventory. This ratio measures how quickly a firm is turning over its inventories. A high ratio usually implies efficiency because the firm is selling inventories quickly. This ratio varies greatly with the line of business, however. A supermarket must have a high turnover ratio because it is dealing with perishable goods; in contrast, a jewelry store selling diamonds has a much lower turnover ratio. The seasonality of the product must also be considered. Auto dealers have high inventories in the fall, when the new autos arrive, and lower inventories in other seasons. On the other hand, Christmas tree dealers have rather low inventories in August!

Profitability ratios measure the profitability of the firm’s operations. One of these ratios is the return on total assets, defined as net income divided by total assets. This ratio, often abbreviated ROA, measures how much the company has earned on its total investment of financial resources. Looked at in another way, it measures how well the firm used funds, regardless of how the firm’s assets are divided into fixed and current assets. As Fig. 2.20 suggests, Merck had a higher ROA than Johnson & Johnson until 2004–2007.

Fig. 2.19

Inventory turnover for JNJ and MRK

Fig. 2.20

Return on total assets for JNJ and MRK

Fig. 2.21

Net profit margin for JNJ and MRK

The net profit margin, defined as net income divided by net sales, is another measure of profitability. This ratio gages the percentage of sales revenue that consists of profit. This ratio varies for different industries; a successful supermarket might have a ratio of 20 %, whereas most manufacturing firms tend to have ratios around 8 %. Although many Americans believe that corporations make a profit of 25 cents or more on each dollar of sales, the average net profit ratio for the Fortune 500 industrial firms in 1981 was 4.6 %. The net profit margins for Merck and Johnson & Johnson are presented in Fig. 2.21.

An indirect profitability indicator is the payout ratio, which measures the proportion of current earnings paid out in dividends. This ratio, which is expressed as dividends per share divided by earnings per share, can fluctuate widely because of the variability in earnings per share.. The reason, for example, why the payout ratio was so high for Merck in 1981 is that earnings per share were low. The payout ratios for Merck and Johnson & Johnson are illustrated in Fig. 2.22.

Market value ratios measure how stock price per share is related to either earnings per share or book value per share. The price/earnings ratio, or P/E, is shown in Fig. 2.23. This ratio, defined as the price per share of a stock divided by the earnings per share, is usually reported in stock quotations in newspapers such as the Wall Street Journal every day. However, you should be careful in looking at P/E ratios because a high ratio can be the result of low earnings. This seems to have been the case for Merck in 2007. Moreover, firms calculate earnings per share differently, making comparisons of P/E ratios between firms difficult or even misleading.

Fig. 2.22

Payout ratio for JNJ and MRK

Fig. 2.23

Price/earnings ratio for JNJ and MRK