Adventure Works Cycles Manufactures And Sells Metal And Composite Bicycles And Related Products In North America, Europe, And Asia.

data mining


Lab 4: Developing a KPI Dashboard

Lab Overview


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.



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.




Date of the call center operation


Indicates whether the day was a weekend, weekday, or holiday


The shift for which calls are recorded: The call center divides the working day into four shifts: AM, PM1, PM2, and midnight.


The number of Level 1 operators on duty (less experienced)


The number of Level 2 operators on duty (more experienced)


Total number of operators on duty during the shift


Number of calls received during the shift


Number of calls handled entirely by automated call processing (interactive voice response) system


Number of orders resulting from calls


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


Average time in minutes to resolve an issue that was not handled during the initial call


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.


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.




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



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



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



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



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.






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.

Related Questions in data mining category

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.