Grader - Instructions Excel 2016 Project EX16_XL_VOL1_GRADER_CAP_HW - Software Training Books 1.8 Project Description: You are a vice president for a publisher of software training books. Your division publishes three series that focus on Microsoft Office and Windows. You want to analyze the sales data and calculate author royalties. You will format the worksheet, insert formulas and functions to perform calculations, sort and filter data to review specific book sales, and prepare a chart that compares sales by series. Steps to Perform: Step 1 2 3 6 7 Points Instructions Possible Start Excel, Download and open the file named exploring cap grader bl.a . O On the Data worksheet, select the range A6 K6, wrap the text, and apply Center 3 alignment. Change the row height to 30 for row 6. In cell F7 in the Data worksheet, insert a formula that calculates the percentage 4 of books returned based on the number of books returned and the quantity sold Copy the formula from cell F7 to the range FXF22 In cell H7 in the Data worksheet, insert a formula that calculates the net sales 4 This monetary amount reflects the number of books not returned and the unit price. Copy the formula from cell H7 to the range HS:H22. In cell 17 in the Data worksheet, insert a formula that calculates the amount of 4 the first author's royalties. An author's royalties are based on the Royalty Rate located in the Input Arca and the respective Net Sales. Copy the formula from cell 17 to the range 18:122 In cell K7 in the Data worksheet, insert a formula that adds the first author's 4 royalty amount to the bonus. Copy the formula from cell K7 to the range K8:K22 In cell J2 in the Data worksheet, insert a function to calculate the average net 6 sales. In cell 3 insert a function to calculate the highest net sales. In cell 4 insert a function to calculate the lowest net sales Select the range LI N2 in the Data worksheet, copy the selected data and transpose the data when pasting it to cell A2. Delete the data in the range LIEN Click cell C6 in the Data worksheet and insert a column. Type Series Name in S cell C6. Click cell C7 in the Data worksheet and insert a lookup function that identifies the series code, compares it to the series legend, and then returns the name of the series. Copy the function you entered from cell C7 to the range C8-C22. Change the width of column C to 18 Click cell K7 in the Data worksheet and replace the current contents with an IFS function that compares the percent returned for the first book to the return rate in the Input Area. If the percent returned is less than the return rate, the result is $500. Otherwise, the author receives no bonus. The only value you may type directly in the function is where needed. Copy the function you entered from cell K7 to the range K8-K22 Select the range G7 G22 in the Data worksheet and apply the Percent Style 6 format with one decimal place. Select the range K7 K22 and apply the Accounting Number Format. Merge and center the label Series Led in the range AI:C1 in the Data worksheet. Apply Thick Outside Borders to the range Al C4 8 10 11 Note, the border type may be Thick Box Border, depending on the version of Office used 12 Select Landscape orientation, adjust the scaling so that the data fins on one page 4 and set 0.1 left and right margins for the Data worksheet. 13 Click the Sales sheet tab, convert the data to a table, and apply Table Style Light 4 14 Sort the data by Series Name in alphabetical order and then within Series Name, 4 sort by Net Sales from largest to smallest. 15 Add a total row to display the sum of the Net Sales column. Change the column 4 width to 14 for the Net Sales column. 16 Select the values in the Percent Returned column and apply conditional 3 formatting to apply Light Red Fill with Dark Red Text for values that are greater than 9.9% 17 Select the values in the Net Sales column and apply a filter to display only net 4 sales that are less than $100,000. 18 Click the Net Sales sheet tab, select the range A3:D7, and create a clustered 4 column chart. 19 Move the chart so that the top-left corner is positioned inside cell A9. Change 3 the chart width to 4.66 inches and the chart height to 2.9 inches. 20 Link the chart title to cell Al. Format the value axis to display whole numbers 2 only. 21 Format the chart title, value axis, category axis, and legend with Black, Text 3 font color. 22 Select the Series Sales tab, select the ranges A4:A7 and C4:C7 and create a pie 5 chart. Move the pie chart to a chart sheet named Office Reference. Move the Office Reference chart sheet to the right of the Series Sales sheet. 23 Change the chart title to Office Reference Series, 24 Apply the Style 12 chart style and change the colors to Colorful Palette 4. Apply 4 bold and change the font size to 18 for the chart title. 25 Display data labels in the Inside End position Display Percentage data labels: 6 remove the Value data labels. With the Series 1 Data Labels selected, apply bold, change the font size to 18, and then apply White, Background i font color. 26 Ensure that the worksheets are correctly named and placed in the following 0 order in the workbook: Data, Sales, Net Sales, Series Sales, Office Reference. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Total Points 100 1 EX16_XL_VOLI_GRADER_CAP_HW - Software Training Books 1.8 Data Sales Net Sales Series Sales Series Legend Input Area Royalty Rail Retum Rate CE ORTB CertificatioOffice Relo True Beginner 500 Author Royal Bonus Author Karnings Quantity Sol No. Books Percent Re UnitPrice Net Sales 1995 1.847 39.95 11,841 49,95 Yang CE OR 1042 3,44 19.95 50 N014 R14 49.95 Author Series Ced Software LON Word 2016 Krupin TB Word 2016 Cole Word 2016 Insel 2016 Tremblay TB For 2016 Tams Freel 2016 Martin AccessNI16 Access 2016 Daniels CE Access N16 PowerPoint 2016 PowerPoint 2016 Kumar CE PowerPoint 2016 BartolottiTB Outlook 2016 Wallace OR Windows 10 Toute TR Windows 10 Coleman CE Windows 10 415 14.49 500 1.971 1.279 120 Om ws 49.95 Soo 34449 500 500 175 2.507 1,884 14.750 N. 142 6.124 1.839 19.95 500 34,49 Data Sales Net Sales Series Sales Series Name Software Percent Returned Unit Price Net Sales 499 Author LON Krupin Cole Yeung Tremblay Torres Martin Allera Daniels 39.95 49.95 39.95 49.95 Office Reference The Beginner Certification Series Office Reference True Beginner Certification Series Ollice Reference True Beginner Certification Series Office Reference True Beginner Certification Series Tre Beginner Office Reference The Beginner Certification Series Word 2016 Word 2016 Word 2016 Excel 2016 Excel 2016 Excel 2016 Access 2016 Access 2016 Access 2016 PowerPoint 2016 PowerPoint 2016 PowerPoint 2016 Outlook 2016 Windows 10 Windows 10 Windows 10 5.8% 14.7% 14.9% 8.8% 10.1% 1.9% 10.3% 9.7% 10.4% 94 17.5% 75% 9.5% 12.5 9.6% 12.14 403,795.8 19.400 91.245.8 519.410.05 255.540 324.234.2 361,138.5 91.980 122 N77 57,892.05 21.650 0,016, 42.725 644,904.45 18.475 650 67 34.49 49.95 34.49 Wong Kumar Bantaloni Wallace Toulou Coleman 4995 3449 Data Sales Net Sales Series Sales Net Sales Software Access 2016 Excel 2016 PowerPoint 2016 Wend 2016 Certification Series Office Reference 122.887.87 361.138.5 524 234.2 $59.410.05 80,016,8 57.892.05 91,245,8 403,795.8 True Beginner 91.980 235 540 21.650 39,400 DataSales Net Sales Series Sales Net Sales Software Excel 2016 Word 2016 Access 2016 PowerPoint 2016 Certification Series 124.244. 2 91.1458 122,887,87 80,016,8 Office Reference 5 19,410.05 403.795.8 361.138.5 57,192.05 True Beginner 235 540 39. 400 91.980 21.650 Grader - Instructions Excel 2016 Project EX16_XL_VOL1_GRADER_CAP_HW - Software Training Books 1.8 Project Description: You are a vice president for a publisher of software training books. Your division publishes three series that focus on Microsoft Office and Windows. You want to analyze the sales data and calculate author royalties. You will format the worksheet, insert formulas and functions to perform calculations, sort and filter data to review specific book sales, and prepare a chart that compares sales by series. Steps to Perform: Step 1 2 3 6 7 Points Instructions Possible Start Excel, Download and open the file named exploring cap grader bl.a . O On the Data worksheet, select the range A6 K6, wrap the text, and apply Center 3 alignment. Change the row height to 30 for row 6. In cell F7 in the Data worksheet, insert a formula that calculates the percentage 4 of books returned based on the number of books returned and the quantity sold Copy the formula from cell F7 to the range FXF22 In cell H7 in the Data worksheet, insert a formula that calculates the net sales 4 This monetary amount reflects the number of books not returned and the unit price. Copy the formula from cell H7 to the range HS:H22. In cell 17 in the Data worksheet, insert a formula that calculates the amount of 4 the first author's royalties. An author's royalties are based on the Royalty Rate located in the Input Arca and the respective Net Sales. Copy the formula from cell 17 to the range 18:122 In cell K7 in the Data worksheet, insert a formula that adds the first author's 4 royalty amount to the bonus. Copy the formula from cell K7 to the range K8:K22 In cell J2 in the Data worksheet, insert a function to calculate the average net 6 sales. In cell 3 insert a function to calculate the highest net sales. In cell 4 insert a function to calculate the lowest net sales Select the range LI N2 in the Data worksheet, copy the selected data and transpose the data when pasting it to cell A2. Delete the data in the range LIEN Click cell C6 in the Data worksheet and insert a column. Type Series Name in S cell C6. Click cell C7 in the Data worksheet and insert a lookup function that identifies the series code, compares it to the series legend, and then returns the name of the series. Copy the function you entered from cell C7 to the range C8-C22. Change the width of column C to 18 Click cell K7 in the Data worksheet and replace the current contents with an IFS function that compares the percent returned for the first book to the return rate in the Input Area. If the percent returned is less than the return rate, the result is $500. Otherwise, the author receives no bonus. The only value you may type directly in the function is where needed. Copy the function you entered from cell K7 to the range K8-K22 Select the range G7 G22 in the Data worksheet and apply the Percent Style 6 format with one decimal place. Select the range K7 K22 and apply the Accounting Number Format. Merge and center the label Series Led in the range AI:C1 in the Data worksheet. Apply Thick Outside Borders to the range Al C4 8 10 11 Note, the border type may be Thick Box Border, depending on the version of Office used 12 Select Landscape orientation, adjust the scaling so that the data fins on one page 4 and set 0.1 left and right margins for the Data worksheet. 13 Click the Sales sheet tab, convert the data to a table, and apply Table Style Light 4 14 Sort the data by Series Name in alphabetical order and then within Series Name, 4 sort by Net Sales from largest to smallest. 15 Add a total row to display the sum of the Net Sales column. Change the column 4 width to 14 for the Net Sales column. 16 Select the values in the Percent Returned column and apply conditional 3 formatting to apply Light Red Fill with Dark Red Text for values that are greater than 9.9% 17 Select the values in the Net Sales column and apply a filter to display only net 4 sales that are less than $100,000. 18 Click the Net Sales sheet tab, select the range A3:D7, and create a clustered 4 column chart. 19 Move the chart so that the top-left corner is positioned inside cell A9. Change 3 the chart width to 4.66 inches and the chart height to 2.9 inches. 20 Link the chart title to cell Al. Format the value axis to display whole numbers 2 only. 21 Format the chart title, value axis, category axis, and legend with Black, Text 3 font color. 22 Select the Series Sales tab, select the ranges A4:A7 and C4:C7 and create a pie 5 chart. Move the pie chart to a chart sheet named Office Reference. Move the Office Reference chart sheet to the right of the Series Sales sheet. 23 Change the chart title to Office Reference Series, 24 Apply the Style 12 chart style and change the colors to Colorful Palette 4. Apply 4 bold and change the font size to 18 for the chart title. 25 Display data labels in the Inside End position Display Percentage data labels: 6 remove the Value data labels. With the Series 1 Data Labels selected, apply bold, change the font size to 18, and then apply White, Background i font color. 26 Ensure that the worksheets are correctly named and placed in the following 0 order in the workbook: Data, Sales, Net Sales, Series Sales, Office Reference. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Total Points 100 1 EX16_XL_VOLI_GRADER_CAP_HW - Software Training Books 1.8 Data Sales Net Sales Series Sales Series Legend Input Area Royalty Rail Retum Rate CE ORTB CertificatioOffice Relo True Beginner 500 Author Royal Bonus Author Karnings Quantity Sol No. Books Percent Re UnitPrice Net Sales 1995 1.847 39.95 11,841 49,95 Yang CE OR 1042 3,44 19.95 50 N014 R14 49.95 Author Series Ced Software LON Word 2016 Krupin TB Word 2016 Cole Word 2016 Insel 2016 Tremblay TB For 2016 Tams Freel 2016 Martin AccessNI16 Access 2016 Daniels CE Access N16 PowerPoint 2016 PowerPoint 2016 Kumar CE PowerPoint 2016 BartolottiTB Outlook 2016 Wallace OR Windows 10 Toute TR Windows 10 Coleman CE Windows 10 415 14.49 500 1.971 1.279 120 Om ws 49.95 Soo 34449 500 500 175 2.507 1,884 14.750 N. 142 6.124 1.839 19.95 500 34,49 Data Sales Net Sales Series Sales Series Name Software Percent Returned Unit Price Net Sales 499 Author LON Krupin Cole Yeung Tremblay Torres Martin Allera Daniels 39.95 49.95 39.95 49.95 Office Reference The Beginner Certification Series Office Reference True Beginner Certification Series Ollice Reference True Beginner Certification Series Office Reference True Beginner Certification Series Tre Beginner Office Reference The Beginner Certification Series Word 2016 Word 2016 Word 2016 Excel 2016 Excel 2016 Excel 2016 Access 2016 Access 2016 Access 2016 PowerPoint 2016 PowerPoint 2016 PowerPoint 2016 Outlook 2016 Windows 10 Windows 10 Windows 10 5.8% 14.7% 14.9% 8.8% 10.1% 1.9% 10.3% 9.7% 10.4% 94 17.5% 75% 9.5% 12.5 9.6% 12.14 403,795.8 19.400 91.245.8 519.410.05 255.540 324.234.2 361,138.5 91.980 122 N77 57,892.05 21.650 0,016, 42.725 644,904.45 18.475 650 67 34.49 49.95 34.49 Wong Kumar Bantaloni Wallace Toulou Coleman 4995 3449 Data Sales Net Sales Series Sales Net Sales Software Access 2016 Excel 2016 PowerPoint 2016 Wend 2016 Certification Series Office Reference 122.887.87 361.138.5 524 234.2 $59.410.05 80,016,8 57.892.05 91,245,8 403,795.8 True Beginner 91.980 235 540 21.650 39,400 DataSales Net Sales Series Sales Net Sales Software Excel 2016 Word 2016 Access 2016 PowerPoint 2016 Certification Series 124.244. 2 91.1458 122,887,87 80,016,8 Office Reference 5 19,410.05 403.795.8 361.138.5 57,192.05 True Beginner 235 540 39. 400 91.980 21.650