WRITE ANSWERS IN FORMULA
AutoSave OFF s u AVERAGE and Financial ratios Q- Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Commen- 2 - 320 ErayO Cell Styles Insert Delete Format Sort & Filter Ideas XV Find & Select M N O P Q R Calibri (Body) 11 AA EE = ov a Wrap Text General Paste BI U DA SE 9 Merge & Center $ 68.90 P % Conditional Format 70 0 020 Formatting as Table Q14 X fx A B C D E F G H I J K L 1 click on the "Ratios" tab and enter your name in Cell C1. If Cell C1 is left blank, you will not be able to see your score. 2 Each yellow cell requires a formula. 3 The formula must only contain cell addresses. 4 Each correct formula will begin with =, +, or - 5 6 The basic mathematical operators are +, - * and/ 7 Addition: To add the values in cells A1 and B4, the formula is =A1+B4 8 Subtraction: To subtract the value in cell B4 from the value in cell A1, the formula is =A1-B4 9 Multiplication: To multiply the values in cells A1 and B4, the formula is =A1*B4 10 Division: To divide the value in cell A1 by the value in cell B4, the formula is =A1/B4 11 12 Using the AVERAGE function 13 The AVERAGE function has the following syntax: =AVERAGE(Number 1, Number 2...) 14 15 Order of operations 16 Remember the acronym PEMDAS when creating your formulas. Parentheses Exponents Multiplication Division Addition Subtraction 17 When calculating a fraction, make sure to put parentheses around any formula used in either the numerator or denominator. 18 For example =(B1+B2)/(C1+C2) 19 20 Using absolute addresses Once a formula is created, you will often want to copy the formula to other columns and rows. It's important to understand that excel always uses "relative addresses" unless you indicate otherwise by creating an "absolute address". For example, if you are calculating the cost of purchasing 2 boxes of cereal at a cost of $3.95 per box, the formula in cell B28 is =B26*B27. Although we read cell B28 as "Multiply 2 boxes by $3.95", Excel is actually calculating the value in cell B28 as "Multiply the value in the cell in the same column (column B) 2 rows above (row 26) by the value in the cell in the same column 1 row above (row 27). 25 26 Boxes of cereal 15 27 Price per box $3.95 28 Total cost $7.90 $23.70 $43.45 $59.25 11 29 30 This works just fine to calculate the cost of 2 boxes, but if we copy the formula in cell B28 to C28, Excel will assume relative references. The formula in cell 31 C28 will become =C26*c27. Since Cell C27 is blank, the formula will return a value of $0 (which is an excellent deal for 6 boxes of cereal, but it isn't accurate!) AutoSave OFF A svo - AVERAGE and Financial ratios Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments ab Wrap Text AS General HII - DV 3 Merge & Center $ % ) to .90 Insert Conditional Format Cell Formatting as Table Styles Delete Format Ideas DX Calibri (Body) 11 AA EE = YOU BIU PA E xv fx A B C D E F Sort & Filter Find & Select Q14 G H I J K L M N O P Q R S 15 25 26 Boxes of cereal 6 11 27 Price per box $3.95 28 Total cost $7.90 $23.70 $43.45 $59.25 29 30 This works just fine to calculate the cost of 2 boxes, but if we copy the formula in cell B28 to C28, Excel will assume relative references. The formula in cell 31 C28 will become =C26*C27. Since Cell C27 is blank, the formula will return a value of $0 (which is an excellent deal for 6 boxes of cereal, but it isn't accurate!) 32 Adding a $sign before the column or row reference locks the formula to a specific location. 33 34 35 If we modify the formula in cell B28 to =B26*$B$27 before we copy the formula, Excel will not change the row or the column of the second variable. When 36 the formula is copied to columns C through E, the formulas become: 37 Cell C28 =C26*$B$27 39 Cell D28 =D26*$B$27 40 Cell E28 =E26*$B$27 41 42 Sometimes, you will want the column to stay fixed, but allow the row to remain relative when it's copied. If so, add a dollar sign before the column, but not 43 the row (i.e.$B27) If you want the row to stay fixed, but allow the column to remain relative when it's copied, add a dollar sign before the row, but not the 44 column (.e. B$27). Tapping the F4 button while entering the formula (or in the formula bar) toggles among the 3 absolute options (Absolute Column and 45 Absolute Row $B$28, Absolute Column and Relative Row $B28, Relative Column and Absolute Row B$28) 46 47 50 AutoSave OFF A sro - AVERAGE and Financial ratios Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments 11 A A = = 0 General = E a Wrap Text Merge & Center v -- 02-D AEB Conditional Format Cell 28" O S Paste D- A $ % 9 8 .00 Insert Delete Format Sort & Filter Ideas Formatting as Table Styles X Find & Select B7 Calibri (Body) BI U xv fx | B Name: A D E H GN GOG GQGR GSGT GU GV GW GX C Mariane Rishmawi F Your score: G 0% The Mariane Rishmawi Company began operations in 2020. Its comparative income statements and balance sheets follow. Use a formula to calculate the ratios below. The AVERAGE formula must be used in any ratios using average accounts receivable, average inventory or average total assets. 9 10 11 12 Net sales Cost of goods sold Gross margin Operating expenses Net income Mariane Rishmawi Company Comparative Income Statements For Years Ended December 31, 2023-2020 2023 2022 2021 $256,000 $228,000 $199,000 132,000 165,000 122.000 124,000 63,000 77,000 56,000 28,000 35,000 $68,000 $35,000 $42.000 2020 $141,000 108,000 33,000 15,000 $18,000 13 14 Cash Accounts receivable, net Merchandise inventory Plant assets, net Total assets Mariane Rishmawi Company Comparative Balance Sheets For Years Ended December 31, 2023-2020 2023 2022 2021 $112,000 $77,000 $76,000 72,000 64,000 56,000 59,000 74,000 55,000 170,000 160,000 130,000 $413,000 $375,000 $317,000 2020 $82,000 39,000 49,000 130,000 $300,000 25 26 27 28 29 30 Current liabilities Long-term liabilities Common stock Retained earnings Total liabilities and equity $40,000 160,000 50,000 163,000 $413,000 $50,000 180,000 50,000 95,000 $375,000 $37,000 170,000 50,000 60,000 $317,000 $32,000 200,000 50,000 18,000 $300,000 31 38 2023 2022 2021 40 41 42 Liquidity and Efficiency Current ratio Acid-test ratio Accounts receivable turnover Inventory turnover Total asset turnover Solvency 43 44 2023 2022 AutoSave OFF A sro - AVERAGE and Financial ratios Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments LD[B 11 A A = = 0 General = E a Wrap Text Merge & Center v -- 02-D AEB Conditional Format Cell 28" O S Paste Calibri (Body) BI U xv fx| D- A $ % 9 8 .00 Insert Delete Format Ideas Sort & Filter Formatting as Table Styles X Find & Select B7 G H GN GOGPGQGR GSGT GU GV GW GX Name: Mariane Rishmawi Your score: 0% Cash Accounts receivable, net Merchandise inventory Plant assets, net Total assets Mariane Rishmawi Company Comparative Balance Sheets For Years Ended December 31, 2023-2020 2023 2022 2021 $112,000 $77,000 $76,000 72,000 64,000 56,000 59,000 74,000 55,000 170,000 160,000 130,000 $413,000 $375,000 $317,000 2020 $82,000 39,000 49,000 130,000 $300,000 22 23 24 25 26 27 28 29 Current liabilities Long-term liabilities Common stock Retained earnings Total liabilities and equity $40,000 160,000 50,000 163,000 $413,000 $50,000 180,000 50,000 95,000 $375,000 $37,000 170,000 50,000 60,000 $317,000 $32,000 200,000 50,000 18,000 $300,000 30 31 36 37 38 2023 2022 2021 39 40 41 43 44 45 2023 2022 2021 Liquidity and Efficiency Current ratio Acid-test ratio Accounts receivable turnover Inventory turnover Total asset turnover Solvency Debt ratio Equity ratio Sum of Debt and Equity ratios Debt-to-equity ratio Profitability Profit margin ratio Gross margin ratio Return on total assets 46 49 2023 2022 2021 50 52
