problem #2. please show full working out

1. Prepare vertical common-size income statements and balance sheets for both companies (8 points).
2. Prepare horizontal analysis on income statements and balance sheets for both companies (8 points).
3. Ratio analyses (17 points).
4. Comment on the analytical results of the two companies based on your work in excel (10 points).
5. Conclusive summary on the firms (2 points)

A B USE THIS INFORMATION ONLY IF NEEDED Trade receivables Less charge-backs \& cash discounts Less allowance for expected credit loss 2020 7.882 645 18 2019 Land Buildings Machinery, equipment \& fixtures Construction in progress Gross property, plant \& equipment Less accumulated depreciation 1895,7323,0634879,4713,58535% 1876,3363,15752710,2073,955 \begin{tabular}{|r|} \hline 2018 \\ \hline 4,914 \\ 245 \\ 33 \\ \hline \end{tabular} G H \begin{tabular}{l|l} 4 & Tax rate \\ 5 & \\ 6 & Net cash flows from operating activities \end{tabular} 14,052 1045,2312,9625488,8453,818 2017 4.599 209 43 1004,8483.0599808,9873,98635% 8,067 5,940 5.275 \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \multirow{2}{*}{1} & \multicolumn{3}{|c|}{ A } & B & D & \multirow{2}{*}{E} & \multirow[t]{2}{*}{G} & \multirow[t]{2}{*}{ H } \\ \hline & \multicolumn{5}{|c|}{ USE 2017 INFORMATION ONLY FOR CALCULATION OF RATIOS. } & & & \\ \hline 2 & \multicolumn{3}{|c|}{ Pfizer Inc (NYS: PFE) } & & & & & \\ \hline 3 & \multicolumn{3}{|l|}{ Report Date } & 2020 & 2019 & 2018 & & 2017 \\ \hline 4 & \multicolumn{3}{|l|}{ Cash \& cash equivalents } & 1784.000 & 1,305,000 & 1.139 .000 & & 1.342 .000 \\ \hline 5 & \multicolumn{3}{|l|}{ Short-term imestments } & 10,437,000 & 8,525,000 & 17,694,000 & & 18,650,000 \\ \hline 6 & \multicolumn{3}{|l|}{ Trade accounts receivable } & 7,930,000 & 8,724,000 & 8,025,000 & & 8,221,000 \\ \hline 7 & \multicolumn{3}{|l|}{ Inventories } & 8.047,000 & 8.284,000 & 7,508.000 & & 7,577,000 \\ \hline 8 & \multicolumn{3}{|l|}{ Curfent tax assets } & 3,264,000 & 3,344,000 & 3,374,000 & & 3,050,000 \\ \hline 9 & \multicolumn{3}{|l|}{ Other current assets } & 3.438,000 & 2.600 .000 & 2.461 .000 & & 2.289 .000 \\ \hline 10 & \multicolumn{3}{|l|}{ Assets held for sale } & 167,000 & 21,000 & 9,725,000 & & 12.000 \\ \hline 11 & \multicolumn{3}{|l|}{ Total current assets } & 35,067,000 & 32,803,000 & 49,926,000 & & 41,141,000 \\ \hline 12 & \multicolumn{3}{|l|}{ Equity-method investments } & 16.855 .000 & 17,133,000 & . & & . \\ \hline 13 & \multicolumn{3}{|l|}{ Long-term imestments } & 3,406,000 & 3,014,000 & 2,767,000 & & 7,015,000 \\ \hline 14 & Property, plant \& eguipment & & & 13.900,000 & 13.967 .000 & 13,385,000 & & 13.865 .000 \\ \hline 15 & Identifable intangble assets & & & 28,471,000 & 35,370,000 & 35,210,000 & & 48,742,000 \\ \hline 16 & Goodwill & & & 49.577,000 & 58.653 .000 & 53,411,000 & & 55,952,000 \\ \hline 17 & Noncurrent deferred tax assets & other noncurremt tax & ssets & 2.383,000 & 2,099,000 & 1,924,000 & & 1,855,000 \\ \hline 18 & Other noncurrent assets & & & 4,569,000 & 4,450,000 & 2,799,000 & & 3,227,000 \\ \hline 19 & Total assets & & & 154,229,000 & 167,489,000 & 159,422,000 & & 171,797,000 \\ \hline 20 & & & & & & & & \\ \hline 21 & Liabilities & & & & & & & \\ \hline 22 & Short-term borrowings, including & current portion of lon & term debt & 2,703,000 & 16,195,000 & 8,831,000 & & 9,953.000 \\ \hline 23 & Trade accounts payable & & & 4.309 .000 & 4.220,000 & 4,674,000 & & 4,656,000 \\ \hline 24 & Dividends payable & & & 2.162,000 & 2.104,000 & 2,047,000 & & 2,029.000 \\ \hline 25 & Income taxes payable & & & 1,049,000 & 980,000 & 1,265,000 & & 477,000 \\ \hline 26 & Accrued compensation \& related & items & & 3.057,000 & 2,722,000 & 2,396.000 & & 2,197,000 \\ \hline 27 & Other current liabilities & & & 12.640,000 & 11,083,000 & 12.643 .000 & & 11,115,000 \\ \hline 28 & Total current liabilities & & & 25,920,000 & 37,304,000 & 31,858,000 & & 30,427,000 \\ \hline 29 & Longterm debt & & & 37,133,000 & 35.955,000 & 32,909,000 & & 33.538 .000 \\ \hline 30 & Pension benefit obligations & & & 4,766,000 & 5,638,000 & 5,272,000 & & 6,926,000 \\ \hline 31 & Postretirement benefit obligation & & & 645.000 & 1,124,000 & 1,338.000 & & 1,504,000 \\ \hline 32 & Noncurtent deforred tax liabilitie & & & 4,063,000 & 5,578,000 & 3,700,000 & & 3.900,000 \\ \hline 33 & Other taxes payable & & & 11,560,000 & 12,126,000 & 14,737,000 & & 18,697,000 \\ \hline 34 & Other noncurrent liablities & & & 6.669,000 & 6.317,000 & 5,050.000 & & 5,149,000 \\ \hline 35 & Total liabilities & & & 90,756,000 & 104,042,000 & 95,664,000 & & 100,141,000 \\ \hline 36 & & & & & & & & \\ \hline 37 & Stockholders' Equity & & & & & & & \\ \hline 38 & Preterred stock & & & - & 17,000 & 19.000 & & 21,000 \\ \hline 39 & Common stock & & & 470,000 & 468,000 & 467,000 & & 464,000 \\ \hline 40 & Addtional paid-in captal & & & 88,674,000 & 87,428,000 & 85,253,000 & & 64,270,000 \\ \hline 41 & Treasury stock & & & 110.988,000 & 110,801,000 & 101,610,000 & & 69,425.000 \\ \hline 42 & Rotained eamings (accumulsted & deficit) & & 96,770,000 & 97,670,000 & 69,554,000 & & 85,291,000 \\ \hline 43 & Accumulated other comprehens & ve income (loss) & & (11,688,000) & (11,640,000) & (11,275,000) & & (9.321,000) \\ \hline 44 & Equity attinbutable to noncontrol & ling interests & & 235,000 & 305,000 & 350.000 & & 348.000 \\ \hline 45 & Total equity (deficit) & & & 63,473,000 & 63,447,000 & 63,758,000 & & 71,656,000 \\ \hline 46 & & & & & & & & \\ \hline 47 & & & & & & & & \\ \hline 48 & & & & & & & & \\ \hline & Balance Sheet & income statement & Addition & () & & & & \\ \hline \end{tabular} Bristol Myers Squibb Co. (NYS: BMY) Report Date Net product sales Alliance \& other revenues Total revenues Cost of products sold Marketing, selling \& administrative expenses Research \& development expenses Amortization of acquired intangible assets Interest expense 1 Royalties \& licensing income 2 Equity investment gains Other expense (gain) Total expenses Earnings (loss) before income taxes Provision for (benefit from) income taxes Earnings after income taxes Net earnings (loss) attributable to noncontrolling interest Net Income 2020 41,321 1,197 42,518 11,773 7,661 11,143 9.688 1,420 1,527 1,228 10,459 49,389 (6,871) 2, 124 (8,995) (20) (9,015) 2019 25, 174 971 26,145 8,078 4,871 6,148 1,135 656 1,360 279 1,921 21,170 4,975 1,515 3,460 (21) 3,439 2018 21,581 980 22,561 6,547 4,551 6.345 183 1.353 (512) (192) 16,593 5,968 1,021 4,947 (27) 4,920 Balance sheet Income statement Additional info Please check the blackboard for the financial statements. (3) Prepare ratio analyses (for 2020, 2019, and 2018) for both companies. You should include the following ratios in your computations: - Profitability ratios Gross Profit margin Profit margin Return on assets Return on equity - Productivity Inventory Turnover Accounts Receivable Turnover PPE Turnover Asset Turnover - Solvency Debt-to-equity Times interest earned Return on Financial leverage - Liquidity Current Ratio Quick Ratio Operating cash flow to current liabilities Working capital (3) Comment on the analytical results of the two companies based on your work in excel. In addition to contrasting the ratios between the companies, you should interpret the numbers and make suggestions as to why the ratio of one company might be higher/lower than the other. \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 4 & A & B & C & D & E & F & G & H \\ \hline 1 & & USE THIS IN & IFORM & ION ONLY IF & NEEDED & & & \\ \hline 2 & & 2020 & & 2019 & & 2018 & & 2017 \\ \hline 3 & Accounts receivable, gross & 8,438,000 & & 9.251,000 & & 8,566,000 & & 8,805,000 \\ \hline 4 & Less allowance for doubtful accounts & 508,000 & & 527,000 & & 541,000 & & 584,000 \\ \hline 5 & & & & & & & & \\ \hline 6 & Land & 444,000 & & 516,000 & & 500,000 & & 540,000 \\ \hline 7 & Buildings & 9,022,000 & & 10,068,000 & & 9,920,000 & & 10,254,000 \\ \hline 8 & Machinery \& equipment & 11,153,000 & & 12,281,000 & & 11,871,000 & & 11,902,000 \\ \hline 9 & Furniture, fixtures \& other property, p & 4,541,000 & & 4,930,000 & & 4,693,000 & & 4,661,000 \\ \hline 10 & Construction in progress & 3,552,000 & & 2,960,000 & & 2,992,000 & & 2,680,000 \\ \hline 11 & Property, plant \& equipment, at cost & 28,712,000 & & 30,755,000 & & 29,976,000 & & 30,037,000 \\ \hline 12 & Less: accumulated depreciation & 14,812,000 & - & 16,788,000 & - & 16,591,000 & - & 16,172,000 \\ \hline 13 & & & & & & & & \\ \hline 14 & Tax rate & 35% & & 35% & & 35% & & 35% \\ \hline 15 & & & & & & & & \\ \hline 16 & Cash from operations & 14,403,000 & & 12,588,000 & & 15,827,000 & & 16,470,000 \\ \hline 17 & & & & & & & & \\ \hline 18 & & & & & & & & \\ \hline 19 & & & & & & & & \\ \hline 20 & & & & & & & & \\ \hline 21 & & & & & & & & \\ \hline 22 & & & & & & & & \\ \hline 23 & & & & & & & & \\ \hline 24 & & & & & & & & \\ \hline 25 & & & & & & & & \\ \hline 26 & & & & & & & & \\ \hline 27 & & & & & & & & \\ \hline 28 & & & & & & & & \\ \hline 29 & & & & & & & & \\ \hline 30 & & & & & & & & \\ \hline 31 & & & & & & & & \\ \hline 32 & & & & & & & & \\ \hline 33 & & & & & & & & \\ \hline 34 & & & & & & & & \\ \hline 35 & & & & & & & & \\ \hline 36 & & & & & & & & \\ \hline 37 & & & & & & & & \\ \hline 38 & & & & & & & & \\ \hline 39 & & & & & & & & \\ \hline 10 & & & & & & & & \\ \hline 41 & & & & & & & & \\ \hline 12 & & & & & & & & \\ \hline 43 & & & & & & & & \\ \hline 14 & & & & & & & & \\ \hline 45 & & & & & & & & \\ \hline 46 & & & & & & & & \\ \hline 47 & & & & & & & & \\ \hline 48 & & & & & & & & \\ \hline 49 & & & & & & & & \\ \hline & Balance Sheet & ome statement & & itional Data & & & & \\ \hline \end{tabular} A B USE THIS INFORMATION ONLY IF NEEDED Trade receivables Less charge-backs \& cash discounts Less allowance for expected credit loss 2020 7.882 645 18 2019 Land Buildings Machinery, equipment \& fixtures Construction in progress Gross property, plant \& equipment Less accumulated depreciation 1895,7323,0634879,4713,58535% 1876,3363,15752710,2073,955 \begin{tabular}{|r|} \hline 2018 \\ \hline 4,914 \\ 245 \\ 33 \\ \hline \end{tabular} G H \begin{tabular}{l|l} 4 & Tax rate \\ 5 & \\ 6 & Net cash flows from operating activities \end{tabular} 14,052 1045,2312,9625488,8453,818 2017 4.599 209 43 1004,8483.0599808,9873,98635% 8,067 5,940 5.275 \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \multirow{2}{*}{1} & \multicolumn{3}{|c|}{ A } & B & D & \multirow{2}{*}{E} & \multirow[t]{2}{*}{G} & \multirow[t]{2}{*}{ H } \\ \hline & \multicolumn{5}{|c|}{ USE 2017 INFORMATION ONLY FOR CALCULATION OF RATIOS. } & & & \\ \hline 2 & \multicolumn{3}{|c|}{ Pfizer Inc (NYS: PFE) } & & & & & \\ \hline 3 & \multicolumn{3}{|l|}{ Report Date } & 2020 & 2019 & 2018 & & 2017 \\ \hline 4 & \multicolumn{3}{|l|}{ Cash \& cash equivalents } & 1784.000 & 1,305,000 & 1.139 .000 & & 1.342 .000 \\ \hline 5 & \multicolumn{3}{|l|}{ Short-term imestments } & 10,437,000 & 8,525,000 & 17,694,000 & & 18,650,000 \\ \hline 6 & \multicolumn{3}{|l|}{ Trade accounts receivable } & 7,930,000 & 8,724,000 & 8,025,000 & & 8,221,000 \\ \hline 7 & \multicolumn{3}{|l|}{ Inventories } & 8.047,000 & 8.284,000 & 7,508.000 & & 7,577,000 \\ \hline 8 & \multicolumn{3}{|l|}{ Curfent tax assets } & 3,264,000 & 3,344,000 & 3,374,000 & & 3,050,000 \\ \hline 9 & \multicolumn{3}{|l|}{ Other current assets } & 3.438,000 & 2.600 .000 & 2.461 .000 & & 2.289 .000 \\ \hline 10 & \multicolumn{3}{|l|}{ Assets held for sale } & 167,000 & 21,000 & 9,725,000 & & 12.000 \\ \hline 11 & \multicolumn{3}{|l|}{ Total current assets } & 35,067,000 & 32,803,000 & 49,926,000 & & 41,141,000 \\ \hline 12 & \multicolumn{3}{|l|}{ Equity-method investments } & 16.855 .000 & 17,133,000 & . & & . \\ \hline 13 & \multicolumn{3}{|l|}{ Long-term imestments } & 3,406,000 & 3,014,000 & 2,767,000 & & 7,015,000 \\ \hline 14 & Property, plant \& eguipment & & & 13.900,000 & 13.967 .000 & 13,385,000 & & 13.865 .000 \\ \hline 15 & Identifable intangble assets & & & 28,471,000 & 35,370,000 & 35,210,000 & & 48,742,000 \\ \hline 16 & Goodwill & & & 49.577,000 & 58.653 .000 & 53,411,000 & & 55,952,000 \\ \hline 17 & Noncurrent deferred tax assets & other noncurremt tax & ssets & 2.383,000 & 2,099,000 & 1,924,000 & & 1,855,000 \\ \hline 18 & Other noncurrent assets & & & 4,569,000 & 4,450,000 & 2,799,000 & & 3,227,000 \\ \hline 19 & Total assets & & & 154,229,000 & 167,489,000 & 159,422,000 & & 171,797,000 \\ \hline 20 & & & & & & & & \\ \hline 21 & Liabilities & & & & & & & \\ \hline 22 & Short-term borrowings, including & current portion of lon & term debt & 2,703,000 & 16,195,000 & 8,831,000 & & 9,953.000 \\ \hline 23 & Trade accounts payable & & & 4.309 .000 & 4.220,000 & 4,674,000 & & 4,656,000 \\ \hline 24 & Dividends payable & & & 2.162,000 & 2.104,000 & 2,047,000 & & 2,029.000 \\ \hline 25 & Income taxes payable & & & 1,049,000 & 980,000 & 1,265,000 & & 477,000 \\ \hline 26 & Accrued compensation \& related & items & & 3.057,000 & 2,722,000 & 2,396.000 & & 2,197,000 \\ \hline 27 & Other current liabilities & & & 12.640,000 & 11,083,000 & 12.643 .000 & & 11,115,000 \\ \hline 28 & Total current liabilities & & & 25,920,000 & 37,304,000 & 31,858,000 & & 30,427,000 \\ \hline 29 & Longterm debt & & & 37,133,000 & 35.955,000 & 32,909,000 & & 33.538 .000 \\ \hline 30 & Pension benefit obligations & & & 4,766,000 & 5,638,000 & 5,272,000 & & 6,926,000 \\ \hline 31 & Postretirement benefit obligation & & & 645.000 & 1,124,000 & 1,338.000 & & 1,504,000 \\ \hline 32 & Noncurtent deforred tax liabilitie & & & 4,063,000 & 5,578,000 & 3,700,000 & & 3.900,000 \\ \hline 33 & Other taxes payable & & & 11,560,000 & 12,126,000 & 14,737,000 & & 18,697,000 \\ \hline 34 & Other noncurrent liablities & & & 6.669,000 & 6.317,000 & 5,050.000 & & 5,149,000 \\ \hline 35 & Total liabilities & & & 90,756,000 & 104,042,000 & 95,664,000 & & 100,141,000 \\ \hline 36 & & & & & & & & \\ \hline 37 & Stockholders' Equity & & & & & & & \\ \hline 38 & Preterred stock & & & - & 17,000 & 19.000 & & 21,000 \\ \hline 39 & Common stock & & & 470,000 & 468,000 & 467,000 & & 464,000 \\ \hline 40 & Addtional paid-in captal & & & 88,674,000 & 87,428,000 & 85,253,000 & & 64,270,000 \\ \hline 41 & Treasury stock & & & 110.988,000 & 110,801,000 & 101,610,000 & & 69,425.000 \\ \hline 42 & Rotained eamings (accumulsted & deficit) & & 96,770,000 & 97,670,000 & 69,554,000 & & 85,291,000 \\ \hline 43 & Accumulated other comprehens & ve income (loss) & & (11,688,000) & (11,640,000) & (11,275,000) & & (9.321,000) \\ \hline 44 & Equity attinbutable to noncontrol & ling interests & & 235,000 & 305,000 & 350.000 & & 348.000 \\ \hline 45 & Total equity (deficit) & & & 63,473,000 & 63,447,000 & 63,758,000 & & 71,656,000 \\ \hline 46 & & & & & & & & \\ \hline 47 & & & & & & & & \\ \hline 48 & & & & & & & & \\ \hline & Balance Sheet & income statement & Addition & () & & & & \\ \hline \end{tabular} Bristol Myers Squibb Co. (NYS: BMY) Report Date Net product sales Alliance \& other revenues Total revenues Cost of products sold Marketing, selling \& administrative expenses Research \& development expenses Amortization of acquired intangible assets Interest expense 1 Royalties \& licensing income 2 Equity investment gains Other expense (gain) Total expenses Earnings (loss) before income taxes Provision for (benefit from) income taxes Earnings after income taxes Net earnings (loss) attributable to noncontrolling interest Net Income 2020 41,321 1,197 42,518 11,773 7,661 11,143 9.688 1,420 1,527 1,228 10,459 49,389 (6,871) 2, 124 (8,995) (20) (9,015) 2019 25, 174 971 26,145 8,078 4,871 6,148 1,135 656 1,360 279 1,921 21,170 4,975 1,515 3,460 (21) 3,439 2018 21,581 980 22,561 6,547 4,551 6.345 183 1.353 (512) (192) 16,593 5,968 1,021 4,947 (27) 4,920 Balance sheet Income statement Additional info Please check the blackboard for the financial statements. (3) Prepare ratio analyses (for 2020, 2019, and 2018) for both companies. You should include the following ratios in your computations: - Profitability ratios Gross Profit margin Profit margin Return on assets Return on equity - Productivity Inventory Turnover Accounts Receivable Turnover PPE Turnover Asset Turnover - Solvency Debt-to-equity Times interest earned Return on Financial leverage - Liquidity Current Ratio Quick Ratio Operating cash flow to current liabilities Working capital (3) Comment on the analytical results of the two companies based on your work in excel. In addition to contrasting the ratios between the companies, you should interpret the numbers and make suggestions as to why the ratio of one company might be higher/lower than the other. \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 4 & A & B & C & D & E & F & G & H \\ \hline 1 & & USE THIS IN & IFORM & ION ONLY IF & NEEDED & & & \\ \hline 2 & & 2020 & & 2019 & & 2018 & & 2017 \\ \hline 3 & Accounts receivable, gross & 8,438,000 & & 9.251,000 & & 8,566,000 & & 8,805,000 \\ \hline 4 & Less allowance for doubtful accounts & 508,000 & & 527,000 & & 541,000 & & 584,000 \\ \hline 5 & & & & & & & & \\ \hline 6 & Land & 444,000 & & 516,000 & & 500,000 & & 540,000 \\ \hline 7 & Buildings & 9,022,000 & & 10,068,000 & & 9,920,000 & & 10,254,000 \\ \hline 8 & Machinery \& equipment & 11,153,000 & & 12,281,000 & & 11,871,000 & & 11,902,000 \\ \hline 9 & Furniture, fixtures \& other property, p & 4,541,000 & & 4,930,000 & & 4,693,000 & & 4,661,000 \\ \hline 10 & Construction in progress & 3,552,000 & & 2,960,000 & & 2,992,000 & & 2,680,000 \\ \hline 11 & Property, plant \& equipment, at cost & 28,712,000 & & 30,755,000 & & 29,976,000 & & 30,037,000 \\ \hline 12 & Less: accumulated depreciation & 14,812,000 & - & 16,788,000 & - & 16,591,000 & - & 16,172,000 \\ \hline 13 & & & & & & & & \\ \hline 14 & Tax rate & 35% & & 35% & & 35% & & 35% \\ \hline 15 & & & & & & & & \\ \hline 16 & Cash from operations & 14,403,000 & & 12,588,000 & & 15,827,000 & & 16,470,000 \\ \hline 17 & & & & & & & & \\ \hline 18 & & & & & & & & \\ \hline 19 & & & & & & & & \\ \hline 20 & & & & & & & & \\ \hline 21 & & & & & & & & \\ \hline 22 & & & & & & & & \\ \hline 23 & & & & & & & & \\ \hline 24 & & & & & & & & \\ \hline 25 & & & & & & & & \\ \hline 26 & & & & & & & & \\ \hline 27 & & & & & & & & \\ \hline 28 & & & & & & & & \\ \hline 29 & & & & & & & & \\ \hline 30 & & & & & & & & \\ \hline 31 & & & & & & & & \\ \hline 32 & & & & & & & & \\ \hline 33 & & & & & & & & \\ \hline 34 & & & & & & & & \\ \hline 35 & & & & & & & & \\ \hline 36 & & & & & & & & \\ \hline 37 & & & & & & & & \\ \hline 38 & & & & & & & & \\ \hline 39 & & & & & & & & \\ \hline 10 & & & & & & & & \\ \hline 41 & & & & & & & & \\ \hline 12 & & & & & & & & \\ \hline 43 & & & & & & & & \\ \hline 14 & & & & & & & & \\ \hline 45 & & & & & & & & \\ \hline 46 & & & & & & & & \\ \hline 47 & & & & & & & & \\ \hline 48 & & & & & & & & \\ \hline 49 & & & & & & & & \\ \hline & Balance Sheet & ome statement & & itional Data & & & & \\ \hline \end{tabular}