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. 

Paste F11 A 1 2 [ Arial BIU X fx B C Jan - June Sales Summary - Actual v 10 A A = V A II D E F G H Sales Mfg. Cost per Markup Total Sales to % of total 3 Model Volume Vehicle Total Cost Percentage Dealers Volume 4 VX207 126 35608.14 22% 5 VX212 159 35431.20 21% 6 VX208 32476.92 23% 7 VX217 794 31837.44 21% 8 VX220 1,485 31427.52 20% 9 VX219 2,678 29167.59 21% 10 VX201 2,831 29362.60 21% 11 VX209 5,605 28247.82 21% 12 VX204 669 27978.45 22% 13 VX215 4,464 27998.08 22% 14 VX210 4,923 26507.06 20% 15 VX211 27334.60 20% 16 VX216 5,281 25198.81 18% 17 VX214 5,415 24585.48 18% 18 VX224 5,710 23899.15 20% 19 VX218 5,891 18237.60 19% 20 VX223 12,118 17184.68 18% 21 VX213 6,462 17371.70 15% 22 VX206 6,393 16774.66 16% 23 VX202 9,308 16195.41 12% 24 VX205 7,178 14523.84 25% 25 VX222 7,493 13713.52 23% 26 VX221 7,468 12926.70 15% 27 VX203 28 7,704 12381.30 16% 29 total 30 average 31 highest 32 lowest 33 34 # models available for sale 35 36 37 38 39 40 41 42 43 44 1st Half + 1,214 MAR 30 80 DOO 000 Mac