New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Wild Planet Conservation DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES GETTING STARTED • Open the file NP_EX16_CS5-8a_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES GETTING STARTED

• Open the file NP_EX16_CS5-8a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX16_CS5-8a_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_CS5-8a_Staff.xlsx • With the file NP_EX16_CS5-8a_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. PROJECT STEPS 1. Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information. Switch to the Local Offices worksheet, and then unprotect it. 2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows: a. In cell A7, insert a hyperlink to the Support_NP_EX16_CS5-8a_Staff.xlsx file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) b. Use Staff Information as the text to display. c. Use Detailed staff information for all locations as the ScreenTip text. 3. View the comment in cell A12. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment. 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows: a. Edit the defined name associated with cell D12 to be CA_Run instead of CA_RunWalk. b. Create a defined name for cell D24 using WA_Run as the name. c. Select the range F16:G20 and create names from the selection using the values shown in the Left column. 5. In cell G11, insert a formula using the SUM function that uses the defined names CA_Adopt, OR_Adopt, and WA_Adopt to calculate the total amount raised for the Adopt an Animal fundraiser. 6. To include a note about revenue from fundraisers, insert a comment into cell G13. Add This amount exceeded our goal this year! and if necessary, hide the comment so it is not a distraction. 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows: a. In cell G17, insert a data validation rule that accepts values from a List, ignores blanks, and appears as an in-cell dropdown. b. Enter the list California, Oregon, Washington as the validation source. c. For the input message, use Office Location as the title and Select an office location from the list. (including the period) as the Input message. d. For the error alert, use the Stop style with Invalid location as the title. 8. Create a data validation rule that accepts only specified date values as follows: a. In cell G18, insert a data validation rule that accepts Date values for dates between 3/1/2019 and 3/31/2019. b. For the input message, use March Event Date as the title and use the text Enter date for March fundraiser. (including the period) as the Input message. c. For the error alert, use the Stop style with Invalid March event date as the title. 9. Edit the data validation rule associated with cell G19 as follows: a. Change the input message title to May Event Date and use Enter date for May fundraiser. (including the period) as the Input message. b. Change the error alert to use the Stop style with Invalid May event date as the title. 10. Test the data validation rules by entering the following information into the worksheet: a. In cell G16, enter Amanda Arnett as the Name. b. In cell G17, select Oregon as the WPC Office. c. In cell G18, enter 3/12/2019 as the March Date. d. In cell G19, enter 5/15/2019 as the May Date. e. In cell G20, select Run for the Wild as the Event. 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows: a. Group the California, Oregon, and Washington worksheets. b. In cell A4, edit the text to read Patron (instead of Member). c. Bold the values in the range G4:G8. d. In cell B8, enter a formula using the SUM function that totals the revenue for January (the range B4:B7). Copy the formula to the range C8:F8. e. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step. 12. Switch to the Consolidated worksheet. Consolidate the revenue data from each of the offices as follows: a. In cell A4, enter a formula without using a function that references cell A4 in the Washington worksheet. Copy the formula from cell A4 to the range A5:A7. b. In cell B4, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B4 in the California:Washington worksheets. c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting. d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7. 13. Andre has set a revenue goal of $20,000 for January, February, and April, when the organization has no fundraisers, and a revenue goal of $200,000 for March and May, when the organization does have fundraisers. In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month: a. Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7 equals 0 and the Total in cell B8 is greater than 20000. b. If the logical test is true, display Yes (using “Yes” for the value_if_true argument). c. If the logical test is false, insert a nested IF function. d. Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000. e. If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument). f. If the logical test is false, display No (using “No” for the value_if_false argument). Copy the formula in cell B10 to the range C10:F10. 14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows: a. Edit the conditional formatting rule applied to the range B10:F10. b. Change the format so the Fill color is No Color, while keeping the same font color and style. 15. Go to the Spring worksheet. In the Spring and Fall worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the Spring worksheet to match the Fall worksheet as follows: a. Format the range A2:E56 as an Excel table with headers using the Lime, Table Style Medium 4 table style. (Hint: Depending on your version of Office, the table style may be written as Table Style Medium 4.) b. Use SpringMemberships as the name of the table. 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows: a. In cell C3, enter a formula using the HLOOKUP function. b. Use a structured reference to the Amount column ([Amount]) as the lookup value. c. Look up that value in the table array shown in the range G2:J3, using an absolute reference to that range. d. Return the value in row 2 of the table array. e. Use TRUE as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) f. Fill the formula into the range C4:C55, if necessary. g. Clear the contents of cell C56 in the Total row. 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows: a. In the range A3:A55, apply a conditional formatting Highlight Cells Rule that formats any duplicate values with Light Red Fill with Dark Red Text. 18. Correct the duplicate values by updating the following Member ID values. (Hint: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.) a. Use CA-240 as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. b. Use WA-122 as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. 19. Switch to the Fall worksheet. In the Fall worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the VLOOKUP function as follows: a. Use cell H2 as the lookup value. b. Use a reference to the FallMemberships table as the table array. c. Use column 2 as the column index number. d. Use FALSE as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account. a. In cell H7, enter a formula using the COUNTIF function to count the number of FaceLink members. b. Use a structured reference to the Type column in the FallMemberships table as the range. c. Use “FaceLink” as the formula criteria. 21. Determine the average amount FaceLink members paid to join the organization as follows: a. In cell I7, enter a formula using the AVERAGEIF function to average the amount paid by FaceLink members. b. Use a structured reference to the Type column in the FallMemberships table as the range. c. Use “FaceLink” as the formula criteria. d. Use a structured reference to the Amount column in the FallMemberships table as the average_range argument. 22. Update the FallMemberships table as follows to make it easier to analyze: a. Sort the data in the FallMemberships table first in ascending order by the Level field and then in the descending order by the Amount field. b. Insert a Total Row in the FallMemberships table. (Hint: The total of the values in the Amount field will automatically appear in cell E63.) c. In cell D63, use the Count function in the in-cell dropdown to calculate the number of members using the values in the Animal field. 23. Andre would like to be able to manipulate and filter the fall membership data in several ways. a. Create a PivotTable based on the FallMemberships table in a new worksheet, using Fall PivotTable as the worksheet name. b. Add the Animal field and the Member ID field (in that order) to the Rows area. c. Add the Amount field to the Values area. d. Update the Sum of Amount field in the Values area to display the name Membership Amount using the Accounting number format with 0 decimal places and $ as the symbol. e. Apply the Lime, Pivot Style Medium 4 PivotTable style to the PivotTable. (Hint: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) 24. Filter the PivotTable as follows to make it easier to analyze information: a. Create a filter for the PivotTable by adding the Type field to the Filters area. b. Filter the table so it displays only members who signed up through FaceLink. c. Insert a slicer that filters the PivotTable based on the Level field value. d. Resize and reposition the slicer so that its upper-left corner appears within cell E3 and its lower-right corner appears within cell H10. e. Use the slicer to filter the PivotTable to display only members at the Benefactor level. Your workbook should look like the Final Figures on the following pages. 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: Local Offices Worksheet Final Figure 2: California Worksheet Final Figure 3: Oregon Worksheet Final Figure 4: Washington Worksheet Final Figure 5: Consolidated Worksheet Final Figure 6: Spring Worksheet Final Figure 7: Fall PivotTable Worksheet Final Figure 8: Fall Worksheet

 

NP_EX16_CS5-8a_AngelaGerdts_2.xlsx