Financial Evaluation Assignment
mcyrayvahgr
cf_Financial_Analysis_Toolkit1.xlsxC3 Took Kit
| THE HOME DEPOT | ||||||
| Tool Kit for Analysis of Financial Statements | ||||||
| Financial statements are analyzed by calculating certain key ratios and then comparing them with the ratios of other firms and by examining the trends in ratios over time. We can also combine ratios to make the analysis more revealing, those indicated below are exceptionally useful for this type of analysis. | ||||||
| RATIO ANALYSIS (Section 3.1) | ||||||
| Input Data: | ||||||
| 2017 | 2016 | |||||
| Year-end common stock price | $23.00 | $26.00 | ||||
| Year-end shares outstanding (in millions) | 50 | 50 | ||||
| Tax rate | 40% | 40% | ||||
| After-tax cost of capital | 11.0% | 10.8% | ||||
| Lease payments | $28 | $28 | ||||
| Required sinking fund payments | $20 | $20 | ||||
| Balance Sheets | ||||||
| (in millions of dollars) | ||||||
| Assets | 2017 | 2016 | ||||
| Cash and equivalents | $3,595 | $2,538 | ||||
| Short-term investments | $0 | $0 | ||||
| Accounts receivable | $1,952 | $2,029 | ||||
| Inventories | $12,748 | $12,549 | ||||
| Other current assets | $638 | $608 | ||||
| Total current assets | $18,933 | $17,724 | ||||
| Goodwill | $22,075 | $21,914 | ||||
| Other assets | $1,246 | $1,235 | ||||
| Net plant and equipment | $2,275 | $2,093 | ||||
| Total assets | $44,529 | $42,966 | ||||
| Liabilities and equity | ||||||
| Accounts payable | $7,244 | $7,000 | ||||
| Short-term debt | $1,559 | $710 | ||||
| Sales taxes payable | $520 | $508 | ||||
| Deferred revenue | $1,805 | $1,669 | ||||
| Income tax payable | $54 | $25 | ||||
| Current Installments of long-term debt | $1,202 | $542 | ||||
| Other accrued expenses | $2,170 | $2,195 | ||||
| Accruals | $1,640 | $1,484 | ||||
| Total current liabilities | $16,194 | $14,133 | ||||
| Long-term debt, excluding current installments | $24,267 | $22,349 | ||||
| Deferred income taxes | $440 | $296 | ||||
| Long-term bonds | $2,174 | $1,855 | ||||
| Total liabilities | $43,075 | $38,633 | ||||
| Common stock (400,000 shares) | $89 | $88 | ||||
| Paid-in capital | $10,192 | $9,787 | ||||
| Retained earnings | $39,935 | $35,519 | ||||
| Treasury stock | -$48,196 | -$40,194 | ||||
| Accumulated other comprehensive loss | -$566 | -$867 | ||||
| Total common equity | $1,454 | $4,245 | ||||
| Total liabilities and equity | $44,529 | $42,966 | ||||
| Income Statements | ||||||
| (in millions of dollars) | ||||||
| 2017 | 2016 | |||||
| Net sales | $100,904.0 | $94,595.0 | ||||
| Operating costs | $66,548.0 | $62,282.0 | ||||
| Earnings before interest, taxes, depr. & amort. (EBITDA) | $34,356.0 | $32,313.0 | ||||
| Selling, general and adminsitrative | $17,864.0 | $17,132.0 | ||||
| Depreciation | $1,811.0 | $1,754.0 | ||||
| Amortization | $0.0 | $0.0 | ||||
| Depreciation and amortization | $19,675.0 | $18,886.0 | ||||
| Earnings before interest and taxes (EBIT) | $14,681.0 | $13,427.0 | ||||
| Less interest | $983.0 | $936.0 | ||||
| Earnings before taxes (EBT) | $13,698.0 | $12,491.0 | ||||
| Provision for income taxes | $5,068.0 | $4,534.0 | ||||
| Net income before preferred dividends | $8,630.0 | $7,957.0 | ||||
| Preferred dividends | $0.0 | $0.0 | ||||
| Net income available to common stockholders | $8,630.0 | $7,957.0 | ||||
| Common dividends | $0.0 | $0.0 | ||||
| Addition to retained earnings | $8,630.0 | $7,957.0 | ||||
| Calculated Data: Operating Performance and Cash Flows | ||||||
| 2017 | 2016 | |||||
| Net operating working capital (NOWC) | $9,411.0 | $8,632.0 | ||||
| Total operating capital | $11,686.0 | $10,725.0 | ||||
| Net Operating Profit After Taxes (NOPAT) | $8,808.6 | $8,056.2 | ||||
| Net Cash Flow (Net income + Depreciation) | $28,305.0 | $26,843.0 | ||||
| Operating Cash Flow (OCF) | $28,483.6 | $26,942.2 | ||||
| Free Cash Flow (FCF) | $7,847.6 | N/A | ||||
| Calculated Data: Per-share Information | ||||||
| 2017 | 2016 | |||||
| Earnings per share (EPS) | $172.60 | $159.14 | ||||
| Dividends per share (DPS) | $0.00 | $0.00 | ||||
| Book value per share (BVPS) | $29.08 | $84.90 | ||||
| Cash flow per share (CFPS) | $566.10 | $536.86 | ||||
| Free cash flow per share (FCFPS) | $156.95 | N/A | ||||
| LIQUIDITY RATIOS (Section 3.2) | Industry | |||||
| 2017 | 2016 | Average | ||||
| Liquidity ratios | ||||||
| Current Ratio | 1.17 | 1.25 | 0.16 | |||
| Quick Ratio | 0.38 | 0.37 | 0.21 | |||
| ASSET MANAGEMENT RATIOS (Section 3.3) | Industry | |||||
| 2017 | 2016 | Average | ||||
| Asset Management ratios | ||||||
| Inventory Turnover | 7.92 | 7.54 | 9 | |||
| Days Sales Outstanding | 7.1 | 7.83 Christopher Buzzard: To calculate the DSO ratio, a 365-day accounting year was used. | 36 | |||
| Fixed Asset Turnover | 44.35 | 45.20 | 3 | |||
| Total Asset Turnover | 2.27 | 2.20 | 1.8 | |||
| DEBT MANAGEMENT RATIOS (Section 3.4) | Industry | |||||
| 2017 | 2016 | Average | ||||
| Debt Management ratios | ||||||
| Debt Ratio | 96.73% | 89.92% | 40.00% | |||
| Debt-to-Equity Ratio | 29.63 | 8.92 | 0.67 | |||
| Market Debt Ratio | 97.40% | 96.74% | N/A | |||
| Times Interest Earned | 14.93 | 14.35 | 6 | |||
| EBITDA Coverage Ratio | 33.35 Brigham: (EBITDA + Lease Payments) / (Interest + Loan Payments + Lease Payments) | 32.87 | 8 | |||
| PROFITABILITY RATIOS (Section 3.5) | Industry | |||||
| 2017 | 2016 | Average | ||||
| Profitability ratios | ||||||
| Profit Margin | 8.55% | 8.41% | 5.00% | |||
| Basic Earning Power | 32.97% | 31.25% | 17.20% | |||
| Return on Assets | 19.38% | 18.52% | 9.00% | |||
| Return on Equity | 593.54% | 187.44% | 15.00% | |||
| MARKET VALUE RATIOS (Section 3.6) | Industry | |||||
| 2017 | 2016 | Average | ||||
| Market Value ratios | ||||||
| Price-to Earnings Ratio | 0.13 | 0.16 | 12.5 | |||
| Price-to-Cash Flow Ratio | 0.04 | 0.05 Christopher Buzzard: P/CF ratio is calculated by dividing the price by the net cash flow per share. |
Brigham: (EBITDA + Lease Payments) / (Interest + Loan Payments + Lease Payments) |
Christopher Buzzard: To calculate the DSO ratio, a 365-day accounting year was used. | 6.8 | |
| Price-to-EBITDA | 0.03 | 0.04 | 4.6 | |||
| Market-to-Book Ratio | 0.79 | 0.31 | 1.7 | |||
| TREND ANALYSIS, COMMON SIZE ANALYSIS, AND PERCENT CHANGE ANALYSIS (Section 3.7) | ||||||
| TREND ANALYSIS | ||||||
| Trend analysis allows you to see how a firm's results are changing over time. For instance, a firm's ROE may be slightly below the benchmark, but if it has been steadily rising over the past four years, that should be seen as a good sign. | ||||||
| A trend analysis and graph have been constructed on this data regarding MicroDrive's ROE over the past 5 years. (MicroDrive and indusry average data for earlier years has been provided.) | ||||||
| ROE | ||||||
| THE HOME DEPOT | INDUSTRY | |||||
| 2013 | 14.0% | 13.2% | ||||
| 2014 | 16.1% | 15.0% | ||||
| 2015 | 14.8% | 16.0% | ||||
| 2016 | 187.4% | 16.2% | ||||
| 2017 | 593.5% | 15.0% | ||||
| Figure 3-1 Rate of Return on Common Equity | ||||||
| COMMON SIZE ANALYSIS | ||||||
| In common size income statements, all items for a year are divided by the sales for that year. | ||||||
| Figure 3-2 Common Size Income Statements | ||||||
| Industry Composite | THE HOME DEPOT | |||||
| 2017 | 2017 | 2016 | ||||
| Net sales | 100.0% | 100.0% | 100.0% | |||
| Operating costs | 87.6% | 66.0% | 65.8% | |||
| Earnings before interest, taxes, depr. & amort. (EBITDA) | 12.4% | 34.0% | 34.2% | |||
| Depreciation and amortization | 2.8% | 19.5% | 20.0% | |||
| Earnings before interest and taxes (EBIT) | 9.6% | 14.5% | 14.2% | |||
| Less interest | 1.3% | 1.0% | 1.0% | |||
| Earnings before taxes (EBT) | 8.3% | 13.6% | 13.2% | |||
| Provision for income taxes | 3.3% | 5.0% | 4.8% | |||
| Net income before preferred dividends | 5.0% | 8.6% | 8.4% | |||
| Preferred dividends | 0.0% | 0.0% | 0.0% | |||
| Net income available to common stockholders (profit margin) | 5.0% | 8.6% | 8.4% | |||
| In common sheets, all items for a year are divided by the total assets for that year. | ||||||
| Figure 3-3 Common Size Balance Sheets | ||||||
| Industry Composite | THE HOME DEPOT | |||||
| 2017 | 2017 | 2016 | ||||
| Assets | ||||||
| Cash and equivalents | 1.0% | 8.1% | 5.9% | |||
| Short-term investments | 2.2% | 0.0% | 0.0% | |||
| Accounts receivable | 17.8% | 4.4% | 4.7% | |||
| Inventories | 19.8% | 28.6% | 29.2% | |||
| Total current assets | 40.8% | 42.5% | 41.3% | |||
| Net plant and equipment | 59.2% | 5.1% | 4.9% | |||
| Total assets | 100.0% | 100.0% | 100.0% | |||
| Liabilities and equity | ||||||
| Accounts payable | 1.8% | 16.3% | 16.3% | |||
| Short-term debt | 4.4% | 3.5% | 1.7% | |||
| Accruals | 3.6% | 3.7% | 3.5% | |||
| Total current liabilities | 9.8% | 36.4% | 32.9% | |||
| Long-term bonds | 30.2% | 4.9% | 4.3% | |||
| Total liabilities | 40.0% | 96.7% | 89.9% | |||
| Preferred stock | 0.0% | 0.2% | 0.2% | |||
| Total common equity | 60.0% | 3.3% | 9.9% | |||
| Total liabilities and equity | 100.0% | 100.0% | 100.0% | |||
| PERCENT CHANGE ANALYSIS | ||||||
| In percent change analysis, all items are divided by the that item's value in the beginning, or base, year. | ||||||
| Figure 3-4 Income Statement Percent Change Analysis | ||||||
| Base year = | 2016 | Percent Change in | ||||
| 2017 | ||||||
| Net sales | 6.7% | |||||
| Operating costs | 6.8% | |||||
| Earnings before interest, taxes, depr. & amort. (EBITDA) | 6.3% | |||||
| Depreciation and amortization | 4.2% | |||||
| Earnings before interest and taxes (EBIT) | 9.3% | |||||
| Less interest | 5.0% | |||||
| Earnings before taxes (EBT) | 9.7% | |||||
| Provision for income taxes | 11.8% | |||||
| Net income before preferred dividends | 8.5% | |||||
| Preferred dividends | 0.0% | |||||
| Net income available to common stockholders | 8.5% | |||||
| Balance Sheet Percent Change Analysis (not in textbook) | ||||||
| Base year = | 2016 | Percent Change in | ||||
| 2017 | ||||||
| Assets | ||||||
| Cash and equivalents | 41.6% | |||||
| Short-term investments | ERROR:#DIV/0! | |||||
| Accounts receivable | -3.8% | |||||
| Inventories | 1.6% | |||||
| Total current assets | 6.8% | |||||
| Net plant and equipment | 8.7% | |||||
| Total assets | 3.6% | |||||
| Liabilities and equity | ||||||
| Accounts payable | 3.5% | |||||
| Short-term debt | 119.6% | |||||
| Accruals | 10.5% | |||||
| Total current liabilities | 14.6% | |||||
| Long-term bonds | 17.2% | |||||
| Total liabilities | 11.5% | |||||
| Common stock (400,000 shares) | 1.1% | |||||
| Paid-in capital | 4.1% | |||||
| Retained earnings | 12.4% | |||||
| Total common equity | -65.7% | |||||
| Total liabilities and equity | 3.6% | |||||
| DU PONT ANALYSIS (Section 3.8) | ||||||
| ROE = | (Profit margin) | (TA turnover) | (Equity Multiplier) | |||
| THE HOME DEPOT | 2017 | 593.54% | 8.55% | 2.27 | 30.63 | |
| THE HOME DEPOT | 2016 | 187.44% | 8.41% | 2.20 | 10.12 | |
| Industry Average | 15.00% | 5.00% | 1.80 | 1.67 | ||
2013 2014 2015 2016 2017 0.14000000000000001 0.161 0.14800000000000002 1.8744405182567727 5.9353507565337003 INDUSTRY
2013 2014 2015 2016 2017 0.13200000000000001 0.15 0.16 0.16200000000000001 0.15
ROE (%)
Sheet1
| -74 |
| 1057 |
| 983 |
