Financial reporting and statement analysis report 2500 words
SADAUDV
Valuationtemplate.xlsxCopyright
| Coca-Cola: Residual Income Valuation Exercise & Coca-Cola: Residual Income Valuation Exercise (TN) |
| Harvard Business School Case 9-113-056 & 5-113-065 |
| Courseware 9-113-704 |
| This courseware was prepared solely as the basis for class discussion. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. Copyright © 2013 President and Fellows of Harvard College. No part of this product may be reproduced, stored in a retrieval system, used in a spreadsheet or transmitted in any form or by any means—electronic, mechanical, photocopying, recording or otherwise—without the permission of Harvard Business School. |
Copyright
&P
Template For Students
| Coca-Cola (ticker symbol KO on NYSE) | ||||||||||||||||||
| STANDARDIZED BALANCE SHEET AND INCOME STATEMENT (millions) | FORECAST ASSUMPTIONS | |||||||||||||||||
| Cost of Capital Parameters | Financial Projections | 2011 | 2012 | 2013 (Terminal) | ||||||||||||||
| BALANCE SHEET | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | |||||||||||||
| ASSETS | Market Risk Premium | 5.00% | Sales Growth Rate | 10.00% | 8.00% | 3.00% | ||||||||||||
| Cash and Marketable Securities | 2,590.00 | 4,308.00 | 4,979.00 | 9,409.00 | 11,511.00 | Risk Free Rate | 3.00% | NOPAT / Sales | 20.00% | 20.00% | 15.00% | |||||||
| + | Accounts Receivable | 2,587.00 | 3,317.00 | 3,090.00 | 3,758.00 | 4,430.00 | Tax Rate | 35.00% | Beg. Net Working Capital / Sales | 3.00% | 3.00% | |||||||
| + | Inventory | 1,641.00 | 2,220.00 | 2,187.00 | 2,354.00 | 2,650.00 | Cost of Debt | 4.50% | Beg. Net Long-Term Assets / Sales | 105.00% | 100.00% | |||||||
| + | Other Current Assets | 1,623.00 | 2,260.00 | 1,920.00 | 2,030.00 | 2,988.00 | Common Equity Beta | 0.60 | ||||||||||
| = | Total Current Assets | 8,441.00 | 12,105.00 | 12,176.00 | 17,551.00 | 21,579.00 | Net Debt / Book Value of Net Capital | 27.75% | 27.75% | 27.75% | ||||||||
| Preferred Equity / Book Value of Net Capital | 0.00% | 0.00% | 0.00% | |||||||||||||||
| Long-Term Tangible Assets | 9,436.00 | 11,102.00 | 9,976.00 | 11,441.00 | 16,672.00 | Shareholders' Equity / Book Value of Net Capital | 72.25% | 72.25% | 72.25% | |||||||||
| + | Long-Term Intangible Assets | 5,135.00 | 12,219.00 | 12,505.00 | 12,828.00 | 26,909.00 | ||||||||||||
| + | Other Long-Term Assets | 6,783.00 | 7,777.00 | 5,779.00 | 6,755.00 | 7,663.00 | PRO FORMAS & VALUATION | |||||||||||
| = | Total Long-Term Assets | 21,354.00 | 31,098.00 | 28,260.00 | 31,024.00 | 51,244.00 | HISTORICAL | FORECAST | TERMINAL | |||||||||
| TOTAL ASSETS | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | |||||
| PRO FORMA - BEGINNING BALANCE SHEET | ||||||||||||||||||
| LIABILITIES | ||||||||||||||||||
| Accounts Payable | 929.00 | 1,380.00 | 1,370.00 | 1,410.00 | 1,887.00 | Beginning Net Working Capital | 229.00 | 624.00 | 740.00 | 1,221.00 | 936.00 | 1,254.53 | ||||||
| + | Short-Term Debt | 3,268.00 | 6,052.00 | 6,531.00 | 6,800.00 | 9,376.00 | + | Beginning Net Long Term Assets | 18,683.00 | 26,141.00 | 24,065.00 | 26,028.00 | 41,973.00 | 43,908.48 | ||||
| + | Other Current Liabilities | 4,693.00 | 5,793.00 | 5,087.00 | 5,511.00 | 7,245.00 | = | Beginning Net Operating Assets | 18,912.00 | 26,765.00 | 24,805.00 | 27,249.00 | 42,909.00 | 45,163.01 | ||||
| = | Total Current Liabilities | 8,890.00 | 13,225.00 | 12,988.00 | 13,721.00 | 18,508.00 | ||||||||||||
| Net Debt | 1,992.00 | 5,021.00 | 4,333.00 | 2,450.00 | 11,906.00 | 12,531.42 | ||||||||||||
| Long Term Debt | 1,314.00 | 3,277.00 | 2,781.00 | 5,059.00 | 14,041.00 | + | Preferred Stock | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||||
| + | Deferred Taxes | 440.00 | 1,824.00 | 794.00 | 1,484.00 | 4,163.00 | + | Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | 32,631.58 | ||||
| + | Other Long-Term Liabilities | 2,231.00 | 3,133.00 | 3,401.00 | 2,965.00 | 4,794.00 | = | Net Capital | 18,912.00 | 26,765.00 | 24,805.00 | 27,249.00 | 42,909.00 | |||||
| = | Total Long-Term Liabilities | 3,985.00 | 8,234.00 | 6,976.00 | 9,508.00 | 22,998.00 | ||||||||||||
| TOTAL LIABILITIES | 12,875.00 | 21,459.00 | 19,964.00 | 23,229.00 | 41,506.00 | |||||||||||||
| Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 547.00 | 314.00 | |||||||||||||
| Sales Growth | ERROR:#DIV/0! | 10.70% | -2.94% | 13.53% | 10.00% | |||||||||||||
| SHAREHOLDERS' EQUITY | NOPAT / Sales | 21.29% | 18.43% | 22.23% | 34.23% | 20.00% | ||||||||||||
| Preferred Stock | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | Beg. Net Working Capital / Sales | 0.79% | 1.95% | 2.39% | 3.47% | 2.42% | |||||||
| + | Common Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | Beg. Net Operating Long Term Assets / Sales | 64.74% | 81.83% | 77.61% | 73.94% | 108.40% | ||||||
| = | Total Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | ||||||||||||
| TOTAL LIABILITIES & EQUITY | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | Net Debt / Book Value of Net Capital | 10.53% | 18.76% | 17.47% | 8.99% | 27.75% | |||||||
| Preferred Equity / Book Value of Net Capital | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |||||||||||||
| Common Shares Outstanding at Fiscal Year End | 2,318.00 | 2,318.00 | 2,312.00 | 2,303.00 | 2,292.00 | Shareholders' Equity / Book Value of Net Capital | 89.47% | 81.24% | 82.53% | 91.01% | 72.25% | |||||||
| PRO FORMA - INCOME STATEMENT | ||||||||||||||||||
| INCOME STATEMENT | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | Sales | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | 38,720.00 | 41,817.60 | ||||||
| NOPAT (Net Operating Profits After Tax) | 6,143.23 | 5,886.47 | 6,892.78 | 12,047.32 | 7,744.00 | |||||||||||||
| Sales | 24,088.00 | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | - | Net Interest Expenses After Tax | 162.23 | 79.47 | 68.78 | 238.32 | 348.25 | ||||||
| - | Cost of Goods Sold | 7,222.00 | 9,229.00 | 10,146.00 | 9,864.00 | 11,234.00 | = | Net Income | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | 7,395.75 | |||||
| = | Gross Profit | 16,866.00 | 19,628.00 | 21,798.00 | 21,142.00 | 23,966.00 | - | Preferred dividends | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||||
| - | SG&A | 9,431.00 | 10,945.00 | 11,774.00 | 11,381.00 | 13,179.00 | = | Net Income to Common | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | 7,395.75 | |||||
| - | Other Operating Expenses | 938.00 | 1,163.00 | 1,283.00 | 1,343.00 | 1,703.00 | ||||||||||||
| = | Operating Income | 6,497.00 | 7,520.00 | 8,741.00 | 8,418.00 | 9,084.00 | Book Value on Common Equity Growth Rate | |||||||||||
| Investment Income | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||||||||||||
| Other Income, net of Other Expense | 6.00 | -95.00 | -1,923.00 | -161.00 | 4,425.00 | RESIDUAL INCOME VALUATION | ||||||||||||
| Other Income | 195.00 | 173.00 | 53.00 | 92.00 | 5,312.00 | Cost of Common Equity | 5.15% | |||||||||||
| Other Expense | 189.00 | 268.00 | 1,976.00 | 253.00 | 887.00 | Net Income to Common | 7,744.00 | |||||||||||
| - | Net Interest Expense (Income) | 27.00 | 220.00 | 105.00 | 92.00 | 291.00 | - | Charge for Common Equity (Beg Book Value of Equity x Cost of Common Equity) | 2,208.43 | |||||||||
| Interest Income | 193.00 | 236.00 | 333.00 | 249.00 | 317.00 | = | Residual Income | 5,535.57 | ||||||||||
| Interest Expense | 220.00 | 456.00 | 438.00 | 341.00 | 608.00 | Present Value of Residual Income | 5,264.61 | |||||||||||
| - | Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 82.00 | 50.00 | Present Value of Terminal Abnormal Earnings | |||||||||||
| = | Pretax Income | 6,476.00 | 7,205.00 | 6,713.00 | 8,083.00 | 13,168.00 | ||||||||||||
| - | Tax Expense | 1,498.00 | 1,892.00 | 1,632.00 | 2,040.00 | 2,384.00 | Beg. Book Value of Equity | 31,003.00 | ||||||||||
| + | Unusual Gains, Net of Unusual Losses | 102.00 | 668.00 | 726.00 | 781.00 | 1,025.00 | + | PV of Residual Income (forecasted years 1-2) | 5,264.61 | |||||||||
| = | Net Income | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | + | PV of Residual Income (terminal value, beginning year 3) | 0.00 | |||||||||
| - | Preferred Dividends | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | = | Estimated Value of Equity | 36,267.61 | |||||||||
| = | Net Income to Common | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | ||||||||||||
| = | Number of Shares Outstanding (MM) | 2,292.00 | ||||||||||||||||
| NOTE A: "Minority interest" is now called "non-controlling interest." | Estimated Value Per Share | 15.82 | ||||||||||||||||
Original
| Coca-Cola (ticker symbol KO on NYSE) | ||||||||||||||||||
| STANDARDIZED BALANCE SHEET AND INCOME STATEMENT (millions) | FORECAST ASSUMPTIONS | |||||||||||||||||
| Cost of Capital Parameters | Financial Projections | 2011 | 2012 | 2013 (Terminal) | ||||||||||||||
| BALANCE SHEET | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | |||||||||||||
| ASSETS | Market Risk Premium | 5.00% | Sales Growth Rate | 10.00% | 8.00% | 3.00% | ||||||||||||
| Cash and Marketable Securities | 2,590.00 | 4,308.00 | 4,979.00 | 9,409.00 | 11,511.00 | Risk Free Rate | 3.00% | NOPAT / Sales | 20.00% | 20.00% | 15.00% | |||||||
| + | Accounts Receivable | 2,587.00 | 3,317.00 | 3,090.00 | 3,758.00 | 4,430.00 | Tax Rate | 35.00% | Beg. Net Working Capital / Sales | 3.00% | 3.00% | |||||||
| + | Inventory | 1,641.00 | 2,220.00 | 2,187.00 | 2,354.00 | 2,650.00 | Cost of Debt | 4.50% | Beg. Net Long-Term Assets / Sales | 105.00% | 100.00% | |||||||
| + | Other Current Assets | 1,623.00 | 2,260.00 | 1,920.00 | 2,030.00 | 2,988.00 | Common Equity Beta | 0.60 | ||||||||||
| = | Total Current Assets | 8,441.00 | 12,105.00 | 12,176.00 | 17,551.00 | 21,579.00 | Net Debt / Book Value of Net Capital | 27.75% | 27.75% | 27.75% | ||||||||
| Preferred Equity / Book Value of Net Capital | 0.00% | 0.00% | 0.00% | |||||||||||||||
| Long-Term Tangible Assets | 9,436.00 | 11,102.00 | 9,976.00 | 11,441.00 | 16,672.00 | Shareholders' Equity / Book Value of Net Capital | 72.25% | 72.25% | 72.25% | |||||||||
| + | Long-Term Intangible Assets | 5,135.00 | 12,219.00 | 12,505.00 | 12,828.00 | 26,909.00 | ||||||||||||
| + | Other Long-Term Assets | 6,783.00 | 7,777.00 | 5,779.00 | 6,755.00 | 7,663.00 | PRO FORMAS & VALUATION | |||||||||||
| = | Total Long-Term Assets | 21,354.00 | 31,098.00 | 28,260.00 | 31,024.00 | 51,244.00 | HISTORICAL | FORECAST | TERMINAL | |||||||||
| TOTAL ASSETS | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | |||||
| PRO FORMA - BEGINNING BALANCE SHEET | ||||||||||||||||||
| LIABILITIES | ||||||||||||||||||
| Accounts Payable | 929.00 | 1,380.00 | 1,370.00 | 1,410.00 | 1,887.00 | Beginning Net Working Capital | 229.00 | 624.00 | 740.00 | 1,221.00 | 936.00 | 1,254.53 | 1,292.16 | 1,330.93 | ||||
| + | Short-Term Debt | 3,268.00 | 6,052.00 | 6,531.00 | 6,800.00 | 9,376.00 | + | Beginning Net Long Term Assets | 18,683.00 | 26,141.00 | 24,065.00 | 26,028.00 | 41,973.00 | 43,908.48 | 43,072.13 | 44,364.29 | ||
| + | Other Current Liabilities | 4,693.00 | 5,793.00 | 5,087.00 | 5,511.00 | 7,245.00 | = | Beginning Net Operating Assets | 18,912.00 | 26,765.00 | 24,805.00 | 27,249.00 | 42,909.00 | 45,163.01 | 44,364.29 | 45,695.22 | ||
| = | Total Current Liabilities | 8,890.00 | 13,225.00 | 12,988.00 | 13,721.00 | 18,508.00 | ||||||||||||
| Net Debt | 1,992.00 | 5,021.00 | 4,333.00 | 2,450.00 | 11,906.00 | 12,531.42 | 12,309.80 | 12,679.10 | ||||||||||
| Long Term Debt | 1,314.00 | 3,277.00 | 2,781.00 | 5,059.00 | 14,041.00 | + | Preferred Stock | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| + | Deferred Taxes | 440.00 | 1,824.00 | 794.00 | 1,484.00 | 4,163.00 | + | Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | 32,631.58 | 32,054.49 | 33,016.12 | ||
| + | Other Long-Term Liabilities | 2,231.00 | 3,133.00 | 3,401.00 | 2,965.00 | 4,794.00 | = | Net Capital | 18,912.00 | 26,765.00 | 24,805.00 | 27,249.00 | 42,909.00 | 45,163.01 | 44,364.29 | 45,695.22 | ||
| = | Total Long-Term Liabilities | 3,985.00 | 8,234.00 | 6,976.00 | 9,508.00 | 22,998.00 | ||||||||||||
| TOTAL LIABILITIES | 12,875.00 | 21,459.00 | 19,964.00 | 23,229.00 | 41,506.00 | IMPLIED DIVIDEND PAYOUT | 5,767.17 | |||||||||||
| IMPLIED DIVIDEND PAYOUT RATIO | ERROR:#DIV/0! | |||||||||||||||||
| Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 547.00 | 314.00 | |||||||||||||
| Sales Growth | ERROR:#DIV/0! | 10.70% | -2.94% | 13.53% | 10.00% | 8.00% | 3.00% | 3.00% | ||||||||||
| SHAREHOLDERS' EQUITY | NOPAT / Sales | 21.29% | 18.43% | 22.23% | 34.23% | 20.00% | 20.00% | 15.00% | 15.00% | |||||||||
| Preferred Stock | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | Beg. Net Working Capital / Sales | 0.79% | 1.95% | 2.39% | 3.47% | 2.42% | 3.00% | 3.00% | 3.00% | ||||
| + | Common Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | Beg. Net Operating Long Term Assets / Sales | 64.74% | 81.83% | 77.61% | 73.94% | 108.40% | 105.00% | 100.00% | 100.00% | |||
| = | Total Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | ||||||||||||
| TOTAL LIABILITIES & EQUITY | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | Net Debt / Book Value of Net Capital | 10.53% | 18.76% | 17.47% | 8.99% | 27.75% | 27.75% | 27.75% | 27.75% | ||||
| Preferred Equity / Book Value of Net Capital | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | ||||||||||
| Common Shares Outstanding at Fiscal Year End | 2,318.00 | 2,318.00 | 2,312.00 | 2,303.00 | 2,292.00 | Shareholders' Equity / Book Value of Net Capital | 89.47% | 81.24% | 82.53% | 91.01% | 72.25% | 72.25% | 72.25% | 72.25% | ||||
| PRO FORMA - INCOME STATEMENT | ||||||||||||||||||
| INCOME STATEMENT | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | Sales | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | 38,720.00 | 41,817.60 | 43,072.13 | 44,364.29 | ||||
| NOPAT (Net Operating Profits After Tax) | 6,143.23 | 5,886.47 | 6,892.78 | 12,047.32 | 7,744.00 | 8,363.52 | 6,460.82 | 6,654.64 | ||||||||||
| Sales | 24,088.00 | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | - | Net Interest Expenses After Tax | 162.23 | 79.47 | 68.78 | 238.32 | 348.25 | 366.54 | 360.06 | 370.86 | |||
| - | Cost of Goods Sold | 7,222.00 | 9,229.00 | 10,146.00 | 9,864.00 | 11,234.00 | = | Net Income | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | 7,395.75 | 7,996.98 | 6,100.76 | 6,283.78 | ||
| = | Gross Profit | 16,866.00 | 19,628.00 | 21,798.00 | 21,142.00 | 23,966.00 | - | Preferred dividends | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| - | SG&A | 9,431.00 | 10,945.00 | 11,774.00 | 11,381.00 | 13,179.00 | = | Net Income to Common | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | 7,395.75 | 7,996.98 | 6,100.76 | 6,283.78 | ||
| - | Other Operating Expenses | 938.00 | 1,163.00 | 1,283.00 | 1,343.00 | 1,703.00 | ROE | 0.35 | 0.27 | 0.33 | 0.48 | 0.24 | 0.25 | 0.19 | 0.19 | |||
| = | Operating Income | 6,497.00 | 7,520.00 | 8,741.00 | 8,418.00 | 9,084.00 | Book Value on Common Equity Growth Rate | 25.02% | 5.25% | -1.77% | 3.00% | |||||||
| Investment Income | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||||||||||||
| Other Income, net of Other Expense | 6.00 | -95.00 | -1,923.00 | -161.00 | 4,425.00 | |||||||||||||
| Other Income | 195.00 | 173.00 | 53.00 | 92.00 | 5,312.00 | |||||||||||||
| Other Expense | 189.00 | 268.00 | 1,976.00 | 253.00 | 887.00 | |||||||||||||
| - | Net Interest Expense (Income) | 27.00 | 220.00 | 105.00 | 92.00 | 291.00 | ||||||||||||
| Interest Income | 193.00 | 236.00 | 333.00 | 249.00 | 317.00 | |||||||||||||
| Interest Expense | 220.00 | 456.00 | 438.00 | 341.00 | 608.00 | |||||||||||||
| - | Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 82.00 | 50.00 | ||||||||||||
| = | Pretax Income | 6,476.00 | 7,205.00 | 6,713.00 | 8,083.00 | 13,168.00 | ||||||||||||
| - | Tax Expense | 1,498.00 | 1,892.00 | 1,632.00 | 2,040.00 | 2,384.00 | ||||||||||||
| + | Unusual Gains, Net of Unusual Losses | 102.00 | 668.00 | 726.00 | 781.00 | 1,025.00 | ||||||||||||
| = | Net Income | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | ||||||||||||
| - | Preferred Dividends | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||||||||
| = | Net Income to Common | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | ||||||||||||
| NOTE A: "Minority interest" is now called "non-controlling interest." | ||||||||||||||||||
AOP
| Coca-Cola (ticker symbol KO on NYSE) | |||||||||||||||||||
| REFORMULATED BALANCE SHEET AND INCOME STATEMENT (millions) | FORECAST ASSUMPTIONS | ||||||||||||||||||
| BALANCE SHEET | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | Cost of Capital Parameters | Financial Projections | 2011 | 2012 | 2013 (Terminal) | |||||||||
| Operating Working Capital | 229.00 | 624.00 | 740.00 | 1,221.00 | 936.00 | Market Risk Premium | 5.00% | Sales Growth Rate | 10.00% | 8.00% | 3.00% | ||||||||
| Net non-current operating assets | 18,683.00 | 26,141.00 | 24,065.00 | 26,575.00 | 42,287.00 | Risk Free Rate | 3.00% | NOPAT / Sales | 20.00% | 20.00% | 15.00% | ||||||||
| NET OPERATING ASSETS | 18,912.00 | 26,765.00 | 24,805.00 | 27,796.00 | 43,223.00 | Tax Rate | 35.00% | Beg. Net Working Capital / Sales | 3.00% | 3.00% | |||||||||
| NET DEBT | 1,992.00 | 5,021.00 | 4,333.00 | 2,450.00 | 11,906.00 | Cost of Debt | 4.50% | Beg. Net Long-Term Assets / Sales | 105.00% | 100.00% | |||||||||
| SHAREHOLDERS' EQUITY | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | Common Equity Beta | 0.60 | ||||||||||||
| NON-CONTROLLING INTEREST | 0.00 | 0.00 | 0.00 | 547.00 | 314.00 | WACC | 5.147% | Net Debt / Book Value of Net Capital | 27.75% | 27.75% | 27.75% | ||||||||
| Cost of Equity | 6.00% | Preferred Equity / Book Value of Net Capital | 0.00% | 0.00% | 0.00% | ||||||||||||||
| Common Shares Outstanding at Fiscal Year End | 2,318.00 | 2,318.00 | 2,312.00 | 2,303.00 | 2,292.00 | Cost of debt after tax | 2.93% | Shareholders' Equity / Book Value of Net Capital | 72.25% | 72.25% | 72.25% | ||||||||
| INCOME STATEMENT | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | PRO FORMAS & VALUATION | |||||||||||||
| Sales | 24,088.00 | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | HISTORICAL | FORECAST | TERMINAL | |||||||||||
| EBIT (operating profit) from sales (adjusted EBIT) | 6,497.00 | 7,520.00 | 8,741.00 | 8,418.00 | 9,084.00 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | ||||||
| EBIT including Other Income, net of Other Expense* | 6,503.00 | 7,425.00 | 6,818.00 | 8,257.00 | 13,509.00 | PRO FORMA - BEGINNING BALANCE SHEET | |||||||||||||
| NOPAT* | 5,100.75 | 6,143.23 | 5,886.47 | 6,974.78 | 12,097.32 | ||||||||||||||
| NET FINANCE COST AFTER TAX | 20.75 | 162.23 | 79.47 | 68.78 | 238.32 | Beginning Net Working Capital | 229.00 | 624.00 | 740.00 | 1,221.00 | 936.00 | 1,254.53 | 1,292.16 | 1,330.93 | |||||
| NON-CONTROLLING INTEREST | 0.00 | 0.00 | 0.00 | 82.00 | 50.00 | + | Beginning Net Long Term Assets | 18,683.00 | 26,141.00 | 24,065.00 | 26,575.00 | 42,287.00 | 43,908.48 | 43,072.13 | 44,364.29 | ||||
| NET INCOME | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | = | Beginning Net Operating Assets | 18,912.00 | 26,765.00 | 24,805.00 | 27,796.00 | 43,223.00 | 45,163.01 | 44,364.29 | 45,695.22 | ||||
| Adj. EBIT margin | 26.97% | 26.06% | 27.36% | 27.15% | 25.81% | ||||||||||||||
| Adj. EBIT/NOA | 0.34 | 0.28 | 0.35 | 0.30 | 0.21 | ||||||||||||||
| Sales Growth | 19.80% | 10.70% | -2.94% | 13.53% | 10.00% | 8.00% | 3.00% | 3.00% | |||||||||||
| Cost of sales / sales | 29.98% | 31.98% | 31.76% | 31.81% | 31.91% | NOPAT / Sales | 21.29% | 18.43% | 22.49% | 34.37% | 20.00% | 20.00% | 10.00% | 10.00% | |||||
| SG&A /Sales | 39.15% | 37.93% | 36.86% | 36.71% | 37.44% | Beg. Net Working Capital / Sales | 0.79% | 1.95% | 2.39% | 3.47% | 2.42% | 3.00% | 3.00% | 3.00% | |||||
| Other operating expense / sales | 3.89% | 4.03% | 4.02% | 4.33% | 4.84% | Beg. Net Operating Long Term Assets / Sales | 64.74% | 81.83% | 77.61% | 75.50% | 109.21% | 105.00% | 100.00% | 100.00% | |||||
| Total expenses/sales | 73.03% | 73.94% | 72.64% | 72.85% | 74.19% | ||||||||||||||
| PRO FORMA - INCOME STATEMENT | |||||||||||||||||||
| STANDARDIZED BALANCE SHEET AND INCOME STATEMENT (millions) | Sales | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | 38,720.00 | 41,817.60 | 43,072.13 | 44,364.29 | ||||||||||
| NOPAT (Net Operating Profits After Tax) | 6,143.23 | 5,886.47 | 6,974.78 | 12,097.32 | 7,744.00 | 8,363.52 | 6,460.82 | 6,654.64 | |||||||||||
| BALANCE SHEET | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | RNOA | 0.32 | 0.22 | 0.28 | 0.44 | 0.18 | 0.19 | 0.15 | 0.15 | |||||
| + | ASSETS | RESIDUAL OPERATING INCOME VALUATION | |||||||||||||||||
| + | Cash and Marketable Securities | 2,590.00 | 4,308.00 | 4,979.00 | 9,409.00 | 11,511.00 | WACC | 5.15% | 5.15% | 5.15% | 5.15% | ||||||||
| + | Accounts Receivable | 2,587.00 | 3,317.00 | 3,090.00 | 3,758.00 | 4,430.00 | NOPAT | 7,744.00 | 8,363.52 | 6460.82 | 6654.64 | ||||||||
| = | Inventory | 1,641.00 | 2,220.00 | 2,187.00 | 2,354.00 | 2,650.00 | - | Charge for Net operating assets (Beg Book Value of net operating assets x Cost of capital) | 2,224.59 | 2,324.44 | 2,283.33 | 2,351.83 | |||||||
| Other Current Assets | 1,623.00 | 2,260.00 | 1,920.00 | 2,030.00 | 2,988.00 | = | Residual Operating Income (AOP) | 5,519.41 | 6,039.08 | 4,177.49 | 4,302.81 | ||||||||
| Total Current Assets | 8,441.00 | 12,105.00 | 12,176.00 | 17,551.00 | 21,579.00 | Present Value of Residual operating Income | 5,249.24 | 5,462.34 | |||||||||||
| + | Present Value of Terminal Abnormal Operating Earnings | 3,593.58 | 200,431.32 | ||||||||||||||||
| + | Long-Term Tangible Assets | 9,436.00 | 11,102.00 | 9,976.00 | 11,441.00 | 16,672.00 | FCF | 5,803.99 | 9,162.24 | 5,129.8904448 | 5,283.7871581 | 1.03 | |||||||
| = | Long-Term Intangible Assets | 5,135.00 | 12,219.00 | 12,505.00 | 12,828.00 | 26,909.00 | Beg. Book Value of net operating assets | 43,223.00 | |||||||||||
| Other Long-Term Assets | 6,783.00 | 7,777.00 | 5,779.00 | 6,755.00 | 7,663.00 | + | PV of Residual operating Income (forecasted years 1-2) | 14,305.16 | |||||||||||
| Total Long-Term Assets | 21,354.00 | 31,098.00 | 28,260.00 | 31,024.00 | 51,244.00 | + | PV of Residual operating Income (terminal value, beginning year 3) | 172,416.05 | |||||||||||
| TOTAL ASSETS | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | = | Estimated Enterprise Value | 229,944.22 | |||||||||||
| + | LIABILITIES | ||||||||||||||||||
| + | Accounts Payable | 929.00 | 1,380.00 | 1,370.00 | 1,410.00 | 1,887.00 | = | Number of Shares Outstanding (MM) | 2,292.00 | ||||||||||
| = | Short-Term Debt | 3,268.00 | 6,052.00 | 6,531.00 | 6,800.00 | 9,376.00 | Estimated Enterprise Value Per Share | 100.32 | |||||||||||
| Other Current Liabilities | 4,693.00 | 5,793.00 | 5,087.00 | 5,511.00 | 7,245.00 | ||||||||||||||
| Total Current Liabilities | 8,890.00 | 13,225.00 | 12,988.00 | 13,721.00 | 18,508.00 | PV of FCF | 5,519.90 | 8,287.23 | 4,412.86 | 211,724.23 | |||||||||
| + | = | Estimated Enterprise Value | 229,944.22 | ||||||||||||||||
| + | Long Term Debt | 1,314.00 | 3,277.00 | 2,781.00 | 5,059.00 | 14,041.00 | |||||||||||||
| = | Deferred Taxes | 440.00 | 1,824.00 | 794.00 | 1,484.00 | 4,163.00 | |||||||||||||
| Other Long-Term Liabilities | 2,231.00 | 3,133.00 | 3,401.00 | 2,965.00 | 4,794.00 | ||||||||||||||
| Total Long-Term Liabilities | 3,985.00 | 8,234.00 | 6,976.00 | 9,508.00 | 22,998.00 | ||||||||||||||
| TOTAL LIABILITIES | 12,875.00 | 21,459.00 | 19,964.00 | 23,229.00 | 41,506.00 | ||||||||||||||
| Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 547.00 | 314.00 | ||||||||||||||
| + | SHAREHOLDERS' EQUITY | ||||||||||||||||||
| = | Preferred Stock | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||||||||||||
| Common Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | ||||||||||||||
| Total Shareholder's Equity | 16,920.00 | 21,744.00 | 20,472.00 | 24,799.00 | 31,003.00 | ||||||||||||||
| TOTAL LIABILITIES & EQUITY | 29,795.00 | 43,203.00 | 40,436.00 | 48,575.00 | 72,823.00 | ||||||||||||||
| Common Shares Outstanding at Fiscal Year End | 2,318.00 | 2,318.00 | 2,312.00 | 2,303.00 | 2,292.00 | ||||||||||||||
| INCOME STATEMENT | 12/31/06 | 12/31/07 | 12/31/08 | 12/31/09 | 12/31/10 | ||||||||||||||
| - | |||||||||||||||||||
| = | Sales | 24,088.00 | 28,857.00 | 31,944.00 | 31,006.00 | 35,200.00 | |||||||||||||
| - | Cost of Goods Sold | 7,222.00 | 9,229.00 | 10,146.00 | 9,864.00 | 11,234.00 | |||||||||||||
| - | Gross Profit | 16,866.00 | 19,628.00 | 21,798.00 | 21,142.00 | 23,966.00 | |||||||||||||
| = | SG&A | 9,431.00 | 10,945.00 | 11,774.00 | 11,381.00 | 13,179.00 | |||||||||||||
| Other Operating Expenses | 938.00 | 1,163.00 | 1,283.00 | 1,343.00 | 1,703.00 | ||||||||||||||
| Operating Income | 6,497.00 | 7,520.00 | 8,741.00 | 8,418.00 | 9,084.00 | ||||||||||||||
| Investment Income | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||||||||||
| Other Income, net of Other Expense | 6.00 | -95.00 | -1,923.00 | -161.00 | 4,425.00 | ||||||||||||||
| - | Other Income | 195.00 | 173.00 | 53.00 | 92.00 | 5,312.00 | |||||||||||||
| Other Expense | 189.00 | 268.00 | 1,976.00 | 253.00 | 887.00 | ||||||||||||||
| Net Interest Expense (Income) | 27.00 | 220.00 | 105.00 | 92.00 | 291.00 | ||||||||||||||
| - | Interest Income | 193.00 | 236.00 | 333.00 | 249.00 | 317.00 | |||||||||||||
| = | Interest Expense | 220.00 | 456.00 | 438.00 | 341.00 | 608.00 | |||||||||||||
| - | Minority Interest (NOTE A) | 0.00 | 0.00 | 0.00 | 82.00 | 50.00 | |||||||||||||
| + | Pretax Income | 6,476.00 | 7,205.00 | 6,713.00 | 8,083.00 | 13,168.00 | |||||||||||||
| = | Tax Expense | 1,498.00 | 1,892.00 | 1,632.00 | 2,040.00 | 2,384.00 | |||||||||||||
| - | Unusual Gains, Net of Unusual Losses | 102.00 | 668.00 | 726.00 | 781.00 | 1,025.00 | |||||||||||||
| = | Net Income | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | |||||||||||||
| Preferred Dividends | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||||||||||
| Net Income to Common | 5,080.00 | 5,981.00 | 5,807.00 | 6,824.00 | 11,809.00 | ||||||||||||||
| 5,080.00 | |||||||||||||||||||
| NOTE A: "Minority interest" is now called "non-controlling interest." | |||||||||||||||||||
| Tax on EBIT | 1,504.25 | 1,949.77 | 1,657.53 | 2,063.22 | 2,436.68 |
