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
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.
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 and structured
references to determine a staff member’s base salary (which is based on
Service Years):
a.
Use a structured reference to the Service Years column ([Service
Years]) as the lookup_value argument.
b.
Use an absolute reference to the range Q15:U16 as the table_array
argument.
c.
Use 2 as the row_index_num argument.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
d.
Use TRUE as the range_lookup argument, so that the formula will find
the closest approximate value (as staff members do not receive a raise
after every year of service.)
Fill the formula into the range E3:E31 if necessary.
4.
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
Certification. The formula should subtract the value shown in the First Aid
Certification Year column ([First Aid Certification Year]) 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 ([Age]).
b.
If the staff member’s age is greater than or equal to 23, the function
should return the text Yes (using “Yes” for the value_if_true argument).
c.
If the staff member’s age is not greater than or equal to 23, the function
should return the text No (using “No” for the value_if_false argument).
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
([Service Years]) and the College Graduate columns ([College
Graduate]).
b.
If a staff member meets one or both of those criteria, the function should
return the text Yes (using “Yes” for the value_if_true argument).
c.
If a staff member meets neither of those criteria, the function should
return the text No (using “No” for the value_if_false argument).
To be a Bunk Leader, a staff member must have over 4 service years and have
completed leadership training.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
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.
9.
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 ([Service Years]) and the Leadership Training columns
([Leadership Training]).
b.
If a staff member meets both of those criteria, the function should return
the text Yes (using “Yes” for the value_if_true argument).
c.
If a staff member meets none or only one of those criteria, the function
should return the text No (using “No” for the value_if_false argument).
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 (using
“Completed” as the value_if_true argument). Remember to use a
structured reference to the Leadership Training column ([Leadership
Training]).
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
([Service Years]) is greater than 1.
c.
If the staff member’s Service Years value is greater than 1, the formula
should return the text Yes (using “Yes” as the value_if_true argument of
the nested IF function).
d.
If the staff member’s Service Years value is not greater than 1, the
formula should return the text No (using “No” as the value_if_false
argument of the nested IF function).
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.
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 ([Service Years]).
b.
If the staff member’s Service Years is greater than 3, the function should
return the text Senior (using “Senior” for the value_if_true argument).
New Perspectives Excel 2016 | Module 8: SAM Project 1a
c.
12.
If the staff member’s Service Years is not greater than 3, the function
should return the text Junior (using “Junior” for the value_if_false
argument).
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.
14.
In cell Q4, enter a formula using the VLOOKUP function using the following
parameters.
a.
Use cell Q2 as the lookup_value argument.
b.
Use CBFStaff[#Data] (which represents the table shown in A2:N31) as
the table_array argument.
c.
Use 4 as the col_index_num argument.
d.
Use FALSE as the range_lookup argument, so that the function will only
return an exact match to the Staff ID value shown in cell Q2.
Dean wishes to determine several totals and averages for his staff.
In cell Q9, enter a formula using the COUNTIF function to count the number of
staff with Leadership Training. Use the Leadership Training column
(CBFStaff[Leadership Training]) as the range in the formula and “Yes” as
the formula criteria.
15.
16.
In cell R9, enter a formula using the AVERAGEIF function to determine the
average number or service years of staff members with leadership training.
a.
Use the Leadership Training column (CBFStaff[Leadership Training])
as the range in the formula.
b.
Use “Yes” as the formula criteria.
c.
Use the Service Years column (CBFStaff[Service Years]) as the
average_range argument.
In cell R10, enter a formula using the AVERAGE function to determine the
average service years of all staff as shown in the Service Years column
(CBFStaff[Service Years]).
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.
New Perspectives Excel 2016 | Module 8: SAM Project 1a
Final Figure 1: Staff Listing Worksheet – Columns A:N
Final Figure 2: Staff Listing Worksheet – Columns O:U
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a
SpringLeaf Designs
FORMATTING, FORMULAS, AND CHARTS
GETTING STARTED
•
Open the file NP_EX16_CS1-4a_FirstLastName_1.xlsx, available for
download from the SAM website.
•
Save the file as NP_EX16_CS1-4a_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_CS1-4a_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.
Geneva Huddleston works for SpringLeaf Designs. Over the years, the company
has released a variety of products, and Geneva’s manager would like her to
make a spreadsheet to help the company keep track of sales figures.
Change the theme of the workbook to Office.
2.
On the Merchandise worksheet, change the width of columns D through G to
14.00 characters.
3.
Change the height of row 2 to 32 pt.
4.
Merge and center the contents of the range B2:J2.
5.
Format the merged ranges B2:J2 and L2:M2 as described below:
6.
a.
Apply the Title cell style.
b.
Apply bold formatting.
c.
Change the font color to Green, Accent 6, Darker 50% (10th column,
6th row of the Theme Colors palette).
d.
Change the cell fill color to Green, Accent 6, Lighter 40% (10th column,
4th row of the Theme Colors palette).
Format the ranges B3:J3 and L3:M3 as described below:
a.
Center cell contents.
b.
Change the font to Arial Black.
c.
Change the font size to 9 pt.
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a
d.
Change the font color to Green, Accent 6, Darker 50% (10th column,
6th row of the Theme Colors palette).
e.
Apply the Top and Double Bottom Border cell border.
f.
Apply wrap text formatting.
7.
Select range B4:B14, and then increase the indent of the cell contents once.
8.
Italicize the range B15:B18 and the range B20:B21.
9.
Format the range I4:I18 with the Accounting number format with zero
decimal places and use $ as the symbol. (Hint: Depending on how you
complete this action, the number format may appear as Custom instead of
Accounting.)
10.
Geneva’s manager would like to see some additional information to help make
sales decisions.
Enter a formula in cell J4 using the IF function to it whether or not the item
Beaded Earrings should go on sale. It will go on sale if its current sales rank (in
cell G4) is 8th or lower. (Hint: If the cell value is greater than or equal to 8, it
will go on sale.)
a.
If this condition is true, the item will go on sale. (Hint: The if_true value
should be “YES”.)
b.
If this condition is false, the item doesn’t need to go on sale. (Hint: The
if_false value should be “-”.)
c.
Copy the formula created in cell J4 to the range J5:J14.
11.
Enter a formula in cell E4 to calculate how long each item has been on sale.
This is calculated by subtracting the launch date in cell D4 from the Data
Updated date in cell C20. To show the time in years, divide that result by 365.
Use an absolute reference to the date in cell C20. Copy the formula from cell
E4 to cells E5:E14.
12.
For the range E4:E18, update the number format by decreasing the number of
decimal places displayed to 1.
13.
Enter a formula in cell I15 using the SUM function to total the current sales in
the range I4:I14.
14.
Enter a formula in cell I16 using the AVERAGE function to calculate the
average current product sales based on the range I4:I14.
15.
Enter a formula in cell I17 using the MAX function to calculate the highest
selling product based on the range I4:I14.
16.
Enter a formula in cell I18 using the MIN function to calculate the lowest selling
product based on the range I4:I14.
17.
Add Solid Fill Green Data Bars to range I4:I14.
18.
Enter a formula in cell M6 using the VLOOKUP function to find an approximate
match for the sale status of scarves. Use “Scarves” as the lookup_value, the
range B3:J14 as the table_array, 9 as the col_index_num argument, and
TRUE as the range_lookup argument.
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a
19.
In cell C21, enter a formula using the TODAY function that displays the current
date.
20.
Hide column F.
21.
The company is considering expanding, and Geneva’s manager asked her to
review some numbers for growth options.
On the Projections worksheet, use AutoFit to modify the width of column B to
best fit cell contents.
22.
Use the Format Painter to copy the format only from the range B3:F3 to the
range B11:F11. Then use the Format Painter to copy the format only from the
range B9:F9 to the range B15:F15.
23.
Enter a formula in cell C9 using the PMT function to calculate the monthly
payment on a loan using the assumptions listed in the Status Quo scenario. In
the PMT formula, use C6 as the monthly interest rate (rate), C8 as the total
number of payments (nper), and C4 as the loan amount (pv). Enter this
formula in cell C9, and then copy the formula to the range D9:F9.
24.
Select cell F9 and use Goal Seek Analysis to determine the loan amount in the
Aggressive Expansion scenario based on a monthly payment of $12,000. In the
Goal Seek calculations, set the value of cell F9 to -12,000 and select cell F4
(Total Loan Amount) as the changing cell. Keep the outcome of the Goal Seek
Analysis as the value of cell F4.
25.
Enter a formula in cell C15 using the SUM function to calculate total revenues
for the current year (or the range C12:C14). Use the Fill Handle to copy the
formula from cell C15 to the range D15:F15.
26.
There’s a lot of numerical information in this worksheet, so some visuals might
help make the data easier to interpret.
Insert Column Sparklines in the range G12:G14 based on data in the range
C12:F14. Change the sparkline color to Green, Accent 6, Darker 50% (10th
column, 6th row of the Theme Colors palette).
27.
Select the range B12:C14, and insert a 2-D Pie chart segmenting revenues by
product. Reposition the chart so the upper-left corner is in cell H2.
28.
Make the following changes to the chart:
29.
a.
Change the chart style to Style 2.
b.
Change the chart title to Current Year Revenues and change the font
color to Blue (8th column, 1st row of the Standard Colors palette).
c.
Add Center data labels to the chart.
d.
Move the chart legend to the Left position.
Select the Actual and Projected Revenues chart located in the range H16:M30.
Make the following changes to the chart:
a.
Change the major units of the vertical axis to 5000000.
b.
Add Growth Scenarios as the horizontal axis title and Revenue as the
vertical axis title.
c.
Remove the data labels from the graph.
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a
d.
Move the chart legend to the Bottom position.
30.
Set the range B1:G15 as the print area of the worksheet. Change the
worksheet orientation to Landscape.
31.
Delete the Sales Summary worksheet.
Your workbook should look like the Final Figures on the following pages. (The value
in cell F4 on the Projections worksheet generated by the Goal Seek Analysis has
intentionally been blurred in the Final Figure.) 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: Merchandise Worksheet
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a
Final Figure 2: Projections Worksheet – A1:G16
Final Figure 3: Projections Worksheet – H1:M31
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 1-4: ...

Purchase answer to see full
attachment