Highland Community Preschool
Collaborating on a Shared Workbook
PROJECT DESCRIPTION
Highland Community Preschool, a local non-profit nursery school, held its annual "Casino Night" fundraising event last weekend. Becca Gerner, the Chairperson of the Fundraising Committee for the school, generated a workbook to track the expenses and income from the event and shared that workbook with the committee to get their feedback. She asked for your help consolidating the information provided by the board members into her original workbook. Becca has already received feedback from Abraham Miller, but will need you to merge the edited workbooks from Stephanie Lorenz and Kevin Staszowski and then review the changes made by the board members. After addressing all the suggested changes, you will prepare the workbook for a final review by Becca Gerner.
GETTING STARTED
- Download the following files from the SAM website:
- NP_Excel2013_T12_P1a_FirstLastName_1.xlsx
- Open the file you just downloaded and save it with the name:
- NP_Excel2013_T12_P1a_FirstLastName_2.xlsx
- Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel 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:
- support_NP_E13_T12_P1a_ksnotes.xlsx
- support_NP_E13_T12_P1a_slnotes.xlsx
- Open files NP_Excel2013_T12_P1a_FirstLastName_2.xlsx. Ensure that your first and last name is displayed in cell B6 of the Documentation sheet. 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 will need to add the Compare and Merge Workbooks button to the Quick Access Toolbar. To add this button to the Quick Access toolbar, complete the following set of instructions:
- In the Quick Access Toolbar, click the Customize Quick Access Toolbar button and select the More Commands option from the drop-down menu.
- In the Excel Options dialog box, select the Commands Not in the Ribbon option from the drop-down menu associated with the Choose commands from: option.
- Find the Compare and Merge Workbooks option from the list of commands. Click on this command and click the Add button. The Compare and Merge Workbooks option should be the last item in the Quick Access Toolbar.
- Click the OK button to close the Excel Options dialog box.
PROJECT STEPS
1. Using the Compare and Merge Workbooks option, perform the following steps:
a. Merge the support_NP_E13_T12_P1a_slnotes.xlsx workbook into the NP_Excel2013_T12_P1a_FirstLastName_2.xlsx workbook.
b. Merge the support_NP_E13_T12_P1a_ksnotes.xlsx workbook into the NP_Excel2013_T12_P1a_FirstLastName_2.xlsx workbook.
For Steps 2-12, you will be accepting or rejecting the changes in the workbook as described below.
2. Go to the Overview worksheet. Using the Accept/Reject Changes option, review the changes made in the range A1:F16 of the Overview worksheet (Hint: Confirm your Select Changes to Accept or Reject dialog box matches Figure 1 before proceeding).
Figure 1: Overview Worksheet - Select Changes to Accept or Reject Dialog Box
| Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved. |
On the Overview worksheet, accept the change made by Stephanie Lorenz to cell B2.
3. On the Overview worksheet, reject the change made in cell C12.
4. On the Overview worksheet, accept the change made in cell D12.
5. Go to the Income worksheet. Using the Accept/Reject Changes option, review the changes made in the range A1:P34 of the Income worksheet (Hint: Confirm your Select Changes to Accept or Reject dialog box matches Figure 2 before proceeding).
Figure 2: Income Worksheet - Select Changes to Accept or Reject Dialog Box
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
On the Income worksheet, accept the change made by Stephanie Lorenz to the cell B2.
6. On the Income worksheet, accept the change made in cell C7.
7. On the Income worksheet, accept the change made in cell H11.
8. On the Income worksheet, accept the change made in cell O11.
9. Go to the Expenses worksheet. Using the Accept/Reject Changes option, review the changes made in the range A1:H14 of the Expenses worksheet (Hint: Confirm your Select Changes to Accept or Reject dialog box matches Figure 3 before proceeding).
Figure 3: Expenses Worksheet - Select Changes to Accept or Reject Dialog Box
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
On the Expenses worksheet, accept the change made by Stephanie Lorenz to cell B2.
10. On the Expenses worksheet, accept the change made in cell B6 by Abraham Miller.
11. On the Expenses worksheet, reject the change made in cell D10.
12. On the Expenses worksheet, reject the change made in cell C5 and then turn off workbook sharing, clicking yes when prompted with a warning message.
13. Go to the Overview worksheet. Delete the four (4) comments made by Abraham Miller from this worksheet. Do not delete the comments made by Stephanie Lorenz or Kevin Staszowski (Hint: Use the Show/Hide Comment option to quickly identify all the comments).
14. Go to the Income worksheet. Delete all comments in this worksheet.
15. Go to the Expenses worksheet. Delete only the comments associated with cell B2, B6, and D10.
16. 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).
17. Add the following document properties to the workbook:
a. Title: Casino Night Fundraiser
b. Tags: Casino Night; Fundraiser
Hint: This field may also be called Keywords, depending on how you access the document properties of the workbook.
c. Comments: Final Review draft of the Casino Night Fundraiser report for 2016
d. Status: Final Review Draft
18. Add a custom Document property to the worksheet with the name Checked By, the Text data type (which is the default for this property), and the value AM, SL, KS.
19. Mark the workbook as Final. Save and close the workbook.
Your workbook should look like the Final Figure on the following page. Note that the 'Show all comments' option is selected for the worksheets shown in the Final Figure. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.
| |
Final Figure 1: Overview Worksheet
Final Figure 2: Income Worksheet
| |
Final Figure 3: Expenses Worksheet
