Question

1 Approved Answer

Posted on Sep 11, 2024

Can you please screenshot your excel to show work thank you will rate 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

image text in transcribed Can you please screenshot your excel to show work thank you will rate

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 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 Global Manufacturing, Inc. BALANCE SHEET 2020 2021 ASSETS $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 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 $4,839,100 5,687,100 10,526,200 8,073,900 18,600,100 5,250,000 23,402,200 28.652.200 $4,690,500 7,147,100 11,837,600 9,805,000 21,642,600 5,250,000 29,133,200 34.38.3.200 $47,252,300 $56,025,800 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% Fixed assets as a percent of total capacity. 98.50% 81.20% End of year stock price Number of common shares outstanding $66.11 2,550,000 $115.67 2,550,000 PERCENTAGE CHANGE ANALYSIS HELP: See the CASE STUDY HELP files named "HELP WITH ANALYZING COMMON SIZE and PERCENT CHANGE ANALYSIS and RATIOS" and "02-SAMPLE COMPANY PERCENTAGE CHANGE CALCULATION & ANALYSIS.pdf" for help with your LINE-BY-LINE ANALYSIS PERCENT CHANGE INSTRUCTIONS For all of these calculations use the financial information provided on the FINANCIAL DATA worksheet. Always use cell references to the FINANCIAL DATA worksheet data, do not type actual values into your formulas. Using typed in values may produce incorrect values later in the spreadsheet. CALCULATIONS: On this worksheet calculate the Percent Change between 2020 and 2021 for all indicated Income Statement and Balance Sheet items. . FORMULA: (2021 Value - 2020 Value) % Change - 2020 Value . ANALYSIS: See the "TUTORIAL FOR COMMON SIZE and PERCENT CHANGE ANALYSIS.pdf" for help in calculating and analyzing Percent Change values. Complete a Line-by-Line Analysis of indicated income statement and balance sheet items compared to Global's own Percent Change in Sales to determine if each statement item is BETTER, SAME AS, or WORSE than Global's own Percent Change in Sales NOTE: Cells shaded in YELLOW are the cells you must provide the calculations or analysis. (Do not include the percent sign (%) and round your answer to ONE decimal places. (e.g., 12.3)) Always use cell reference for all calculations. If you choose to use Excel's "IF" statements for your Line-By-Line Analysis be sure to round your calculated % Change value to match the same decimal places as the Benchmark value. Global Manufacturing, Inc. PERCENTAGE CHANGE CALCULATION AND ANALYSIS LINE BY LINE ANALYSIS % Change COMPARED TO Global GLOBAL'S % CHANGE IN SALES Manufacturing BETTER, SAME AS, WORSE INCOME STATEMENT 2020 to 2021 than Global's % Change in Sales Total sales 19.6% Cost of goods sold 0.0% Administrative expense 0.0% Earnings before interest, taxes, depreciation & amortization 0.0% (EBITDA) Depreciation 0.0% Earnings before interest & taxes.(EBIT) 0.0% Interest expense 0.0% Earnings Before Taxes (EBT) 0.0% Taxes 0.0% Net income 0.0% Dividends 0.0% Additions to Retained Earnings 0.0% LINE BY LINE ANALYSIS COMPARED TO GLOBAL'S % CHANGE IN SALES BETTER, SAME AS, WORSE than Global's % Change in Sales % Change Global Manufacturing BALANCE SHEET 2020 to 2021 ASSETS Cash 0.0% Accounts receivable 0.0% Inventory 0.0% Total Current Assets 0.0% Net fixed assets 0.0% TOTAL ASSETS 0.0% LIABILITIES & STOCKHOLDERS' EQUITY Accounts payable 0.0% Notes payable 0.0% Total Current Liabilities 0.0% Long-term debt 0.0% TOTAL LIABILITIES 0.0% Common stock 0.0% Retained earnings* 0.0% TOTAL STOCKHOLDERS' EQUITY 0.0% TOTAL LIABILITIES & STOCKHOLDERS' EQUITY 0.0% 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 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 Global Manufacturing, Inc. BALANCE SHEET 2020 2021 ASSETS $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 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 $4,839,100 5,687,100 10,526,200 8,073,900 18,600,100 5,250,000 23,402,200 28.652.200 $4,690,500 7,147,100 11,837,600 9,805,000 21,642,600 5,250,000 29,133,200 34.38.3.200 $47,252,300 $56,025,800 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% Fixed assets as a percent of total capacity. 98.50% 81.20% End of year stock price Number of common shares outstanding $66.11 2,550,000 $115.67 2,550,000 PERCENTAGE CHANGE ANALYSIS HELP: See the CASE STUDY HELP files named "HELP WITH ANALYZING COMMON SIZE and PERCENT CHANGE ANALYSIS and RATIOS" and "02-SAMPLE COMPANY PERCENTAGE CHANGE CALCULATION & ANALYSIS.pdf" for help with your LINE-BY-LINE ANALYSIS PERCENT CHANGE INSTRUCTIONS For all of these calculations use the financial information provided on the FINANCIAL DATA worksheet. Always use cell references to the FINANCIAL DATA worksheet data, do not type actual values into your formulas. Using typed in values may produce incorrect values later in the spreadsheet. CALCULATIONS: On this worksheet calculate the Percent Change between 2020 and 2021 for all indicated Income Statement and Balance Sheet items. . FORMULA: (2021 Value - 2020 Value) % Change - 2020 Value . ANALYSIS: See the "TUTORIAL FOR COMMON SIZE and PERCENT CHANGE ANALYSIS.pdf" for help in calculating and analyzing Percent Change values. Complete a Line-by-Line Analysis of indicated income statement and balance sheet items compared to Global's own Percent Change in Sales to determine if each statement item is BETTER, SAME AS, or WORSE than Global's own Percent Change in Sales NOTE: Cells shaded in YELLOW are the cells you must provide the calculations or analysis. (Do not include the percent sign (%) and round your answer to ONE decimal places. (e.g., 12.3)) Always use cell reference for all calculations. If you choose to use Excel's "IF" statements for your Line-By-Line Analysis be sure to round your calculated % Change value to match the same decimal places as the Benchmark value. Global Manufacturing, Inc. PERCENTAGE CHANGE CALCULATION AND ANALYSIS LINE BY LINE ANALYSIS % Change COMPARED TO Global GLOBAL'S % CHANGE IN SALES Manufacturing BETTER, SAME AS, WORSE INCOME STATEMENT 2020 to 2021 than Global's % Change in Sales Total sales 19.6% Cost of goods sold 0.0% Administrative expense 0.0% Earnings before interest, taxes, depreciation & amortization 0.0% (EBITDA) Depreciation 0.0% Earnings before interest & taxes.(EBIT) 0.0% Interest expense 0.0% Earnings Before Taxes (EBT) 0.0% Taxes 0.0% Net income 0.0% Dividends 0.0% Additions to Retained Earnings 0.0% LINE BY LINE ANALYSIS COMPARED TO GLOBAL'S % CHANGE IN SALES BETTER, SAME AS, WORSE than Global's % Change in Sales % Change Global Manufacturing BALANCE SHEET 2020 to 2021 ASSETS Cash 0.0% Accounts receivable 0.0% Inventory 0.0% Total Current Assets 0.0% Net fixed assets 0.0% TOTAL ASSETS 0.0% LIABILITIES & STOCKHOLDERS' EQUITY Accounts payable 0.0% Notes payable 0.0% Total Current Liabilities 0.0% Long-term debt 0.0% TOTAL LIABILITIES 0.0% Common stock 0.0% Retained earnings* 0.0% TOTAL STOCKHOLDERS' EQUITY 0.0% TOTAL LIABILITIES & STOCKHOLDERS' EQUITY 0.0%