Individual Case Study - Descriptive Modeling PDF

Preview

Summary

Download Individual Case Study - Descriptive Modeling PDF

Description

Nova Southeastern University Wayne Huizenga Graduate School of Business & Entrepreneurship

Assignment for Course:

QNT 5160 - Analytical Modeling for Decision Making

Submitted to:

Professor Dr. Harris Jr.

Submitted by:

********

Date of Submission:

June 9th, 2020

Title of Assignment:

Individual Case Study - Descriptive Modeling

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 of words, whether quoted directly or paraphrased. I also certify that this paper was prepared by me specifically for this course.

Student Signature:

*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_ Instructor’s Grade on Assignment: Instructor’s Comments:

Individual Case Study - Descriptive Modeling

2

INDIVIDUAL PROJECT GRADING RUBRIC Course: Date: Name of Student: Name of Faculty:

QNT 5160

June 9th, 2020 ******** Dr. William Harris Jr. Earning maximum points in each box in ‘PROFICIENT’ column and / or points in columns to the right of ‘PROFICIENT’ meets standard. >

Performance Criteria Identify the problem (Q1a-Q1c)

Describes assumptions and methods (Q2a-Q2e)

Calculate results using a spreadsheet (Q3a-Q3c)

Explain implications of output of spreadsheet analysis (Q2a-Q2e)

Basic

Developing

Proficient

Accomplished

Exemplary

Does not identify the problem or does not identify the right problem.

Identifies symptoms

Identifies some elements of the problem.

Substantially identifies the problem.

Effectively and succinctly identifies the problem.

(0 pts) Does not describe assumptions and methods used

(5 pts) Does not precisely describe the assumptions and methods used

(10 pts) Somewhat describes assumptions and methods used

(12 pt) Substantially describes assumptions and methods used

(15 pts) Effectively describes assumptions and methods used

(0 pts) Does not calculate appropriate results using a spreadsheet and/or does not provide evidence of calculations

(3 pts) Calculates appropriate results using a spreadsheet (most answers are not correct)

(7 pts) Calculates appropriate results using a spreadsheet (not all answers are correct)

(8 pts) Calculates appropriate results using a spreadsheet (most answers are correct)

(10 pts) Effectively calculates results using a spreadsheet (almost all answers are correct)

(0 pt) Does not explain implications of output of spreadsheet analysis

(13 pts) Partially explains implications of output of spreadsheet analysis

(21 pts) Somewhat explains implications of output of spreadsheet analysis

(25 pts) Substantially explains implications of output of spreadsheet analysis

(30 pts) Effectively explains implications of output of spreadsheet analysis

(0 pt)

(3pts)

(7 pts)

(8 pts)

(10 pts)

Score

. . . continued . . .

Individual Case Study - Descriptive Modeling

Course: Date: Name of Student: Name of Faculty:

3

QNT 5160

June 9th, 2020 ******** Dr. William Harris Jr. Earning maximum points in each box in ‘PROFICIENT’ column and / or points in columns to the right of ‘PROFICIENT’ meets standard. >

Performance Criteria

Basic

Developing

Proficient

Accomplished

Exemplary

Generates recommendati ons based on analysis and context (Q2f, Q3d)

Does not generate appropriate recommendati ons based on analysis and context.

Generates recommendati ons (does not justify conclusions).

Partially: *generates and justifies recommendati ons based on analysis and context; and *justifies conclusions.

Substantially: *generates and justifies recommendati ons based on analysis and context; and *justifies conclusions.

Effectively: *generates and justifies recommendati ons based on analysis and context; and *justifies conclusions.

(0 pt) Does not use prescribed format and writing style

(7 pts) May use prescribed format OR writing style (only one)

(15 pts) Generally, uses prescribed format and writing style

(20pts) Substantially uses prescribed format and writing style

(25 pts) Effectively uses prescribed format and writing style

(0 pt)

(3 pts)

(7 pts) (8 pts)

(10 pts)

Uses prescribed format (including cover sheet and grading rubric) and writing style (language, grammar, punctuation, and spelling)

OVERALL GRADE (100 total possible points):

Scor e

%

Comments:

__________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________

Individual Case Study - Descriptive Modeling

4

Individual Case Study - Descriptive Modeling - QNT 5160

