C5 fx A B D E F G H J L M 1 Compute the following ratios. Do not round your calculations. 2 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.) 3 4 2019 2018 5 Working Capital 6 Current Ratio 7 Acid-Test (Quick) Ratio 8 Cash Ratio 9 Accounts Receivable Turnover 10 Days' Sales in Receivables 11 Inventory Turnover 12 Days' Sales in Inventory 13 Gross Profit Percentage 14 Debt Ratio 15 Debt to Equity Ratio 16 Times-Interest-Earned Ratio 17 Profit Margin Ratio 18 Rate of Return on Total Assets 19 Asset Turnover Ratio 20 Rate of Return on Common Stockholders' Equity 21 Earnings per Share (EPS) 22 Price/Earnings Ratio 23 Dividend Yield 24 Dividend Payout 25 26 27 28 HINTS 29 Cell Hint: 30 C5 | Begin the formula with an equals sign (=), use the balance in the horizontal analysis worksheet for your calculations, and press the enter key. 31 07 | Use the function =SUM() to calculate the numerator. 32 09 | =(0.75*"Vertical Analysis'!C8)/(('Horizontal Analysis'!C14+'Horizontal Analysis'!D14)/2) 33 010 Assume 365 days in a year and use the correct ratio in this worksheet for your calculations. 34 35 36 37 E8 fx A B D E F G H | L M . N O P Q R S 1 Perform a vertical analysis on the income statement. 2 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.) 3 4 RIVERSIDE SWEETS 5 Income Statement 6 Year Ended June 30, 2019 7 8 Net Sales $ 2,800,000 9 Cost of Goods Sold 1,551,600 10 Gross Profit 1,248,400 11 Operating Expenses 450,540 12 Operating Income 797,860 13 Other Income and (Expenses) 14 Interest Expense 15,000 15 Income Before Income Taxes 782,860 16 Income Tax Expense 153,529 17 Net Income $ 629,331 18 19 20 21 HINTS 22 1. For cell references, begin the formula with an equals sign (=), use the income statement in this worksheet for your calculations, and press the enter key. 23 24 F12 xv fx K L A B C D G H I J 1 Perform a horizontal analysis on the balance sheets for 2018 and 2019, 2 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.) 3 4 RIVERSIDE SWEETS 5 Balance Sheet 6 June 30, 2018 and 2019 7 8 Increase (Decrease) 9 2019 2018 Amount Percentage 10 Assets 11 Current Assets: 12 Cash $ 125,000 $ 119,000 13 Short-term Investments 685,000 650,000 14 Accounts Receivable 225,000 198,000 15 Merchandise Inventory 65,000 70,000 16 Other Current Assets 195,000 191,000 17 Total Current Assets 1,295,000 1,228,000 18 Property, Plant, and Equipment 875,000 832,000 19 Total Assets $ 2,170,000 $2,060,000 20 21 Liabilities 22 Current Liabilities: 23 Accounts Payable $ 265,000 $ 251,750 24 Accrued Liabilities 641,000 725,523 25 Total Current Liabilities 906,000 977,273 26 Long-term Liabilities 27 Bonds Payable 250,000 150,000 28 Mortgage Payable 150,000 175,000 29 Total Long-term Liabilities 400,000 325,000 30 Total Liabilities 1,306,000 1,302,273 31 Stockholders' Equity 32 Common Stock, $1 par, 225,000 shares issued and outstanding 225,000 225,000 33 Paid-In Capital in Excess of Par 58,000 58,000 34 Retained Earnings 581,000 474,727 35 Total Stockholders' Equity 864,000 757,727 36 Total Liabilities and Stockholders' Equity $ 2,170,000 $2,060,000 37 38 39 40 HINTS 41 1. For cell references, begin the formula with an equals sign (=), use the balance in this worksheet for your calculations, and press the enter key. 42 43 44 45 46 47 48 49 50 51 52 53 54 Instructions ENTERANSWERS1 ENTERANSWERS2 ENTERANSWERS3 + A1 fx Financial Statement Analysis C D E F Financial srutement Analysis 2 Using Excel for financial analysis 3 4 Riverside Sweets, a retail candy store chain, reported the following figures: 5 6 RIVERSIDE SWEETS 7 Balance Sheet 8 June 30, 2018 and 2019 9 10 11 2019 2018 12 Assets 13 Current Assets: 14 Cash $ 125,000 $ 119,000 15 Short-term Investments 685,000 650,000 16 Accounts Receivable 225,000 198,000 17 Merchandise Inventory 65,000 70,000 18 Other Current Assets 195,000 191,000 19 Total Current Assets 1,295,000 1,228,000 20 Property, Plant, and Equipment 875,000 832,000 21 Total Assets $ 2,170,000 $ 2,060,000 22 23 Liabilities 24 Current Liabilities: 25 Accounts Payable $ 265,000 $ 251,750 26 Accrued Liabilities 641,000 725,523 27 Total Current Liabilities 906,000 977,273 28 Long-term Liabilities 29 Bonds Payable 250,000 150,000 30 Mortgage Payable 150,000 175,000 31 Total Long-term Liabilities 400,000 325,000 32 Total Liabilities 1,306,000 1,302,273 33 Stockholders' Equity 34 Common Stock, $1 par, 225,000 shares issued and outstanding 225,000 225,000 35 Paid-In Capital in Excess of Par 58,000 58,000 36 Retained Earnings 581,000 474,727 37 Total Stockholders' Equity 864,000 757,727 38 Total Liabilities and Stockholders' Equity $ 2,170,000 $ 2,060,000 39 40 41 RIVERSIDE SWEETS 42 Income Statement 43 Year Ended June 30, 2019 44 45 Net Sales $ 2,800,000 46 Cost of Goods Sold 1,551,600 47 Gross Profit 1,248,400 48 Operating Expenses 450,540 49 Operating Income 797,860 50 Other Income and (Expenses) 51 Interest Expense 15,000 52 Income Before Income Taxes 782,860 53 Income Tax Expense 153,529 54 Net Income $ 629,331 55 56 57 Additional financial information: 58 a. 75% of net sales are on account. 59 b. Market price of stock is $36 per share on June 30, 2019. 60 c. Annual dividend for 2019 was $1.50 per share. 61 d. All short-term investments are cash equivalents. 62 Use the blue shaded areas on the EN TERANSWERS tab for inputs. ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be 63 marked wrong. All values should be added as positive numbers