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: StepInstructionsPoints Possible 1START EXCEL.
DOWNLOAD AND OPEN THE WORKBOOK NAMED EXPLORING_E01_GRADER_EOC. XLSX. 0 2ENTER 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 3INSERT 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.0 4IN 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 5IN 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 6IN 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 7IN 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 8COPY THE FORMULAS FROM THE B15:B16 TO C15:C16 AND FROM B20:B21 TO C20:C21. 3 9APPLY THE ACCOUNTING NUMBER FORMAT TO THE MONETARY VALUES IN THE RANGES B13:C13, B15:C16, AND B20:C21. 7.
5 10APPLY BOLD FORMATTING AND THE PURPLE, ACCENT 4, LIGHTER 40% FILL COLOR TO THE UNITS SOLD HEADING IN CELL A7. 11INDENT THE TEXT IN CELLS A13:A16 TWO TIMES.4 12APPLY AN OUTSIDE BORDER (DEFAULT SETTINGS) TO THE RANGE A7:C10. APPLY THE SAME BORDER STYLE TO THE RANGES A12:C16 AND A18:C21. 6 13INSERT 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 14COPY THE DATA FROM THE RANGE A1:C21 IN THE JACOBS WORKSHEET, AND PASTE IT INTO THE SAME CELLS IN THE LOPEZ WORKSHEET. 3 15CHANGE 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 16SELECT 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. 0 17WITH BOTH SHEETS SELECTED, SELECT THE OPTIONS TO DISPLAY THE GRIDLINES AND ROW AND COLUMN HEADINGS WHEN PRINTED. 4 18ON THE LOPEZ WORKSHEET ONLY, DISPLAY THE CELL FORMULAS. 3 19ON THE LOPEZ WORKSHEET, ADJUST THE WIDTH OF COLUMN A TO 11.
00 (165 PIXELS). ADJUST THE WIDTH OF COLUMNS B AND C OF THE LOPEZ WORKSHEET TO 7. 00 (108 PIXELS). 4. 5 20Ensure 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 Points100
