Step 1: Create Dashboard with Summary of Key Metrics
Adventure Works has supplied you with one month of call center data extracted from their enterprise data warehouse in the form of an Excel file. In this section, you will begin creating a prototype dashboard based on this data, starting with a summary of key metrics.

a. Download and save the file AWCallCenterData.xlsx If you are not given the option of saving the file when clicking on the link, right-click it and select "Save link as" or "Save target as."
b. For this lab, you may use your own copy of Microsoft Excel 2016, or you may use Excel 2016 in the Citrix virtual lab environment. You are recommended to use the virtual lab to become familiar with it, as it will be required for some future labs. You should definitely use the virtual lab if your own copy of Excel is not the 2016 version or if you are not using the Microsoft Windows operating system. (The version of Excel for the Mac OS is significantly different from the version for Windows.)

If you will be using the virtual lab environment, log into it as directed in the Lab Resources section above, and copy the AWCallCenterData.xlsx file to your virtual home drive (the F: drive in the virtual environment, labeled with your DSI number). See the Lab Resources section on the Introduction & Resources area under Modules > Course Resources page for tutorials on accessing the Citrix virtual lab environment, installing the required Citrix Receiver software, finding and launching applications, and copying files to and from your virtual home drive.

c. Launch Excel 2016 and open the AWCallCenterData.xlsx file.
d. Add a new worksheet to the workbook, and rename it Calcs to contain your intermediate calculations.
e. Add a new worksheet to the workbook, and rename it Dashboard to contain your dashboard display.
f. Create a pivot table based on the call center data on the data tab. Place the pivot table on the calcs tab. Use date as the row labels and shift as the column labels. For values, use sum of calls, sum of orders, and average of abandon rate.
g. Create an area at the top right of the dashboard with the title key metrics. This should have columns for calls, trend, abandon rate, orders, and rows for all shifts, AM, PM1, PM2, and midnight. The calls column should show the number of calls; the trend column should display a line sparkline showing number of calls per day; the abandon rate column should display the percentage of abandoned calls and a traffic light status indicator; and the orders column should display the number of orders. The traffic light status indicator for abandon rate should be red for greater than 10%; yellow for less than or equal to 10% but greater than 5%; and green for less than or equal to 5%.
h. Format the summary area with appropriate, professional fonts, background colors, and number formats, and apply a thick box border around the area.
i. Save the workbook as Lab4_yourlastname.xlsx.

Step 2: Create Calls Handled by Auto Response Chart
In this section, you will create a chart on the dashboard to allow managers to monitor the percentage of calls handled by auto response by shift, day type, and date.

a. Create a pivot table based on the call center data on the data tab. Place the pivot table on the calcs tab below the pivot table you created in Step 1. Use shift as the row labels. Add a calculated field named percent auto responses that divides automatic responses by calls and averages the results. Add a second calculated field named target that is set to a constant value of 65%.
b. Create a pivot chart based on the above pivot table using a combo chart, with the percent auto responses data series displayed as columns and the target series displayed as a line. Place the chart on the dashboard sheet below the key metrics summary area. Give the chart a descriptive title and horizontal and vertical axis labels. Hide the field list and filed buttons for the pivot chart.
c. Add slicers to the chart for day type and date.
d. Format the chart with appropriate, professional fonts, background colors, and number formats, and apply a shape outline to the chart.
e. Save the workbook.

Step 3: Create Additional KPI Displays
In this section, you will identify at least two additional KPIs related to the call center objective established by Adventure Works, and add displays for these additional KPIs to the dashboard.
a. On your own: Identify at least two additional KPIs that are relevant to the strategic objective established by Adventure Works of improving customer service provided by the call center. These should be KPIs that you are able to determine based on the call center data provided.
b. On your own: Add visual displays for your additional KPIs to the dashboard, placing them on the right side of the dashboard sheet so that ideally all four displays (the key metrics summary, the calls handled by auto response chart, and your two additional displays) can all be viewed without scrolling. Be sure to follow the design guidelines discussed in this week's assigned reading when creating your KPI displays.
c. On your own: Format your KPI displays with appropriate, professional fonts, background colors, and number formats. Apply a border or outline to each display.

Step 1: Create Dashboard with Summary of Key Metrics Adventure Wor...