PROJECT DESCRIPTION
Henrique Silva works in the Sales department of Spring Software, a
software development company headquartered in Cambridge, Massachusetts.
Henrique has created a worksheet to capture customer sales data for the
Nashua office for the past three years. He has asked you to enhance the
workbook using conditional formatting and advanced formulas to better
analyze the customer data, highlight trends, and identify top customers.
GETTING STARTED
⢠Download the following file from the SAM website:
o NP_Excel2013_T8_P1a_FirstLastName_1.xlsx
⢠Open the file you just downloaded and save it with the name:
o NP_Excel2013_T8_P1a_FirstLastName_2.xlsx
o Hint: If you do not see the .xlsx file extension in the Save file
dialog box, do not type it. Excel will add the file extension for you
automatically.
⢠With the file NP_Excel2013_T8_P1a_FirstLastName_2.xlsxstill open,
ensure that your first and last name is displayed in cell B6 of the
Documentation sheet. If cell B6 does not display your name, delete the
file and download a new copy from the SAM website.
PROJECT STEPS
1. Go to the CustomerListworksheet and complete the following actions:
a. Apply a conditional formatting rule to the range A4:A32 that formats
any duplicate values with Yellow Fill with Dark Yellow Text.
b. Update the Customer ID value for Firehouse Partners LLC to 1015 and
the Customer ID value of the Applewild Learning Center to 1023.
The conditional formatting rule should no longer highlight any values in the range A4:A32.
2. Edit the conditional formatting rule applied to the range G4:G32 so
that the highlighted cells are formatted with the font color is White,
Background 1 and the fill color Blue, Accent 5, Lighter 40% (9th column,
4th row in the Theme color palette.)
3. In cell D4, enter a formula that calculates customer tenure, in
years, using absolute and structured references. The formula should
calculate tenure based on subtracting the value in the First Order
column from the current year value in cell B1. If necessary, copy the
formula you created in step D4 to the range D5:D32. (Tip: Remember to
use an absolute reference to the current year value in cell B1.)
4. In cell I4, enter a formula that uses the IF functionand structured referencesto calculate the 2-Year Growth Percentage.
a. The 2-Year Growth Percentage can be calculated if the value in 2015 Sales column is greater than 0.
b. To calculate the 2-Year Growth Percentage, divide the value in the
2-Year Sales Growth column by the value in the 2016 Sales column.
c. If the value in the 2015 Sales column is not greater than 0, return a value of N/A.
5. In cell J4, enter a formula that uses an IF function and structured
referencesto determine customer discount eligibility.(Tip: You will need
to use the OR function in this formula.)
a. A customer is eligible for a discount if the customerâs 2016
salesgreater than or equal to 100000OR if the customers First Order was
placed in 2016.
b. If the customer qualifies for a discount, return a value of Y. (Tip: For the value_if_true value, use âYâ.)
c. If the customer does not qualify for a discount, return a value of N. (Tip: for the value_if_false, use âNâ.)
6. In cell K4, enter a formula that uses a nested IF functionand structured references to calculate the discount percentage:
a. If the value in Discount Y/N column is equal to N, the Discount column value should be 0.
b. If the value in the Discount Y/N column is equal to Y, the formula
should check if the value in the Tenure (Yrs) column is less than 5.
c. If the value in Tenure (Yrs) column is less than 5, the Discount column value should be 0.07
d. Otherwise, the value of Discount column should be 0.1.
(Note: The Discount column is formatted with the Percentage Number
format, so the values returned by the nestedIF function will appear as
0%, 7%, or 10%.)
7. In cell L4, Enter a formula that uses an IF function and structured
references to assign a priority value to each customer. (Tip: You will
need to use an AND function in this formula.)
a. The If function should check if a customer has a Tenure field value
greater than or equal to 5 yearsAND2016 sales field value greater than
or equal to 150000.
b. If the customer meets both those criteria, the function should return the value High.
c. If the customer does not meet both those criteria, the function should return the valueLow.
8. In cell Q4, nest the VLOOKUP function in an IFERROR function. The
cell should display the error message Invalid Customer ID instead of the
error value, if the VLOOKUP function finds an error. Confirm the error
message appears and then update the Customer ID value in cell Q3 to
1019.
9. In cell Q6, enter a formula using the VLOOKUP function to lookup the
Customer ID value shown in cell Q3in the CustomerNashua table (located
in the range A3:L32). The VLOOKUP function should then retrieve the
Discount value from the CustomerNashua table (the 11th column in the
table) for this record. The VLOOKUP function should find an exact match
to the value in Q3.
10. In cell Q7, enter a formula using the HLOOKUP function to determine
the free services customer incentive offered to Spring Sales bigger
clients.The HLOOKUP function should look up the value in cell Q5in the
range N10:R11 (which has the defined name CustomerIncentives). The
HLOOKUP function should then retrieve the value in the 2nd row of the
CustomerIncentiveslookup table. Since these free services are offered to
customers that meet or exceed the yearly sales levels listed in the
CustomerIncentives table, the HLOOKUP function should find an
approximate match to the value in Q5.
11. Go to Customer Analysis worksheet. In cell C4, enter a formula that
uses the COUNT function and structured references that counts the total
number of customers in the CustomersNashuatable on the CustomerList
worksheet (Tip: The COUNT function only counts rows that contain a
value, you cannot use the Customer column as an argument in the
function. Use the Customer ID column instead.)
12. In cell D4, enter a formula that uses the COUNTIF functionand
structured references to count the number of customers with a Priority
rating of Highin the CustomersNashuatable on the CustomerList worksheet
13. In cell C5, enter a formulathat uses the SUM function and structured references to calculate 2016sales for all customers.
14. In cell D5, enter a formula that uses the SUMIF function and
structured references to calculate 2016 sales for customers with a
Priority rating of High in the CustomersNashuatable on the CustomerList
worksheet
15. In cell D6, enter a formula to calculate the percentage of High
Priority CustomersSales out of All Customers2016 sales. (Tip: The
calculation should divide the 2016 High Priority Customer sales by the
total 2016 sales for all customers.) Format the cell using the
Percentage number format with no decimal places.
16. In cell C7, enter a formula that uses the AVERAGE function and
structured references to calculate the average tenure of all customers
in the CustomersNashuatable on the CustomerList worksheet
17. In cell D7, enter a formulathat uses the AVERAGEIF function and
structured references to calculate the average tenureof customers with a
Priority rating of High in the CustomersNashuatable on the CustomerList
worksheet
18. In cell C8, enter a formula to calculate the average sales per customer for all customers.
19. In cell D8, enter a formula to calculate the average sales per High Priority Customer.
Your workbookshould look like the Final Figures on the following page.
Save your changes, close the workbook, and exit Excel. Follow the
directions on the SAM website to submit your completed project.
