Homework answers / question archive / New Perspectives Excel 2013 Tutorial 8: SAM Project 1b Fit Kix Footwear ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING PROJECT DESCRIPTION Arun Modur works in the Customer Relations department of Fit Kix Footwear, an athletic shoe retailer based in New England

New Perspectives Excel 2013 Tutorial 8: SAM Project 1b Fit Kix Footwear ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING PROJECT DESCRIPTION Arun Modur works in the Customer Relations department of Fit Kix Footwear, an athletic shoe retailer based in New England

MS Excel

Share With

---

New Perspectives Excel 2013 Tutorial 8: SAM Project 1b

Fit Kix Footwear

ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING

PROJECT DESCRIPTION

Arun Modur works in the Customer Relations department of Fit Kix Footwear, an athletic

shoe retailer based in New England. He has created a worksheet to capture customer sales

data for the past two years, and year-to-date sales data for the current year. Arun 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

Open the file you just downloaded and save it with the name:

NP_Excel2013_T8_P1b_ FirstLastName _2.xlsx

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_P1b_ FirstLastName _2.xlsx still 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 All

Customers

worksheet and complete the following actions:

a.

Apply a

conditional formatting

rule to the range A4:A27 that

formats

any duplicate

values with

Green Fill

with Dark Green

Text

.

b.

Update the Customer ID value for

Sarah Sun

to

125

and the

Customer ID value of

Ariel Valdez

to

126

.

The conditional formatting rule should no longer highlight any values in the

range A4:A27.

2. 

Edit the conditional formatting rule applied to the range

J4:J27 so that

the highlighted cells are formatted with the font color

Dark Red

(1

st

icon in

Standard Colors palette)

and the fill color

Orange

(3

rd

icon in Standard Colors

palette).

3. 

In cell E4, 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 M1. If necessary, copy the formula you created in

cell E4

to the range E5:E27. (

Hint:

Remember to use an

absolute

reference

to the current year value in cell M1.)

4. 

In cell I4, enter a formula that uses the

IF

function and

structured

references

to calculate customer sales Growth (as a %).

a. Growth can be calculated if the value in the

2015 Purchases

column is greater than 0.

b.  To calculate Growth (as a %), divide the value in the

Growth

($)

column by the value in the

2016 Purchases

column.

c.

If the value in the

2015 Purchases

column is not greater than

0, return a value of N/A. If necessary, copy the formula you

created in cell I4

to the range I5:I27.

5. 

In cell K4, enter a formula that uses an

IF

function and

structured

references

to determine customer discount eligibility.

(Hint:

You will need to

use the

OR

function in this formula.)

a. 

A customer is eligible for a discount if the value in a customer’s

2016 Purchases

is

greater than or equal

to $250

OR

if the

customer’s

First Order

was placed before

2012

.

b. 

If the customer qualifies for a discount, return a value of Y.

(

Hint:

For the value_if_true value, use “Y”.)

c. 

If the customer does not qualify for a discount, return a value

of N. (

Hint:

For the value_if_false, use “N”.) If necessary, copy the

formula you created in cell K4 to the range K5:K27.

6. 

In cell L4, enter a formula that uses a nested

IF

function and

structured references

to calculate the Discount Amount:

a. 

If the value in the

Discount Y/N

column is equal to N, the

Discount Amount

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 4.

c. 

If the value in

Tenure (Yrs)

column is less than 4, the

Discount Amount

column value should be

0.10

.

d. 

Otherwise, the value of

Discount Amount

column should be

0.15

.

(

Hint:

The Discount Amount

column is formatted with the Percentage

Number

format, so the values returned by the nested IF function will appear as 0%,

10%, or 15%.)

7. 

In cell M4, enter a formula that uses an

IF

function

and

structured

references

to assign a value rating to each customer. (

Hint

: 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 3

years

AND

2016 Purchases greater than

$250.

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 value

Low

. If necessary, copy the formula you

created in cell M4 to the range M5:M27.

8..

In cell P5, 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 P4 to

119

.

9.

In cell P7, enter a formula using the

VLOOKUP

function to look up the

Customer ID

value shown in cell P4 in the

Customers

table (located in the

range A3:M27). The VLOOKUP function should then retrieve the

Discount

Amount

from the

Customers

table (the 12

th

column in the table) for this

record. The VLOOKUP function should find an

exact

match to the value in P4.

10.

In cell

P8, enter a formula using the

HLOOKUP

function to determine

the Reward that the customer qualifies for based on their 2016 spending. The

HLOOKUP function should look up the value in cell P6

in the range P13:S14

(which has the defined name

Rewards

). The HLOOKUP function should then

retrieve the value in the

2

nd

row

of the

Rewards

lookup table. Since these

rewards are offered to customers that meet or exceed the yearly spending

levels listed in the

Rewards

table, the HLOOKUP function should find an

approximate

match to the value in P6.

11.

Go to the

Analysis

worksheet. In cell B5, enter a formula that uses the

COUNT

function and

structured references

that counts the total number of

customers in the

Customers

table on the

All

Customers

worksheet. (

Hint

: The

COUNT function only counts rows that contain a value; you cannot use the

LastName column as an argument in the function. Use the Customer ID

column instead.)

12. .

In cell B11, enter a formula that uses the

COUNTIF

function and

structured references

to count the number of customers with a Value

Rating of

High

in the

Customers

table on the

All

Customers

worksheet. (

Hint:

The COUNTIF formula should use a structured reference to the

Customer

Value Rating

column.)

13. 

In cell B6, enter a formula that uses the

SUM

function and

structured

references

to calculate the total Current Year Purchases (YTD) for all

customers.

14. 

In cell B12, enter a formula that uses the

SUMIF

function and

structured references

to calculate total Current Year Purchases (YTD) for

customers with a Customer Value Rating of

High

in the

Customers

table on

the

All

Customers

worksheet.

15.

In cell B13, enter a formula to calculate the percentage of all Current

Year Purchases accounted for by High Value customers. (

Hint:

The calculation

should divide the Total Sales (Current Year) for High Value Customers by the

Total Sales (Current Year) for all customers.) Format the cell using the

Percentage

Number format with

0 decimal places

.

16.

In cell B7, enter a formula that uses the

AVERAGE

function and

structured references

to calculate the average tenure of all customers in

the

Customers

table on the

All

Customers

worksheet.

 

17. 

In cell B14, enter a formula that uses the

AVERAGEIF

function and

structured references

to calculate the average tenure of customers with a

Customer Value Rating of

High

in the

Customers

table on the

All

Customers

worksheet.

18. 

In cell B8, enter a formula to calculate the average spending per

customer for all customers.

(Hint:

To calculate this average, divide the Total

Sales (Current Year) in cell B6 by the Number of Customers in cell B5, rather

than using the AVERAGE function.)

19. 

In cell B15, enter a formula to calculate the average spending per

customer for high value customers.

(Hint:

To calculate this average, divide

the Total Sales (Current year) for high value customers in cell B12 by the

total number of high value customers in cell B11, rather than using the

AVERAGEIF function.)

Your workbook should look like the Final Figures below and on the following pages. Save

your changes, close the workbook, and exit Excel. Follow the directions on the SAM website

to submit your completed project.

 

Final Figure 1: All Customers Worksheet

Final Figure 2: All Customers Worksheet

Final Figure 3: Analysis Worksheet