Question;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.
Paper#45967 | Written in 18-Jul-2015Price : $32