New PerspectivesExcel 2016 | Modules 5-8: SAM Capstone Project1a Wild Planet Conservation DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES GETTING STARTED ?Open the fleNP_EX16_CS5-8a_FirstLastName_1

Subject:MS ExcelPrice:20.87 Bought25

Share With

New PerspectivesExcel 2016 | Modules 5-8: SAM Capstone Project1a

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PIVOTTABLES

GETTING STARTED

?Open the fleNP_EX16_CS5-8a_FirstLastName_1.xlsx, available ±ordownload ±rom the SAM website

.?Save the fle asNP_EX16_CS5-8a_FirstLastName_2.xlsxby changing the “1”to a “2”.oI± you do not see the.xlsxfle extension in the Save As dialog box, do nottype it. The program will add the fle extension ±or you automatically.
?To complete this SAM Project, you will also need to download and save the±ollowing data fles ±rom the SAM website onto your computer:oSupport_NP_EX16_CS5-8a_Staf.xlsx

?With the fleNP_EX16_CS5-8a_FirstLastName_2.xlsxstill open, ensure thatyour frst and last name is displayed in cell B6 o± the Documentation sheet.oI± cell B6 does not display your name, delete the fle and download a newcopy ±rom the SAM website.

PROJECT STEPS

1.Andre Suarez is the senior director o± Wild Planet Conservation, a nonproftorganization that supports A±rican wildli±e and operates in Cali±ornia, Oregon,and Washington state. He has asked ±or your help updating the worksheet hecreated to track and record fnancial in±ormation.Switch to theLocal Ofcesworksheet, and then unprotect it

.2.To make it easy to fnd detailed sta² in±ormation stored in another workbook,create a hyperlink as ±ollows:a.In cell A7, insert a hyperlink to theSupport_NP_EX16_CS5-8a_Staf.xlsxfle, available ±or download ±rom the SAM website. (Makesure all the fles ±or this project are in the same ±older.)b.UseStaf InFormationas the text to display.c.UseDetailed staf inFormation For all locationsas the ScreenTip text.

3.View the comment in cell A12. Follow the instructions in the comment to updatea value in the worksheet, and then delete the comment.

4.Andre started to create named ranges in the worksheet and has asked you tofnish the updates as ±ollows:

a.Edit the defned name associated with cell D12 to beCA_Runinstead o±CA_RunWalk.

b.Create a defned name For cell D24 usingWA_Runas the name.

c.Select the range ±16:G20 and create names From the selection using thevalues shown in theLeft column.

5.In cell G11, insert a Formula using theSUMFunction that uses the defnednamesCA_Adopt,OR_Adopt, andWA_Adoptto calculate the total amountraised For the Adopt an Animal Fundraiser.

6.To include a note about revenue From Fundraisers, insert a comment into cellG13. ±irst typeThis amount exceeded our goal this year!, delete anypreviously 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 adata validation rule that accepts only values From a list as Follows:

a.In cell G17, insert a data validation rule that accepts values From aList,ignores blanks, and appears as an in-cell dropdown.

b.Enter the listCalifornia, Oregon, Washingtonas the validation source.

c.±or the input message, useOFce Locationas the title andSelect anoFce location from the list.(including the period) as the Inputmessage.

d.±or the error alert, use theStopstyle withInvalid locationas the title.

8.Create a data validation rule that accepts only specifed date values as Follows:

a.In cell G18, insert a data validation rule that acceptsDatevalues Fordates between3/1/2019and3/31/2019.

b.±or the input message, useMarch Event Dateas the title and use thetextEnter date for March fundraiser.(including the period) as theInput message.

c.±or the error alert, use theStopstyle withInvalid March event dateasthe title.

9.Edit the data validation rule associated with cell G19 as Follows:

a.Change the input message title toMay Event Dateand useEnter datefor May fundraiser.(including the period) as the Input message.

b.Change the error alert to use theStopstyle withInvalid May eventdateas the title.

10.Test the data validation rules by entering the Following inFormation into theworksheet:
a.In cell G16, enterAmanda Arnettas the Name.

b.In cell G17, selectOregonas the WPC O²ce.

c.In cell G18, enter3/12/2019as the March Date.

d.In cell G19, enter5/15/2019as the May Date.

e.In cell G20, selectRun for the Wildas the Event.

11.Andre receives monthly revenue worksheets From each oF the three local o²ces.Apply the same Formatting to the three worksheets and update them as Follows:

a.Group theCalifornia,Oregon, andWashingtonworksheets.

b.In cell A4, edit the text to readPatron(instead of Member).

c.Boldthe values in the range G4:G8.

d.In cell B8, enter a formula using theSUMfunction that totals the revenuefor January (the rangeB4:B7). Copy the formula to the range C8:F8.

e.Ungroup the worksheets and then check to con±rm that all threeworksheets re²ect the changes you made in this step.

12.Switch to theConsolidatedworksheet. Consolidate the revenue data from eachof the o³ces as follows:

a.In cell A4, enter a formula without using a function that references cellA4in theWashingtonworksheet. Copy the formula from cell A4 to the rangeA5:A7.

b.In cell B4, enter a formula using theSUMfunction, 3-D references, andgrouped worksheets that totals the values from cellB4in theCalifornia:Washingtonworksheets.

c.Copy the formula from cell B4 to the range B5:B7 without copying theformatting.

d.Copy the formulas and the formatting from the range B4:B7 to the rangeC4:F7.

13.Andre has set a revenue goal of $20,000 for January, February, and April, whenthe organization has no fundraisers, and a revenue goal of $200,000 for Marchand May, when the organization does have fundraisers.In cell B10, enter a formula using theIFandANDfunctions to indicate whetherthe revenue goal has been met that month:

