University of North Texas Camp Bright Firewood Worksheet

User Generated

Xnggrl

Business Finance

University of North Texas

Description

There are 2 files word file is the instruction and excel file is where all work is done. All the instruction provided in the word should be done in excel. the excel file should be saved as mentioned in instruction. overall, everything should be exactly what the instruction file is asking you to do.

Unformatted Attachment Preview

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 t SAM website. New Perspectives Excel 2016 | Module 8: SAM Project 1a Camp Bright Firewood WORKING WITH ADVANCED FUNCTIONS Anuja Kattel edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Staff ID 1103 1053 1056 1035 1094 1066 1050 1054 1070 1040 1010 1073 1069 1060 1043 1034 1084 1078 1036 1074 1076 1009 1089 1022 1033 1065 1078 1048 1053 1064 Name Adam Moriarty Artie Jimenez Bo Peterson Claire Hunter Eileen Randall Flossie Frampton Grace Richards Hayley Jin Hiro Marumi Isaac Runyon Isabella Thorne James Gutierrez Joey Stockton Julio deSouza Kira Weston Liam Swanson Maria Flatley Melinda Masterson Oscar Alberts Philip Jones Richard Tepper Robert Miller Sarah Winters Sienna Shapiro Stephanie Ito Stephen McAllister Sylvia Lee Tanya Oldman Tristan Reinholt Walter Kaminsky Age Service Years 18 28 24 35 18 23 30 25 23 33 42 22 23 24 32 35 19 20 33 22 21 44 19 38 35 23 20 30 25 23 Base Salary 1 9 7 10 1 0 5 8 4 8 25 3 0 7 4 18 1 2 16 5 4 25 2 16 17 6 3 13 8 6 First Aid Certification Year 2017 2015 2013 2016 2013 2016 2017 2013 2013 2016 2017 2014 2015 2013 2015 2016 2017 2017 2015 2015 2014 2014 2015 2016 2016 2017 2013 2013 2014 2013 Years since First Aid Certification College Graduate No Yes Yes No No Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No No Yes No No Yes No Yes Yes No No Yes Yes Yes Leadership Training No Yes No Yes No No Yes No Yes No Yes No No Yes No Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes Transportation Group Leader Bunk Leader Candidate for Leadership Training Staff ID Staff Name Service Years 1036 Oscar Alberts Total Staff Leadership Trained Staff All Staff Service Years Base Salary Average Service Years 0 $ 0 15.00 $ 1 15.50 $ 2 16.75 $ 5 17.50 $ 7 18.50 New Perspectives Excel 2016 | Module 8: SAM Project 1a Camp Bright Firewood WORKING WITH ADVANCED FUNCTIONS GETTING STARTED • Open the file NP_EX16_8a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX16_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. With the file NP_EX16_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. Dean Hilson is the director of Camp Bright Firewood. He started planning the camp staffing assignments for next year in an Excel table, and needs your help completing the table. First, he’d like to confirm that all the staff data was entered correctly into the worksheet. Go to the Staff Listing worksheet and complete the following steps: a. Apply a conditional formatting Highlight Cells Rule to the range A2:A31 that formats any duplicate values with Light Red Fill with Dark Red Text. b. Correct the duplicate values by updating the Staff ID for Artie Jimenez to 1055 and the Staff ID for Sylvia Lee to 1087. (Hint: When this sub-step is completed, the conditional formatting rule should no longer highlight any values in the range.) 2. Delete the conditional formatting rule applied to the range D2:D31. 3. In cell E2, enter a formula using the HLOOKUP function to determine a staff member’s base salary (which is based on the number of years of service). 4. a. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2nd row of the table in the range Q15:U16. Since base salary is tiered based on the number of years of service, find an approximate match. b. Fill the formula into the range E3:E31, if necessary. In cell G2, enter a formula without a function using structured references to determine the number of years since Adam Moriarty received his last First Aid New Perspectives Excel 2016 | Module 8: SAM Project 1a Certification. The formula should subtract the value shown in the First Aid Certification Year column from 2018 and should automatically fill to the range G3:G31. 5. The conditional formatting rule Dean created in the range G2:G31 highlights staff members who were last First Aid certified 3 or more years ago (and will be required to retake the First Aid Certification class), but it doesn’t stand out enough. Edit the conditional formatting rule applied to the range G2:G31, so that the highlighted cells are formatted using the bold font style and the Orange, Accent 2 font color (6th column, 1st row in the Theme Colors palette). 6. According to camp policy, staff members need to be at least 23 years old to transport students. Dean now wants to determine how many members of his staff will be available to drive. In cell J2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty can be a Transportation team member. 7. a. The IF function should first determine if the staff member’s age is greater than or equal to 23. Use a structured reference to the Age column. b. The function should return the text Yes if the staff member’s age is greater than or equal to 23. c. The function should return the text No if the staff member’s age is not greater than or equal to 23. To be a Group Leader, the staff member must either have more than 3 service years or be a college graduate. In cell K2 enter a formula using the IF and OR functions, as well as structured references, to determine if Adam Moriarty can be a group leader. 8. a. The IF function should first determine if the staff member’s Service Years is greater than 3 OR if the staff member’s college graduate status is “Yes”. Remember to use a structured reference to the Service Years and the College Graduate columns. b. The function should return the text Yes if a staff member meets one or both of those criteria. c. The function should return the text No if a staff member meets neither of those criteria. To be a Bunk Leader, a staff member must have over 4 service years and have completed leadership training. In cell L2, enter a formula using the IF and AND functions, as well as structured references to determine if Adam Moriarty can be a bunk leader. a. The logical test in the IF function should determine if the staff member’s Service Years is greater than 4 AND the staff member’s Leadership Training status is “Yes”. Remember to use a structured reference to the Service Years and the Leadership Training columns. b. The function should return the text Yes if a staff member meets both of those criteria. New Perspectives Excel 2016 | Module 8: SAM Project 1a c. 9. The function should return the text No if a staff member meets none or only one of those criteria. Many of the special staff teams require leadership training, which is offered to staff with more than 1 year of service at Camp Bright Firewood. Dean wants to identify the staff members eligible for leadership training in the table. In cell M2, enter a formula using a nested IF function and structured references to determine first if a staff member already has completed leadership training, and if not, whether that staff member is eligible for leadership training. 10. a. If the value of the Leadership Training column is equal to the text “Yes”, the formula should return the text Completed. Remember to use a structured reference to the Leadership Training column. b. If the value of the Leadership Training column is not equal to yes, the formula should determine if the value in the Service Years column is greater than 1. c. The formula should return the text Yes if the staff member’s Service Years value is greater than 1. d. The formula should return the text No if the staff member’s Service Years value is not greater than 1. Staff members with over 3 years of experience are considered Senior Staff members and receive first choice of staffing assignments. Dean needs a new calculated column in the table to identify senior staff members. In cell N1, enter the text Staff Level as the calculated column heading. (Hint: If a filter arrow appears in cell N1, ignore it.) 11. 12. In cell N2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty is a Senior Staff member. a. The IF function should first determine if the staff member’s Service Years is greater than 3. Remember to use a structured reference to the Service Years column. b. The function should return the text Senior if the staff member’s Service Years is greater than 3. c. The function should return the text Junior if the staff member’s Service Years is not greater than 3. Dean wants a quick way to look up staff members by their Staff ID. In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text “Invalid Staff ID” should be displayed by the formula. (Hint: You can test that this formula is working by changing the value in cell Q2 to 0, but remember to set the value of cell Q2 back to 1036 when the testing is complete.) 13. In cell Q4, enter a formula using the VLOOKUP function to display a staff member’s years of service. Look up the staff ID listed in cell Q2. Use a structured reference to retrieve the value in the 4th column of the CBFStaff table. Since the formula is looking for a specific staff member’s data, find an exact match. New Perspectives Excel 2016 | Module 8: SAM Project 1a 14. Dean wishes to determine several totals and averages for his staff. In cell Q9, enter a formula using the COUNTIF function and structured references to count the number of staff members who have participated in Leadership Training. 15. In cell R9, enter a formula using the AVERAGEIF function and structured references to determine the average the number of service years of staff members who have participated in Leadership Training as shown in the Service Years column. 16. In cell R10, enter a formula using the AVERAGE function and a structured reference to determine the average number of years of service of all staff as shown in the Service Years column. Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Staff Listing Worksheet – Columns A:N New Perspectives Excel 2016 | Module 8: SAM Project 1a Final Figure 2: Staff Listing Worksheet – Columns O:U
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached is the complete solution.I have attached the same file by zip folder also (in case needed.)

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 t
SAM website.

