Create an Excel file using the file name (first name. last name. Financial Analysis 2019). Create a separate worksheet (and name appropriately) for each requirement below. Complete the requirements below using Excel formulas for the two most recent years reported in the annual report 1. Income Statement Analysis. Create vertical and horizontal analysis income statement for the two most recent years, focusing in on the following lines. Stop at net income. Do not include earnings per share data other comprehensive income (loss). 2. Balance Sheet Analysis. Create vertical and horizontal analysis balance sheet for the two most recent years. 3. Discuss financial the trends and impact supported by the above analysis in the space below. 4. Review the analytical measures on page 926 and 927 of the text. Compute the following financial analysis measures, if applicable, for the two most recent years. a. Cash ratio 8. Inventory Turnover m. Rate of return on Common Stockholders' Equity n. Earnings per Share 0. Dividend Yield b. Acid-test ratio c. Current Ratio h. Days' Sales in Inventory | Accounts Receivable Turnover Days' Sales in Receivable k. Gross Profit Percentage Profit margin ratio p. Dividend Payout d. Debt Ratio The Weather Cha... : Yelp TripAdv ogle W Wikipedia Facebook Twitter in Linkedin American Homes 4 Rent Consolidated Balance Sheets (Amounts in thousands, except share data) December 31, 2019 December 31, 2018 $ 1,756,504 S 1,713,496 7,691,877 7,483,600 9,448,381 9,197,096 (1,462 105) (1.176,499) 7,986,276 8,020,597 355,427 153,651 209,828 318,327 8,551,531 8,492,575 37,575 30,284 126,544 144,930 29,618 29,027 202,056 146,034 6,840 12,686 25,666 25,666 120,279 120,279 $ 9,100,109 $ 9,001,481 s Assets Single-family properties Land. Buildings and improvements Single-family properties in operation Less: accumulated depreciation Single-family properties in operation, net Single-family properties under development and development land Single-family properties held for sale, net Total real estate assets, net Cash and cash equivalents Restricted cash Rent and other receivables, net Escrow deposits, prepaid expenses and other assets Deferred costs and other intangibles, net Asset-backed securitization certificates Goodwill Total assets Liabilities Revolving credit facility Term loan facility, net Asset-backed securitizations, net Unsecured senior notes, net Accounts payable and accrued expenses Amounts payable to affiliates Total liabilities. Commitments and contingencies (see Note 14) Equity Shareholders' equity; Class A common shares ($0.01 par value per share, 450,000,000 shares authorized, 300,107,599 and 296,014,546 shares issued and outstanding at December 31, 2019 and 2018, respectively).... Class B common shares ($0.01 par value per share, 50,000,000 shares authorized, 635,075 shares issued and outstanding at December 31, 2019 and 2018) Preferred shares (50.01 par value per share, 100,000,000 shares authorized, 35,350,000 shares issued and outstanding at December 31, 2019 and 2018) Additional paid-in capital Accumulated deficit Accumulated other comprehensive income Total shareholders' equity Noncontrolling interest Total equity I Total liabilities and equity 1,945,044 888,453 243,193 4,629 3,081,319 $ 250,000 99,232 1,961,511 492,800 219,229 4,967 3,027,739 3,001 2,960 6 6 354 354 5,790,775 5,732,466 (465,368) (491,214) 6,658 7,393 5,335,426 5,251,965 683,364 721,777 6,018,790 5,973,742 $ 9,100,109 $ 9,001,481 The accompanying notes are an integral part of these consolidated financial statements Financials The Weather Cha... Yelp Facebook Twitter in Linkedin Google w Wikipedia American Homes 4 Rent Consolidated Statements of Operations (Amounts in thousands, excepe share and per share data) For the Year Ended December 31, 2019 2015 2017 $ 1,132,137 $ 1,066,675 $ 11,241 6,180 1,143,378 1,072,855 954,831 5,568 960,399 433,854 86.908 43,206 127,114 3,224 329,293 412.905 74,573 36,575 122,900 5.225 318,685 Revenues: Rents and other single-family property revenues Other Total revenues Expenses Property operating expenses Property management expenses General and administrative expense Interest expense Acquisition and other transaction costs Depreciation and amortization Hurricane-related charges, net. Other.. Total expenses. Gain on sale of single-family properties and other, net Loss on carly extinguishment of debt Remeasurement of participating preferred shares Net income.. Noncontrolling interest Dividends on preferred shares Redemption of participating preferred shares Net income (loss) attributable to common shareholders Weighted average common shares outstanding: Basic Diluted Net income (Loss) attributable to common shareholders per share: 6,733 1,030,332 43,873 (659) 7,265 978,128 17,946 (1,447) 1,212 112,438 4,165 52.586 32,215 23,472 $ 355,074 69,712 34,732 112,620 4,623 297,290 7,963 5,005 887,019 6,826 (6,555) 2,841 76,492 (4,507) 60,718 42,416 (22,135) 156,260 15,221 55,128 $ 85,911 $ 299,415,397 293,640,500 264.254,718 299,918,966 294,268,330 264,254,718 Basic $ $ 0.29 $ 0.29 $ 0.08 $ 0.08 $ (0.08) (0.08) Diluted American Homes 4 Rent Consolidated Statements of Comprehensive Income (Loss) (Amounts in thousands) For the Years Ended December 31, 2019 2018 2017 $156,260 $112,438 $ 76,492 9,553 75 (963) (842) (28) Net income Other comprehensive (loss) income: Gain on cash flow hedging instruments: Gain on settlement of cash flow hedging instrument Reclassification adjustment for amortization of interest expense included in net income .... Gain on investment in equity securities: Reclassification adjustment for realized gain included in net income ..... Other comprehensive (loss) income Comprehensive income Comprehensive income (loss) attributable to noncontrolling interests .... Dividends on preferred shares... Redemption of participating preferred shares Comprehensive income (loss) attributable to common shareholders (963) 155,297 8,711 121,149 (20) 76,472 15,073 5,547 (4,504) 55,128 52,586 60,718 32,215 42,416 $ 85,096 $ 30,801 $(22,158) Create an Excel file using the file name (first name. last name. Financial Analysis 2019). Create a separate worksheet (and name appropriately) for each requirement below. Complete the requirements below using Excel formulas for the two most recent years reported in the annual report 1. Income Statement Analysis. Create vertical and horizontal analysis income statement for the two most recent years, focusing in on the following lines. Stop at net income. Do not include earnings per share data other comprehensive income (loss). 2. Balance Sheet Analysis. Create vertical and horizontal analysis balance sheet for the two most recent years. 3. Discuss financial the trends and impact supported by the above analysis in the space below. 4. Review the analytical measures on page 926 and 927 of the text. Compute the following financial analysis measures, if applicable, for the two most recent years. a. Cash ratio 8. Inventory Turnover m. Rate of return on Common Stockholders' Equity n. Earnings per Share 0. Dividend Yield b. Acid-test ratio c. Current Ratio h. Days' Sales in Inventory | Accounts Receivable Turnover Days' Sales in Receivable k. Gross Profit Percentage Profit margin ratio p. Dividend Payout d. Debt Ratio The Weather Cha... : Yelp TripAdv ogle W Wikipedia Facebook Twitter in Linkedin American Homes 4 Rent Consolidated Balance Sheets (Amounts in thousands, except share data) December 31, 2019 December 31, 2018 $ 1,756,504 S 1,713,496 7,691,877 7,483,600 9,448,381 9,197,096 (1,462 105) (1.176,499) 7,986,276 8,020,597 355,427 153,651 209,828 318,327 8,551,531 8,492,575 37,575 30,284 126,544 144,930 29,618 29,027 202,056 146,034 6,840 12,686 25,666 25,666 120,279 120,279 $ 9,100,109 $ 9,001,481 s Assets Single-family properties Land. Buildings and improvements Single-family properties in operation Less: accumulated depreciation Single-family properties in operation, net Single-family properties under development and development land Single-family properties held for sale, net Total real estate assets, net Cash and cash equivalents Restricted cash Rent and other receivables, net Escrow deposits, prepaid expenses and other assets Deferred costs and other intangibles, net Asset-backed securitization certificates Goodwill Total assets Liabilities Revolving credit facility Term loan facility, net Asset-backed securitizations, net Unsecured senior notes, net Accounts payable and accrued expenses Amounts payable to affiliates Total liabilities. Commitments and contingencies (see Note 14) Equity Shareholders' equity; Class A common shares ($0.01 par value per share, 450,000,000 shares authorized, 300,107,599 and 296,014,546 shares issued and outstanding at December 31, 2019 and 2018, respectively).... Class B common shares ($0.01 par value per share, 50,000,000 shares authorized, 635,075 shares issued and outstanding at December 31, 2019 and 2018) Preferred shares (50.01 par value per share, 100,000,000 shares authorized, 35,350,000 shares issued and outstanding at December 31, 2019 and 2018) Additional paid-in capital Accumulated deficit Accumulated other comprehensive income Total shareholders' equity Noncontrolling interest Total equity I Total liabilities and equity 1,945,044 888,453 243,193 4,629 3,081,319 $ 250,000 99,232 1,961,511 492,800 219,229 4,967 3,027,739 3,001 2,960 6 6 354 354 5,790,775 5,732,466 (465,368) (491,214) 6,658 7,393 5,335,426 5,251,965 683,364 721,777 6,018,790 5,973,742 $ 9,100,109 $ 9,001,481 The accompanying notes are an integral part of these consolidated financial statements Financials The Weather Cha... Yelp Facebook Twitter in Linkedin Google w Wikipedia American Homes 4 Rent Consolidated Statements of Operations (Amounts in thousands, excepe share and per share data) For the Year Ended December 31, 2019 2015 2017 $ 1,132,137 $ 1,066,675 $ 11,241 6,180 1,143,378 1,072,855 954,831 5,568 960,399 433,854 86.908 43,206 127,114 3,224 329,293 412.905 74,573 36,575 122,900 5.225 318,685 Revenues: Rents and other single-family property revenues Other Total revenues Expenses Property operating expenses Property management expenses General and administrative expense Interest expense Acquisition and other transaction costs Depreciation and amortization Hurricane-related charges, net. Other.. Total expenses. Gain on sale of single-family properties and other, net Loss on carly extinguishment of debt Remeasurement of participating preferred shares Net income.. Noncontrolling interest Dividends on preferred shares Redemption of participating preferred shares Net income (loss) attributable to common shareholders Weighted average common shares outstanding: Basic Diluted Net income (Loss) attributable to common shareholders per share: 6,733 1,030,332 43,873 (659) 7,265 978,128 17,946 (1,447) 1,212 112,438 4,165 52.586 32,215 23,472 $ 355,074 69,712 34,732 112,620 4,623 297,290 7,963 5,005 887,019 6,826 (6,555) 2,841 76,492 (4,507) 60,718 42,416 (22,135) 156,260 15,221 55,128 $ 85,911 $ 299,415,397 293,640,500 264.254,718 299,918,966 294,268,330 264,254,718 Basic $ $ 0.29 $ 0.29 $ 0.08 $ 0.08 $ (0.08) (0.08) Diluted American Homes 4 Rent Consolidated Statements of Comprehensive Income (Loss) (Amounts in thousands) For the Years Ended December 31, 2019 2018 2017 $156,260 $112,438 $ 76,492 9,553 75 (963) (842) (28) Net income Other comprehensive (loss) income: Gain on cash flow hedging instruments: Gain on settlement of cash flow hedging instrument Reclassification adjustment for amortization of interest expense included in net income .... Gain on investment in equity securities: Reclassification adjustment for realized gain included in net income ..... Other comprehensive (loss) income Comprehensive income Comprehensive income (loss) attributable to noncontrolling interests .... Dividends on preferred shares... Redemption of participating preferred shares Comprehensive income (loss) attributable to common shareholders (963) 155,297 8,711 121,149 (20) 76,472 15,073 5,547 (4,504) 55,128 52,586 60,718 32,215 42,416 $ 85,096 $ 30,801 $(22,158)