1. Financial Data Using the same company you selected in Module Three, add another two years of financial statement data so that you have three years of annual data to review for historical analysis. In all, your Excel file must include the following: o Three worksheets of annual balance sheet data o Three worksheets of annual income statement data o Three worksheets of annual statement of cash flow data Important Note: Be sure to label each worksheet in Excel with the appropriate year, as you did in the Module Three assignment. 2. Ratio Calculation On each data tab, use formulas to calculate the following financial indicators for each year of data: o o Current ratio o Debt/equity ratio Free cash flow o Earnings per share Price/earnings ratio Return on equity Net profit margin o 3. Written Responses In a separate Word document, respond to the following: Describe how and why each of the ratios has changed over the three-year period. For example, did the current ratio increase or decrease? Why? Describe how three of the ratios you calculated for your company compare to the general industry. Find general industry data by entering your specific company's ticker symbol here. Be sure to watch the Mergent Online video (written instructions can be found here), as it will help you find the industry data. Report Date 12/31/2017 USD 12/31/2018 USD 12/31/2019 USD Currency Audit Status Not Qualified Yes Thousands 17504 17147 53651 9300 63 9237 4402 6384 Not Available Yes Millions 16718 17233 54353 11289 94 11195 4536 6684 Not Qualified Yes Millions 18492 20435 52210 11011 412 10599 4397 6779 11176 899 10277 11220 Consolidated Scale Cash & cash equivalents Marketable securities Financial Services finance receivables, net Trade & other receivables, gross Allowances Trade & other receivables Raw materials, work-in-process & supplies Finished products Total inventories under first-in, first-out Less: last-in, first-out adjustment Inventories Assets held for sale Other current assets Total current assets Financial Services finance receivables, net Net investment in operating leases Land Buildings & land improvements Machinery, equipment & other property Software Construction in progress Total land, plant & equipment & other property Accumulated depreciation Net land, plant & equipment & other property Tooling, net of amortization Net property Equity in net assets of affiliated companies Deferred income taxes Other assets Total assets Payables Dealer & customer allowances & claims Deferred revenue Accrued employee benefit plans Accrued interest Accrued other postretirement employee benefits ("OPEB") 10786 2383 3339 114047 53703 29230 421 11900 38939 3691 1710 56661 31020 25641 10828 36469 2519 11863 10706 258537 20673 13113 2091 1857 1128 332 3930 114649 55544 29119 445 11477 38720 3349 2066 56057 30243 25814 10364 36178 2709 10412 7929 256540 21520 11369 2095 1755 988 339 3889 115902 56182 28235 411 11096 37533 3118 2608 54766 29862 24904 10423 35327 3085 10973 8104 257808 23282 10902 2107 1661 1057 348 339 204 348 229 332 185 367 3914 22987 315 3806 20556 614 591 540 -1 1445 13717 15062 23609 1 -17 591 1125 -16 2314 14705 22130 14373 2 -16 -15 51179 3393 19697 1396 361 591 1031 -23 3356 17153 13298 17817 1 -16 12 48265 95569 9423 5220 2497 3985 94600 9932 5821 2471 3829 Accrued other postretirement employee benefits ("OPEB") Accrued pension Operating Lease, Liability, Current Other current automotive other liabilities & deferred revenue Total other liabilities & deferred revenue Short-term borrowings Public unsecured debt securities U.S. Department of Energy (DOE) Advanced Technology Veh Other debt Unamortized (discount)/premium Automotive debt payable within one year Short-term Unsecured debt Asset-backed debt Unamortized (discount premium Unamortized issuance costs Fair value adjustments Ford Credit debt payable within one year Other debt payable within one year Liabilities held for sale Total current liabilities Pension OPEB Dealer & dealers' customer allowances & claims Deferred revenue Operating lease liabilities Employee benefit plans Other non-current liabilities Total non-current other liabilities & deferred revenue Automotive - Public unsecured debt securities Automotive - DOE ATVM Incentive Program Automotive - Delayed draw term loan Automotive - Other debt Automotive - Unamortized (discount)premium Automotive - Unamortized issuance costs Automotive long-term debt Financial Services - Unsecured debt Financial Services - Asset-backed debt Financial Services - Unamortized (discount premium Financial Services - Unamortized issuance costs Financial Services - Fair value adjustments Ford Credit long-term debt Unsecured debt Unamortized (discount)/premium Unamortized issuance costs Other long-term debt Deferred income taxes Total liabilities Redeemable noncontrolling interest Common stock Class B stock 1080 1383 23588 9033 1470 1139 1519 24711 9033 2060 52371 130 526 98132 9878 5740 1921 4191 1047 1104 1443 25324 10583 880 1500 547 -161 -116 13233 55148 32162 6 -197 539 87658 474 -3 -1 470 490 225307 1026 -224 -72 11233 52409 36844 1848 -290 -76 12575 56291 34052 -5 -214 -33 90091 -195 -171 88887 604 -3 -1 600 597 220474 100 40 815 222792 98 40 40 1 22006 22668 4800 -59 Capital in excess of par value of stock Retained earnings (accumulated deficit) Foreign currency translation Marketable securities Derivative instruments Pension & other postretirement benefits Accumulated other comprehensive income (loss) Treasury stock Total equity (deficit) attributable to Ford Motor Company Equity (deficit) attributable to noncontrolling interests Total equity (deficit) 22165 20320 4626 71 488 -2685 -7728 1613 33185 45 33230 201 -2708 -7366 1417 35932 34 35966 21843 21218 4277 48 18 -2652 -6959 1253 34890 28 34918 Ratios Industry average 2017 2018 2019 Current Ratio Debt / Equity Ratio Free Cash Flow (000) Earnings per Share Price / earnings ratio Return on Equity Net profit margin Number of Shares Outstanding Closing Price Last Business Day in December As Reported Annual Income Statement Report Date Currency Audit Status 12/31/2019 12/31/2018 12/31/2017 USD USD USD Not Qualified Not Available Not Qualified Yes Millions 143599 12260 41 Yes Millions 148294 12018 26 Yes Millions 145653 11113 155900 160338 10 156776 131332 134693 11161 9472 155326 574 963 57 136269 11403 9463 157135 11527 9104 151963 1133 1171 57 -1602 809 -29 786 667 33 1757 346 -3 144 -22 Consolidated Scale Automotive revenues Ford Credit revenues Mobility revenues Other revenues Total revenues Automotive cost of sales Cost of sales Selling, administrative &other expenses Ford Credit interest, operating & other expenses Total costs & expenses Operating income (loss) Interest expense on automotive debt Interest expense on other debt Net periodic pension & OPEB income (cost), excluding service cost Investment-related interest income Interest income (expense) on income taxes Realized & unrealized gains (losses) on cash equivalents & marketable securities, & other investments Gain & loss on investment Gains (losses) on changes in investments in affiliates Gains (losses on extinguishment of debt Royalty income Other income (loss), net Other automotive interest income & other income (loss), net Other income/loss), net Investment-related interest income Interest income (expense) on income taxes Other income (loss), net Financial services other loss (income), net Equity in net income (loss) of affiliated companies Income before income taxes - U.S. Income (loss) before income taxes - non U.S. Income (loss) before income taxes Current provision for (benefit from) income taxes - federal Current provision for (benefit from) income taxes - non-U.S. Current provision for (benefit from) income taxes - state & local Total current provision for (benefit from) income taxes Deferred provision for (benefit from income taxes - federal Deferred provision for (benefit from) income taxes - non-U.S. 115 42 7 20 -55 381 106 678 491 113 -226 2247 5 32 297 3060 113 5 89 207 1201 4861 3298 8148 -125 868 2656 -3296 -640 -101 738 123 2051 2294 4345 75 690 33 670 -1190 -70 -6 759 -360 239 85 828 -1096 593 Deferred provision for (benefit from) income taxes - state & lod Total deferred provision for (benefit from income taxes Provision for (benefit from) income taxes Net income (loss) Less: income (loss) attributable to noncontrolling interests Net income (loss attributable to Ford Motor Company Weighted average shares outstanding-basic Weighted average shares outstanding-diluted Year end shares outstanding Net income (loss) per share-basic Net income (loss) per share-diluted Cash dividends declared Total number of employees Number of common stockholders Number of class B stockholders Foreign currency translation adjustments -134 -1394 -724 84 -37 47 3972 4004 3964.93 0.01 0.01 0.6 190000 112618 3 174 12 -109 650 3695 -18 3677 3974 3998 3978.552 0.93 0.92 0.73 199000 116764 3 -1024 195 -308 520 7628 -26 7602 3975 3998 3973.352 1.91 1.9 0.65 202000 120626 3 314 Report Date Currency Audit Status 12/31/2019 USD Not Qualified 12/31/2018 12/31/2017 USD USD Not Not Qualified Available Yes Yes Millions Millions 3695 7628 9280 9122 -972 -669 Consolidated Scale Net income Depreciation & tooling amortization Other amortization Held-for-sale impairment charges Provision for credit & insurance losses Pension & OPEB expense Equity investment dividends received in excess of earnings) Foreign currency adjustments Net loss (gain) on changes in investments in affiliates Stock compensation Net change in wholesale & other receivables Provision for deferred income taxes Accounts receivable & other assets Inventory Accounts payable & accrued & other liabilities Other cash flow from operating activs Net cash flows from operating activities Capital spending Acquisitions of finance receivables & operating leases Collections of finance receivables & operating leases Purchases of equity & debt securities Sales & maturities of equity & debt securities Settlements of derivatives Other investing activities Net cash flows from investing activities Cash dividends Purchases of common stock Net changes in short-term debt Proceeds from issuance of other debt Principal payments on other debt Other financing activities Net cash flows from financing activities Effect of exchange rate changes on cash & cash equivalents Net increase (decrease) in cash & cash equivalents Cash & cash equivalents at beginning of year Cash & cash equivalents at end of year Effect of exchange rate changes on cash, cash equivalents, Net increase (decrease) in cash, cash equivalents, & restricte Cash, cash equivalents, & restricted cash at beginning of per Cash, cash equivalents, & restricted cash at end of period Cash paid for interest expense Cash paid (refunded) for income taxes Yes Millions 84 9689 -1199 804 413 2625 203 -54 -29 228 1554 -1370 -816 206 5260 41 17639 -7632 -55576 50182 -17472 16929 -114 -38 -13721 -2389 -237 -1384 47604 46497 -226 -31 609 400 206 529 42 191 -2408 -197 -2239 -828 6781 17 15022 -7785 -62924 50880 -17140 20527 358 -177 -16261 -2905 -164 -2819 50130 44172 -192 -122 717 -608 240 403 -7 246 -836 -232 -2297 -959 6089 65 18096 -7049 -59354 44641 -27567 29898 100 -61 -19392 -2584 -131 1229 45801 40770 -151 489 2587 15905 18492 45 834 16907 17741 5100 599 -370 -1731 18638 16907 4700 821 4000 586 Last 7 Business Day Stock Prices (most recent day listed first) Date 7/13/2020 7/10/2020 7/9/2020 7/8/2020 7/7/2020 7/6/2020 7/2/2020 Close 6.06 6.1 5.84 6.09 6.12 6.19 6.05 Rate Of Return 0.16 "The stock had a . 16 percent return over the period July 2nd through July 13th 2020.". 1. Financial Data Using the same company you selected in Module Three, add another two years of financial statement data so that you have three years of annual data to review for historical analysis. In all, your Excel file must include the following: o Three worksheets of annual balance sheet data o Three worksheets of annual income statement data o Three worksheets of annual statement of cash flow data Important Note: Be sure to label each worksheet in Excel with the appropriate year, as you did in the Module Three assignment. 2. Ratio Calculation On each data tab, use formulas to calculate the following financial indicators for each year of data: o o Current ratio o Debt/equity ratio Free cash flow o Earnings per share Price/earnings ratio Return on equity Net profit margin o 3. Written Responses In a separate Word document, respond to the following: Describe how and why each of the ratios has changed over the three-year period. For example, did the current ratio increase or decrease? Why? Describe how three of the ratios you calculated for your company compare to the general industry. Find general industry data by entering your specific company's ticker symbol here. Be sure to watch the Mergent Online video (written instructions can be found here), as it will help you find the industry data. Report Date 12/31/2017 USD 12/31/2018 USD 12/31/2019 USD Currency Audit Status Not Qualified Yes Thousands 17504 17147 53651 9300 63 9237 4402 6384 Not Available Yes Millions 16718 17233 54353 11289 94 11195 4536 6684 Not Qualified Yes Millions 18492 20435 52210 11011 412 10599 4397 6779 11176 899 10277 11220 Consolidated Scale Cash & cash equivalents Marketable securities Financial Services finance receivables, net Trade & other receivables, gross Allowances Trade & other receivables Raw materials, work-in-process & supplies Finished products Total inventories under first-in, first-out Less: last-in, first-out adjustment Inventories Assets held for sale Other current assets Total current assets Financial Services finance receivables, net Net investment in operating leases Land Buildings & land improvements Machinery, equipment & other property Software Construction in progress Total land, plant & equipment & other property Accumulated depreciation Net land, plant & equipment & other property Tooling, net of amortization Net property Equity in net assets of affiliated companies Deferred income taxes Other assets Total assets Payables Dealer & customer allowances & claims Deferred revenue Accrued employee benefit plans Accrued interest Accrued other postretirement employee benefits ("OPEB") 10786 2383 3339 114047 53703 29230 421 11900 38939 3691 1710 56661 31020 25641 10828 36469 2519 11863 10706 258537 20673 13113 2091 1857 1128 332 3930 114649 55544 29119 445 11477 38720 3349 2066 56057 30243 25814 10364 36178 2709 10412 7929 256540 21520 11369 2095 1755 988 339 3889 115902 56182 28235 411 11096 37533 3118 2608 54766 29862 24904 10423 35327 3085 10973 8104 257808 23282 10902 2107 1661 1057 348 339 204 348 229 332 185 367 3914 22987 315 3806 20556 614 591 540 -1 1445 13717 15062 23609 1 -17 591 1125 -16 2314 14705 22130 14373 2 -16 -15 51179 3393 19697 1396 361 591 1031 -23 3356 17153 13298 17817 1 -16 12 48265 95569 9423 5220 2497 3985 94600 9932 5821 2471 3829 Accrued other postretirement employee benefits ("OPEB") Accrued pension Operating Lease, Liability, Current Other current automotive other liabilities & deferred revenue Total other liabilities & deferred revenue Short-term borrowings Public unsecured debt securities U.S. Department of Energy (DOE) Advanced Technology Veh Other debt Unamortized (discount)/premium Automotive debt payable within one year Short-term Unsecured debt Asset-backed debt Unamortized (discount premium Unamortized issuance costs Fair value adjustments Ford Credit debt payable within one year Other debt payable within one year Liabilities held for sale Total current liabilities Pension OPEB Dealer & dealers' customer allowances & claims Deferred revenue Operating lease liabilities Employee benefit plans Other non-current liabilities Total non-current other liabilities & deferred revenue Automotive - Public unsecured debt securities Automotive - DOE ATVM Incentive Program Automotive - Delayed draw term loan Automotive - Other debt Automotive - Unamortized (discount)premium Automotive - Unamortized issuance costs Automotive long-term debt Financial Services - Unsecured debt Financial Services - Asset-backed debt Financial Services - Unamortized (discount premium Financial Services - Unamortized issuance costs Financial Services - Fair value adjustments Ford Credit long-term debt Unsecured debt Unamortized (discount)/premium Unamortized issuance costs Other long-term debt Deferred income taxes Total liabilities Redeemable noncontrolling interest Common stock Class B stock 1080 1383 23588 9033 1470 1139 1519 24711 9033 2060 52371 130 526 98132 9878 5740 1921 4191 1047 1104 1443 25324 10583 880 1500 547 -161 -116 13233 55148 32162 6 -197 539 87658 474 -3 -1 470 490 225307 1026 -224 -72 11233 52409 36844 1848 -290 -76 12575 56291 34052 -5 -214 -33 90091 -195 -171 88887 604 -3 -1 600 597 220474 100 40 815 222792 98 40 40 1 22006 22668 4800 -59 Capital in excess of par value of stock Retained earnings (accumulated deficit) Foreign currency translation Marketable securities Derivative instruments Pension & other postretirement benefits Accumulated other comprehensive income (loss) Treasury stock Total equity (deficit) attributable to Ford Motor Company Equity (deficit) attributable to noncontrolling interests Total equity (deficit) 22165 20320 4626 71 488 -2685 -7728 1613 33185 45 33230 201 -2708 -7366 1417 35932 34 35966 21843 21218 4277 48 18 -2652 -6959 1253 34890 28 34918 Ratios Industry average 2017 2018 2019 Current Ratio Debt / Equity Ratio Free Cash Flow (000) Earnings per Share Price / earnings ratio Return on Equity Net profit margin Number of Shares Outstanding Closing Price Last Business Day in December As Reported Annual Income Statement Report Date Currency Audit Status 12/31/2019 12/31/2018 12/31/2017 USD USD USD Not Qualified Not Available Not Qualified Yes Millions 143599 12260 41 Yes Millions 148294 12018 26 Yes Millions 145653 11113 155900 160338 10 156776 131332 134693 11161 9472 155326 574 963 57 136269 11403 9463 157135 11527 9104 151963 1133 1171 57 -1602 809 -29 786 667 33 1757 346 -3 144 -22 Consolidated Scale Automotive revenues Ford Credit revenues Mobility revenues Other revenues Total revenues Automotive cost of sales Cost of sales Selling, administrative &other expenses Ford Credit interest, operating & other expenses Total costs & expenses Operating income (loss) Interest expense on automotive debt Interest expense on other debt Net periodic pension & OPEB income (cost), excluding service cost Investment-related interest income Interest income (expense) on income taxes Realized & unrealized gains (losses) on cash equivalents & marketable securities, & other investments Gain & loss on investment Gains (losses) on changes in investments in affiliates Gains (losses on extinguishment of debt Royalty income Other income (loss), net Other automotive interest income & other income (loss), net Other income/loss), net Investment-related interest income Interest income (expense) on income taxes Other income (loss), net Financial services other loss (income), net Equity in net income (loss) of affiliated companies Income before income taxes - U.S. Income (loss) before income taxes - non U.S. Income (loss) before income taxes Current provision for (benefit from) income taxes - federal Current provision for (benefit from) income taxes - non-U.S. Current provision for (benefit from) income taxes - state & local Total current provision for (benefit from) income taxes Deferred provision for (benefit from income taxes - federal Deferred provision for (benefit from) income taxes - non-U.S. 115 42 7 20 -55 381 106 678 491 113 -226 2247 5 32 297 3060 113 5 89 207 1201 4861 3298 8148 -125 868 2656 -3296 -640 -101 738 123 2051 2294 4345 75 690 33 670 -1190 -70 -6 759 -360 239 85 828 -1096 593 Deferred provision for (benefit from) income taxes - state & lod Total deferred provision for (benefit from income taxes Provision for (benefit from) income taxes Net income (loss) Less: income (loss) attributable to noncontrolling interests Net income (loss attributable to Ford Motor Company Weighted average shares outstanding-basic Weighted average shares outstanding-diluted Year end shares outstanding Net income (loss) per share-basic Net income (loss) per share-diluted Cash dividends declared Total number of employees Number of common stockholders Number of class B stockholders Foreign currency translation adjustments -134 -1394 -724 84 -37 47 3972 4004 3964.93 0.01 0.01 0.6 190000 112618 3 174 12 -109 650 3695 -18 3677 3974 3998 3978.552 0.93 0.92 0.73 199000 116764 3 -1024 195 -308 520 7628 -26 7602 3975 3998 3973.352 1.91 1.9 0.65 202000 120626 3 314 Report Date Currency Audit Status 12/31/2019 USD Not Qualified 12/31/2018 12/31/2017 USD USD Not Not Qualified Available Yes Yes Millions Millions 3695 7628 9280 9122 -972 -669 Consolidated Scale Net income Depreciation & tooling amortization Other amortization Held-for-sale impairment charges Provision for credit & insurance losses Pension & OPEB expense Equity investment dividends received in excess of earnings) Foreign currency adjustments Net loss (gain) on changes in investments in affiliates Stock compensation Net change in wholesale & other receivables Provision for deferred income taxes Accounts receivable & other assets Inventory Accounts payable & accrued & other liabilities Other cash flow from operating activs Net cash flows from operating activities Capital spending Acquisitions of finance receivables & operating leases Collections of finance receivables & operating leases Purchases of equity & debt securities Sales & maturities of equity & debt securities Settlements of derivatives Other investing activities Net cash flows from investing activities Cash dividends Purchases of common stock Net changes in short-term debt Proceeds from issuance of other debt Principal payments on other debt Other financing activities Net cash flows from financing activities Effect of exchange rate changes on cash & cash equivalents Net increase (decrease) in cash & cash equivalents Cash & cash equivalents at beginning of year Cash & cash equivalents at end of year Effect of exchange rate changes on cash, cash equivalents, Net increase (decrease) in cash, cash equivalents, & restricte Cash, cash equivalents, & restricted cash at beginning of per Cash, cash equivalents, & restricted cash at end of period Cash paid for interest expense Cash paid (refunded) for income taxes Yes Millions 84 9689 -1199 804 413 2625 203 -54 -29 228 1554 -1370 -816 206 5260 41 17639 -7632 -55576 50182 -17472 16929 -114 -38 -13721 -2389 -237 -1384 47604 46497 -226 -31 609 400 206 529 42 191 -2408 -197 -2239 -828 6781 17 15022 -7785 -62924 50880 -17140 20527 358 -177 -16261 -2905 -164 -2819 50130 44172 -192 -122 717 -608 240 403 -7 246 -836 -232 -2297 -959 6089 65 18096 -7049 -59354 44641 -27567 29898 100 -61 -19392 -2584 -131 1229 45801 40770 -151 489 2587 15905 18492 45 834 16907 17741 5100 599 -370 -1731 18638 16907 4700 821 4000 586 Last 7 Business Day Stock Prices (most recent day listed first) Date 7/13/2020 7/10/2020 7/9/2020 7/8/2020 7/7/2020 7/6/2020 7/2/2020 Close 6.06 6.1 5.84 6.09 6.12 6.19 6.05 Rate Of Return 0.16 "The stock had a . 16 percent return over the period July 2nd through July 13th 2020