Data Analytics for Husky Motors Case Study

Midterm Exam
Case Requirements
You are required to fully and completely answer questions 1-3 included under assignment in the “Husky
Motors – Case Study” document that can be found in the midterm exam folder on Folio. Question 4 is
NOT required for your midterm exam. Data necessary to complete the case is also located in the midterm
exam folder. Below you can find questions 1-3 that are required for the midterm exam. These questions
also appear at the end of the case document in the midterm exam folder on Folio.
Assume you are Jan Morrison. Kevin has asked you to do a preliminary analysis of the data. Specifically,
Kevin has asked you to determine:
1. Overall performance analytics:
a. How is HMC performing globally?
b. How are various HMC brands performing?
c. How are the various sales channels performing?
d. What are the most and least profitable models?
2. Financial analytics:
a. What is the current contribution margin (CM) per model?
b. What is the average variable cost per model, and how has that changed over time?
c. Which model has the most variability in variable costs?
d. What is the current CM per channel?
3. Operations analytics:
a. What model options are the top and bottom sellers?
b. How many days are the various models on the lot prior to sale?
DELIVERABLES
You are required to submit two files for your midterm exam. These files include a business report, not to
exceed 10 pages double-spaced (not including title page) and the Excel workbook including the cleaned
data, formulas, analyses, and any visualizations used in your analysis (i.e. pivot tables, graphs, charts,
etc).
*Each section of the business report must be appropriately labeled according to the formatting
requirements below. You may use either MLA or APA formatting for your business report. Your Word
document must conform to the following requirements 1-inch margins, Times New Roman 11 point font,
and 10 page limit (not including title page).
**Please note that the data file included for your midterm contains errors that must be corrected prior to
performing your analysis. These errors include but are not limited to duplicate VINs, missing information
(i.e. days on lot, etc), incorrect model numbers, incorrect regions, car models and options packages that
do not exist, etc. Therefore, prior to performing your analysis, you must perform a data integrity check to
correct any errors included in the data file.
You should prepare a business report formatted as follows:
I. Title page
II. Executive summary
a. Clear and concise information about the problem the report is analyzing
b. Main points, conclusions, and recommendations (use bullets or numbered lists to
highlight important points)
III. Introduction
IV. Main body
a. Follow the order of the questions in the assignment
b. A paragraph about the relevance of the findings can be included in each section
c. Include a discussion of relevant descriptive statistics
V. Data tabulations/visualizations
a. These should support your findings in the main body
b. You may include these within the main body if you prefer
VI. Recommendations
a. Based on your analysis, what should be HMC’s course of action? Are there countries,
brands, models, options, and so on that the company should increase or eliminate?
b. What are the potential implications of those actions? Consider both financial and
nonfinancial implications.
You should also submit a file with your analyses and dashboards.