ACCT group project
estelle0112
IllinoisToolsQ1-Q2-Q3Downloadtoexceltoseeformulas.xlsxData
| Illinois Tool Works | |||
| Consolidated Statement of Income | |||
| ($ in millions) | Year 3 | Year 2 | Year1 |
| Sales | 9468 | 9293 | 9984 |
| Cost of goods sold | 5936 | 5910 | 6192 |
| Gross Profit | 3532 | 3383 | 3792 |
| Selling and administrative expenses | 1720 | 1691 | 1814 |
| Depreciation and amoritization | 306 | 385 | 414 |
| Operating Profit | 1506 | 1307 | 1564 |
| Interest Expense | 68 | 65 | 72 |
| Nonoperating Expense | 4 | 7 | 13 |
| Unusual Losses | 337 | 0 | 0 |
| Pre-tac income | 1097 | 1235 | 1479 |
| Income taxes | 384 | 429 | 521 |
| Net Income | 713 | 806 | 958 |
| Illinois Tool Works | |||
| Consolidated Statement of Shareholders Equity | |||
| ($ in millions) | Year 3 | Year 2 | Year1 |
| Balance at beginning of year | 9823 | 9604 | 9061 |
| Net income (loss) | 713 | 806 | 958 |
| Stock issued (repurchased) | 361 | -337 | -192 |
| Common Stock dividends | -273 | -250 | -223 |
| Balance at end of year | 10624 | 9823 | 9604 |
| 0 |
Q1 & Q2
| Abnormal Earning Valuation | ||||||||||
| Forcasted Results | ||||||||||
| Cost of Capital | 9% | |||||||||
| Intitial Book Value 12/31 Year 3 | $ 10,624.00 | |||||||||
| Dividend Payout | 25% | |||||||||
| Forecasted ROCE on beginning equity | 9.50% | |||||||||
| 1. Year 4 | 2. Year 5 | 3. Year 6 | 4. Year 7 | 5. Year 8 | 6. Year 9 | 7. Year 10 | 8. Year 11 | 9. Year 12 | 10. Year 13 | |
| (a) Earnings Forecasts | $ 1,009.28 | $ 1,081.19 | $ 1,158.23 | $ 1,240.75 | $ 1,329.15 | $ 1,423.86 | $ 1,525.30 | $ 1,633.98 | $ 1,750.40 | $ 1,875.12 |
| (b) Equity Book Value-Forecasts (per share) | ||||||||||
| Equity book value at beginning | $ 10,624.00 | $ 11,380.96 | $ 12,191.85 | $ 13,060.52 | $ 13,991.09 | $ 14,987.95 | $ 16,055.84 | $ 17,199.82 | $ 18,425.31 | $ 19,738.11 |
| Earnings Forecasts | $ 1,009.28 | $ 1,081.19 | $ 1,158.23 | $ 1,240.75 | $ 1,329.15 | $ 1,423.86 | $ 1,525.30 | $ 1,633.98 | $ 1,750.40 | $ 1,875.12 |
| Stock Issued | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
| Other Comprehensive Income | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | |||
| Dividend | $ (252.32) | $ (270.30) | $ (289.56) | $ (310.19) | $ (332.29) | $ (355.96) | $ (381.33) | $ (408.50) | $ (437.60) | $ (468.78) |
| Equity book value at year end | $ 11,380.96 | $ 12,191.85 | $ 13,060.52 | $ 13,991.09 | $ 14,987.95 | $ 16,055.84 | $ 17,199.82 | $ 18,425.31 | $ 19,738.11 | $ 21,144.45 |
| ROE (Return on Equity) | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% | 9.50% |
| (c) Abnormal Earnings | ||||||||||
| Equity book value at beginning | $ 10,624.00 | $ 11,380.96 | $ 12,191.85 | $ 13,060.52 | $ 13,991.09 | $ 14,987.95 | $ 16,055.84 | $ 17,199.82 | $ 18,425.31 | $ 19,738.11 |
| Equity cost pf capital | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% |
| Normal Earnings | $ 956.16 | $ 1,024.29 | $ 1,097.27 | $ 1,175.45 | $ 1,259.20 | $ 1,348.92 | $ 1,445.03 | $ 1,547.98 | $ 1,658.28 | $ 1,776.43 |
| Actual or Forecasted Earnings | $ 1,009.28 | $ 1,081.19 | $ 1,158.23 | $ 1,240.75 | $ 1,329.15 | $ 1,423.86 | $ 1,525.30 | $ 1,633.98 | $ 1,750.40 | $ 1,875.12 |
| Abnormal Earnings | $ 53.12 | $ 56.90 | $ 60.96 | $ 65.30 | $ 69.96 | $ 74.94 | $ 80.28 | $ 86.00 | $ 92.13 | $ 98.69 |
| Discount Factor (at 9%) | 91.74% | 84.17% | 77.22% | 70.84% | 64.99% | 59.63% | 54.70% | 50.19% | 46.04% | 42.24% |
| Abnormal Earnings Discounted at present | $ 48.73 | $ 47.90 | $ 47.07 | $ 46.26 | $ 45.47 | $ 44.68 | $ 43.92 | $ 43.16 | $ 42.42 | $ 41.69 |
| (c) Estimated share value | ||||||||||
| Sum of discounted abnormal earnings over horizon | $ 451.29 | |||||||||
| Current equity book value | $ 10,624.00 | |||||||||
| Estimated current value of Equity at Year 3 | $ 11,075.29 | Million | Q1 | |||||||
| Number of shares (Millions) | 307 | |||||||||
| Predicted Share Vale | $ 36.08 | Million | Q2 | |||||||
| Actural High | $ 64.00 | |||||||||
| Actual Low | $ 56.00 | |||||||||
| Illinois Tool Works | ||||||||||
| Consolidated Statement of Income | ||||||||||
| ($ in millions) | Year 3 | Year 2 | Year1 | |||||||
| Sales | 9468 | 9293 | 9984 | |||||||
| Cost of goods sold | 5936 | 5910 | 6192 | |||||||
| Gross Profit | 3532 | 3383 | 3792 | |||||||
| Selling and administrative expenses | 1720 | 1691 | 1814 | |||||||
| Depreciation and amoritization | 306 | 385 | 414 | |||||||
| Operating Profit | 1506 | 1307 | 1564 | |||||||
| Interest Expense | 68 | 65 | 72 | |||||||
| Nonoperating Expense | 4 | 7 | 13 | |||||||
| Unusual Losses | 337 | 0 | 0 | |||||||
| Pre-tax income | 1097 | 1235 | 1479 | |||||||
| Income taxes | 384 | 429 | 521 | |||||||
| Net Income | 713 | 806 | 958 | |||||||
| Illinois Tool Works | ||||||||||
| Consolidated Statement of Shareholders Equity | ||||||||||
| ($ in millions) | Year 3 | Year 2 | Year1 | |||||||
| Balance at beginning of year | 9823 | 9604 | 9061 | |||||||
| Net income (loss) | 713 | 806 | 958 | |||||||
| Stock issued (repurchased) | 361 | -337 | -192 | |||||||
| Common Stock dividends | -273 | -250 | -223 | |||||||
| Balance at end of year | 10624 | 9823 | 9604 |
Q3
| Abnormal Earning Valuation | ||||||||||
| Forcasted Results | ||||||||||
| Cost of Capital | 9% | |||||||||
| Intitial Book Value 12/31 Year 3 | $ 10,624.00 | |||||||||
| Dividend Payout | 25% | |||||||||
| Forecasted ROCE on beginning equity | 16.00% | |||||||||
| 1. Year 4 | 2. Year 5 | 3. Year 6 | 4. Year 7 | 5. Year 8 | 6. Year 9 | 7. Year 10 | 8. Year 11 | 9. Year 12 | 10. Year 13 | |
| (a) Earnings Forecasts | $ 1,699.84 | $ 1,903.82 | $ 2,132.28 | $ 2,388.15 | $ 2,674.73 | $ 2,995.70 | $ 3,355.18 | $ 3,757.80 | $ 4,208.74 | $ 4,713.79 |
| (b) Equity Book Value-Forecasts (per share) | ||||||||||
| Equity book value at beginning | $ 10,624.00 | $ 11,898.88 | $ 13,326.75 | $ 14,925.96 | $ 16,717.07 | $ 18,723.12 | $ 20,969.89 | $ 23,486.28 | $ 26,304.63 | $ 29,461.19 |
| Earnings Forecasts | $ 1,699.84 | $ 1,903.82 | $ 2,132.28 | $ 2,388.15 | $ 2,674.73 | $ 2,995.70 | $ 3,355.18 | $ 3,757.80 | $ 4,208.74 | $ 4,713.79 |
| Stock Issued | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
| Other Comprehensive Income | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | |||
| Dividend | $ (424.96) | $ (475.96) | $ (533.07) | $ (597.04) | $ (668.68) | $ (748.92) | $ (838.80) | $ (939.45) | $ (1,052.19) | $ (1,178.45) |
| Equity book value at year end | $ 11,898.88 | $ 13,326.75 | $ 14,925.96 | $ 16,717.07 | $ 18,723.12 | $ 20,969.89 | $ 23,486.28 | $ 26,304.63 | $ 29,461.19 | $ 32,996.53 |
| ROE (Return on Equity) | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% | 16.00% |
| (c) Abnormal Earnings | ||||||||||
| Equity book value at beginning | $ 10,624.00 | $ 11,898.88 | $ 13,326.75 | $ 14,925.96 | $ 16,717.07 | $ 18,723.12 | $ 20,969.89 | $ 23,486.28 | $ 26,304.63 | $ 29,461.19 |
| Equity cost pf capital | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% | 9.00% |
| Normal Earnings | $ 956.16 | $ 1,070.90 | $ 1,199.41 | $ 1,343.34 | $ 1,504.54 | $ 1,685.08 | $ 1,887.29 | $ 2,113.77 | $ 2,367.42 | $ 2,651.51 |
| Actual or Forecasted Earnings | $ 1,699.84 | $ 1,903.82 | $ 2,132.28 | $ 2,388.15 | $ 2,674.73 | $ 2,995.70 | $ 3,355.18 | $ 3,757.80 | $ 4,208.74 | $ 4,713.79 |
| Abnormal Earnings | $ 743.68 | $ 832.92 | $ 932.87 | $ 1,044.82 | $ 1,170.19 | $ 1,310.62 | $ 1,467.89 | $ 1,644.04 | $ 1,841.32 | $ 2,062.28 |
| Discount Factor (at 9%) | 91.74% | 84.17% | 77.22% | 70.84% | 64.99% | 59.63% | 54.70% | 50.19% | 46.04% | 42.24% |
| Abnormal Earnings Discounted at present | $ 682.28 | $ 701.05 | $ 720.35 | $ 740.17 | $ 760.55 | $ 781.48 | $ 802.99 | $ 825.09 | $ 847.80 | $ 871.13 |
| (c) Estimated share value | ||||||||||
| Sum of discounted abnormal earnings over horizon | $ 7,732.88 | |||||||||
| Current equity book value | $ 10,624.00 | |||||||||
| Estimated current value of Equity at Year 3 | $ 18,356.88 | |||||||||
| Number of shares (Millions) | 307 | |||||||||
| Predicted Share Vale | $ 59.79 | Million | Q3 | |||||||
| Actural High | $ 64.00 | |||||||||
| Actual Low | $ 56.00 | |||||||||
| Illinois Tool Works | ||||||||||
| Consolidated Statement of Income | ||||||||||
| ($ in millions) | Year 3 | Year 2 | Year1 | |||||||
| Sales | 9468 | 9293 | 9984 | |||||||
| Cost of goods sold | 5936 | 5910 | 6192 | |||||||
| Gross Profit | 3532 | 3383 | 3792 | |||||||
| Selling and administrative expenses | 1720 | 1691 | 1814 | |||||||
| Depreciation and amoritization | 306 | 385 | 414 | |||||||
| Operating Profit | 1506 | 1307 | 1564 | |||||||
| Interest Expense | 68 | 65 | 72 | |||||||
| Nonoperating Expense | 4 | 7 | 13 | |||||||
| Unusual Losses | 337 | 0 | 0 | |||||||
| Pre-tax income | 1097 | 1235 | 1479 | |||||||
| Income taxes | 384 | 429 | 521 | |||||||
| Net Income | 713 | 806 | 958 | |||||||
| Illinois Tool Works | ||||||||||
| Consolidated Statement of Shareholders Equity | ||||||||||
| ($ in millions) | Year 3 | Year 2 | Year1 | |||||||
| Balance at beginning of year | 9823 | 9604 | 9061 | |||||||
| Net income (loss) | 713 | 806 | 958 | |||||||
| Stock issued (repurchased) | 361 | -337 | -192 | |||||||
| Common Stock dividends | -273 | -250 | -223 | |||||||
| Balance at end of year | 10624 | 9823 | 9604 |
