Sally wants you to create a workbook to record the recent book purchases made by Sparrow&Pond. The
Question:
Sally wants you to create a workbook to record the recent book purchases made by Sparrow&Pond. The workbook should list the recent acquisitions from private sellers, libraries, and other vendors; include a description of each book; and calculate the total number of books acquired and the total amount spent by Sparrow&Pond. Complete the following:
1. Create a new, blank workbook, and then save the workbook as Book List in the location specified by your instructor. 2. Rename the Sheet1 worksheet as Documentation, and then enter the data shown in Figure1-42 in the specified cells.
3. Set the font size of the title text in cellA1 to 26points. 4. Add a new worksheet after the Documentation sheet, and then rename the sheet as Books. 5. In cell A1, enter the text Book Acquisitions. Set the font size of this text to 26points. 6. In cell A2, enter the text DATE as the label. In cellB2, enter the date 4/3/2016. 7. In the range A4:G9, enter the data shown in Figure1-43.
8. Insert cells into the rangeA4:A9, shifting the other cells to the right. 9. Enter the label BOOK ID in cell A4, type 02103-New in cell A5, and then type 09076-Used in cellA6. 10. Use Flash Fill to fill in the remaining book IDs. 11. Set the width of columnsA through D to 15characters each. Set the width of columnE to 30characters. Set the width of columnF to 20characters. Set the width of columnG to 15 characters. 12. Set the book titles in the rangeE4:E9 to wrap to a new line. 13. Autofit the heights of rows4 through 9. 14. Move the book list in the rangeA4:H9 to the rangeA8:H13. 15. In cell G15, enter the text TOTAL. In cellH15, enter a function to add the prices in the rangeH9:H13. 16. In cell A4, enter the text TOTAL BOOKS. In cellB4, enter a function to count the number of numeric values in the rangeH9:H13.
17. In cell A5, enter the text TOTAL PRICE. In cellB5, display the value from cellH15. 18. In cell A6, enter the text AVERAGE PRICE. In cellB6, enter a formula to calculate the total price paid for the books (listed in cellB5) divided by the number of books purchased (listed in cellB4). 19. Add borders around each cell in the nonadjacent rangeA4:B6;A8:H13;G15:H15. 20. For the Books worksheet, change the page orientation to landscape and scale the worksheet to print on a single page for both the width and the height. If you are instructed to print, print the entire workbook. 21. Display the formulas in the Books worksheet, and set the gridlines and row/column headings to print. If you are instructed to print, print the entire worksheet. 22. Save and close the workbook.