5/3/2023 .In co refer Pens B. In 2 9. It Highlighters 10 Markers 4 79 22 Crayons 31 Colored 2.29 60 28 Pencils Staples 1.89 Tissues 1.49 22 Hand Sanitizer 16 3. Merge and center cells Al through HI. Format the school name as Title style. 4. Merge and center cells A2 through H2. Format the label as Heading 1 style. 5. Bold the labels in row 4 and right align the labels above the numeric values. 6. Format the Cost/Unit values as Currency with 2 decimal places. Your spreadsheet should look like the figure below. B C D E F G H Any Town Elementary School 1 School Supplies Distribution WIN 4 Classroom Supplies Cost/Unit Sept. Units Sept. Cost % of Grand Total Oct. Units Oct. Cost % of Grand Total $3,99 40 32 5 Copy Paper 12 6 #2 Pencils $0.75 29 22 8 7 Pens $4.00 10 8 Highlighters $3.29 12 35 22 9 Markers $4.79 7 10 Crayons $3.10 15 28 11 Colored Pencils $2.29 60 12 Staples $1.89 12 22 13 Tissues $1.49 $2.00 16 14 Hand Sanitizer 15duction Microsoft Word Microsoft Word Graded Project Microsoft Excel 100% 45 of 45 Topics Completed 100% 8 of 8 Topics Completed 100% 31 of 31 Topics Completed DD 7. In column D, enter appropriate formulas to calculate the September costs for each school supply. Your formulas must use cell eferences. Format the calculated values as Currency with 2 decimal places. 8. In cell A16, enter the label "Grand Total" and format it as bold. 9. In cell D16, enter a formula that uses a function to calculate the total of the column. 10. In column E, enter formulas that calculate the percentage of the grand total for each type of school supply (total cost of each item divided by the grand total). Your formulas must use appropriate absolute cell references. Format the calculated values as Percentage with 0 decimal places. 1 1. In cell E16, enter a formula that uses a function to calculate the total of the column (it should total 100%). 12. Repeat the process you used in steps 7-11 to calculate values for October. 13. Sort the school supplies alphabetically. (Be sure to select cells A5:H14 to sort the entire row of data.) Your spreadsheet should look similar to the figure below. B C D E F G H W 1 Any Town Elementary School 2 School Supplies Distribution S 3 4 Classroom Supplies Cost/Unit Sept. Units Sept. Cost % of Grand Total Oct. Units Oct. Cost % of Grand Total 5 #2 Pencils $0.75 29 $21.75 3% 12 $9.00 2% X 2 6 Colored Pencils $2.29 50 $137.40 18% 28 $64.12 15% 3 7 Copy Paper $3.99 40 $159.60 21% 32 $127.68 30% 8 Crayons $3.10 15 $46.50 6% $21.70 5% $ 9 Hand Sanitizer $2.00 16 $32.00 4% $14.00 3% 4 10 Highlighters $3.29 12 $39.48 5% 10 $32.90 8% 1 11 Markers $4.79 35 $167.65 22% 22 $105.38 25% 12 Pens $4.00 22 $88.00 12% Co $32.00 7% 9 13 Staples $1.89 12 $22.68 3% $7.56 2% hp 10 14 Tissues $1.49 22 $32.78 4% $13.41 3% 15 16 Grand Total $747.84 100% $427.75 100% 14. Save the modified spreadsheet. Create a Chart 1. Create a chart by first selecting the cell ranges for the chart. Drag from cell A4 to A14 to select the range and then press and hold the Ctrl key while you drag from cell D4 to D14. Continue to press and hold the Ctrl key while dragging from cell G4 to G14. Your selected cells should look like the figure below.Microsoft Word Graded Project Microsoft Excel 100% 45 of 45 Topics Completed C D F G H Any Town Elementary School UN School Supplies Distribution 3 4 Classroom Supplies Cost/Unit Sept. Units Sept. Cost % of Grand Total Oct. Units Oct. Cost % of Grand Total 5 #2 Pencils $0.75 29 $21.75 3% 12 $9.00 2% Colored Pencils $2.29 60 $137.40 18% 28 $64.12 15% 7 |Copy Paper $3.99 40 $159.60 21% 32 $127.68 30% 8 Crayons $3.10 15 $46.50 6% 7 $21.70 5% 9 Hand Sanitizer $2.00 16 $32.00 4% 7 $14.00 3%% 10 Highlighters $3.29 12 $39.48 5%% 10 $32.90 8% 11 Markers $4.79 35 $167.65 22% 22 $105.38 25% 12 Pens $4.00 22 $88.00 12%% 8 $32.00 7%% 13 Staples $1.89 12 $22.68 3% 4 $7.56 2% 14 Tissues $1.49 22 $32.78 496 $13.41 15 16 Grand Total $747.84 100% $427.75 100% 2. Insert a 3-D Clustered Column chart. 3. Change the chart title to read "Cost of Supplies." 4. Position the chart with the upper-left corner in cell A18 and then size the chart until it extends to column H, as shown in the figure below. B D E F G 13 Staples $1.89 12 $22.68 $7.56 2% 3% 14 Tissues $1.49 22 $32.78 $13.41 15 100% $427.75 100% 16 Grand Total $747.84 17 18 Cost of Supplies 19 20 21 $180.00 Stap 22 $160.00 Tissu 23 24 $140.00 rand 25 $120.00 26 $100.00 27 28 $80.00 29 $60.00 30 31 $40.00 32 $20.00 33 Pens Tissues $0.00 Staples Markers 34 Crayons Highlighters Copy Paper Hand Sanitizer 35 #2 Pencils Colored Pencil 36 37 Sept. Cost . Oct. Cost 38 39 40