The deremiah Johnson Company began operations in 2024. Its comparative income statements and balanoe sheets follow. Lise a formula to oaloulate the ratios below. The AVERAGE formula must be used in any ratios using average acoounts receivable, average inventory or average total assets. \begin{tabular}{lrrrr} Current liabilities & $38,000 & $41,000 & $32,000 & $28,0 \\ Long-term liabilities & 100,000 & 110,000 & 100,000 & 120,0 \\ Common stock & 30,000 & 30,000 & 30,000 & 30,00 \\ Retained earnings & 168,000 & 106,000 & 63,000 & 20,00 \\ Total liabilities and equity & $336,000 & $287,000 & $225,000 & $198,00 \\ \cline { 2 - 5 } & & & & \end{tabular} Applying Excel 11: Complete the Excel flle - AVERAGE and Financlal Ratios - Download and save the file. - Click Enable Edung" at the top. - Read the information on the instructions tab - Cick on the Ratios tab and enter your name in cell C1. - Each yellow cell requires a formula - The AVERAGE function must be used to calculate Accounts recelvable tumovet, Inventory turnover, Total asset tumover, and Retum on total asse - Incorrect answers will appear in red. - Save the file again. - Upload the file to your instructot Cliok 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. Each yellow cellrequires a formuls: The formula must only contan cell addresses. Each correot formula will beginith =,+, or The basio mathematicaloperators are + and i Addion. To add the values in oells A1 and B4, the formula is =A1+BA Subtraction. To subtract the value in cell B4 from the value in cell A1, the formuls is =AB4. Multiplioation: To multiply the values in oells A1 and B4, the formula is =A1 B B Division To divide the value in cell A1bythe value in cell B4, the formula is =A1B4 Using the AVERAGE funotion The AVERAGEfunction has the folloning syin = AVERAGEINumber 1. Number 2.) Order of operations Femember the soronym PEMDAS when cieating your formulas. Parenheses Exponen Mulhplication Dwsion Addition Subtraction When oalculating a fraction, make sure to put pacentheses around any tormula used in either the nurnector or denominator For enamble =BBi+B2MC(CHC2) Using absolute addresses you are calculating the cost of purchasing 2 boses of cereal at a cost of 1395 per bou the formula in cell B20 is = B266 927 Although we read oell B28 w: "Mutiply 2 bonerhy $3.95". Excelis aotually oulculating the volue in oell B28 ss Multiply the value in the cellin the same column (column B) Hows above liow 26 ) by the value in the oell in the same oolumn 1 rom above This works iust fine to caloulate the cost of 2 boses. but if we oopy the formula in oell B26 to C28, Excel will assume relative references. The formula in oell C28 will beoome =C266C27. Since Cell C27 is blank, the formula will return a value of $0 (which is an escellent deal for 6 bones of cereal, butit isrit aocuratel) Adding a s sign before the oolumn on row reference locks the formula to a specific location. If we modify the formula in cell B28 to =826$6$27 before we copy the formula. Excel will not ohange the row or the column of the second variable. When the formula is copied to colurnns C through E, the formulas become: CellC28=C26$5$27CellD28=D26$B427CellE28=E26$6$27 Sometimes, you wil want the polumn to stay fixed, but allow the row to temain telative when it s fopied. If so, add a dollar sign before the column, but not the row (..e.\$B27) If you want the row to stay fixed. but allow the column to temain relotive when it's copied, add a dollar sign before the row, but not the column (i. E\$27). Tapping the F4 button while entering the formula for in the formula bar) toggles among the 3 absolute options (Absolute Column and Absolute Poy $3$28. Absolute Colump and Relative Rov \$B28, Relative Column and Absolute Rov B\$28) The deremiah Johnson Company began operations in 2024. Its comparative income statements and balanoe sheets follow. Lise a formula to oaloulate the ratios below. The AVERAGE formula must be used in any ratios using average acoounts receivable, average inventory or average total assets. \begin{tabular}{lrrrr} Current liabilities & $38,000 & $41,000 & $32,000 & $28,0 \\ Long-term liabilities & 100,000 & 110,000 & 100,000 & 120,0 \\ Common stock & 30,000 & 30,000 & 30,000 & 30,00 \\ Retained earnings & 168,000 & 106,000 & 63,000 & 20,00 \\ Total liabilities and equity & $336,000 & $287,000 & $225,000 & $198,00 \\ \cline { 2 - 5 } & & & & \end{tabular} Applying Excel 11: Complete the Excel flle - AVERAGE and Financlal Ratios - Download and save the file. - Click Enable Edung" at the top. - Read the information on the instructions tab - Cick on the Ratios tab and enter your name in cell C1. - Each yellow cell requires a formula - The AVERAGE function must be used to calculate Accounts recelvable tumovet, Inventory turnover, Total asset tumover, and Retum on total asse - Incorrect answers will appear in red. - Save the file again. - Upload the file to your instructot Cliok 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. Each yellow cellrequires a formuls: The formula must only contan cell addresses. Each correot formula will beginith =,+, or The basio mathematicaloperators are + and i Addion. To add the values in oells A1 and B4, the formula is =A1+BA Subtraction. To subtract the value in cell B4 from the value in cell A1, the formuls is =AB4. Multiplioation: To multiply the values in oells A1 and B4, the formula is =A1 B B Division To divide the value in cell A1bythe value in cell B4, the formula is =A1B4 Using the AVERAGE funotion The AVERAGEfunction has the folloning syin = AVERAGEINumber 1. Number 2.) Order of operations Femember the soronym PEMDAS when cieating your formulas. Parenheses Exponen Mulhplication Dwsion Addition Subtraction When oalculating a fraction, make sure to put pacentheses around any tormula used in either the nurnector or denominator For enamble =BBi+B2MC(CHC2) Using absolute addresses you are calculating the cost of purchasing 2 boses of cereal at a cost of 1395 per bou the formula in cell B20 is = B266 927 Although we read oell B28 w: "Mutiply 2 bonerhy $3.95". Excelis aotually oulculating the volue in oell B28 ss Multiply the value in the cellin the same column (column B) Hows above liow 26 ) by the value in the oell in the same oolumn 1 rom above This works iust fine to caloulate the cost of 2 boses. but if we oopy the formula in oell B26 to C28, Excel will assume relative references. The formula in oell C28 will beoome =C266C27. Since Cell C27 is blank, the formula will return a value of $0 (which is an escellent deal for 6 bones of cereal, butit isrit aocuratel) Adding a s sign before the oolumn on row reference locks the formula to a specific location. If we modify the formula in cell B28 to =826$6$27 before we copy the formula. Excel will not ohange the row or the column of the second variable. When the formula is copied to colurnns C through E, the formulas become: CellC28=C26$5$27CellD28=D26$B427CellE28=E26$6$27 Sometimes, you wil want the polumn to stay fixed, but allow the row to temain telative when it s fopied. If so, add a dollar sign before the column, but not the row (..e.\$B27) If you want the row to stay fixed. but allow the column to temain relotive when it's copied, add a dollar sign before the row, but not the column (i. E\$27). Tapping the F4 button while entering the formula for in the formula bar) toggles among the 3 absolute options (Absolute Column and Absolute Poy $3$28. Absolute Colump and Relative Rov \$B28, Relative Column and Absolute Rov B\$28)