Question

Series Legend				Input Area				Net Sales			CE	OR	TB
				Royalty Rate:		7.3%		Average			Certification Series	Office Reference	True Beginner
				Return Rate:		10.0%		Highest
				Bonus Amount:		$500.00		Lowest
Author	Series Code	Software	Quantity Sold	No. Books Returned	Percent Returned	Unit Price	Net Sales	Author Royalties	Bonus	Author Earnings
Lopez	OR	Word 2016	8,584	500		$49.95			500
Krupin	TB	Word 2016	1,847	271		$25.00			500
Cote	CE	Word 2016	2,684	400		$39.95			500
Yeung	OR	Excel 2016	11,841	1,042		$49.95			500
Tremblay	TB	Excel 2016	9,475	957		$30.00			500
Torres	CE	Excel 2016	8,443	327		$39.95			500
Martin	OR	Access 2016	8,064	834		$49.95			500
Alfero	TB	Access 2016	3,397	331		$30.00			500
Daniels	CE	Access 2016	3,978	415		$34.49			500
Ortiz	OR	PowerPoint 2016	1,279	120		$49.95			500
Wong	TB	PowerPoint 2016	1,050	184		$25.00			500
Kumar	CE	PowerPoint 2016	2,507	187		$34.49			500
Bartalotti	TB	Outlook 2016	1,884	175		$25.00			500
Wallace	OR	Windows 10	14,750	1,839		$49.95			500
Toulou	TB	Windows 10	8,342	803		$25.00			500
Coleman	CE	Windows 10	6,124	741		$34.49			500
		1 Start Excel. Download and open the file named exploring_ecap_grader_h1.xlsx. 2 On the Data worksheet, select the range A6:K6, wrap the text, and apply Center alignment. Change the row height to 30 for row 6. 3 In cell F7 in the Data worksheet, insert a formula that calculates the percentage of books returned based on the number of books returned and the quantity sold. Copy the formula from cell F7 to the range F8:F22. 4 In cell H7 in the Data worksheet, insert a formula that calculates the net sales. This monetary amount reflects the number of books not returned and the unit price. Copy the formula from cell H7 to the range H8:H22. 5 In cell I7 in the Data worksheet, insert a formula that calculates the amount of the first author’s royalties. An author’s royalties are based on the Royalty Rate located in the Input Area and the respective Net Sales. Copy the formula from cell I7 to the range I8:I22. 6 In cell K7 in the Data worksheet, insert a formula that adds the first author’s royalty amount to the bonus. Copy the formula from cell K7 to the range K8:K22. 7 In cell J2 in the Data worksheet, insert a function to calculate the average net sales. In cell J3 insert a function to calculate the highest net sales. In cell J4 insert a function to calculate the lowest net sales. 8 Select the range L1: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 L1:N2. 9 Click cell C6 in the Data worksheet and insert a column. Type Series Name in 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. 10 Click cell K7 in the Data worksheet and replace the current contents with an IF 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 0 where needed. Copy the function you entered from cell K7 to the range K8:K22. 11 Select the range G7:G22 in the Data worksheet and apply the Percent Style format with one decimal place. Select the range K7:K22 and apply the Accounting Number Format. Merge and center the label Series Legend in the range A1:C1 in the Data worksheet. Apply Thick Outside Borders to the range A1:C4. 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 fits on one page, and set 0.1 left and right margins for the Data worksheet.

Answer 1

Please find the screenshots. I am also mentioning the formulaes at the bottom.

Formulaes:

Percentage of books returned - (E7/D7)*100

Net Sales : (D7-E7)*G7

Author Royalty: $G$2*H7

Author Earnings: I7+J7

Simpl drag these formulae to apply in each row.