Required Submission Complete the following using Microsoft Excel and Word. All quantitative analysis will be done in Excel, while all qualitative analysis will be completed in Word. You must use formulas for all computations. In Microsoft Excel: 1. Construct horizontal analysis (year-over-year growth) on the provided financial statements for 2019 and 2020. 2. Build common size balance sheets for 2018 - 2020, respectively, and common size income statements for 2018 - 2020, respectively. 3. Create Statements of Cash Flows for 2019 and 2020 using the indirect method. Also compute Free Cash Flow for each year. 4. Calculate all the financial ratios discussed in chapter 16 (use exhibit 16-6 as a guide) for 2019 and 2020. A B F 2018 2019 2020 $414 78 398 79 2,146 $1.299 $1,816 $333 93 424 196 2,441 -$1.467 $2,020 $362 119 446 121 4,148 -$1.646 $3,550 $237 248 84 1.247 $1,816 $242 302 73 1.403 $2,020 $286 466 1,329 1.469 $3,550 2. All values, except stock price, are in thousands ($000) 3 4 Table 1 Balance Sheets 5 6 Assets 7 Cash 8 Accounts receivable 9 Inventory 10 Other current assets 11 Gross Property, Plant, & Equipment 12 Accumulated Depreciation 13 Total assets 14 15 Liabilities and equity 16 Accounts payable 17 Accruals & other current liabilities 18 Long-term debt 19 Total equity 20 Total liabilities and equity 21 22 23 Table 2 Income Statements 24 25 Sales 26 Cost of goods sold 27 Operating expenses: 28 Depreciation 29 Selling & Administrative Expense 30 Other Operating Expenses 31 Operating Income 32 Interest expense 33 Pre-tax Profit 34 Income Tax Expense 35 Net Income 36 37 38 Table 3 Other Information 39 40 Dividends Paid 41 Stock Issuance 42 Stock price 43 Avg. Shares outstanding 44 Avg. Interest Rate on Long-term debt Comparative Historical Fin. St. 2018 2019 2020 $3,796 2,425 $4,036 2,548 $4,308 2,786 167 880 168 981 21 303 179 1,029 80 234 21 $213 54 2 337 1 $336 83 $253 1 $302 83 $219 $159 2018 2019 2020 $89 $0 $39.27 177 1.20% $97 $0 $26.10 172 1.40% $93 $0 $11.55 167 1.60% + F G H 1 K L M N Common-size Analysis 2018 2019 2020 Trend Analysis (% change) 2020 2019 Ratio Analysis 2019 2020 Common-size Analysis 2018 2019 Trend Analysis(% change) 2019 2020 2020 Statement of Cash Flows 2019 2020 Required Submission Complete the following using Microsoft Excel and Word. All quantitative analysis will be done in Excel, while all qualitative analysis will be completed in Word. You must use formulas for all computations. In Microsoft Excel: 1. Construct horizontal analysis (year-over-year growth) on the provided financial statements for 2019 and 2020. 2. Build common size balance sheets for 2018 - 2020, respectively, and common size income statements for 2018 - 2020, respectively. 3. Create Statements of Cash Flows for 2019 and 2020 using the indirect method. Also compute Free Cash Flow for each year. 4. Calculate all the financial ratios discussed in chapter 16 (use exhibit 16-6 as a guide) for 2019 and 2020. A B F 2018 2019 2020 $414 78 398 79 2,146 $1.299 $1,816 $333 93 424 196 2,441 -$1.467 $2,020 $362 119 446 121 4,148 -$1.646 $3,550 $237 248 84 1.247 $1,816 $242 302 73 1.403 $2,020 $286 466 1,329 1.469 $3,550 2. All values, except stock price, are in thousands ($000) 3 4 Table 1 Balance Sheets 5 6 Assets 7 Cash 8 Accounts receivable 9 Inventory 10 Other current assets 11 Gross Property, Plant, & Equipment 12 Accumulated Depreciation 13 Total assets 14 15 Liabilities and equity 16 Accounts payable 17 Accruals & other current liabilities 18 Long-term debt 19 Total equity 20 Total liabilities and equity 21 22 23 Table 2 Income Statements 24 25 Sales 26 Cost of goods sold 27 Operating expenses: 28 Depreciation 29 Selling & Administrative Expense 30 Other Operating Expenses 31 Operating Income 32 Interest expense 33 Pre-tax Profit 34 Income Tax Expense 35 Net Income 36 37 38 Table 3 Other Information 39 40 Dividends Paid 41 Stock Issuance 42 Stock price 43 Avg. Shares outstanding 44 Avg. Interest Rate on Long-term debt Comparative Historical Fin. St. 2018 2019 2020 $3,796 2,425 $4,036 2,548 $4,308 2,786 167 880 168 981 21 303 179 1,029 80 234 21 $213 54 2 337 1 $336 83 $253 1 $302 83 $219 $159 2018 2019 2020 $89 $0 $39.27 177 1.20% $97 $0 $26.10 172 1.40% $93 $0 $11.55 167 1.60% + F G H 1 K L M N Common-size Analysis 2018 2019 2020 Trend Analysis (% change) 2020 2019 Ratio Analysis 2019 2020 Common-size Analysis 2018 2019 Trend Analysis(% change) 2019 2020 2020 Statement of Cash Flows 2019 2020