How to calculate royalty rate excel

Royalty Statement

Project Description: You manage a small publishing company in Austin, Texas. You publish and sell hardback and paperback novels to regional bookstores. At the end of every six-month period, you prepare royalty statements for the authors based on the net sales and a standard royalty rate. Your assistant prepared a standard royalty statement for one author. You need to insert formulas to calculate missing values and format the worksheets. In addition, you need to insert worksheets to prepare a royalty statement 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_EOC.xlsx. 0 2 Enter the text Royalty Statement in cell A1. Merge and center the title over the range A1:C1. Change the font size of the title to 16, and apply bold formatting. Change the font color to Purple (under Standard Colors). 9 3 Insert a new row between rows 13 and 14. Enter Standard Discount Rate in cell A14, 0.55 in cell B14, and 0.5 in cell C14. Format the two values in B14 and C14 as Percent Style. 10 4 In cell B15, enter the formula to calculate the price to the bookstore. The price to the bookstore is calculated by subtracting the standard discount from the retail price. The discount is calculated by multiplying the retail price (B13) by the standard discount rate (B14). 4 5 In cell B16, enter the formula to calculate the net retail sales. The net retail sales number is found by multiplying the net units sold by the retail price (B13). The net units sold is calculated by subtracting the returned units (B9) from the gross units sold (B8). 4 6 In cell B20, enter the formula to calculate the royalties to the author. Royalties are calculated by multiplying the net retail sales (B16) by the applicable royalty rate (B19). 4 7 In cell B21, enter the formula to calculate the royalty per book. This amount is calculated by multiplying the retail price of the book (B13) by the royalty rate (B19). 4 8 Copy the formulas from the B15:B16 to C15:C16 and from B20:B21 to C20:C21. 3 9 Apply the Accounting Number Format to the monetary values in the ranges B13:C13, B15:C16, and B20:C21. 7.5 10 Apply bold formatting and the Purple, Accent 4, Lighter 40% fill color to the Units Sold heading in cell A7. 4 11 Indent the text in cells A13:A16 two times. 4 12 Apply an Outside Border (default settings) to the range A7:C10. Apply the same border style to the ranges A12:C16 and A18:C21. 6 13 Insert a new worksheet on the right side of the Jacobs worksheet. Rename the new worksheet Lopez. Change the Lopez sheet tab color to Dark Blue (under Standard Colors). 9 14 Copy the data from the range A1:C21 in the Jacobs worksheet, and paste it into the same cells in the Lopez worksheet. 3 15 Change the following values on the Lopez worksheet: Cell: New value: B4 Lopez B8 5000 C8 15000 B9 400 C9 175 B13 19.95 C13 7.95 7 16 Select both worksheets and center the data horizontally on the page when printed. Insert a custom header with the name Cecil Kramer in the left section, the Sheet Name code in the center, and the File Name code in the right section. 10 17 With both sheets selected, select the options to display the gridlines and row and column headings when printed. 4 18 On the Lopez worksheet only, display the cell formulas. 3 19 In Normal view, on the Lopez worksheet, adjust the width of column A to 11.00 (165 pixels). Adjust the widths of columns B and C of the Lopez worksheet to 7.00 (108 pixels). 4.5 20 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.