New Perspectives Excel 2016 | Module 8: SAM Project 1a

Camp Bright Firewood

WORKING WITH ADVANCED FUNCTIONS
Anuja Kattel

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

Staff ID
1103
1055
1056
1035
1094
1066
1050
1054
1070
1040
1010
1073
1069
1060
1043
1034
1084
1078
1036
1074
1076
1009
1089
1022
1033
1065
1087
1048
1053
1064

Name
Adam Moriarty
Artie Jimenez
Bo Peterson
Claire Hunter
Eileen Randall
Flossie Frampton
Grace Richards
Hayley Jin
Hiro Marumi
Isaac Runyon
Isabella Thorne
James Gutierrez
Joey Stockton
Julio deSouza
Kira Weston
Liam Swanson
Maria Flatley
Melinda Masterson
Oscar Alberts
Philip Jones
Richard Tepper
Robert Miller
Sarah Winters
Sienna Shapiro
Stephanie Ito
Stephen McAllister
Sylvia Lee
Tanya Oldman
Tristan Reinholt
Walter Kaminsky

Age

Service Years
18
28
24
35
18
23
30
25
23
33
42
22
23
24
32
35
19
20
33
22
21
44
19
38
35
23
20
30
25
23

Base Salary
1
9
7
10
1
0
5
8
4
8
25
3
0
7
4
18
1
2
16
5
4
25
2
16
17
6
3
13
8
6

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

15,50
18,50
18,50
18,50
15,50
15,00
17,50
18,50
16,75
18,50
18,50
16,75
15,00
18,50
16,75
18,50
15,50
16,75
18,50
17,50
16,75
18,50
16,75
18,50
18,50
17,50
16,75
18,50
18,50
17,50

First Aid Certification Year
2017
2015
2013
2016
2013
2016
2017
2013
2013
2016
2017
2014
2015
2013
2015
2016
2017
2017
2015
2015
2014
2014
2015
2016
2016
2017
2013
2013
2014
2013

Years since First Aid
Certification
1
3
5
2
5
2
1
5
5
2
1
4
3
5
3
2
1
1
3
3
4
4
3
2
2
1
5
5
4
5

College
Graduate
No
Yes
Yes
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Ye...


Anonymous
Really useful study material!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags