New Perspectives Excel 365 | Module 7: SAM Project B Triangle Home Security

GETTING STARTED

1. Save the file NP_EX365_7B_FirstLastName_1.xlsx as NP_EX365_7B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_7B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Ashley Glenn is an account manager for Triangle Home Security in Lansing, Michigan. She has been tracking quarterly security incidents in an Excel workbook and asks for your help in analyzing the data. Go the Incident Report worksheet. This worksheet contains a table named Incidents that lists data about each security incident. Ashley wants you to list the incident type for each reported incident, which corresponds to the data in the Prefix column. Provide the policy types as follows:

a. In cell F5, enter a formula using the HLOOKUP function to look up the incident type. Use a structured reference to look up the value in the Prefix column.

b. Retrieve the value in row 2 of the Incident_Types named range on the Lookups worksheet.

c. Each policy type covers a range of prefix values, so find an approximate match (TRUE).

d. Fill the formula into the rest of the Policy Type column, if necessary.

2. Ashley asks you to also list each response type, which corresponds to the code in the Response Code column. Provide the response types as follows:

a. In cell I5, enter a formula using the XLOOKUP function to look up the response type. Use a structured reference to look up the value in the Response Code column.

b. Use the Codes named range on the Lookups worksheet as the lookup array. Return the corresponding value from the Response_Types named range on the Lookups worksheet.

c. Specify an exact match or next larger item (1) as the match mode.

d. Fill the formula into the rest of the Response Type column, if necessary.

3. After an incident response, clients provide feedback to Triangle Home Security by rating the company's equipment, value, and service. Ashley entered the ratings in the Incidents table and wants you to calculate the average rating for each incident as follows:

a. In cell M5, enter a formula using the AVERAGE function and structured references to average the values in the Equipment to Service columns of the Incidents table.

b. Fill the formula into the rest of the Average column, if necessary.

4. The company policy is to contact each client within one day if they experienced an digital incident and within three days if they provide an average rating of 4.0 or less. Include the contact policy information as follows:

a. In cell N5, insert a formula using the IFS function.

b. Use a structured reference to test whether the Incident Type is "Digital" and display the text "1 day" if it is.

c. Use a structured reference to test whether the Average rating is less than or equal to 4 and display the text "3 days" if it is.

d. Add a final default argument to test whether any other condition is TRUE and display a blank cell ("") for that condition.

e. Fill the formula into the rest of the Contact column, if necessary.

5. Ashley asks you to complete the Summary information in the range P3:R11. Calculate the number of incidents made for each sales rep as follows:

a. In cell Q5, enter a formula using the COUNTIF function.

b. Count the entries in the Sales Rep column of the Incidents table (Incidents[Sales Rep]) to display the number of incidents for Collier (cell P5).

c. Fill the formula into the range Q6:Q11, filling without formatting.

6. Calculate the average rating associated with each sales rep as follows:

a. In cell R5, enter a formula using the AVERAGEIF function.

b. Use the entries in the Sales Rep (Incidents[Sales Rep]) and Average (Incidents[Average]) columns in the Incidents table to average the ratings for Collier (cell P5).

c. Fill the formula into the range R6:R11, filling without formatting.

7. For the Record Locator information in the range P13:Q16, Ashley asks you to list the position of the records in the Incidents table with the highest and lowest average client rating. Identify the record with the highest average rating as follows:

a. In cell Q15, enter a formula using the XMATCH function.

b. Look up the highest rating listed in the Client Ratings area (cell P20).

c. Use the entries in the Average column in the Incidents table (Incidents[Average]) as the lookup array.

d. Specify an exact match or next largest item (1) as the match mode.

8. Identify the record with the lowest average rating as follows:

a. In cell Q16, enter a formula using the XMATCH function.

b. Look up the lowest rating listed in the Client Ratings area (cell P26).

c. Use the entries in the Average column in the Incidents table (Incidents[Average]) as the lookup array.

d. Specify an exact match or next largest item (1) as the match mode.

9. Go to the Incident Type by Rep worksheet. Ashley asks you to compare the incident data by type for each sales rep. Insert a PivotTable to make this comparison as follows:

a. In cell B3, insert a PivotTable based on the Incidents table.

b. Use Reps as the name of the PivotTable.

c. Display the Sales Rep names as column headings.

d. Display first the Incident Type and then the Response Type data as row headings.

e. Use a count of the Incident Type data as values. If necessary, show all subtotals at the top of the group.

10. Format the Reps PivotTable as follows:

a. Apply Tan, Pivot Style Medium 10 to the PivotTable to coordinate with the table on the Incident Report worksheet.

b. Center the data in the range C5:J29 to make it easier to read.

c. In cell B4, use Incident Types to replace the "Row Labels" text.

d. In cell C3, use Sales Reps to replace the "Column Labels" text.

11. Ashley asks you to insert a chart that compares the sales reps with the top three incidents. Add and filter a PivotChart as follows:

a. Insert a Stacked Column PivotChart based on the Reps PivotTable.

b. Filter the PivotChart to show data only for sales reps Collier, Hamdan, and Joyce.

c. Resize and reposition the PivotChart so it covers the range H3:P22.

d. Change the PivotChart colors to Monochromatic Palette 2 to coordinate with the Reps PivotTable.

e. Hide the field buttons to remove clutter from the PivotChart.

12. Go to the Ratings by Incident Type worksheet. Ashley asks you to identify the average client ratings by the types of incidents reported each month. Insert a PivotTable to provide this information as follows:

a. In cell B3, insert another PivotTable based on the Incidents table.

b. Use Ratings as the name of the PivotTable.

c. Display the Date values as column headings, which automatically groups the data by month.

d. Display the Incident Type data as row headings.

e. Use the Average data as values.

13. Change the summary function to Average for the data in the Values area and apply the Number format with 1 decimal place to those values to make them easier to interpret. Apply Tan, Pivot Style Medium 10 to the PivotTable to coordinate with the table on the Incident Report worksheet.

14. Ashley asks you to focus on ratings for theft incidents in June. Display only theft data as follows:

a. Create a slicer for the Ratings PivotTable based on the Incident Type field.

b. Resize and reposition the slicer so it covers the range G3:H12.

c. Format the slicer using Tan, Slicer Style Light 2 to coordinate with the Ratings PivotTable.

d. Use the slicer to filter the Ratings PivotTable by the Theft incident type.

15. Use a timeline slicer to focus on the June incidents as follows:

a. Create a timeline slicer based on the Date field in the Ratings PivotTable.

b. Apply the Tan, Timeline Style Light 2 style to the timeline slicer to coordinate with the Ratings PivotTable.

c. Resize the timeline slicer to a width of 4.25".

d. Move the slicer so it covers the range B14:E21.

e. Use the timeline slicer to show data for June only.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Incident Report Worksheet

Final Figure 2: Incident Type by Rep Worksheet

Final Figure 3: Ratings by Incident Type Worksheet

Final Figure 4: Lookups Worksheet