Learning outcomes: 1. To introduce the concept of analysis of accounts using ratios. 2. To introduce different categories of ratios 3. To introduce the concept of trends over years. 4. To understand the different ways of expressing ratios (e.g., percentage, times a year, simple ratio) Our Microsoft Excel video tutorials can provide help with completing our Excel Project assignments. Excel Project Steps to Perform Step Instructions 1 Start Excel 2 In cell E45, by using cell references, calculate the gross profit ratio. Copy the formula from cell E45 and paste onto cell G45. In cell E46, by using cell references, calculate the net profit ratio. Copy the formula from cell E46 and paste onto cell G46. In cell E47, by using cell references, calculate the return on shareholders' funds. Copy the formula from cell E47 and paste onto cell G47. Points Possible 0 2 2 2 2 2 Note: Use the profit before tax and total equity in your calculation. 5 In cell E49, by using cell references, calculate the current ratio. Copy the formula from cell E49 and paste onto cell G49. 6 In cell E50, by using cell references, calculate the quick ratio. Copy the formula from cell E50 and paste onto cell G50. In cell E52, by using cell references, calculate the non-current assets turnover. Copy the formula from cell E52 and paste onto cell G52. 7 Note: Use end-of-year, not average, values in your calculation. In cell E53, by using cell references, calculate the inventory turnover. Copy the formula from cell E53 and paste onto cell G53. 2 2 2 Note: Use the cost of sales and end-of-year, not average, values in your calculation In cell E54, by using cell references, calculate the trade receivables turnover. Copy the formula from cell E54 and paste onto cell G54. 9 Note: Use the cost of sales and end-of-year, not average, values in your calculation. 10 In cell E56, by using cell references, calculate the long-term debt to equity ratio. Copy the formula from cell E56 and paste onto cell G56. In cell E57, by using cell references, calculate the earnings per share. Copy the formula from cell E57 and paste onto cell G57. 11 Note: Use end-of-year, not average, values in your calculation. 12 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 2 2 0 F G H AB E Bigwig's Income Statement as at 31.3.17 and 31.3.18 2018 Em Sales Revenue 567,584 Cost of Sales 373,486 Gross Profit 194,098 Expenses 143,258 Operating Profit 50,840 Finance Costs 5,000 Profit before Tax 45,840 Tax at 20% 9,168 Profit after tax 36,672 2017 m 510,619 323,512 187,107 176.139 10,968 10,968 2,194 8,774 2017 m 2017 m 229,430 Bigwig's Statement of Financial position at at 31.3.17 and 31.3.18 2018 2018 Em Em ASSETS Non-current Assets 274,400 Current Assets Inventories 85,450 Trade Receivables 85,300 Cash at Bank 92,007 262,757 Total Assets 537,157 52,345 65,490 43,222 161,057 390,487 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 250,000 62,157 235,000 25,487 312, 157 260,487 CAPITAL AND LIABILITIES Equity Ordinary Share Capital (1 shares) Retained Earnings Long-term Liabilities Long-term Borrowings Current Liabilities Trade Payables Total Capital and Liabilities 80,000 145,000 537.157 130,000 390,487 Additional information: Assume that all sales are credit sales. No dividend is proposed You are required to: calculate the key ratios. 2018 2017 :1 :1 :1 :1 Performance Ratios Gross Profit Ratio Net Profit Ratio Return on Shareholder's Funds Liquidity Ratios Current Ratio Quick Ratio Efficiency Ratios Non-current Assets Turnover Inventory Turnover Trade Receivables Turnover Investment Ratios Gearing (Debt/Equity) Earnings Per Share times times times times times times