after 3 tables of data, please analyze common size calculations and analysis for 2021. please show me work in excel

will rate!!

B Global Manufacturing, Inc. Introduction Phil Jackson, President and CEO, knew that good products and good service were important to his customers. Global Manufacturing, Inc. provides floor cleaning products for household users and business customers worldwide. The company offers many different types of floor cleaning vacuum systems. Global Manufacturing, Inc. sells its products to many different distributors, retailers, and resellers through its sales force. There has been an increasing demand for things Global Manufacturing, Inc. sells as the economy continues to improve. The growth in the sales was the good news for Phil. The less than good news was the intense competition they faced from other companies worldwide, especially Apex Manufacturing, Inc. Kim Holland, the company's Chief Financial Officer (CFO), was preparing for the end-of-year Senior Staff and Board meeting where all senior management would be presenting their expectations for next year. Kim wants to be fully prepared for this meeting to discuss the current financial condition of the company and the expectations for the future. Kim decided to enlist your help in preparing the financial analysis to present to the Senior Staff and the Board of Directors. She wants to examine the firm's financial statements from the last two years to see how well they had done and from this analysis she wants to develop an action plan for correcting any weaknesses without diminishing the strengths that the company already has. Kim had the accounting department prepare all the financial information you will need to complete this case study for Global Manufacturing. You will find all of the financial data needed for this case study presented in the various Excel worksheets of this file. Always use cell references to the FINANCIAL DATA worksheet data, do not type actual values into your formulas. CASE STUDY INSTRUCTIONS FINANCIAL DATA COMMON SIZE 2020 COMMON SIZE 2021 % CHANGE RAS Global Manufacturing, Inc. INCOME STATEMENT 2020 2021 $53,037,400 38,240,000 5,103,800 $63,412,800 46,418,200 5,985,800 9,693,600 11,008,800 TABLE #1 Total sales Cost of goods sold Administrative expense Earnings before interest, taxes, depreciation & amortization (EBITDA) Depreciation Earnings before interest & taxes.(EBIT) Interest expense Earnings Before Taxes (EBT) Taxes Net income Dividends Additions to Retained Earnings 75 16 17 18 714,800 8,978,800 830,675 8,148,125 1,629,600 6,518,525 1,303,700 5,214,825 887,800 10,121,000 1,166,300 8,954,700 1,790,900 7,163,800 1,432,800 5,731,000 19 20 21 N. Manufacturing, Inc. BALANCE SHEET - 2020 2021 $750,500 12,076,200 11,239,600 24,066,300 23,186,000 $47,252,300 $1,267,400 14,605,100 12,419,500 28,292,000 27,733,800 $56,025,800 TABLE #2 ASSETS Cash Accounts receivable Inventory Total Current Assets Net Fixed Assets TOTAL ASSETS LIABILITIES & STOCKHOLDER'S EQUITY Accounts payable Notes payable Total current liabilities Long-term debt TOTAL LIABILITIES Common stock Retained earnings* TOTAL STOCKHOLDERS' EQUITY TOTAL LIABILITIES & STOCKHOLDER'S EQUITY 8 $4,839,100 5,687,100 10,526,200 8,073,900 18,600,100 5,250,000 23,402,200 28,652,200 $47,252,300 $4,690,500 7,147,100 11,837,600 9,805,000 21,642,600 5,250,000 29,133,200 34,383,200 $56,025,800 -9 10 41 42 43 Global Manufacturing, Inc. ADDITIONAL INFORMATION Information 2020 2021 TABLE #3 Depreciation as a % of net fixed assets Short-term interest expense Long-term interest expense Income tax rate Dividend payout ratio 3.00% 5.42% 6.75% 20.00% 20.00% 3.50% 5.77% 7.29% 20.00% 20.00% 81.20% 98.50% Fixed assets as a percent of total capacity. $66.11 2,550,000 End of year stock price Number of common shares outstanding $115.67 2,550,000 .4 55 56 57 58 MON SIZE 2020 COMMON SIZE 2021 % CHANGE A B C D Global Manufacturing BENCHMARK FIRM LINE BY LINE ANALYSIS OF 2021 INCOME STATEMENT COMPARED TO THE BENCHMARK FIRM (BETTER, SAME AS, WORSE than the Benchmark firm) 2021 0.0% 2021 100.0% 74.4% 9.3% 0.0% 0.0% 2 2021 COMMON SIZE INCOME STATEMENT 3 Total sales 14 Cost of goods sold 15 Administrative expense Earnings before interest, taxes, depreciation & 16 amortization (EBITDA) 17 Depreciation 18 Earnings before interest & taxes.(EBIT) 19 Interest expense 20 Earnings Before Taxes (EBT) 0.0% 16.3% 0.0% 2.5% 0.0% 13.8% 0.0% 1.6% 0.0% 12.2% 0.0% 21 Taxes 0.0% 9.2% 22 Net income 23 Dividends CASE STUDY INSTRUCTIONS 0.0% COMMON SIZE 2021 1.3% % CHANGE EVALUA FINANCIAL DATA COMMON SIZE 2020 Rendy 52F 2! B D 23 Dividends 0.0% 1.3% 24 Additions to Retained Earnings 0.0% 7.9% 25 26 Global Manufacturing BENCHMARK FIRM LINE BY LINE ANALYSIS OF 2021 BALANCE SHEET COMPARED TO THE BENCHMARK FIRM (BETTER, SAME AS, WORSE than the Benchmark firm) 2021 2021 27 2021 COMMON SIZE BALANCE SHEET 28 ASSETS 29 Cash 0.0% 6.9% 30 Accounts receivable 0.0% 15.5% 31 Inventory 0.0% 0.0% 31.3% 53.7% 32 Total Current Assets 33 Net fixed assets 0.0% 46.3% 0.0% TOTAL ASSETS 34 100.0% 35 LIABILITIES & STOCKHOLDERS' EQUITY 36 Accounts payable CASE STUDY INSTRUCTIONS FINANCIAL DATA COMMON SIZE 2020 0.0% COMMON SIZE 2021 13.3% % CHANGE RAM EVALUA Accounts receivable D 0.0% 15.5% 0.0% 1 Inventory 32 Total Current Assets 33 Net fixed assets 0.0% 0.0% 0.0% 31.3% 53.7% 46.3% 100.0% 34 TOTAL ASSETS 35 LIABILITIES & STOCKHOLDERS' EQUITY 36 Accounts payable 0.0% 37 Notes payable 0.0% 38 Total Current Liabilities 0.0% 13.3% 15.5% 28.8% 16.4% 45.2% 39 Long-term debt 0.0% 40 TOTAL LIABILITIES 0.0% 41 Common stock 0.0% 4.1% 0.0% 50.7% 42 Retained earnings* 43 TOTAL STOCKHOLDERS' EQUITY 44 TOTAL LIABILITIES & STOCKHOLDERS' EQUITY 0.0% 54.8% 0.0% 100.0% 45 ON SIZE 2020 COMMON SIZE 2021 % CHANGE EVALUA B Global Manufacturing, Inc. Introduction Phil Jackson, President and CEO, knew that good products and good service were important to his customers. Global Manufacturing, Inc. provides floor cleaning products for household users and business customers worldwide. The company offers many different types of floor cleaning vacuum systems. Global Manufacturing, Inc. sells its products to many different distributors, retailers, and resellers through its sales force. There has been an increasing demand for things Global Manufacturing, Inc. sells as the economy continues to improve. The growth in the sales was the good news for Phil. The less than good news was the intense competition they faced from other companies worldwide, especially Apex Manufacturing, Inc. Kim Holland, the company's Chief Financial Officer (CFO), was preparing for the end-of-year Senior Staff and Board meeting where all senior management would be presenting their expectations for next year. Kim wants to be fully prepared for this meeting to discuss the current financial condition of the company and the expectations for the future. Kim decided to enlist your help in preparing the financial analysis to present to the Senior Staff and the Board of Directors. She wants to examine the firm's financial statements from the last two years to see how well they had done and from this analysis she wants to develop an action plan for correcting any weaknesses without diminishing the strengths that the company already has. Kim had the accounting department prepare all the financial information you will need to complete this case study for Global Manufacturing. You will find all of the financial data needed for this case study presented in the various Excel worksheets of this file. Always use cell references to the FINANCIAL DATA worksheet data, do not type actual values into your formulas. CASE STUDY INSTRUCTIONS FINANCIAL DATA COMMON SIZE 2020 COMMON SIZE 2021 % CHANGE RAS Global Manufacturing, Inc. INCOME STATEMENT 2020 2021 $53,037,400 38,240,000 5,103,800 $63,412,800 46,418,200 5,985,800 9,693,600 11,008,800 TABLE #1 Total sales Cost of goods sold Administrative expense Earnings before interest, taxes, depreciation & amortization (EBITDA) Depreciation Earnings before interest & taxes.(EBIT) Interest expense Earnings Before Taxes (EBT) Taxes Net income Dividends Additions to Retained Earnings 75 16 17 18 714,800 8,978,800 830,675 8,148,125 1,629,600 6,518,525 1,303,700 5,214,825 887,800 10,121,000 1,166,300 8,954,700 1,790,900 7,163,800 1,432,800 5,731,000 19 20 21 N. Manufacturing, Inc. BALANCE SHEET - 2020 2021 $750,500 12,076,200 11,239,600 24,066,300 23,186,000 $47,252,300 $1,267,400 14,605,100 12,419,500 28,292,000 27,733,800 $56,025,800 TABLE #2 ASSETS Cash Accounts receivable Inventory Total Current Assets Net Fixed Assets TOTAL ASSETS LIABILITIES & STOCKHOLDER'S EQUITY Accounts payable Notes payable Total current liabilities Long-term debt TOTAL LIABILITIES Common stock Retained earnings* TOTAL STOCKHOLDERS' EQUITY TOTAL LIABILITIES & STOCKHOLDER'S EQUITY 8 $4,839,100 5,687,100 10,526,200 8,073,900 18,600,100 5,250,000 23,402,200 28,652,200 $47,252,300 $4,690,500 7,147,100 11,837,600 9,805,000 21,642,600 5,250,000 29,133,200 34,383,200 $56,025,800 -9 10 41 42 43 Global Manufacturing, Inc. ADDITIONAL INFORMATION Information 2020 2021 TABLE #3 Depreciation as a % of net fixed assets Short-term interest expense Long-term interest expense Income tax rate Dividend payout ratio 3.00% 5.42% 6.75% 20.00% 20.00% 3.50% 5.77% 7.29% 20.00% 20.00% 81.20% 98.50% Fixed assets as a percent of total capacity. $66.11 2,550,000 End of year stock price Number of common shares outstanding $115.67 2,550,000 .4 55 56 57 58 MON SIZE 2020 COMMON SIZE 2021 % CHANGE A B C D Global Manufacturing BENCHMARK FIRM LINE BY LINE ANALYSIS OF 2021 INCOME STATEMENT COMPARED TO THE BENCHMARK FIRM (BETTER, SAME AS, WORSE than the Benchmark firm) 2021 0.0% 2021 100.0% 74.4% 9.3% 0.0% 0.0% 2 2021 COMMON SIZE INCOME STATEMENT 3 Total sales 14 Cost of goods sold 15 Administrative expense Earnings before interest, taxes, depreciation & 16 amortization (EBITDA) 17 Depreciation 18 Earnings before interest & taxes.(EBIT) 19 Interest expense 20 Earnings Before Taxes (EBT) 0.0% 16.3% 0.0% 2.5% 0.0% 13.8% 0.0% 1.6% 0.0% 12.2% 0.0% 21 Taxes 0.0% 9.2% 22 Net income 23 Dividends CASE STUDY INSTRUCTIONS 0.0% COMMON SIZE 2021 1.3% % CHANGE EVALUA FINANCIAL DATA COMMON SIZE 2020 Rendy 52F 2! B D 23 Dividends 0.0% 1.3% 24 Additions to Retained Earnings 0.0% 7.9% 25 26 Global Manufacturing BENCHMARK FIRM LINE BY LINE ANALYSIS OF 2021 BALANCE SHEET COMPARED TO THE BENCHMARK FIRM (BETTER, SAME AS, WORSE than the Benchmark firm) 2021 2021 27 2021 COMMON SIZE BALANCE SHEET 28 ASSETS 29 Cash 0.0% 6.9% 30 Accounts receivable 0.0% 15.5% 31 Inventory 0.0% 0.0% 31.3% 53.7% 32 Total Current Assets 33 Net fixed assets 0.0% 46.3% 0.0% TOTAL ASSETS 34 100.0% 35 LIABILITIES & STOCKHOLDERS' EQUITY 36 Accounts payable CASE STUDY INSTRUCTIONS FINANCIAL DATA COMMON SIZE 2020 0.0% COMMON SIZE 2021 13.3% % CHANGE RAM EVALUA Accounts receivable D 0.0% 15.5% 0.0% 1 Inventory 32 Total Current Assets 33 Net fixed assets 0.0% 0.0% 0.0% 31.3% 53.7% 46.3% 100.0% 34 TOTAL ASSETS 35 LIABILITIES & STOCKHOLDERS' EQUITY 36 Accounts payable 0.0% 37 Notes payable 0.0% 38 Total Current Liabilities 0.0% 13.3% 15.5% 28.8% 16.4% 45.2% 39 Long-term debt 0.0% 40 TOTAL LIABILITIES 0.0% 41 Common stock 0.0% 4.1% 0.0% 50.7% 42 Retained earnings* 43 TOTAL STOCKHOLDERS' EQUITY 44 TOTAL LIABILITIES & STOCKHOLDERS' EQUITY 0.0% 54.8% 0.0% 100.0% 45 ON SIZE 2020 COMMON SIZE 2021 % CHANGE EVALUA