Question

1 Approved Answer

Posted on Jul 10, 2024

Put into a working excel sheet with formulas that produce each output of the spreadsheet. 1. Prepare a Working Spreadsheet for Dom's Baseball Equipment, Inc.

image text in transcribed Put into a working excel sheet with formulas that produce each output of the spreadsheet.

1. Prepare a Working Spreadsheet for Dom's Baseball Equipment, Inc. for the Years 2019 and 2020 a. The financial statements are attached. 2. Using the financial statements and your spreadsheet, create the ratios covered in the text for Dom's Baseball Equipment, Inc. for the year 2019 and 2020. 3. Be sure this is a working spreadsheet and that you are not just plugging numbers Dom's Baseball Equipment, Inc. Income Statement 2019 26,400 Preferred Shares Common Shares 528 43,164 Preferred Dividends $50.00 per Preferred Share Common Dividends 20% NICE 203,496 Price/Common Share 2020 $ 72.54 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Earnings Before Interest and Tax Je Interest Expense Earnings Before Tax Tax (21) Net Income $ $ ES $ $ IS $ $ $ $ 5,000,000 3.100.000 1.900,000 820,000 1,080,000 83,000 997,000 209,370 100% 62% 38% 16% 22% 29 20% 4% 787,630 16% Balance Sheet 2019 Cash Accounts Receivables Inventory Prepaid Expenses Total Current Assets land Plant and Equipment Accumulated Depreciation Total Long Term Assets $ $ $ $ $ $ $ $ 5 Assets 176,640 645,600 979,200 120,480 1,921,920 1,136,249 789,936 (144,816) 1.781,369 5% 17% 26% 39 52% 31% 21% Accounts Payable Accruals Notes Payable Total Current Liabilities Debentures Bonds Total Long Term Debt Total Debt Preferred Stock Common Stock Capital Surplus Retained Earnings Total Stockholders Equity Liabilities and Equity $ 523,792 $ 511,264 $ 325,800 $ 1,360,361 $ 351,000 $ 755.040 5 1106.40 $ 2,465,901 $ 52,800 $ 431,640 $ 60,960 $ 690.988 $ 1.285.88 14% 14% 9% 37% 9% 20% 30% 67% 48% 1% 12% 2% 19% 33% Total Assets 3,703,289 100% Total Debt and Equity $ 3,703,289 100% Income Statement 2020 Income Statement 2020 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Earnings Before Interest and Tax Interest Expense Earnings Before Tax Tax (21) Net Income IS $ $ $ $ $ $ $ 5,750,000 3,410,000 2,340,000 934,800 1,405 200 83,830 1,321,370 277,488 1,043,882 100% 59% 41% 16% 24% 1% 23% 5% 18% $ $ $ Balance Sheet 2020 Cash Accounts Receivables Inventory Prepaid Expenses Total Current Assets Land Plant and Equipment Accumulated Depreciation Total Long Term Assets 14% 16% % 8% 39% $ $ S $ $ $ $ $ 5 $ $ Assets 230,000 747,500 1,495,000 172,500 2,645,000 1,782,500 1,207,500 (222,225) 2,767,775 4% 14% 28% 3% 49% 33% 22% 21% 26% Accounts Payable Accruals Notes Payable Total Current Liabilities Debentures Bonds Total Long Term Debt Total Debt Preferred Stock Common Stock Capital Surplus Retained Earnings Total Stockholders Equity Liabilities and Equity $ 523,250 $ 603,750 $ 290.375 $ 1,417,375 $ 760,000 $ 965,000 $ 1.725,000 $ 3,142,375 $ 52,800 $ 431,640 $ 60,960 5 $ 545,400 51% 47% 85% 1% 12% 2% 0% 15% Total Assets $ 5,412,775 100% Total Debt and Equity 5 3,687,775 100% 1. Prepare a Working Spreadsheet for Dom's Baseball Equipment, Inc. for the Years 2019 and 2020 a. The financial statements are attached. 2. Using the financial statements and your spreadsheet, create the ratios covered in the text for Dom's Baseball Equipment, Inc. for the year 2019 and 2020. 3. Be sure this is a working spreadsheet and that you are not just plugging numbers Dom's Baseball Equipment, Inc. Income Statement 2019 26,400 Preferred Shares Common Shares 528 43,164 Preferred Dividends $50.00 per Preferred Share Common Dividends 20% NICE 203,496 Price/Common Share 2020 $ 72.54 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Earnings Before Interest and Tax Je Interest Expense Earnings Before Tax Tax (21) Net Income $ $ ES $ $ IS $ $ $ $ 5,000,000 3.100.000 1.900,000 820,000 1,080,000 83,000 997,000 209,370 100% 62% 38% 16% 22% 29 20% 4% 787,630 16% Balance Sheet 2019 Cash Accounts Receivables Inventory Prepaid Expenses Total Current Assets land Plant and Equipment Accumulated Depreciation Total Long Term Assets $ $ $ $ $ $ $ $ 5 Assets 176,640 645,600 979,200 120,480 1,921,920 1,136,249 789,936 (144,816) 1.781,369 5% 17% 26% 39 52% 31% 21% Accounts Payable Accruals Notes Payable Total Current Liabilities Debentures Bonds Total Long Term Debt Total Debt Preferred Stock Common Stock Capital Surplus Retained Earnings Total Stockholders Equity Liabilities and Equity $ 523,792 $ 511,264 $ 325,800 $ 1,360,361 $ 351,000 $ 755.040 5 1106.40 $ 2,465,901 $ 52,800 $ 431,640 $ 60,960 $ 690.988 $ 1.285.88 14% 14% 9% 37% 9% 20% 30% 67% 48% 1% 12% 2% 19% 33% Total Assets 3,703,289 100% Total Debt and Equity $ 3,703,289 100% Income Statement 2020 Income Statement 2020 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Earnings Before Interest and Tax Interest Expense Earnings Before Tax Tax (21) Net Income IS $ $ $ $ $ $ $ 5,750,000 3,410,000 2,340,000 934,800 1,405 200 83,830 1,321,370 277,488 1,043,882 100% 59% 41% 16% 24% 1% 23% 5% 18% $ $ $ Balance Sheet 2020 Cash Accounts Receivables Inventory Prepaid Expenses Total Current Assets Land Plant and Equipment Accumulated Depreciation Total Long Term Assets 14% 16% % 8% 39% $ $ S $ $ $ $ $ 5 $ $ Assets 230,000 747,500 1,495,000 172,500 2,645,000 1,782,500 1,207,500 (222,225) 2,767,775 4% 14% 28% 3% 49% 33% 22% 21% 26% Accounts Payable Accruals Notes Payable Total Current Liabilities Debentures Bonds Total Long Term Debt Total Debt Preferred Stock Common Stock Capital Surplus Retained Earnings Total Stockholders Equity Liabilities and Equity $ 523,250 $ 603,750 $ 290.375 $ 1,417,375 $ 760,000 $ 965,000 $ 1.725,000 $ 3,142,375 $ 52,800 $ 431,640 $ 60,960 5 $ 545,400 51% 47% 85% 1% 12% 2% 0% 15% Total Assets $ 5,412,775 100% Total Debt and Equity 5 3,687,775 100%