a.Enter the logical test using theANDfunction to determine if theFundraisers amount in cellB7equals 0and the Total in cellB8isgreaterthan 20000.

b.If the logical test is true, displayYes(using “Yes” for the value_if_trueargument).

c.If the logical test is false, insert a nestedIFfunction.

d.Enter the logical test of the nested IF function using theANDfunction todetermine if the Fundraisers amount in cellB7isgreater than 0and theTotal in cellB8isgreater than 200000.

e.If the logical test for the nested IF function is true, displayYes(using“Yes” for the value_if_true argument).

f.If the logical test is false, displayNo(using “No” for the value_if_falseargument).Copy the formula in cell B10 to the range C10:F10.

14.The conditional formatting rule Andre created in the range B10:F10 highlightsmonths that do not meet the revenue goal. Remove the ±ll color from thehighlighting as follows:
a.Edit the conditional formatting rule applied to the range B10:F10.

b.Change the format so the Fill color isNo Color, while keeping the samefont color and style

15.Go to theSpringworksheet. In theSpringandFallworksheets, Andre has storedmembership revenue for the spring and fall of 2018. Format theSpringworksheet to match theFallworksheet as follows:

a.Format the range A2:E56 as an Excel table with headers using theLime,Table Style Medium 4table style. (Hint: Depending on your version ofO±ce, the table style may be written as Table Style Medium 4.)

b.UseSpringMembershipsas 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 HLOOKUPfunction as follows:

a.In cell C3, enter a formula using theHLOOKUPfunction.

b.Use a structured reference to the Amount column ([Amount]) as thelookup value.

c.Look up that value in the table array shown in the rangeG2:J3, using anabsolute reference to that range.

d.Return the value in row2of the table array.

e.UseTRUEas the range_lookup parameter, so that the formula will ²nd theclosest approximate value (as the level covers a range of membershipamounts.)

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 con²rm that all the membership data was entered correctly intothe SpringMemberships table. Check for duplicate Member ID values as .In the range A3:A55, apply a conditional formattingHighlight Cells Rulethat formats anyduplicatevalues withLight Red Fill with Dark RedText.

18.Correct the duplicate values by updating the following Member ID values. (Hint:When you complete this substep, the conditional formatting rule should nolonger highlight any values in the range.)

a.UseCA-240as the MemberID value for the record in row 10, which hasMillion+ as the Type, Patron as the Level, Elephant as the Animal, and$125 as the amount.

b.UseWA-122as the MemberID value for the record in row 37, which hasFaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and$350 as the amount.

19.Switch to theFallworksheet. In theFallworksheet, Andre wants to include aquick way to look up member information based on the Member ID value. Todisplay the type of membership, enter a formula in cell H3 using theVLOOKUPfunction as follows:

a.Use cellH2as the lookup value.

b.Use a reference to theFallMembershipstable as the table array.

c.Use column2as the column index number.

d.UseFALSEas the range_lookup argument, so that the function returns anexact match to the Member ID value shown in cell H2.

20.A popular way to become a member of Wild Planet Conservation is through theFaceLink social media website, and Andre wants to determine the total numberof members who signed up using their FaceLink account.

a.In cell H7, enter a formula using theCOUNTIFfunction to count thenumber of FaceLink members.

b.Use a structured reference to theTypecolumn in the FallMembershipstable as the range.

c.Use“FaceLink”as the formula criteria.

21.Determine the average amount FaceLink members paid to join the organizationas follows:

a.In cell I7, enter a formula using theAVERAGEIFfunction to average theamount paid by FaceLink members.

b.Use a structured reference to theTypecolumn in the FallMembershipstable as the range.

c.Use“FaceLink”as the formula criteria.

d.Use a structured reference to theAmountcolumn in the FallMembershipstable 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 ±rst inascendingorder by theLevel±eld and then in the descending order by theAmount±eld.

b.Insert aTotal Rowin the FallMemberships table. (Hint: The total of thevalues in the Amount ±eld will automatically appear in cell E63.)

c.In cell D63, use the Count function in the in-cell dropdown to calculate thenumber of members using the values in the Animal ±eld

.23.Andre would like to be able to manipulate and ±lter the fall membership data inseveral ways.

a.Create a PivotTable based on the FallMemberships table in a newworksheet, usingFall PivotTableas the worksheet name
.b.Add theAnimal±eld and theMember ID±eld (in that order) to the Rowsarea.

c.Add theAmount±eld to the Values area.

d.Update the Sum of Amount ±eld in the Values area to display the nameMembership Amountusing theAccountingnumber format with0decimal places and$as the symbol.

e.Apply theLime, Pivot Style Medium 4PivotTable style to the PivotTable.(Hint: Depending on your version of O²ce, the PivotTable style may bewritten as Pivot Style Medium 4.)

24.Filter the PivotTable as follows to make it easier to analyze information:

a.Create a ±lter for the PivotTable by adding theType±eld to the Filtersarea.

b.Filter the table so it displays only members who signed up throughFaceLink

.c.Insert a slicer that flters the PivotTable based on theLevelfeld value.

d.Resize and reposition the slicer so that its upper-le±t corner appears withincell E3 and its lower-right corner appears within cell H10.

e.Use the slicer to flter the PivotTable to display only members at theBenefactorlevel.

Your workbook should look like the Final Figures on the ±ollowing pages. Save yourchanges, close the workbook, and then exit Excel. Follow the directions on the SAMwebsite 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