Sam Project for Advanced functions and conditional formatting Excel 2013

I am having trouble with questions 14-19. Assignment do tomorrow

Computer Software 178

New Perspectives Excel 2013 Tutorial 8: SAM Project 1a Spring Software ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING 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.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 CustomerList worksheet 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. New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a 2. Edit the conditional formatting rule applied to the range G4:G32 so that the highlighted cells are formatted with the font color 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 New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a reference to the current year value in cell B1.) 4. In cell I4, enter a formula that uses the IF function and structured references to 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 2Year 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 references to 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 sales greater than or equal to 100000 OR 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 New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a nested IF function and 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 nested IF 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 years AND 2016 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 value Low. 8. In cell Q4, nest the VLOOKUP function in an IFERROR function. (Hint: The arguments for New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a the VLOOKUP function should not be modified.) 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 Q3 in the CustomersN ashua table (located in the range A3:L32). The VLOOKUP function should then retrieve the Discount value from the CustomersN ashua table (the 11th column in the table) for this record. The VLOOKUP New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a 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 Q5 in the range O10:R11 (which has the defined name CustomerInc entives). The HLOOKUP function should then retrieve the value in the 2nd row of the CustomerInce ntives lookup table. Since these free services are offered to customers that meet or exceed the yearly sales levels listed in the CustomerInce ntives table, the HLOOKUP function should find an New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a approximate match to the value in Q5. 11. Go to the CustomerAnal ysis worksheet. In cell C4, enter a formula that uses the COUNT function and structured references that counts the total number of customers in the CustomersNa shua table 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 function and structured references to count the number of customers with a Priority rating of High in the CustomersNa New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a shua table on the CustomerList worksheet. (Tip: The COUNTIF formula should use a structured reference to the Priority column.) 13. In cell C5, enter a formula that uses the SUM function and structured references to calculate 2016 sales 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 CustomersNa shua table on the CustomerList worksheet. 15. In cell D6, enter a formula to calculate the percentage of High Priority Customers Sales out of All Customers 2016 sales. (Tip: The calculation New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a 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 CustomersNa shua table on the CustomerList worksheet. 17. In cell D7, enter a formula that uses the AVERAGEIF function and structured references to calculate the average tenure of customers with a Priority rating of High in the CustomersNa shua table on the CustomerList worksheet. New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a 18. In cell C8, enter a formula to calculate the average sales per customer for all customers. (Tip: To calculate this average, divide the total 2016 sales (cell C5) by the total number of customers (cell C4) rather than using the AVERAGE function.) 19. In cell D8, enter a formula to calculate the average sales per High Priority Customer. (Tip: To calculate this average, divide the total 2016 sales for high priority customers (cell D5) by the total number of high priority customers (cell D4) rather than using the AVERAGEIF function.) Your workbook should look like the Final Figures below and 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. New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a Final Figure 1-1: CustomerList worksheet, Cells A1:L27 Microsoft product screenshots used with permission from Microsoft Corporation. Copyright 2014 Cengage Learning. All Rights Reserved. New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a Final Figure 1-2: CustomerList worksheet, Cells M1:S14 Copyright 2014 Cengage Learning. All Rights Reserved. New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a Final Figure 2: Customer Analysis worksheet Copyright 2014 Cengage Learning. All Rights Reserved. New Perspectives Excel 2013 Tutorial 8: SAM Project 1a Spring Software ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING Author: Pamela Bobo Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Current Year 2017 Spring Software-Active Customer Database (Nashua) Customer Details 2016 Sales 2-Year Sales Growth 2-year Growth Percentage Discount Y/N Discount 720000 Err:508 Err:508 Err:508 Err:508 21,002 Err:508 Err:508 Err:508 Err:508 27,500 $ 29,500 Err:508 Err:508 Err:508 140,000 $ 142,000 $ 145,000 Err:508 Err:508 $ 95,000 $ 96,000 $ 97,000 Err:508 Err:509 $ 323,000 $ 350,000 500000 2011 Err:509 $ 317,000 $ 355,000 410000 Whitehead Financial 2010 Err:509 $ 247,000 $ 256,000 $ 1009 Funstop Toys 2016 Err:509 $ - 1010 Anna's Boutiques 2013 Err:509 $ 1011 Sunnyside Stores 2009 Err:509 1012 Bedfordshire School System 2011 1013 Westview Tutors 1014 Customer ID Customer First Order Tenure (Yrs) 2014 Sales 2015 Sales 1001 Quantum Engineering 2010 Err:509 $ 700,000 $ 1002 Lakeside Caf 2014 Err:509 $ 20,002 $ 20,002 $ 1003 B&W Lumber 2015 Err:509 $ - $ 1004 Surfside Pool & Patio 2014 Err:509 $ 1005 Whitefield School System 2013 Err:509 1006 Grapevine Consulting Grp 2012 1007 Fairfield Asset Management 1008 Priority Customer ID 1019 Customer Name Err:508 Err:509 2016 Sales Err:508 Err:508 Err:509 Discount Err:508 Err:508 Err:508 Err:509 Customer Incentives Err:508 Err:508 Err:508 Err:509 Err:508 Err:508 Err:508 Err:508 Err:509 Err:508 Err:508 Err:508 Err:508 Err:509 Yearly Sales $0 $75,000 $150,000 $400,000 301,000 Err:508 Err:508 Err:508 Err:508 Err:509 Free Service None Onsite Training Weekend Tech Support Dedicated Tech Support $ 43,000 Err:508 Err:508 Err:508 Err:508 Err:509 15,300 $ 15,400 $ 15,500 Err:508 Err:508 Err:508 Err:508 Err:509 $ 183,000 $ 187,000 $ 172,000 Err:508 Err:508 Err:508 Err:508 Err:509 Err:509 $ 65,000 $ 124,000 $ 125,000 Err:508 Err:508 Err:508 Err:508 Err:509 2014 Err:509 $ 105,000 $ 125,000 $ 140,000 Err:508 Err:508 Err:508 Err:508 Err:509 BWM Partners 2016 Err:509 $ - $ 233,000 Err:508 N/A Err:508 Err:508 Err:509 1015 Firehouse Partners LLC 2012 Err:509 $ 75,000 $ 161,000 $ 200,600 Err:508 Err:508 Err:508 Err:508 Err:509 1016 Forest Construction LLC 2015 Err:509 $ - $ 257,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:509 1017 Farmington Strategy Group 2013 Err:509 $ 250,000 $ 275,000 $ 301,000 Err:508 Err:508 Err:508 Err:508 Err:509 1018 Diamond School District 2010 Err:509 $ 183,000 $ 191,000 $ 193,000 Err:508 Err:508 Err:508 Err:508 Err:509 1019 Granite State Communications 2009 Err:509 $ 245,000 $ 251,000 $ 302,000 Err:508 Err:508 Err:508 Err:508 Err:509 1020 Green Mountain Publishing 2014 Err:509 $ 377,500 $ 379,500 $ 381,000 Err:508 Err:508 Err:508 Err:508 Err:509 1021 Atlantic Learning Group 2013 Err:509 $ 235,000 $ 246,000 $ 250,000 Err:508 Err:508 Err:508 Err:508 Err:509 1022 Fontas Convenience 2010 Err:509 $ 79,000 $ 88,450 $ 77,000 Err:508 Err:508 Err:508 Err:508 Err:509 1023 Applewild Learning Center 2009 Err:509 $ 133,000 $ 138,000 $ 142,000 Err:508 Err:508 Err:508 Err:508 Err:509 1024 Acme Printing 2013 Err:509 $ 99,000 $ 101,000 $ 100,000 Err:508 Err:508 Err:508 Err:508 Err:509 1025 Pinnacle Publications 2015 Err:509 $ - $ 157,000 $ 184,010 Err:508 Err:508 Err:508 Err:508 Err:509 1026 Fox Graphics 2015 Err:509 $ - $ 233,000 $ 256,000 Err:508 Err:508 Err:508 Err:508 Err:509 1027 Brookfield Regional Schools 2016 Err:509 $ - $ $ 98,000 Err:508 N/A Err:508 Err:508 Err:509 1028 Ludlow Pharmacy Group 2014 Err:509 $ 58,550 $ 75,650 $ 98,000 Err:508 Err:508 Err:508 Err:508 Err:509 1029 Wayside Public 2008 Err:509 $ 75,000 $ 112,000 $ 117,000 Err:508 Err:508 Err:508 Err:508 Err:509 $ $ 720,000 - - - High Weekend Tech Support Customer Incentives Customer Analysis - Nashua All Customers Number of Customers 2016 Sales Percent of 2016 Sales Average Tenure Average Sales/Customer $ 20 5,952,612.00 High Priority Customers 8