\begin{tabular}{|l|r|r|r|r|r|} \hline Line Items & Year 1 & Year 2 & Year 3 & \multicolumn{1}{l|}{ Year 4 } & \multicolumn{1}{l|}{ Year 5 } \\ \cline { 2 - 7 } & $175,000.00 & $160,000.00 & $190,000.00 & $195,000.00 & $180,000.00 \\ \hline Net Sales & ($125,000.00) & ($110,500.00) & ($130,500.00) & ($135,000.00) & ($115,000.00) \\ \hline Less: Cost of Goods Sold & $50,000.00 & $49,500.00 & $59,500.00 & $60,000.00 & $65,000.00 \\ \hline Lross Margin & ($30,000.00) & ($35,000.00) & ($40,000.00) & ($32,500.00) & ($45,000.00) \\ \hline Less: Operating Expenses & ($8,000.00) & ($7,500.00) & ($10,000.00) & ($9,500.00) & ($9,500.00) \\ \hline Net Income & $12,000.00 & $7,000.00 & $9,500.00 & $18,000.00 & $10,500.00 \\ \hline \end{tabular} Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell references. 2. create absolute cell references to perform calculations efficiently and correctly. 3. apply conditional formatting to highlight cells based on criteria that you specify. statement items relative to overall sales. You also want to highlight cells according to whether the calculated ratio increases or decreases more than a specified amount. Required: Download spreadsheet CommonSizeCaseData-2d62b4.xlsx vertical analysis. Further, you will use conditional formatting to visually differentiate between cells that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH will have no data. you create just one formula in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. I. ='Financial Statement Data'!\$B2/'Financial Statement Data'!B2 II. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B2 III. ='Financial Statement Data'!B2/'Financial Statement Data'!B\$2 IV. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B\$2 correct formula. I. =ABS('Financial Statement Data'!\$B2/'Financial Statement Data'!B2) II. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B2) III. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!B\$2) IV. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B\$2) b. Again, copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places. Enter all percentages as positive numbers. percent, use a yellow fill with dark yellow text. For cells that have a decrease of less than 95 percent, use a light red fill with dark red text. Choose the table with the correct conditional formatting. The correct sheet is 4. Explain the use of the common size financial statements. If you were a manager, which area would require further analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor. \begin{tabular}{|l|r|r|r|r|r|} \hline Line Items & Year 1 & Year 2 & Year 3 & \multicolumn{1}{l|}{ Year 4 } & \multicolumn{1}{l|}{ Year 5 } \\ \cline { 2 - 7 } & $175,000.00 & $160,000.00 & $190,000.00 & $195,000.00 & $180,000.00 \\ \hline Net Sales & ($125,000.00) & ($110,500.00) & ($130,500.00) & ($135,000.00) & ($115,000.00) \\ \hline Less: Cost of Goods Sold & $50,000.00 & $49,500.00 & $59,500.00 & $60,000.00 & $65,000.00 \\ \hline Lross Margin & ($30,000.00) & ($35,000.00) & ($40,000.00) & ($32,500.00) & ($45,000.00) \\ \hline Less: Operating Expenses & ($8,000.00) & ($7,500.00) & ($10,000.00) & ($9,500.00) & ($9,500.00) \\ \hline Net Income & $12,000.00 & $7,000.00 & $9,500.00 & $18,000.00 & $10,500.00 \\ \hline \end{tabular} Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell references. 2. create absolute cell references to perform calculations efficiently and correctly. 3. apply conditional formatting to highlight cells based on criteria that you specify. statement items relative to overall sales. You also want to highlight cells according to whether the calculated ratio increases or decreases more than a specified amount. Required: Download spreadsheet CommonSizeCaseData-2d62b4.xlsx vertical analysis. Further, you will use conditional formatting to visually differentiate between cells that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH will have no data. you create just one formula in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. I. ='Financial Statement Data'!\$B2/'Financial Statement Data'!B2 II. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B2 III. ='Financial Statement Data'!B2/'Financial Statement Data'!B\$2 IV. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B\$2 correct formula. I. =ABS('Financial Statement Data'!\$B2/'Financial Statement Data'!B2) II. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B2) III. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!B\$2) IV. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B\$2) b. Again, copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places. Enter all percentages as positive numbers. percent, use a yellow fill with dark yellow text. For cells that have a decrease of less than 95 percent, use a light red fill with dark red text. Choose the table with the correct conditional formatting. The correct sheet is 4. Explain the use of the common size financial statements. If you were a manager, which area would require further analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor