create excel program basic step , sociology homework help

Anonymous
timer Asked: Jul 5th, 2017
account_balance_wallet $10

Question Description

create excel program basic steps , all the requirments attached

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 • 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 • 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. 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_CS58a_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. New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a 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. First type This amount exceeded our goal this year!, delete any previously existing text, and then 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: 8. 9. 10. 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. 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. 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. 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. New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a 11. 12. 13. 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. 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. 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. New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a 14. 15. 16. 17. 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. 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 Table Style Medium 4 table style. b. Use SpringMemberships as the name of the table. 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. 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. 18. 19. 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. 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. 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 New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a display the type of membership, enter a formula in cell H3 using the VLOOKUP function as follows: 20. 21. 22. 23. 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. 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. 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. 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. 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. New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a 24. 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 Pivot Style Medium 4 PivotTable style to the PivotTable. 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. New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 1: Local Offices Worksheet Final Figure 2: California Worksheet New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 3: Oregon Worksheet Final Figure 4: Washington Worksheet New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 5: Consolidated Worksheet New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 6: Spring Worksheet New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 7: Fall PivotTable Worksheet New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a Final Figure 8: Fall Worksheet
Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from website. New Perspectives Excel 2016 | Modules 5–8: SAM Capstone Project 1a Wild Planet Conservation DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES Saleh Almutairi edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Wild Planet Conservation Local Field Offices Office California Oregon Washington Manager Deb Friesen Andy Wong Gina Mendoza City Sacramento Salem Olympia Phone Email (916) 555-1877 dfriesen@wpc.cengage.net (503) 555-3002 awong@wpc.cengage.net (360) 555-4311 gmendoza@wpc.cengage.net California Fundraisers Type Adopt an Animal Run/Walk Total $ $ March 50,000 $ 21,750 71,750 $ May 37,500 $ 34,600 72,100 $ Total 87,500 56,350 143,850 Oregon Fundraisers Type Adopt an Animal Run for the Wild Total $ $ March 32,000 $ 20,500 52,500 $ May 28,750 $ 30,550 59,300 $ Total 60,750 51,050 111,800 Washington Fundraisers Type Adopt an Animal Run for the Wild Total $ $ March 47,500 $ 21,000 68,500 $ May 32,000 $ 30,500 62,500 $ Total 79,500 51,500 131,000 Event Totals Type Adopt an Animal Run for the Wild Total Total $ 158,900 158,900 Event Coordinators Name WPC Office March Date May Date Event Wild Planet Conservation California Revenue - 2019 Member $ Sponsor Benefactor Fundraisers Total Jan Feb March Apr May 675 $ 350 $ 325 $ 375 $ 250 1,525 650 3,350 4,250 2,500 6,900 5,250 9,750 2,500 1,500 71,750 72,100 Total 1,975 12,275 25,900 143,850 $ 184,000 $ Wild Planet Conservation Oregon Revenue - 2019 Member $ Sponsor Benefactor Fundraisers Total Jan Feb March Apr May 525 $ 300 $ 315 $ 355 $ 225 1,025 625 3,150 4,000 2,100 5,100 4,250 8,150 2,700 2,500 52,500 59,300 Total 1,720 10,900 22,700 111,800 $ 147,120 $ Wild Planet Conservation Washington Revenue - 2019 Member $ Sponsor Benefactor Fundraisers Total Jan Feb March Apr May 595 $ 550 $ 515 $ 555 $ 275 1,525 825 3,050 4,200 2,700 5,800 4,950 8,000 2,950 3,500 68,500 62,500 Total 2,490 12,300 25,200 131,000 $ 170,990 $ Wild Planet Conservation Total Revenue - 2019 Jan Feb March Apr May Total $ $ Total Goal met? $ - $ - $ - $ - $ - Wild Planet Conservation Memberships Spring 2018 Member ID CA-180 CA-218 OR-223 WA-219 OR-147 CA-150 CA-217 CA-241 OR-132 WA-136 WA-157 CA-141 CA-167 CA-173 CA-203 CA-223 WA-121 OR-138 WA-138 WA-159 CA-241 CA-170 CA-175 CA-205 CA-220 WA-124 OR-134 WA-139 WA-151 CA-140 CA-169 CA-174 CA-200 CA-224 WA-123 OR-133 WA-137 WA-158 CA-142 CA-168 CA-179 CA-204 CA-225 WA-123 OR-142 WA-146 Type FaceLink FaceLink Million+ Website Website FaceLink Million+ Million+ Website FaceLink Million+ FaceLink Website FaceLink Website Website FaceLink Website FaceLink Million+ FaceLink Website FaceLink Million+ FaceLink FaceLink Website FaceLink Million+ FaceLink Website Website Million+ Website FaceLink Website FaceLink Website FaceLink Website FaceLink FaceLink FaceLink FaceLink Website FaceLink Level Animal Amount Gorilla $1,000 Gorilla $1,500 Gorilla $250 Rhinoceros $1,250 Lion $1,000 Rhinoceros $150 Elephant $100 Elephant $125 Lion $50 Lion $75 Cheetah $125 Cheetah $250 Elephant $500 Cheetah $500 Gorilla $275 Gorilla $300 Elephant $350 Lion $50 Lion $75 Cheetah $125 Cheetah $250 Elephant $500 Cheetah $500 Gorilla $275 Gorilla $300 Elephant $350 Cheetah $50 Lion $75 Cheetah $125 Gorilla $250 Elephant $500 Cheetah $500 Gorilla $275 Gorilla $300 Elephant $350 Lion $50 Lion $75 Elephant $125 Cheetah $250 Elephant $500 Cheetah $500 Gorilla $275 Elephant $300 Elephant $350 Lion $50 Lion $75 WA-167 CA-151 CA-177 CA-183 CA-213 CA-233 WA-131 Total Million+ Website Website FaceLink Website Website FaceLink Lion Cheetah Elephant Cheetah Gorilla Gorilla Elephant $125 $250 $500 $500 $275 $300 $350 $17,500 Amount Level 0 Patron 250 1000 Sponsor Benefactor Wild Planet Conservation Memberships Fall 2018 Member ID CA-104 CA-115 CA-116 CA-127 CA-130 CA-137 CA-148 CA-152 CA-167 CA-185 CA-189 CA-190 CA-218 CA-223 CA-241 CA-252 CA-263 CA-264 CA-275 CA-276 CA-287 CA-290 OR-120 OR-127 OR-130 OR-138 OR-142 OR-145 OR-154 OR-155 OR-160 OR-171 OR-174 OR-183 OR-184 OR-190 OR-196 WA-101 WA-116 WA-127 WA-129 WA-130 WA-141 WA-142 WA-153 WA-155 Type FaceLink FaceLink Website Website FaceLink Website Million+ Website FaceLink Million+ FaceLink FaceLink Website Million+ FaceLink Million+ Website Website FaceLink Million+ FaceLink Million+ Million+ Million+ Website Website Website Million+ FaceLink FaceLink FaceLink Million+ Website Website FaceLink FaceLink Website Million+ FaceLink FaceLink Website Website Website Million+ FaceLink FaceLink Level Patron Sponsor Benefactor Benefactor Sponsor Sponsor Benefactor Patron Benefactor Sponsor Sponsor Patron Patron Patron Sponsor Sponsor Benefactor Benefactor Benefactor Sponsor Sponsor Benefactor Benefactor Sponsor Patron Sponsor Patron Patron Patron Sponsor Sponsor Patron Benefactor Sponsor Benefactor Benefactor Benefactor Sponsor Benefactor Benefactor Sponsor Sponsor Benefactor Sponsor Benefactor Sponsor Animal Cheetah Rhinoceros Elephant Cheetah Gorilla Gorilla Elephant Gorilla Gorilla Elephant Lion Lion Gorilla Elephant Lion Cheetah Rhinoceros Lion Lion Elephant Elephant Cheetah Gorilla Gorilla Elephant Rhinoceros Rhinoceros Cheetah Elephant Gorilla Lion Cheetah Lion Elephant Elephant Lion Gorilla Rhinoceros Gorilla Cheetah Elephant Lion Lion Cheetah Elephant Gorilla Amount $150 $600 $1,500 $1,200 $250 $250 $1,000 $150 $1,200 $500 $500 $125 $100 $50 $250 $500 $1,000 $1,000 $1,250 $500 $250 $1,000 $1,000 $250 $100 $500 $100 $50 $75 $250 $250 $100 $1,000 $250 $1,000 $1,250 $1,000 $750 $1,000 $1,250 $500 $250 $1,250 $500 $1,500 $500 WA-157 WA-161 WA-167 WA-170 WA-179 WA-204 WA-205 WA-216 WA-218 WA-220 WA-229 WA-231 WA-233 WA-242 Million+ FaceLink Website Website FaceLink Website FaceLink Website FaceLink Million+ Million+ FaceLink FaceLink FaceLink Benefactor Patron Patron Sponsor Sponsor Patron Sponsor Sponsor Benefactor Patron Sponsor Sponsor Patron Patron Gorilla Rhinoceros Cheetah Lion Elephant Gorilla Lion Rhinoceros Cheetah Lion Gorilla Lion Rhinoceros Elephant $1,500 $25 $50 $250 $250 $100 $750 $500 $1,000 $25 $500 $250 $100 $50 Member ID Type Amount WA-204 $ 100 Number FaceLink Members Average $

