Author Royalties

Project Description:
You manage a publishing company that publishes and sells books to bookstores in Austin. Your assistant prepared a standard six-month royalty statement for one author. You need to insert formulas, format the worksheets, and then prepare royalty statements for other authors.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download and open the workbook named exploring_e01_grader_h1.xlsx. 0
2 Merge and center the title over the range A1:D1.
Hint: On the HOME tab, in the Alignment group, click Merge & Center. 4
3 Delete the blank column between the Hardback and Paperback columns.
Hint: Right-click the column header; from the shortcut menu, click Delete. 3
4 Insert a new row between Retail Price and Price to Bookstore. Enter Standard Discount Rate in cell A14, 0.55 in cell B14, and 0.5 in cell C14. Format the range B14:C14 with Percent Style.
Hint: To insert the new row, right-click the row header for row 14 and then click Insert. Type the text into the appropriate cells. To format in the Percent Style, select the cells and then on the HOME tab, in the Number group, click the Percent Style button. 6
5 Enter the Percent Returned formula in cell B10. The Percent Returned is the ratio of No. of Returns over the Gross Units Sold. Format the result with Percent Style with one decimal place. Copy the formula in B10 to C10.
Hint: In cell B10, enter the formula as =B9/B8 and press ENTER. To format in the Percent Style, on the HOME tab, use the tools in the Number group. To copy the formula, use the Fill handle. 6
6 Enter the Price to Bookstore formula in cell B15. This is the price at which you sell the books to the bookstore. It is based on the Retail Price and the Standard Discount Rate. For example, if a book has a $10 retail price and a 55% discount, the price to the bookstore would be $4.50. Copy the formula in cell B15 to cell C15 using the Paste Formulas & Numbering Formatting option.
Hint: In cell B15, type =B13-B13*B14 and press ENTER. To copy the formula, press CTRL+C. To match the formatting, click the Paste Options button and select Formulas & Number Formatting button. 8
7 Enter the Net Retail Sales formula in cell B16. The net retail sales is the revenue from the net units sold at the retail price. Gross units sold minus the returned units equals net units sold. Copy the formula in cell B16 to cell C16 using the Paste Formulas & Numbering Formatting option.
Hint: In cell B16, type =(B8-B9)*B13 and press ENTER. To copy the formula, press CTRL+C. To match the formatting, click the Paste Options button and select Formulas & Number Formatting button. 8
8 Enter the Royalty to Author formula in cell B20. Royalties are based on Net Retail Sales and the Royalty Rate. Copy the formula in cell B20 to cell C20 using the Paste Formulas & Numbering Formatting option.
Hint: In cell B20, type =B16*B19 and type ENTER. 8
9 Enter the Royalty per Book formula in cell B21. This amount is the Royalty to Author Amount divided by the difference between the Gross Units Sold and the No. of Returns. Copy the formula in cell B21 to cell C21 using the Paste Formulas & Numbering Formatting option.
Hint: In cell B21, type =B20/(B8-B9) and press ENTER. 8
10 Apply Comma Style with zero decimal places to the range B8:C9.
Hint: To format in the Comma Style, on the HOME tab, use the tools in the Number group. 4
11 Apply Percent Style with two decimal places to the range B19:C19.
Hint: To format in the Percent Style, on the HOME tab, use the tools in the Number group. 4
12 Apply Accounting Number Format to the cell C13.
Hint: On the HOME tab, in the Number group, click the Accounting Number Formatting button. 4
13 Click cell A7, apply Purple font color, and then apply Gray-25%, Background 2, Darker 10% fill color. Select the range A7:C7 and select Merge Across.
Hint: To apply formats, on the HOME tab, use the tools in the Font group. To merge cells, in the Alignment group, click Merge & Center arrow, and then Merge Across. 6
14 Select the ranges A8:A10, A13:A16, and A19:A21. Indent the labels twice. Widen column A to 23.75.
Hint: Select cells, then on the HOME tab, in the Alignment group, click Increase Indent two times. Select column A, and then in the Cells group, click the Format button, and select Column Width. 6
15 Select the range A7:C10 (the Units Sold section) and apply the Outside Borders border style.
Hint: Select the range A7:C10 and on the HOME tab, in the Font group, click the Border arrow and then click Outside Borders. 3
16 Select the margin setting to center the data horizontally on the page.
Hint: On the PAGE LAYOUT tab, click the Page Setup dialog box launcher. Click the Margins tab. 3
17 Insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
Hint: On the PAGE LAYOUT tab, click the Page Setup dialog box launcher. Click the Header/Footer tab. 6
18 Copy the Jacobs worksheet, move the new worksheet to the end, and then rename it as Lopez. Change cell B4 in the Lopez worksheet to Lopez. Change the Jacobs sheet tab to Red. Change the Lopez sheet tab to Dark Blue. Select options to print the gridlines and headings on the Lopez sheet.
Hint: To copy the worksheet, hold CTRL and drag the Sheet tab to the right until the triangle appears between the tabs. To rename the worksheet and change the tab color, right-click the worksheet tab. To print gridlines/headings, on the PAGE LAYOUT tab, click the Page Setup dialog box launcher. Click the Sheet tab. 13
19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Jacobs, Lopez. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100