Description

Lab 4: Developing a KPI Dashboard

Lab Overview

Scenario/Summary

Adventure Works Cycles manufactures and sells metal and composite bicycles and related products in North America, Europe, and Asia. Adventure Works operates a call center that responds to customers who call to place orders over the phone, request technical support with products, or deal with issues such as warranty claims, products damaged in shipment, or billing errors. For its upcoming fiscal year, Adventure Works has established a strategic objective of improving the level of customer service provided by the call center. Strategic goals and associated key performance indicators (KPIs) that managers established to support this objective include the following.

KPI	Goal
Call Abandonment Rate	Reduce call abandonment rate to 5% by end of fiscal year.
Calls Handled by Automated Response System	Have at least 65% of calls handled by auto response by end of fiscal year.

You have been asked to create a dashboard for call center managers to monitor progress on this strategic objective. The dashboard will be based on a monthly data set extracted from the Adventure Works data warehouse. The call center is a 24-hour/7-day operation. The extract will include one record with summary data for each 6-hour shift, with the following columns.

Column	Description
Date	Date of the call center operation
DayType	Indicates whether the day was a weekend, weekday, or holiday
Shift	The shift for which calls are recorded: The call center divides the working day into four shifts: AM, PM1, PM2, and midnight.
LevelOneOperators	The number of Level 1 operators on duty (less experienced)
LevelTwoOperators	The number of Level 2 operators on duty (more experienced)
TotalOperators	Total number of operators on duty during the shift
Calls	Number of calls received during the shift
AutomaticResponses	Number of calls handled entirely by automated call processing (interactive voice response) system
Orders	Number of orders resulting from calls
IssuesRaised	Number of issues that could not be handled during the initial call and required later follow-up by e-mail or a callback to the customer
AverageTimePerIssue	Average time in minutes to resolve an issue that was not handled during the initial call
AbandonRate	Fraction of calls that were abandoned by the customer

You will create the dashboard in Microsoft Excel because that is a familiar tool for managers. You have been provided with a sample data set covering the month of November 2016 for use in developing a prototype dashboard to be reviewed by management.

Deliverables

You will submit two files for this lab.

·         An Excel workbook titled Lab4_yourlastname.xlsx containing your prototype dashboard

·         A Word document titled Lab4_yourlastname_Paper.docx containing a one-page summary of your findings and recommendations for Adventure Works regarding the call center

When submitting the assignment, provide a comment explaining what you learned from completing this lab activity.

Category	Points	%
Step 1: Create Dashboard with Summary of Key Metrics Summary of key metrics with call numbers, trend lines, abandon rate with traffic light status indicator, and orders for all shifts and each shift individually	10	14.3%
Step 2: Create Calls Handled by Auto Response Chart Column chart for percent of calls handled by auto response, including target line and slicers for day type and date	10	14.3%
Step 3: Create Additional KPI Displays At least two additional KPIs of your choosing relevant to the strategic objective displayed on the dashboard with appropriate visual representations and clear descriptive labeling	20	28.6%
Step 4: Finalize Workbook Dashboard is professionally formatted with data and calculation sheets hidden; gridlines, formula bar, and row/column headings not visible on the dashboard sheet; and professional use of colors, fonts, borders, and shapes	10	14.3%
Step 5: Write Opinion Paper and Submit Paper is in APA format, free of typographical, spelling, and grammar errors, and clearly states appropriate findings and recommendations from the analysis. Findings and recommendations are relevant to the strategic objective and are supported by the dashboard.	20	28.6%
Total	70	100%

Lab Resources

Microsoft Excel 2016

You may use Microsoft Excel on your local PC or from the Virtual Lab-Citrix environment. The link is accessible from the Course Resources page in the Introduction and Resources module. View the Lab Resources section.