Part 1: Question: Define a problem statement which reflects the challenge facing Magic Foods leadership. Magic food Company is a leading manufacturer of pickles, spices, pastes and instant mixes. However, recently the company is experiencing an increase in the competition which led to decrease in the number of sales. Magic food Company leadership wants to know what are the key factors and variables which drives the sell and on which territory and company they should put their focus to be able to increase the sales the most. Part 2: Apply descriptive modeling to analyze relationships between company sales and all predictor variables. Discuss the results of your analysis, implications for future data analysis. Illustrate your answer with appropriate statistics and visualizations. Provide recommendations for the Magic Foods management. Initially we collected data from thirty territories on the last year sales, population in each region, number of food stores in each territory, number of our dealers in each territory and number of popular brands of similar products sold in each territory. We planned to determine the affect and correlation of different variables on sales. Therefore, we used the Excel data analysis tool and data chart we have in the Data Sheet of the excel file along with scatter plot, to determine the strength of the correlation between different variables and sale. Correlation coefficient is between -1 and +1 which shows the strength of the relationship. +1 shows a perfect positive relationship and -1 shows a perfect negative relationship, while 0 shows no correlation.

Individual Case Study - Descriptive Modeling

5

The results of Correlation calculation are showed in Table 1. Correlation Variables coefficient Sales & Population 0.974565 Sales & Stores 0.913674 Sales & Dealers 0.976851 Sales & Brands - 0.91976 Table 1 Variables and Correlation coefficient Results of your analysis: As we can see in the Table 1, Sales and Dealers correlation coefficient is 0.976851, which shows the strongest positive relationship; Sales and Population correlation coefficient is 0.974565, which shows a strong positive relationship, but still weaker than the relationships between Sales and Dealers and Sales and Stores correlation coefficient is 0.913674 which still shows a strong positive relationships, but weaker than the relationships btw Sales and Population AND Sales & Dealers. On the other hand, sales and brands correlation coefficient is - 0.91976, which shows almost a strong negative relationship. That means our variables move in opposite directions and as we can see in the Figure 4, they have an opposite relationship. As we discussed, number of sales and number of dealers shows the strongest and almost a perfect positive relationship. Numbers Population, and numbers of Stores, respectively showed the highest positive relation to the number of sales after the number of dealers. When we compare Figure 3 with Figures 1 and 2, the slope and scattering pattern demonstrates that, as the number of dealers in each territory increase, number of sales also increase, and this correlation is stronger than the other two plots.

Individual Case Study - Descriptive Modeling

6

Recommendations for the Magic Foods management: Since the number of dealers showed the highest correlation with sales, followed by the population and number of stores and based on what I have discussed, my recommendation for the Magic

Foods management is to: 1.

First of all, they should invest more and try to increase the number of dealers first in the territories with higher population, then in territories with higher number of shops and then in the rest of territories and areas.

2.

Secondly, they should try to focus and invest more in the areas with higher population.

3.

Third, they should try to increase their numbers of store in the area with higher population and increase the number of dealers at the same time.

4.

Finally, since number popular brands of similar products sold in each territory showed a negative correlation with sale, they should focus more on the territories with less popular brands of similar products.

Implications for future data analysis: For the future data analysis, since we had data from past year, they should try to obtain the set of data in all territories for past couple of years, this will make the results more accurate and give us the opportunity to obtain more precise result. This time we had data for population in each region, number of food stores in each territory, number of our dealers in each territory and number of popular brands of similar products sold in each territory. If we can include more variables, such as poverty of population in each region or their age, it can also help with the process of prediction and decision making and refine our model.

Individual Case Study - Descriptive Modeling

7

Sales (in $100000) vs. Population (in Thousands) 70 60 50

Sales

40 30 20 10 0 30

50

70

90

110

130

150

170

Population

Figure 2 Sales VS Population

Sales (in $100000) vs. Number of Stores (in hundreds) 60

50

Sales

40 30 20

10 0

0

2

4

6

8

10

12

Number of Stores

Figure 3 Sales VS Number of Stores

14

16

18

20

Individual Case Study - Descriptive Modeling

8

Sales (in $mil) VS No. of Dealers (in hundred) 60 50

Sales

40

30 20

10

0

0

10

20

30

40

50

60

No. of Dealers

Figure 4 Sales VS Number of Dealers

Sales (in $mil) VS No. of Popular Brands 60 50

Sales

40 30

20 10

0

0

2

4

6

8

10

No. of Popular Brands

12

14

16

18

Individual Case Study - Descriptive Modeling

Figure 5 Sales VS Number of Popular Brands

9...