Tutor Answer

criss53
School: UCLA

Here is your workbook.Have a nice day.

Author:

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
website.

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

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES
Saleh Almutairi
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM
website.

Wild Planet Conservation
Local Field Offices
Office
California
Oregon
Washington

Manager
Deb Friesen
Andy Wong
Gina Mendoza

City
Sacramento
Salem
Olympia

Phone
Email
(916) 555-1877 dfriesen@wpc.cengage.net
(503) 555-3002 awong@wpc.cengage.net
(360) 555-4311 gmendoza@wpc.cengage.net

Staff Information

California Fundraisers
Type
Adopt an Animal
Run for the Wild
Total

$
$

March
50.000 $
21.750
71.750 $

May
37.500 $
34.600
72.100 $

Total
87.500
56.350
143.850

Oregon Fundraisers
Type
Adopt an Animal
Run for the Wild
Total

$
$

March
32.000 $
20.500
52.500 $

May
28.750 $
30.550
59.300 $

Total
60.750
51.050
111.800

Washington Fundraisers
Type
Adopt an Animal
Run for the Wild
Total

$
$

March
47.500 $
21.000
68.500 $

May
32.000 $
30.500
62.500 $

Total
79.500
51.500
131.000

Event Totals
Type
Adopt an Animal
Run for the Wild
Total

$
$

Total
227.750
158.900
386.650

Event Coordinators
Name
WPC Office
March Date
May Date
Event

Amanda Arnett
Oregon
3/12/2019
5/15/2019
Run for the Wild

Wild Planet Conservation
California Revenue - 2019
Patron
$
Sponsor
Benefactor
Fundraisers
Total
$

Jan
Feb
March
Apr
May
675 $
350 $
325 $
375 $
250
1.525
650
3.350
4.250
2.500
6.900
5.250
9.750
2.500
1.500
71.750
72.100
9.100 $ 6.250 $ 85.175 $ 7.125 ...

flag Report DMCA
Review

Anonymous
Thank you! Reasonably priced given the quality not just of the tutors but the moderators too. They were helpful and accommodating given my needs.

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors