3 4 5 6 7 8 9 Merge and center the text in cell Al across columns A through J. Format the text 5 using the Title Cell Style. Type your first and last names into cell A2. Merge and center the text in cell A2 5 across columns A through J. Format the text using the Heading 2 Cell Style. In cell ES create a formula that calculates extended cost (quantity X cost) and use the fill handle to fill down to ES In cell E9 create the best formula to add up cells ES through E8. In cell E10 create the best formula to determine the highest value in cells ES through EX In cell Ell create the best formula to determine the lowest value in cells ES through ES In cell E12 create the best formula to determine the average of cells ES through 2 E8. 10 Format cells ES:E12 as Currency with two decimal places. 11 In cell FS create the best formula to display a percentage that represents each 3 extended cost compared to the extended cost total. 12 In cell F9 create the best formula to add up cells F5:F8. The total should be 100% 13 Format cells F5:F9 as Percent with no decimal places. 14 Apply the Total style to cells E9:19. 15 In cell B9 use COUNTIF to determine how many times the word Blue occurs in 3 the range B5:B8. 16 For row 17, turn on Wrap Text so that the headings display on multiple lines. 2 17 Widget sales in January were 9,000 (cell B18). Widget sales in February were 5 9,900 (cell C18). In cell D18 create a formula that calculates the percentage increase from January to February. Don't be surprised if the result shows zero or some other low number: you will change the format shortly. Fill the formula down to D21. If sales went down the result will show as a negative number. Format D18:D21 as Percent with two decimal places. 18 In cell E22 create the best formula to add up cells E18-E21. Use the fill handle 4 to fill cells F22:122. Apply the Total style to cells E22:122. 19 Widget sales in April are 8,000 (cell F18). We are projecting a 3% increase in sales for each of the next three months (cell E15). In cell G18 create a formula that displays the increased amount (about 8,200). Refer to cells F18 and E15 in your formula. 20 Fill the formula in G18 down to G21. Then select G18:G21 and fill across to 121. If you see any problems or error messages go back to cell GIS to correct your formula. Try typing in 5% into cell E15. Cells G18:121 should update automatically. Use UNDO to revert back to 3%. 21 In cell 118 create line-type Sparklines based on widget sales from March through July. Show the High Point. Fill down to 121. 22 Use cell range A17:C21 to make a clustered column chart (not 3-D) 23 Make sure there's a chart title with the words Past Sales and move the entire chart so that the upper-left corner is in the middle of cell A31. 3 24 Simultaneously select cells A17:A21 and E17:E21 and create a pie chart (not 3-4 D). 25 Explode the blue piece by 20%. 26 For all the pieces in the pie chart, add data labels to the outside end. 27 Move the chart so that the upper-left corner is in the middle of cell H31. 28 In cell C27 build a formula that calculates Leonard's commission based on his sales and the commission rate. Refer to cells B27 and C24 in your formula. Fill down to C30. Make sure that if a new value is typed into cell C24 that cells C27:C30 update automatically. 29 In cell D27 build a formula that awards a 5300 bonus IF Leonard sells more than $20,000. Fill down to D30. 30 In cell E27 add up Leonard's commission and his bonus. Fill down to E30. 31 Format cells B27:E30 as Accounting with no decimal places. 32 Add conditional formatting to cells E27:E30 (Gradient Fill Blue Data Bar). 33 Automatically adjust the widths of columns A through I to the ideal settings. 34 Save and close the file. Exit Excel. Submit as directed. Once you submit the Grader, check your score immediately on the next screen. If your score is very low, please double-check to make sure you are submitting the correct file. If you want to find out where you lost marks, so you can submit again (you have unlimited submissions), click on the three dots ? to the right of the Grader name and select View Submissions