1 Financial Statement Analysis 2 Using Excel for financial analysis D E 3 Riverside Sweets, a retail candy store chain, reported the following figures: 5 RIVERSIDE SWEETS Balance Sheet June 30, 2018 and 2019 2019 2018 Assets Current Assets: Cash Short-term Investments Accounts Receivable Merchandise Inventory Other Current Assets Total Current Assets Property, Plant, and Equipment Total Assets $ 125,000 $ 119,000 685,000 650,000 225,000 198,000 65,000 70,000 195,000 191,000 1,295,000 1,228,000 875,000 832,000 S.M.2,170,000 $2,060,000 Current Liabilities: Liabilities $ 265,000 $ 641,000 906,000 251,750 725,523 977,273 Liabilities Current Liabilities: Accounts Payable Accrued Liabilities Total Current Liabilities Long-term Liabilities Bonds Payable Mortgage Payable Total Long-term Liabilities Total Liabilities Stockholders' Equity Common Stock, $1 par, 225,000 shares issued and outstanding Paid-In Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 250,000 150,000 400,000 1,306,000 150,000 175,000 325,000 1,302,273 225,000 225,000 58,000 58,000 581,000 474,727 864,000 757,727 $ 2,170,000 $ 2,060,000 RIVERSIDE SWEETS Income Statement Year Ended June 30, 2019 Net Sales Cost of Goods Sold $ 2.800,000 1.552.600 D E 1,248,400 450,540 797,860 47 Gross Profit 48 Operating Expenses 49 Operating Income 50 Other Income and (Expenses) 51 Interest Expense 52 Income Before Income Taxes 53 Income Tax Expense 54 Net Income 55 6 7 Additional financial information: 3 a. 75% of net sales are on account. b. Market price of stock is $36 per share on June 30, 2019. c. Annual dividend for 2019 was $1.50 per share. d. All short-term investments are cash equivalents. 15,000 782,860 153,529 629,331 $ E SWEETS Balance Sheet June 30, 2018 and 2019 7 8 9 2019 2018 Increase (Decrea Amount Percen 10 Assets 11 Current Assets: 12 Cash 13 Short-term Investments 14 Accounts Receivable 15 Merchandise Inventory 16 Other Current Assets 17 Total Current Assets 18 Property, Plant, and Equipment 19 Total Assets 20 21 Liabilities 22 Current Liabilities: 23 Accounts Payable 24 Accrued Liabilities 25 Total Current Liabilities 26 Long-term Liabilities 27 Bonds Payable 28 Mortgage Payable 29 Total Long-term Liabilities 30 Total Liabilities 31 Stockholders' Equity 125,000 $ 119,000 685,000 650,000 225,000 199,000 65,000 70,000 195,000 191.000 1,295,000 1.228,000 875,000 832.000 $ 2,170,000 $2,060,000 S 265,000 $ 641.000 906,000 251,750 725.523 977,273 250,000 150,000 400,000 1,306,000 150,000 175,000 325,000 1,302,273 32 common stock, $1 par, 225,000 shares issued and outstanding 39 Paid-In Capital in Excess of Par 34 Retained Earnings 35 Total Stockholders' Equity 36 Total Liabilities and Stockholders' Equity 225,000 225.000 58,000 58,000 581,000 474,727 864,000 757.727 $ 2,170,000 $2,060,000 37 38 39 5 RIVERSIDE SWEETS Income Statement Year Ended June 30, 2019 6 7 8 9 10 11 12 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Operating Income Other Income and (Expenses) Interest Expense Income Before Income Taxes Income Tax Expense Net Income $ 2,800,000 1,551,600 1,248,400 450,540 797,860 13 14 15 16 15,000 782,860 153,529 $ 629,331 17 18 10 alas where appropriate to receive full credit. If you copy/paste from tt 2019 2018 orking Capital Trent Ratio nd-Test (Quick) Ratio sh Ratio ecounts Receivable Turnover ays' Sales in Receivables ventory Turnover ays' Sales in Inventory Toss Profit Percentage ebt Ratio ebt to Equity Ratio imes-Interest-Earned Ratio rofit Margin Ratio ate of Return on Total Assets sset Turnover Ratio Rate of Return on Common Stockholders' Equity Earnings per Share (EPS) Price/Earnings Ratio Dividend Yield Dividend Payout HINTS to the balance in the horizontal analysis worksheet for your calculati 1 Financial Statement Analysis 2 Using Excel for financial analysis D E 3 Riverside Sweets, a retail candy store chain, reported the following figures: 5 RIVERSIDE SWEETS Balance Sheet June 30, 2018 and 2019 2019 2018 Assets Current Assets: Cash Short-term Investments Accounts Receivable Merchandise Inventory Other Current Assets Total Current Assets Property, Plant, and Equipment Total Assets $ 125,000 $ 119,000 685,000 650,000 225,000 198,000 65,000 70,000 195,000 191,000 1,295,000 1,228,000 875,000 832,000 S.M.2,170,000 $2,060,000 Current Liabilities: Liabilities $ 265,000 $ 641,000 906,000 251,750 725,523 977,273 Liabilities Current Liabilities: Accounts Payable Accrued Liabilities Total Current Liabilities Long-term Liabilities Bonds Payable Mortgage Payable Total Long-term Liabilities Total Liabilities Stockholders' Equity Common Stock, $1 par, 225,000 shares issued and outstanding Paid-In Capital in Excess of Par Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 250,000 150,000 400,000 1,306,000 150,000 175,000 325,000 1,302,273 225,000 225,000 58,000 58,000 581,000 474,727 864,000 757,727 $ 2,170,000 $ 2,060,000 RIVERSIDE SWEETS Income Statement Year Ended June 30, 2019 Net Sales Cost of Goods Sold $ 2.800,000 1.552.600 D E 1,248,400 450,540 797,860 47 Gross Profit 48 Operating Expenses 49 Operating Income 50 Other Income and (Expenses) 51 Interest Expense 52 Income Before Income Taxes 53 Income Tax Expense 54 Net Income 55 6 7 Additional financial information: 3 a. 75% of net sales are on account. b. Market price of stock is $36 per share on June 30, 2019. c. Annual dividend for 2019 was $1.50 per share. d. All short-term investments are cash equivalents. 15,000 782,860 153,529 629,331 $ E SWEETS Balance Sheet June 30, 2018 and 2019 7 8 9 2019 2018 Increase (Decrea Amount Percen 10 Assets 11 Current Assets: 12 Cash 13 Short-term Investments 14 Accounts Receivable 15 Merchandise Inventory 16 Other Current Assets 17 Total Current Assets 18 Property, Plant, and Equipment 19 Total Assets 20 21 Liabilities 22 Current Liabilities: 23 Accounts Payable 24 Accrued Liabilities 25 Total Current Liabilities 26 Long-term Liabilities 27 Bonds Payable 28 Mortgage Payable 29 Total Long-term Liabilities 30 Total Liabilities 31 Stockholders' Equity 125,000 $ 119,000 685,000 650,000 225,000 199,000 65,000 70,000 195,000 191.000 1,295,000 1.228,000 875,000 832.000 $ 2,170,000 $2,060,000 S 265,000 $ 641.000 906,000 251,750 725.523 977,273 250,000 150,000 400,000 1,306,000 150,000 175,000 325,000 1,302,273 32 common stock, $1 par, 225,000 shares issued and outstanding 39 Paid-In Capital in Excess of Par 34 Retained Earnings 35 Total Stockholders' Equity 36 Total Liabilities and Stockholders' Equity 225,000 225.000 58,000 58,000 581,000 474,727 864,000 757.727 $ 2,170,000 $2,060,000 37 38 39 5 RIVERSIDE SWEETS Income Statement Year Ended June 30, 2019 6 7 8 9 10 11 12 Net Sales Cost of Goods Sold Gross Profit Operating Expenses Operating Income Other Income and (Expenses) Interest Expense Income Before Income Taxes Income Tax Expense Net Income $ 2,800,000 1,551,600 1,248,400 450,540 797,860 13 14 15 16 15,000 782,860 153,529 $ 629,331 17 18 10 alas where appropriate to receive full credit. If you copy/paste from tt 2019 2018 orking Capital Trent Ratio nd-Test (Quick) Ratio sh Ratio ecounts Receivable Turnover ays' Sales in Receivables ventory Turnover ays' Sales in Inventory Toss Profit Percentage ebt Ratio ebt to Equity Ratio imes-Interest-Earned Ratio rofit Margin Ratio ate of Return on Total Assets sset Turnover Ratio Rate of Return on Common Stockholders' Equity Earnings per Share (EPS) Price/Earnings Ratio Dividend Yield Dividend Payout HINTS to the balance in the horizontal analysis worksheet for your calculati