Please answer all questions:

\begin{tabular}{|c|c|c|c|c|c|} \hline & A & B & c & D & F \\ \hline 1 & & \multicolumn{2}{|c|}{ Units Produced and Sold } & \multicolumn{2}{|c|}{ Units Produced and Sold } \\ \hline 2 & & 500,000 & 750,000 & 500,000 & 750,000 \\ \hline 4 & & \multicolumn{2}{|c|}{ Dollar Amounts } & \multicolumn{2}{|c|}{ Cost Percentages } \\ \hline 5 & Revenue & & & & \\ \hline 6 & Cost of goods sold & & & & \\ \hline 7 & Direct materials & & & & \\ \hline 8 & Direct labor & & & & \\ \hline 9 & Overhead & & & & \\ \hline 10 & Indirect plant labor & & & & \\ \hline 11 & Indirect plant supplies & & & & \\ \hline 12 & Machine depreciation & & & & \\ \hline 13 & Plant depreciation & & & & \\ \hline 14 & Plant maintenance & & & & \\ \hline 15 & Plant supervision & & & & \\ \hline 16 & Plant utilities & & & & \\ \hline 17 & Property taxes on plant and equipment & & & & \\ \hline 18 & Total overhead & & & & \\ \hline 19 & Cost of goods sold & & & & \\ \hline 20 & Gross margin & & & & \\ \hline 22 & Cost per unit & & & & \\ \hline 23 & Gross margin per unit & & & & \\ \hline 24 & Total Overhead per Unit & & & & \\ \hline 25 & Direct Cost per Unit & & & & \\ \hline \end{tabular} Required Compute the cost per unit and the gross margin per unit assuming output and sales of: 500,000 units. 750,000 units. Create a visualization based on your analysis. The relevant data has been included within the following file: Click here to access the Assignment 2-74 Student File. Assignment Steps: 1. Download the Assignment 2-74 Student File and open it within Microsoft Excel. 2. Within cell B5 enter the formula =20B2. Copy the formula and paste within cell C5. These formulas calculate revenue by multiplying the $20 estimate of price per unit by the two different levels of units produced and sold. 3. Within cell B7 enter the formula =3B2. This formula multiplies the $3 cost per unit for direct materials with a production volume below 550,000 units by the 500,000 units produced and sold. 4. Within cell C7 enter the formula =(3550,000)+(4.8(C2550,000)). This formula initially multiplies the $3 cost per unit for direct materials by the 550,000 unit production volume for this cost, then adds the $4.80 cost per direct materials by any units produced above the 550,000 unit level. 5. Within cell B8 enter the formula =4.5B2. Copy the formula and paste within cell C8. These formulas calculate direct labor cost by multiplying the $4.50 direct labor cost by the two different levels of units produced and sold 6. Within cell B10 enter the formula =2.25B2. Copy the formula and paste within cell C10. These formulas calculate indirect plant labor cost by multiplying the $2.25 indirect plant labor cost by the two different levels of units produced and sold. 7. Within cell B11 enter the formula =1.25B2. Copy the formula and paste within cell C11. These formulas calculate indirect plant supplies cost by multiplying the $1.25 indirect plant supplies cost by the two different levels of units produced and sold. 8. Fill in the appropriate amounts within the range B12:C17 based on the amounts provided in the Salvay Company Memo. Note that per year figures should be listed as shown, and per shift figures for the 500,000 units produced and sold scenario (as this is below the 600,000 unit threshold) should be listed as shown. Per shift figures for the 750,000 units produced and sold scenario (as this is above the 600,000 unit threshold) require two shifts, and should therefore be multiplied by 2. Lastly, Plant Utilities should be 210,000 for each level of units produced and sold, as neither level exceeds the 800,000 unit threshold. 9. Within cell B18 enter the formula =SUM(B10:B17) to calculate Total Overhead for the 500,000 unit scenario. 10. Within cell B19 enter the formula =B7+B8+B18 to calculate Cost of Goods Sold for the 500,000 unit scenario. 11. Within cell B20 enter the formula = B5 - B19 to calculate Gross Margin for the 500,000 unit scenario. 12. Within cell B22 enter the formula =B19/B2 to calculate Cost per Unit for the 500,000 unit scenario. 13. Within cell B23 enter the formula = B20 / B2 to calculate Gross Margin per Unit for the 500,000 unit scenario. 14. Within cell B24 enter the formula = B18 / B2 to calculate Total Overhead per Unit for the 500,000 unit scenario. 15. Within cell B25 enter the formula =(B7+B8)/B2 to calculate Direct Cost per Unit for the 500,000 unit scenario. 16. Copy the range B18:B25, and paste within the range C18:C25. 17. Within cell E7 enter the formula =B7/$B$19 The dollar signs in front of the column and row references for cell B19 create an absolute cell reference. Absolute formatting ensures that, when copied, a cell reference will be unchanged. This allows the user to copy the formula in cell E7 within a subsequent step. 18. Within cell F7 enter the formula =C7/$C$19 19. Copy the range E7:F7, and paste within both the range E8:F8, and within the range E10:F17. 20. Highlight the range A2:C2, hold the CTRL key, highlight the range A23:C25 (both ranges are now highlighted), choose Insert Column or Bar Chart from the Charts group of the Insert menu, and select Clustered Bar. 21. Click the chart title, and type Unit Costs (Direct and Overhead) and Unit Gross Margin as the new title. 22. Click the background of the chart and drag it so that the top-left corner is located within cell G1. 23. Highlight cell A2, hold the CTRL key, highlight the ranges E2:F2, A7:A8, E7:F8, A10:A17, and E10:F17 (all ranges are now highlighted), choose Insert Column or Bar Chart from the Charts group of the Insert menu, and select Clustered Bar. 24. Click the chart title, and type Percentage of Unit Costs by Cost Component at 500,000 and 750,000 Units of Production as the new title. 25. Click the background of the chart and drag it so that the top-left corner is located within cell G16. 26. Save your progress by choosing Save As from the File menu. You may now answer all questions for Assignment 2-74