60.1K

Verified Solution

Link Copied!

Using excel how do you solve for these? If possible, can you show work? This is for BB company included is the income statement, balance sheet, statement of shareholder equity, cashflow statement, ratios, and any aditional info you may need. thank you so so much!!

ratios only!

1. Vertical analysis (common size) of both the Balance Sheets and Income Statements for the years presented. 2. Horizontal analysis of both the Balance Sheets and Income Statements reflecting the dollar and percent change for the years presented. 3. Financial ratios for two years to compare with the industry average. Income Statements 2 3 4 5 Year ended December 31, 2020 2019 2018 1,491,070 998,920 492,150 1,259,445 869,465 389,980 1,156,255 796,195 360,060 242,335 33,065 152,915 428,315 203,405 29,580 93,075 326,060 187,765 21,505 79,900 289,170 6 7 8 Net sales 9 Cost of goods sold 10 Gross profit 11 12 Operating expenses: 13 Salaries and benefits 14 Depreciation and admin expenses 15 Other expenses 16 Total operating expenses 17 18 Non-operating items: 19 Interest expense 20 Interest income 21 Other income 22 Total non-operating items 23 24 Income before taxes 25 Income tax expense 26 Net income 27 28 (15,725) 1,530 14,620 425 (10,880) 3,485 3,145 (4,250) (3,910) 3,230 5,100 4,420 64,260 21,505 42,755 59,670 22,610 37,060 75,310 26,435 48,875 7 20,145 58,225 302,005 6,545 1,190 388,110 9,095 53,125 266,135 4,590 20,825 353,770 160,905 198,305 170,170 32,215 (149,260) 412,335 142,970 191,080 147,390 32,895 (133,195) 381,140 19,465 2,720 819,910 737,630 8 ASSETS 9 Cash 10 Accounts receivable 11 Inventory 12 Prepaid expenses 13 Marketable securities 14 Total current assets 15 16 Land 17 Buildings 18 Fumiture and equipment 19 Vehicles 20 Less accumulated depreciation 21 Total property, plant and equipment 22 23 Other long-term assets 24 25 Total assets 26 27 LIABILITIES AND EQUITY 28 Accounts payable 29 Accrued liabilities payable Income taxes payable 31 Short-term debt 32 Total current liabilities 33 34 Long-term debt 35 36 Total liabilities 37 38 Common stock 39 Paid-in capital in excess of par 40 Retained earings 41 Total stockholders' equity 42 43 Total liabilities and stockholders' equity 124,610 7,820 3,995 53,465 189,890 110,415 8,840 3,315 21,675 144,245 164,135 163,455 354,025 307,700 42,840 223,975 199,070 465,885 36,805 163,625 229,500 429,930 819,910 737,630 Statements of Stockholders' Equity 2 3 4 5 Common Stock 33,745 Paid-in Capital 138,465 Retained Earnings 180,540 48,875 (17,935) Total 352,750 48,875 (17,935) 26,095 2,805 23,290 36,550 161,755 6 7 8 Balance, January 1, 2018 9 Net income 10 Cash dividends ($.52 per share) 11 Common stock issued 12 13 Balance, January 1, 2019 14 Net income 15 Cash dividends ($.52 per share) 16 Common stock issued 17 18 Balance, January 1, 2020 19 Net income 20 Cash dividends ($1.99 per share) 21 Common stock issued 22 Balance, December 31, 2020 211,480 37,060 (19,040) 409,785 37,060 (19,040) 2,125 255 1,870 36,805 163,625 229,500 42,755 (73,185) 429,930 42,755 (73,185) 66,385 465,885 6,035 42,840 60,350 223,975 199,070 23 Year ended December 31, 2020 2019 42,755 16,065 Operating Activities: Net income Depreciation and amortization expense Loss (gain) on sale of assets Decrease increase) in accounts receivable Decrease increase) in inventory Decrease increase) in prepaid expenses Increase (decrease) in accounts payable Increase (decrease) in accrued liabilities Increase (decrease) in income taxes payable Net cash flows from operating activities (5,100) (35,870) (1,955) 14,195 (1,020) 680 29,750 37,060 29,580 (2,635) (8,245) (43,180) (1,530) 12,240 6,375 1,700 31,365 (47,940) 680 (109,565) 22,100 Investing activities: Purchases of property, plant and equipment Sales of property, plant and equipment Purchases of marketable securities Sales of marketable securities Decrease (increase) in other long-term assets Net cash flows from investing activities 19,635 (16,745) (44,370) 3,655 11,220 (72,590) 31,790 680 Financing activities: Increase in short-term debt Repayment of short-term debt Increase in long-term debt Repayment of long-term debt Issuance of common stock Dividends paid Net cash flows from financing activities (15,895) 89,930 (8,755) 2,125 (19,040) 48,365 66,385 (73,185) 25,670 Increase (decrease) in cash Beginning cash balance Ending cash balance 11,050 9,095 20,145 7,140 1,955 9,095 A/R 1 N 3 Common stock has 70,000 shares authorized at $1 par value. 4 There were 36,805 shares issued on December 31, 2019 and 5 42,840 shares issued at December 31, 2020. 6 7. The stock price was $12.75 at the end of 2019 and $11.05 at the end of 2020. 8 9 Balances as of December 31, 2018 for selected accounts were as follows: 10 (use these to calculate your averages for the 2019 ratios) 11 $ 52,275 Inventory $ 263,500 13 Total assets $ 731,000 14 Total equity $ 409,785 15 16 During 2020, the company made the following purchases: 17 Land $ 17,935 18 Building $ 7,225 19 Furniture $ 22,780 20 All of these purchases were made with cash. 21 22 During 2020, the company also sold vehicles (no gain or loss on the sale) 23 for cash of $680. Industry Average 2020 2019 2020 2019 2.30% 3.80% 5.70% 6.50% 9.20% 14.60% n/a n/a 2.10 2.10 0.60 0.70 2.20 2.30 times 5 6 7 8 Net profit margin 9 10 Return on assets 11 12 Return on equity 13 14 Earnings per share 15 16 urrent ratio 17 18 Quick ratio 19 20 Asset turnover 21 22 A/R turnover 23 24 #days sales 25 26 Inventory turnover 27 28 #days inventory 29 30 Debt to egity 31 32 Debt to assets 33 34 Times interest earned 35 36 Price/earnings ratio 37 38 Dividend yield 42.20 43.50 times 8.65 8.39 days 4.80 5.00 times 76.04 73.00 days 0.80 1.00 0.45 0.58 4.70 7.50 times 12.00 11.60 5.10% 4.20% 20

Answer & Explanation Solved by verified expert

See Answer