New Perspectives Excel 2016 | Module 12: SAM Project 1a Historic Maryland Experience COLLABORATING ON A SHARED WORKBOOK GETTING STARTED ? Open the file NP_EX16_12a_ FirstLastName _1.xlsx , available for download from the SAM website. ? Save the file as NP_EX16_12a_ FirstLastName _2.xlsx by changing the “1” to a “2”. o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. ? To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer: o Support_NP_EX16_12a_Wilson.xlsx o Support_NP_EX16_12a_Yang.xlsx NOTE: Do not open the support files as you may not be able to merge them. ? With the file NP_EX16_12a_ FirstLastName _2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. ? To complete this project, you need to add the Compare and Merge Workbooks button to the Quick Access Toolbar as follows: o On the Quick Access Toolbar, click the Customize Quick Access Toolbar button, and then click More Commands. o In the Excel Options dialog box, click the Choose commands from arrow, and then click Commands Not in the Ribbon. o In the list of commands, click Compare and Merge Workbooks, and then click the Add button. o Click the OK button to close the Excel Options dialog box. PROJECT STEPS 1. You are an assistant at the Historic Maryland Experience, a nonprofit organization in Baltimore, Maryland, that sponsors tours and other activities to engage visitors in the colonial history of the area. George Shaw, the executive director, created a workbook to automate customer invoices, and then shared the workbook with the staff for their review. He asked for your help consolidating the staff’s changes and comments into his original workbook. He already received feedback from Emily Legrey, and now needs to include changes and comments from Jared Wilson and Kendra Yang. He also wants you to prepare the workbook for his final review. Use the Compare and Merge Workbooks feature to include comments and changes from reviewers as follows: a. Merge the Support_NP_EX16_12a_Wilson.xlsx workbook, available from the SAM website, into the NP_EX16_12a_ FirstLastName _2.xlsx workbook. b. Merge the Support_NP_EX16_12a_Yang.xlsx workbook, available from the SAM website, into the NP_EX16_12a_ FirstLastName _2.xlsx workbook. 2. Switch to the Invoice worksheet. Begin to review the comments in the workbook. Respond to the comment from Kendra Yang in cell F3 as follows: a. Change the text in the range F3:F5 to the Automatic color. b. Delete the comment in cell F3. 3. Review the next comment in the workbook, which is from Kendra Yang in cell F8. Edit the comment by including Yes as the response. 4. Review the next comment, which is from Emily Legrey in cell A12, and then delete it. 5. Review and respond to the next comment, which is from Kendra Yang in cell A13, as follows: a. Delete the comment in cell A13. b. Change the text in the range A13:C13 to the Automatic color. 6. Review the next comment, which is from Jared Wilson in cell B19, and then delete it. 7. Using the Accept/Reject Changes command, specify that you want to review the changes made in the range A2:J23 . Do not specify when or who made the changes. a. Accept the change made by Jared Wilson in cell B19. b. Accept only the change made by Kendra Yang in cell F20. Turn off workbook sharing. ( Hint : Click Yes when prompted by the warning message.) 8. Switch to the Customer Data worksheet. Review and respond to the next comment, which is from Emily Legrey in cell B1, as follows: a. Delete the comment in cell B1. b. Insert a row above the first row. c. In cell A1, enter Weekly Sales Data as the worksheet title. d. Apply the Title cell style to the new title. 9. Delete the remaining comments on the Customer Data worksheet. 10. Switch to the Invoice worksheet. In cell J6, use 7/4/2018 as the date instead of 6/4/2018. 11. Delete the hidden worksheet named Notes from the workbook. ( Hint : If you use the Document Inspector, do not delete the document properties or personal information from the workbook.) 12. Add the following document properties to the workbook: a. Title: HME Tour Invoices b. Tags: Invoices, Tickets, Customers ( Hint: This field might also be called “Keywords”, depending on how you access the document properties of the workbook.) c. Comments: Final review draft of the workbook for generating invoices d. Status: Final Review Draft 13. Add a custom Document property with the name Checked by to the workbook. Accept the default Text data type and use GS, EL, JW, KY as the value. 14. Mark the workbook as Final . Your workbook should look like the Final Figures on the following page. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Invoice Worksheet Final Figure 2: Customer Data Worksheet