# SAM Capstone Project 1b Fit Fix Footwear Excel Solution

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

- Download the following file from the SAM website:
**NP_Excel2013_T8_P1b_***FirstLastName*xlsx

- Open the file you just downloaded and save it with the name:
**NP_Excel2013_T8_P1b_***FirstLastName*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_**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.*FirstLastName*xlsx

PROJECT STEPS

- Go to the
*All**Customers*worksheet and complete the following actions:- Apply a
**conditional formatting**rule to the range A4:A27 that formats**any duplicate**values with**Green Fill****with Dark Green Text**. - Update the Customer ID value for
**Sarah Sun**to**125**and the Customer ID value of**Ariel Valdez**to**126**.

- Apply a

The conditional formatting rule should no longer highlight any values in the range A4:A27.

- 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). - 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.) - In cell I4, enter a formula that uses the
**IF**function and**structured references**to calculate customer sales Growth (as a %).- Growth can be calculated if the value in the
**2015 Purchases**column is greater than 0. - To calculate Growth (as a %), divide the value in the
**Growth ($)**column by the value in the**2016 Purchases** - 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.

- Growth can be calculated if the value in the
- 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 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**. - If the customer qualifies for a discount, return a value of Y. (
*Hint:*For the value_if_true value, use “Y”.) - 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.

- A customer is eligible for a discount if the value in a customer’s
- In cell L4, enter a formula that uses a nested
**IF**function and**structured references**to calculate the Discount Amount:- If the value in the
**Discount Y/N**column is equal to N, the**Discount Amount**column value should be 0. - 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. - If the value in
**Tenure (Yrs)**column is less than 4, the**Discount Amount**column value should be**10**. - Otherwise, the value of
**Discount Amount**column should be**15**.

- If the value in the

(*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%.)

- 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.)- The IF function should check if a customer has a
**Tenure**field value**greater than 3**years**AND****2016 Purchases greater than**$250 - If the customer meets both those criteria, the function should return the value
**High**. - 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.

- The IF function should check if a customer has a
- In cell P5, nest the VLOOKUP function in an
**IFERROR**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**. - 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. - 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**of the^{nd}row*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. - Go to the
*Analysis*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.) - 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*(*Hint:*The COUNTIF formula should use a structured reference to the*Customer Value Rating*column.) - 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. - 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* - 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**. - 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* - 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* - 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.) - 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.

**File Format:**.xlsx**Version Used:**2013