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 companys 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 companys 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 companys 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.

The company this should be done on is Disney

This is an example of how it should be done:

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 $3,306 ($992) ($331) $1,984 ($198) $1,785 ($625) $1,160 $3,802 ($1,141) ($380) $2,281 ($228) $2,053 ($719) $1,335 $1,009 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 nc: LADA LOOMAHIHO LJA -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 20% after pf2022 -The required return is 10% -The value of this company is calculated as PO = D1/ (1+r) + D2 / (1+r)^2 + D3 / (1+r)^3 +P3/(1+r)^3 Where P3 = D3 /r-g Using the Projections from XYZ Corporation's Earnings Model, PO = $2.52/(1+0.10) + $2.90 / (1+0.10)^2 + $3.34 / (1.10)^3 + $36.70 / (1.10) ^3 Where P3 = $3.34 / 0.20 - 0.10 PO = $2.29 + $2.40 + $2.51 + $27.57 PO = $34.77 -Once an estimated value is established, there are three possible scenarios when it is compared to a recent quote, as follows: Earnings Model Value PO = $34.77 PO = $34.77 PO = $34.77 XYZ Most Recent Quote (Three Scenarios) Secenario 1) $25, Undervalued Secenario 2) $50, Overvalued Secenario 3) $35, Fairly Valued CONSOLIDATED STATEMENTS OF INCOME (in millions, except per share data) 2018 2017 2016 50,869 8,565 59,434 46,843 8.294 55,137 47,130 8,502 55,632 Revenues: Services Products Total revenues Costs and expenses: Cost of services (exclusive of depreciation and amortization) Cost of products (exclusive of depreciation and amortization) Selling, general, administrative and other Depreciation and amortization Total costs and expenses Restructuring and impairment charges Other income, net Interest expense, net Equity in the income (loss) of investees, net Income before income taxes Income taxes Net income Less: Net income attributable to noncontrolling interests Net income attributable to The Walt Disney Company (Disney) (24,653) (5,340) (8.754) (2.527) (41,274) (156) (27,528) (5,198) (8,860) (3.011) (44,597) (33) 601 (574) (102) 14,729 (1,663) 13,066 (468) 12,598 (25,320) (4,986) (8,176) (2,782) (41,264) (98) 78 (385) 320 13.788 (4.422) 9,366 (386) 8,980 (260) 926 14.868 (5.078) 9.790 (399) 9,391 Earnings per share attributable to Disney: Diluted Basic 8.36 5.69 5.73 8.40 5.73 5.76 Weighted average number of common and common equivalent shares outstanding: Diluted 1,507 1,578 1.639 Basic 1,499 1,568 1.629 Dividends declared per share 1.68 1.68 5 1.56 1.42 TABLE OF CONTENTS CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME (in millions) 2018 2017 2016 Net Income 13,066 9,366 9,790 207 434 (18) (37) 584 Other comprehensive income/(loss), net of tax: Market value adjustments for investments Market value adjustments for hedges Pension and postretirement medical plan adjustments Foreign currency translation and other Other comprehensive income/(loss) Comprehensive income Net income attributable to noncontrolling interests, including redeemable noncontrolling interests Other comprehensive loss attributable to noncontrolling interests Comprehensive income attributable to Disney 13 (359) (1,154) (156) (1,656) 8,134 (289) 359 (103) 426 9,792 13,425 (468) (386) (399) 72 98 25 9,431 13,029 7,833 See Notes to Consolidated Financial Statements 63 CONSOLIDATED BALANCE SHEETS (in millions, except per share data) September 29, 2018 September 30, 2017 $ 4,150 9,334 ASSETS Current assets Cash and cash cquivalents Roccivables Inventories Television costs and advances Other current assets Total current assets Film and television costs 1,392 1,314 4,017 8,633 1,373 1,278 588 15,889 7,481 3,202 635 16,825 7,888 2,899 Investinents Parks, resorts and other property Attractions, buildings and equipment Accumulated depreciation 55,238 (30,764) Projects in progress Land 24,474 3,942 1,124 29,540 6,812 31,269 3,365 98,598 54,043 (29,037) 25,006 2,145 1.255 28.406 6.995 31,426 2.390 95.789 $ Intangible assets, net Goodwill Other assets Total assets LIABILITIES AND EQUITY Current liabilities Accounts payable and other accrued liabilities Current portion of borrowings Deferred revenue and other Total current liabilities Borrowings Deferred income taxes Other long-term liabilities Commitments and contingencies (Note 14) Redeemable noncontrolling interests 99018! 9,479 3,790 4,591 17,860 17,084 3,109 6,590 8,855 6,172 4,568 19,595 19,119 4.480 6,443 1,123 1,148 Borrowings Deferred income taxes 17,084 3,109 6,590 19,119 4,480 6,443 1,123 1,148 Other long-term liabilities Commitments and contingencies (Note 14) Redeemable noncontrolling interests Equity Preferred stock Common stock, $.01 par value, Authorized 4.6 billion shares, Issued - 2.9 billion shares Retained earnings Accumulated other comprehensive loss Treasury stock, at cost, 1.4 billion shares Total Disney Shareholders' equity Noncontrolling interests Total equity Total liabilities and equity 36,779 82,679 (3,097) 116,361 (67,588) 48,773 4,059 52,832 98,598 36,248 72,606 (3,528) 105,326 (64,011) 41,315 3,689 45,004 95,789 $ CONSOLIDATED STATEMENTS OF CASH FLOWS (in millions) 2018 2017 2016 S $ OPERATING ACTIVITIES Net income Depreciation and amortizution Gains on acquisitions and dispositions Deferred income taxes Equity in the income) loss of investees Cash distributions received from equity investees Net change in film and television costs and advances Equity-based compensation Other Changes in operating assets and liabilities: Receivables Inventories 13,066 3,011 (560) (1,573) 102 775 (523) 393 441 9,366 2,782 (289) 334 (320) 788 (1,075) 364 503 9,790 2,527 (26) 1,214 (926) 799 (101) 393 674 (393) 186 (443) Other assets (720) (17) (927) 235 592 14,295 107 (5) (52) (368) 208 12,343 Accounts payable and other accrued liabilities Income taxes (598) 13.136 Cash provided by operations INVESTING ACTIVITIES Investments in parks, resorts and other property Acquisitions Other Cash used in investing activities (4,465) (1,581) 710 (5,336) (3,623) (417) (71) (4,111) (4,773) (850) (135) (5,758) FINANCING ACTIVITIES Commercial paper borrowings,(payments), net Borrowings Reduction of borrowings Dividends Repurchases of common stock Proceeds from exercise of stock options (1,768) 1,056 (1,871) (2,515) (3,577) 210 1,247 4,820 (2,364) (2,445) (9,368) (920) 6,065 (2,205) (2,313) (7,499) 259 276 FINANCING ACTIVITIES Commercial paper borrowings/(payments), net Borrowings Reduction of borrowings Dividends Repurchases of common stock Proceeds from exercise of stock options Contributions from noncontrolling interest holders Other Cash used in financing activities (1,768) 1,056 (1,871) (2,515) (3,577) 210 399 (777) (8,843) 1,247 4,820 (2,364) (2,445) (9,368) 276 17 (1,142) (8,959) (920) 6,065 (2,205) (2,313) (7,499) 259 (607) (7,220) Impact of exchange rates on cash, cash equivalents and restricted cash (25) 31 (123) 35 Change in cash, cash equivalents and restricted cash Cash, cash equivalents and restricted cash, beginning of year Cash, cash equivalents and restricted cash, end of year 91 4,064 4,155 (696) 4,760 4,064 4,725 4,760 Supplemental disclosure of cash flow information: Interest paid Income taxes paid 631 $ 466 $ 395 2,503 3,801 $ 4,133 See Notes to Consolidated Financial Statements 65 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 $3,306 ($992) ($331) $1,984 ($198) $1,785 ($625) $1,160 $3,802 ($1,141) ($380) $2,281 ($228) $2,053 ($719) $1,335 $1,009 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 nc: LADA LOOMAHIHO LJA -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 20% after pf2022 -The required return is 10% -The value of this company is calculated as PO = D1/ (1+r) + D2 / (1+r)^2 + D3 / (1+r)^3 +P3/(1+r)^3 Where P3 = D3 /r-g Using the Projections from XYZ Corporation's Earnings Model, PO = $2.52/(1+0.10) + $2.90 / (1+0.10)^2 + $3.34 / (1.10)^3 + $36.70 / (1.10) ^3 Where P3 = $3.34 / 0.20 - 0.10 PO = $2.29 + $2.40 + $2.51 + $27.57 PO = $34.77 -Once an estimated value is established, there are three possible scenarios when it is compared to a recent quote, as follows: Earnings Model Value PO = $34.77 PO = $34.77 PO = $34.77 XYZ Most Recent Quote (Three Scenarios) Secenario 1) $25, Undervalued Secenario 2) $50, Overvalued Secenario 3) $35, Fairly Valued CONSOLIDATED STATEMENTS OF INCOME (in millions, except per share data) 2018 2017 2016 50,869 8,565 59,434 46,843 8.294 55,137 47,130 8,502 55,632 Revenues: Services Products Total revenues Costs and expenses: Cost of services (exclusive of depreciation and amortization) Cost of products (exclusive of depreciation and amortization) Selling, general, administrative and other Depreciation and amortization Total costs and expenses Restructuring and impairment charges Other income, net Interest expense, net Equity in the income (loss) of investees, net Income before income taxes Income taxes Net income Less: Net income attributable to noncontrolling interests Net income attributable to The Walt Disney Company (Disney) (24,653) (5,340) (8.754) (2.527) (41,274) (156) (27,528) (5,198) (8,860) (3.011) (44,597) (33) 601 (574) (102) 14,729 (1,663) 13,066 (468) 12,598 (25,320) (4,986) (8,176) (2,782) (41,264) (98) 78 (385) 320 13.788 (4.422) 9,366 (386) 8,980 (260) 926 14.868 (5.078) 9.790 (399) 9,391 Earnings per share attributable to Disney: Diluted Basic 8.36 5.69 5.73 8.40 5.73 5.76 Weighted average number of common and common equivalent shares outstanding: Diluted 1,507 1,578 1.639 Basic 1,499 1,568 1.629 Dividends declared per share 1.68 1.68 5 1.56 1.42 TABLE OF CONTENTS CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME (in millions) 2018 2017 2016 Net Income 13,066 9,366 9,790 207 434 (18) (37) 584 Other comprehensive income/(loss), net of tax: Market value adjustments for investments Market value adjustments for hedges Pension and postretirement medical plan adjustments Foreign currency translation and other Other comprehensive income/(loss) Comprehensive income Net income attributable to noncontrolling interests, including redeemable noncontrolling interests Other comprehensive loss attributable to noncontrolling interests Comprehensive income attributable to Disney 13 (359) (1,154) (156) (1,656) 8,134 (289) 359 (103) 426 9,792 13,425 (468) (386) (399) 72 98 25 9,431 13,029 7,833 See Notes to Consolidated Financial Statements 63 CONSOLIDATED BALANCE SHEETS (in millions, except per share data) September 29, 2018 September 30, 2017 $ 4,150 9,334 ASSETS Current assets Cash and cash cquivalents Roccivables Inventories Television costs and advances Other current assets Total current assets Film and television costs 1,392 1,314 4,017 8,633 1,373 1,278 588 15,889 7,481 3,202 635 16,825 7,888 2,899 Investinents Parks, resorts and other property Attractions, buildings and equipment Accumulated depreciation 55,238 (30,764) Projects in progress Land 24,474 3,942 1,124 29,540 6,812 31,269 3,365 98,598 54,043 (29,037) 25,006 2,145 1.255 28.406 6.995 31,426 2.390 95.789 $ Intangible assets, net Goodwill Other assets Total assets LIABILITIES AND EQUITY Current liabilities Accounts payable and other accrued liabilities Current portion of borrowings Deferred revenue and other Total current liabilities Borrowings Deferred income taxes Other long-term liabilities Commitments and contingencies (Note 14) Redeemable noncontrolling interests 99018! 9,479 3,790 4,591 17,860 17,084 3,109 6,590 8,855 6,172 4,568 19,595 19,119 4.480 6,443 1,123 1,148 Borrowings Deferred income taxes 17,084 3,109 6,590 19,119 4,480 6,443 1,123 1,148 Other long-term liabilities Commitments and contingencies (Note 14) Redeemable noncontrolling interests Equity Preferred stock Common stock, $.01 par value, Authorized 4.6 billion shares, Issued - 2.9 billion shares Retained earnings Accumulated other comprehensive loss Treasury stock, at cost, 1.4 billion shares Total Disney Shareholders' equity Noncontrolling interests Total equity Total liabilities and equity 36,779 82,679 (3,097) 116,361 (67,588) 48,773 4,059 52,832 98,598 36,248 72,606 (3,528) 105,326 (64,011) 41,315 3,689 45,004 95,789 $ CONSOLIDATED STATEMENTS OF CASH FLOWS (in millions) 2018 2017 2016 S $ OPERATING ACTIVITIES Net income Depreciation and amortizution Gains on acquisitions and dispositions Deferred income taxes Equity in the income) loss of investees Cash distributions received from equity investees Net change in film and television costs and advances Equity-based compensation Other Changes in operating assets and liabilities: Receivables Inventories 13,066 3,011 (560) (1,573) 102 775 (523) 393 441 9,366 2,782 (289) 334 (320) 788 (1,075) 364 503 9,790 2,527 (26) 1,214 (926) 799 (101) 393 674 (393) 186 (443) Other assets (720) (17) (927) 235 592 14,295 107 (5) (52) (368) 208 12,343 Accounts payable and other accrued liabilities Income taxes (598) 13.136 Cash provided by operations INVESTING ACTIVITIES Investments in parks, resorts and other property Acquisitions Other Cash used in investing activities (4,465) (1,581) 710 (5,336) (3,623) (417) (71) (4,111) (4,773) (850) (135) (5,758) FINANCING ACTIVITIES Commercial paper borrowings,(payments), net Borrowings Reduction of borrowings Dividends Repurchases of common stock Proceeds from exercise of stock options (1,768) 1,056 (1,871) (2,515) (3,577) 210 1,247 4,820 (2,364) (2,445) (9,368) (920) 6,065 (2,205) (2,313) (7,499) 259 276 FINANCING ACTIVITIES Commercial paper borrowings/(payments), net Borrowings Reduction of borrowings Dividends Repurchases of common stock Proceeds from exercise of stock options Contributions from noncontrolling interest holders Other Cash used in financing activities (1,768) 1,056 (1,871) (2,515) (3,577) 210 399 (777) (8,843) 1,247 4,820 (2,364) (2,445) (9,368) 276 17 (1,142) (8,959) (920) 6,065 (2,205) (2,313) (7,499) 259 (607) (7,220) Impact of exchange rates on cash, cash equivalents and restricted cash (25) 31 (123) 35 Change in cash, cash equivalents and restricted cash Cash, cash equivalents and restricted cash, beginning of year Cash, cash equivalents and restricted cash, end of year 91 4,064 4,155 (696) 4,760 4,064 4,725 4,760 Supplemental disclosure of cash flow information: Interest paid Income taxes paid 631 $ 466 $ 395 2,503 3,801 $ 4,133 See Notes to Consolidated Financial Statements 65