assumption growth 10% please
the answer should have the same template as this:
the company assigned is Catapillar. Ticker CAT 5) An annual report service, which is provided by major financial information companies such as Dow Jones -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information can then be used to calculate the fair value of the company's stock price, as based on the stock valuation formulas discussed in Chapters 7 and 20. All assumptions and formulas used for the pro forma projections and the company's fair value must be included at the end of your model. Once a fair value is reached, this can be compared to a recent quote of the company's stock price to determine whether or not the company is fairly valued at its most recent trading price. A separate attachment provides an example of how this should be constructed. Table of Contents Caterpillar Inc. STATEMENT 1 Consolidated Results of Operations for the Years Emded December 31 (Dollars in millions except per share data) 2019 2018 2017 5 5 Sales and revenues: Sales of Machinery, Energy & Transportation Revenues of Financial Products Total sales and revenues 50.755 3,045 50.800 51822 2.900 54.722 42,676 2786 45.462 Operating costs: Cost of goods sold Selling general and administrative expenses Research and development expenses Interest expense of Financial Products Other operating income) expenses Total operating costs 36,630 5.162 1.693 754 1.271 45.510 36,997 5.478 1.850 722 1350 46,429 31.260 4999 1.842 646 2.253 41,002 Operating profit 8290 8.293 4.460 404 (67) 531 153 (57) Interest expense excluding Financial Products Other income (expense) Consolidated profit before taxes 7812 7,822 4,082 3319 Provision benefit) for income taxes Profit of consolidated companies 1.746 6,066 1.09 6,224 743 16 Equity in profit (loss) of unconsolidated affiliated companies 6094 6.145 759 Profit of consolidated and afiliated companies Les Profit (los) attributable to noncontrolling interests 1 5 6,0935 6,1-47 Profit 10.RS 5 1019 5 Profit per common share 10.74 5 10.26 $ 1.26 Profit per common share-diluted 5914 5918 Weighted average common shares outstanding (millions) - Basic - Diluted 561.6 5675 5993 Profit tale to commharchulden Diluted by medence of stock-based comparation was thing the red method See accompanying notes to Consolidated Financial Statements Table of Contents Caterpillar Inc. STATEMENT 2 Consolidated Comprehensive Income (Loss) for the Years Ended December 31 (Millions of dollars) 2019 2018 2017 $ 6,094 5 6,148 5 759 Profit (los) of consolidated and lated companies Other comprehensive income (los), net of tax Foreign currency translation, net of tax (provision benefit of 2019-s{s)2018 - S(24); 2017-896 16 (396) 765 Pension and other postretirement benefits Current yeut prior service credit (cost), net of tax (provision benefit of 2019 - SO: 2018-56): 2017- (26) Amortization of prior service (credit) cost, net of tax (provision)benefit of: 2019.10.2018 - $82017- 59 (6) (28) 48 (16) (30) Derivative financial instruments Gains (losses) deferred, net of tax (provision benefit of 2019-5014, 2018 - S(192017 - S2 (Gains) tosses reclusified to carmings, net of tax (provision)benefit of: 2019-S15;2018-531,2017.5(44) (51) 61 (100) 77 Available-for-sale securities Gains (losses) deferred, net of tax (provision) benefit of: 2019-10): 2018- 53; 2017 - 5(23) (Gains) losses reclassified to earnings, net of tax (provision) benefit of 2019-30, 2018 - SO: 2017.535 35 41 (65) 9 (481) 5,667 847 1.606 Total other comprehensive income (los), net of tax Comprehensive income (loss) Les comprehensive income attributable to the noncontrolling interests Comprehensive income (los) attributable to shareholders 6,103 S 6,102 S 5,666 1.601 See accompanying notes to Consolidated Financial Statements. XYZ Corporation Earnings Model Income & Pro Forma Income Statements 12/31/19 and PF 12/31/20 to 12/31/22 (Values in Millions of Dollars) 2019CS 2020PF 2021PF 2022PF 2019 Sales Cost of Goods Sold Depreciation EBIT Interest Income Before Taxes Taxes, 35% Net Income $2,500 ($750) ($250) $1,500 ($150) $1,350 ($473) $878 100.0% 30.0% 10.0% 60.0% 6.0% 54.0% 18.9% 35.1% $2,875 ($863) ($288) $1,725 ($173) $1,553 ($543) $1,009 $3,306 $3,802 ($992) ($1,141) ($331) ($380) $1,984 $2,281 ($198) ($228) $1,785 $2,053 ($625) ($719) $1.160 $1,335 Dividends Retained Earnings ($219) $658 25.0% 75.0% ($252) $757 ($290) $870 ($334) $1.001 Dividends Per Share (DPS) $2.19 $2.52 $2.90 $3.34 -Dividends and RE are calculated as a percentage of Net Income. Earnings Model Projections and Assumptions: -Pro forma sales growth is 15% per year -Common Size values are calculated as a percentage of sales, unless noted otherwise -DPS is calculated as Dividends / 100 Million Shares Outstanding -Dividends are expected to grow at a constant rate of 10% after pf 2022 -The required return is 20% the company assigned is Catapillar. Ticker CAT 5) An annual report service, which is provided by major financial information companies such as Dow Jones -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information can then be used to calculate the fair value of the company's stock price, as based on the stock valuation formulas discussed in Chapters 7 and 20. All assumptions and formulas used for the pro forma projections and the company's fair value must be included at the end of your model. Once a fair value is reached, this can be compared to a recent quote of the company's stock price to determine whether or not the company is fairly valued at its most recent trading price. A separate attachment provides an example of how this should be constructed. Table of Contents Caterpillar Inc. STATEMENT 1 Consolidated Results of Operations for the Years Emded December 31 (Dollars in millions except per share data) 2019 2018 2017 5 5 Sales and revenues: Sales of Machinery, Energy & Transportation Revenues of Financial Products Total sales and revenues 50.755 3,045 50.800 51822 2.900 54.722 42,676 2786 45.462 Operating costs: Cost of goods sold Selling general and administrative expenses Research and development expenses Interest expense of Financial Products Other operating income) expenses Total operating costs 36,630 5.162 1.693 754 1.271 45.510 36,997 5.478 1.850 722 1350 46,429 31.260 4999 1.842 646 2.253 41,002 Operating profit 8290 8.293 4.460 404 (67) 531 153 (57) Interest expense excluding Financial Products Other income (expense) Consolidated profit before taxes 7812 7,822 4,082 3319 Provision benefit) for income taxes Profit of consolidated companies 1.746 6,066 1.09 6,224 743 16 Equity in profit (loss) of unconsolidated affiliated companies 6094 6.145 759 Profit of consolidated and afiliated companies Les Profit (los) attributable to noncontrolling interests 1 5 6,0935 6,1-47 Profit 10.RS 5 1019 5 Profit per common share 10.74 5 10.26 $ 1.26 Profit per common share-diluted 5914 5918 Weighted average common shares outstanding (millions) - Basic - Diluted 561.6 5675 5993 Profit tale to commharchulden Diluted by medence of stock-based comparation was thing the red method See accompanying notes to Consolidated Financial Statements Table of Contents Caterpillar Inc. STATEMENT 2 Consolidated Comprehensive Income (Loss) for the Years Ended December 31 (Millions of dollars) 2019 2018 2017 $ 6,094 5 6,148 5 759 Profit (los) of consolidated and lated companies Other comprehensive income (los), net of tax Foreign currency translation, net of tax (provision benefit of 2019-s{s)2018 - S(24); 2017-896 16 (396) 765 Pension and other postretirement benefits Current yeut prior service credit (cost), net of tax (provision benefit of 2019 - SO: 2018-56): 2017- (26) Amortization of prior service (credit) cost, net of tax (provision)benefit of: 2019.10.2018 - $82017- 59 (6) (28) 48 (16) (30) Derivative financial instruments Gains (losses) deferred, net of tax (provision benefit of 2019-5014, 2018 - S(192017 - S2 (Gains) tosses reclusified to carmings, net of tax (provision)benefit of: 2019-S15;2018-531,2017.5(44) (51) 61 (100) 77 Available-for-sale securities Gains (losses) deferred, net of tax (provision) benefit of: 2019-10): 2018- 53; 2017 - 5(23) (Gains) losses reclassified to earnings, net of tax (provision) benefit of 2019-30, 2018 - SO: 2017.535 35 41 (65) 9 (481) 5,667 847 1.606 Total other comprehensive income (los), net of tax Comprehensive income (loss) Les comprehensive income attributable to the noncontrolling interests Comprehensive income (los) attributable to shareholders 6,103 S 6,102 S 5,666 1.601 See accompanying notes to Consolidated Financial Statements. XYZ Corporation Earnings Model Income & Pro Forma Income Statements 12/31/19 and PF 12/31/20 to 12/31/22 (Values in Millions of Dollars) 2019CS 2020PF 2021PF 2022PF 2019 Sales Cost of Goods Sold Depreciation EBIT Interest Income Before Taxes Taxes, 35% Net Income $2,500 ($750) ($250) $1,500 ($150) $1,350 ($473) $878 100.0% 30.0% 10.0% 60.0% 6.0% 54.0% 18.9% 35.1% $2,875 ($863) ($288) $1,725 ($173) $1,553 ($543) $1,009 $3,306 $3,802 ($992) ($1,141) ($331) ($380) $1,984 $2,281 ($198) ($228) $1,785 $2,053 ($625) ($719) $1.160 $1,335 Dividends Retained Earnings ($219) $658 25.0% 75.0% ($252) $757 ($290) $870 ($334) $1.001 Dividends Per Share (DPS) $2.19 $2.52 $2.90 $3.34 -Dividends and RE are calculated as a percentage of Net Income. Earnings Model Projections and Assumptions: -Pro forma sales growth is 15% per year -Common Size values are calculated as a percentage of sales, unless noted otherwise -DPS is calculated as Dividends / 100 Million Shares Outstanding -Dividends are expected to grow at a constant rate of 10% after pf 2022 -The required return is 20%
