Individual Case Study – Instructions (Descriptive Analysis)
For the Descriptive Analysis Project, follow the instructions in the Correlation Case Study Instructions Download Correlation Case Study Instructions using this data file Pattern Recognition Data (student) Download Pattern Recognition Data (student).
You may use your own data for this project as long as you get prior written approval from your instructor. You will need to clearly define the problem statement you aim to solve.
In this file “All You Knead. Pattern Recognition Data (Student).xls” you will see these variables in the Data tab of the file:
|
No. of |
No. of Units (in |
No. of Popular Brands |
Population (in |
Sales (in |
Urban Type |
Region |
You will also see two correlations between sales and population and sales and stores with interpretations that have already been done for you. Use the Excel file to generate correlations, scatter plots, and pivot tables. You must turn in your spreadsheet with formulas (not hard coded values) for full credit.
To generate correlations, use:
(Excel > Data Analysis > Correlation OR Excel > Formulas > More Functions > Statistical > CORREL).
To generate scatter diagrams, use:
Excel> Insert > Scatter (Diagrams).
To generate pivot tables, use:
Excel > Insert > Pivot Table. You will need to choose the right variables for the rows and columns and choose one for the interior tabulation. Remember, you can change the tabulation to options including Count, Sum, Average, and Percentages.
If you need a refresher on how to do this, please watch Dr. Yurova’s video.
In this exercise, our dependent variable (that we want to predict) is sales. The other variables are considered independent, or predictors. The difference between quantitative and categorical variables will dictate which research questions you can test.
When we look at scatterplot, we should be able to describe the association we see between the variables. A quick description of the association in a scatterplot should always include a description of the form, direction, and strength of the association, along with the presence of any outliers.
· Form: Is the association linear (more or less a straight line) or nonlinear?
· Direction: Is the association positive or negative?
· Strength: Does the association appear to be strong, moderately strong, or weak?
· Outliers: Do there appear to be any data points that are unusually far away from the general pattern?
· It’s also important to include the context of the two variables in the description of these features.
In your report, make sure you explain each scatter diagram (Excel > Insert > Charts > Scatter), each correlation (Excel > Formulas > More Functions > Statistical > CORREL), and interpret your r values (correlation coefficients). Also in your report, make a recommendation as to which of the independent variables you would focus on to increase sales.
Review the practice activity on descriptive modeling (identifying the relationships between variables), and practice calculation of correlations and building scatter plots.
If you would like an excellent refresher on how to create scatter diagrams, correlation coefficients, trend lines, and interpretation, please watch this excellent short video by Mike GavinLinks to an external site. .
Project reports should be in business report format, such using this template.Download this template.
(Note: please do NOT use pdf files. Use either Excel and Word files).
Business Report Template
Prepared by:
Date:
School:
Assignment for Course:
Submitted to:
Submitted by:
Date of Submission:
Title of Assignment:
CERTIFICATION OF AUTHORSHIP: I certify that I am the author of this paper and that any assistance I received in
its preparation is fully acknowledged and disclosed in the paper. I have also cited any sources from which I used
data, ideas or words, either quoted directly or paraphrased. I also certify that this paper was prepared by me
specifically for this course.
Student’s Signature: _______CR_______________________
*****************************************************************
Instructor’s Grade on Assignment:
Instructor’s Comments:
Management Report
Executive Summary
Individual Case Study Instructions
Instructions
Review the practice activity of applying descriptive models. Then, answer the questions listed below for
each part of the case. Conduct necessary calculations and visualizations to answer the questions.
Submit each of the following:
1. Excel spreadsheet(s) with calculations (not hard-coded numbers) and visualizations to the
assignment Canvas dropbox before the posted deadline. You may submit additional Excel
spreadsheets if you feel they are necessary to support your answers.
2. Prepare a properly formatted Business Report, which includes your answers to the assignment
questions. Your report must follow the proper reporting format as detailed by instructor and
found in the Getting Started module.
Grading
A total of 10 points is possible for this assignment. This includes the point values which are assigned to
each question (point values are noted next to each question below) plus 0.5 points which are earned
based on following the prescribed assignment format, and the proper writing style and APA format.
Background
Whisky Business, Ltd is a leading restaurant supply and distribution company. With quality products and
customer satisfaction, Whisky Business maintains a leading position in the restaurant sector widening its
customer base. However, very recently the company witnessed an increase in competition resulting in a
decrease in its sales. To analyze the factors driving the sales, company collected data across 53
territories on important factors which were determined by company leadership to be crucial in
determining the sales: market potential in the territory, number of supply shops, number of dealers in
each territory, number of brands, population of each territory, urban type, and region.
Executive Summary (1 point):
Start with an Executive Summary that briefly describes each section of your analysis. You should clearly
state the background, problem, methods, results, and recommendation.
Problem Statement (1.5 point):
Define a problem statement which reflects the challenge facing Whisky Business, Ltd leadership.
Methods (1.5 point):
Describe and justify to analytical methods used to conduct the analysis that follows.
Correlation Analysis (2 points):
Apply descriptive modeling to analyze relationships between company sales and the four quantitative
predictor variables. Interpret the meaning of each relationship and how it relates to the Whisky Business
company. Illustrate your answer with appropriate statistics and visualizations.
Contingency and Pivot Tables (2 points):
Conduct an analysis to examine the relationship between Urban type and Region. If you discover a
relationship, how does it affect sales? What does this relationship mean for the Whisky Business
company? Illustrate your answer with the appropriate statistical table.
(Hint. You may need to run 2 pivot tables to answer. One comparing urban type to region and one breaking
down sales by these categories).
Recommendation (1.5 points):
Interpret and discuss the results of your analysis including the implications for future data analysis. Which
factors influence sales the most (or least)? Which type of territories should they focus on for new sales
efforts? Provide recommendations for the Whisky Business, Ltd management.