looking for some help on this spreadsheet project. 

 

Ofce 2016 – myitlab:grader – InstructionsExcel ProjectEX16_XL_VOL1_GRADER_CAP_HW - Software Training Books 1.2Project Description:You are a vice president for a publisher of software training books. Your division publishes threeseries that focus on Microsoft OFce and Windows. You want to analyze the sales data and calculateauthor royalties. You will format the worksheet, insert formulas and functions to performcalculations, sort and ±lter data to review speci±c book sales, and prepare a chart that comparessales by series.Instructions:For the purpose o± grading the project you are required to per±orm the ±ollowing tasks:StepInstructionsPointsPossible1Start Excel. Download and open the file namedexploring_ecap_grader_h1.xlsx.0.0002On the Data worksheet, select the range A6:K6, wrap the text, and apply Center alignment. Change therow height to 30 for row 6.3.0003In cell F7 in the Data worksheet, insert a formula that calculates the percentage of books returned basedon the number of books returned and the quantity sold. Copy the formula from cell F7 to the rangeF8:F22.4.0004In cell H7 in the Data worksheet, insert a formula that calculates the net sales. This monetary amountreflects the number of books not returned and the unit price. Copy the formula from cell H7 to the rangeH8:H22.4.0005In cell I7 in the Data worksheet, insert a formula that calculates the amount of the first author’sroyalties. An author’s royalties are based on the Royalty Rate located in the Input Area and therespective Net Sales. Copy the formula from cell I7 to the range I8:I22.4.0006In cell K7 in the Data worksheet, insert a formula that adds the first author’s royalty amount to thebonus. Copy the formula from cell K7 to the range K8:K22.4.0007In cell J2 in the Data worksheet, insert a function to calculate the average net sales. In cell J3 insert afunction to calculate the highest net sales. In cell J4 insert a function to calculate the lowest net sales.6.0008Select the range L1:N2 in the Data worksheet, copy the selected data, and transpose the data whenpasting it to cell A2. Delete the data in the range L1:N2.4.0009Click cell C6 in the Data worksheet and insert a column. TypeSeries Namein cell C6. Click cell C7 inthe Data worksheet and insert a lookup function that identifies the series code, compares it to the serieslegend, and then returns the name of the series. Copy the function you entered from cell C7 to the rangeC8:C22. Change the width of column C to 18.8.00010Click cell K7 in the Data worksheet and replace the current contents with an IF function that comparesthe percent returned for the first book to the return rate in the Input Area. If the percent returned is lessthan the return rate, the result is $500. Otherwise, the author receives no bonus. The only value you maytype directly in the function is0where needed. Copy the function you entered from cell K7 to the rangeK8:K22.5.000Updated: 10/26/20161Current_Instruction.docx

Series LegendInput AreaRoyalty Rate:7.3%Return Rate:10.0%Bonus Amount:$500.00AuthorSeries CodSofwareQuanTty Sol No. Books RPercent ReUnit Price Net SalesLopezORWord 20168,584500$49.95KrupinTBWord 20161,847271$25.00CoteCEWord 20162,684400$39.95YeungORExcel 201611,8411,042$49.95TremblayTBExcel 20169,475957$30.00TorresCEExcel 20168,443327$39.95Mar±nORAccess 20168,064834$49.95AlferoTBAccess 20163,397331$30.00DanielsCEAccess 20163,978415$34.49Or±zORPowerPoint 20161,279120$49.95WongTBPowerPoint 20161,050184$25.00KumarCEPowerPoint 20162,507187$34.49Bartalo² TBOutlook 20161,884175$25.00WallaceORWindows 1014,7501,839$49.95ToulouTBWindows 108,342803$25.00ColemanCEWindows 106,124741$34.49

Attachments:

• 1494294378-Visockis_exploring_ecap_grader_h1.xlsx
• 1494294379-EX16XLVOL1GRADERCAPHW_-_Software_Training_Books_12_Instructions.docx