I’m working on a data analytics multi-part question and need support to help me learn.
Need help answering 5 multipart questions on data analytics utilizing excel add in functions to solve them. Need tutor that is experienced in data analytics to solve questions on topic areas for comparison tests, regression analysis, forecasting and simulation, and linear optimization. Attached is excel file to be completed. Please complete answers in excel template provided.
PLEASE:
1
2
3
4
5
6
7
8
9
10
Number of questions: 6
Duration: 4 hours
Save this file as “YOURNAME_Exam2.xlsx”.
You can place your answers in each worksheet.
you will submit this single file once you complete it.
Once you start the exam, you need to submit your solution file within 4hours before the exam
You are allowed to submit only one file (YOURNAME_Exam1.xlsx)
The exam window will not accept your file if you are not able to submit it within the allotted ti
Always check your time
Refresh your browser before you submit it.
Comment
Q1
Q2
Q3
Q4
Q5
Q6
Total
0
10
15
10
10
15
10
70
or BINOM.INV(n, p
or BINOM.INV(n, p , RAND())
Student ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
MAJOR
Liberal Arts
Liberal Arts
Business
Sciences
Business
Sciences
Business
Liberal Arts
Sciences
Business
Business
Sciences
Sciences
Liberal Arts
Liberal Arts
Sciences
Business
Business
Business
Sciences
Liberal Arts
Sciences
Sciences
Business
Business
Sciences
Sciences
Business
Sciences
Sciences
Business
Sciences
Sciences
Sciences
Business
Sciences
Sciences
Liberal Arts
GMAT Scores
578
584
605
699
575
600
590
565
584
579
583
593
602
572
570
587
588
593
570
592
562
597
604
585
565
580
610
572
590
594
598
610
589
612
590
588
586
573
Q1_(10pts)
A college is trying to determine if there is a statistically signifi
from different undergraduate backgrounds (Business, Liberal
The Excel file GMAT Scores contain data from a sample of stu
a_ which test should be employed? why? (2pts)
b_ write the null and the alternative hypothesis (2pt)
c_ Place the results of the related analysis on this worksheet (3pts)
d_ Interpret the analysis results. Report all the related statist
stat, qstat,….) (3pts)
e is a statistically significant difference in the mean GMAT score of students
Business, Liberal Arts, and Sciences ) who apply to the MBA program.
ta from a sample of students. What conclusion can be reached?
why? (2pts)
ypothesis (2pt)
s on this worksheet (3pts)
rt all the related statistics in your interpretation ( i.e. p-value, z-stats, t-stat,, H-
Q2_15pts
Table 1: Prob distribution
of the weekly demand
Demand
2
4
6
8
10
12
Probability
0.05
0.27
0.15
0.18
0.25
0.10
A factory produces office chairs. According to the past data, the weekly demand ha
1. The selling price per chair is $120. In addition, the historical data showed that th
a normal probability distribution with mean of $40 and the standard deviation of $
A_ Simulate this production of chairs for a year (52 weeks = 52 rows = 1 replication
calculate the weekly net profit. (4pts)
Hint: “Net profit = Revenu – Cost” where “Revenue = Demand * Unit selling price
Hint: Demand and Cost are random variables based on the given probability distr
B_ Calculate the average weekly profit and the percentage of generating $500 or m
1-replication) (2pts)
C_ Usint Data Table, replicate the mean net profit and mean percentage of genera
(2pts)
D_ Determine the average weekly profit and the percentage of generating $500 or
(1pts)
E_ Evaluate the average weekly profit and the percentage of generating $500 or m
$100 and $110 ( use scenario manager) (3pts)
F_ Determine range of true mean net profit based on 200 replication with 95% con
G_ Insert a histogram chart that shows the net profit based on 200 replications
Please place the question number on your answers (i.e., A, B,…, G)
, the weekly demand has the probability distribution given on Table
cal data showed that the unit cost of manufacturing a chair showed
standard deviation of $5.
= 52 rows = 1 replication ; use the correct distribution functions) to
and * Unit selling price ”
e given probability distributions.
of generating $500 or more net profit based on 52 weeks (based on
an percentage of generating $500 or more obtained in (B) 200 times.
e of generating $500 or more net profit based on 200 replications.
of generating $500 or more net profit if the selling price of a chair is
eplication with 95% confidence level (2pts)
d on 200 replications. (1pt)
A, B,…, G)
Q3_10 Pts
Monthly Sales between 2016 and 2018
Month
2016
2017
January
438
444
February
420
425
March
414
423
April
318
331
May
306
318
June
240
245
July
240
255
August
216
223
September
198
210
October
225
233
November
270
278
December
315
322
2018
450
438
434
338
331
254
264
231
224
243
289
335
For years The Glass Slipper restaurant has operated in a resort communi
during the first few months of the year, when the ski slopes are crowded
seasonal variability. The number of periods indicates the number of sea
When James and Deena Weltee built The Glass Slipper, they had a vision
mountains was breathtaking, a high priority was placed on having large w
the restaurant. Special attention was also given to the lighting, colors, an
all who came to enjoy gourmet dining. Since its opening, The Glass Slippe
visit†places in that region of New Mexico.
While James loves to ski and truly appreciates the mountains and all that
tropical paradise and enjoying a more relaxed lifestyle on the beach. Afte
retirement was many years away. Nevertheless, they were hatching a pla
Glass Slipper and open a bed and breakfast on a beautiful beach in
Mexico. While this would mean that work was still in their future, they co
in the wind and the waves lapping at the shore. They also knew that hirin
begin a semi-retirement in a corner of paradise.
To make this happen, James and Deena would have to sell The Glass Slip
the value of the property and equipment as well as projections of future
A forecast of sales for the next two year is needed to help in the determi
past 3 years are provided in Table.
A_ Which forecasting model is the most appropriate for future sales? Wh
B_ Generate and place the analysis results of the correct forecasting mod
C_ Write the proposed model after the analysis and interpret the slope.
D_ Apply the most appropriate forecasting model to these data to foreca
operated in a resort community near a popular ski area of New Mexico. The restaurant is busiest
hen the ski slopes are crowded and tourists flock to the area. Thus, the revenue shows a
ds indicates the number of seasons within a year.
Glass Slipper, they had a vision of the ultimate dining experience. As the view of surrounding
y was placed on having large windows and providing a spectacular view from anywhere inside
given to the lighting, colors, and overall ambiance, resulting in a truly magnificent experience for
ce its opening, The Glass Slipper has developed and maintained a reputation as one of the “must
ates the mountains and all that they have to offer, he also shares Deena’s dream of retiring to a
xed lifestyle on the beach. After some careful analysis of their financial condition, they knew that
eless, they were hatching a plan to bring them closer to their dream. They decided to sell The
t on a beautiful beach in
was still in their future, they could wake up in the morning to the sight of the palm trees blowing
hore. They also knew that hiring the right manager would allow James and Deena the time to
ould have to sell The Glass Slipper for the right price. The price of the business would be based on
as well as projections of future income.
needed to help in the determination of the value of the restaurant. Monthly sales for each of the
ppropriate for future sales? Why? (1pts)
of the correct forecasting model (4pts)
alysis and interpret the slope. (1pts)
model to these data to forecast (predict) sales for each month of the 2019 and 2020. (4pts)
Q4_ 10 Pts
Hint: “…invest up to 250,000” means that we do not have to invest all of $250,000
a) Answer :
b) Answer:
c) Answer: You can place it anywhere on this worksheet
ID
Non Financial Performance
EducationLevel
Age
Working Conditions
Job Stability
1
3.75 Master
41.00
4.83
4.50
2
3.00 Master
37.00
4.50
3.50
3
4.00 Master
34.00
4.00
4.50
4
1.50 Bachelor
28.00
2.50
3.50
5
3.50 Bachelor
38.00
3.83
4.50
6
3.50 Master
36.00
4.17
5.00
7
3.00 Master
28.00
3.67
2.00
8
3.00 Master
29.00
4.83
1.00
9
3.25 Master
26.00
4.50
4.00
10
3.00 Master
36.00
4.17
3.00
11
2.75 Master
27.00
2.50
1.00
12
3.00 Master
33.00
4.67
4.00
13
3.50 Master
33.00
4.33
2.50
14
1.75 Master
50.00
3.83
4.50
15
4.25 Master
62.00
4.83
5.00
16
2.75 Bachelor
34.00
3.50
3.00
17
3.25 Master
25.00
2.83
3.50
18
3.00 Master
47.00
3.33
4.50
19
4.00 Master
41.00
2.83
5.00
20
3.00 Master
42.00
1.83
5.00
21
2.75 Master
42.00
1.50
5.00
22
4.25 Master
38.00
4.33
3.50
23
3.25 Master
42.00
4.00
5.00
24
2.75 Master
26.00
3.83
4.00
25
2.50 Master
42.00
3.17
3.50
26
3.75 Bachelor
29.00
5.00
5.00
27
2.00 Master
37.00
2.67
4.00
28
2.75 Master
36.00
1.33
4.50
29
2.75 Master
31.00
4.67
4.50
30
1.00 Master
41.00
3.33
5.00
31
3.75 Bachelor
33.00
2.33
3.50
32
1.00 Bachelor
35.00
2.50
1.50
33
3.00 Master
44.00
3.00
2.00
34
2.00 Master
43.00
2.17
5.00
35
1.00 Master
36.00
2.67
1.00
36
3.00 Bachelor
41.00
2.17
2.50
37
5.00 Master
47.00
4.83
3.00
38
2.75 Master
51.00
4.17
4.00
39
5.00 Bachelor
25.00
5.00
5.00
40
5.00 Bachelor
25.00
5.00
5.00
41
4.00 Bachelor
48.00
4.50
4.50
42
4.00 Master
48.00
3.17
5.00
43
5.00 Master
32.00
2.67
2.00
44
5.00 Master
32.00
2.67
2.00
45
4.25 Bachelor
43.00
5.00
4.50
46
3.25 Bachelor
48.00
3.83
4.50
47
1.00 Master
35.00
2.17
5.00
48
2.25 Bachelor
40.00
3.17
3.50
49
4.00 Master
32.00
3.50
5.00
50
3.75 Bachelor
24.00
4.33
4.50
51
3.00 Master
29.00
3.83
4.00
52
3.00 Master
32.00
3.67
5.00
53
4.00 Bachelor
48.00
4.17
4.00
54
2.00 Bachelor
25.00
3.00
3.00
55
3.75 Bachelor
23.00
3.67
2.50
56
3.50 Bachelor
49.00
3.67
4.50
57
3.00 Master
32.00
3.17
2.50
58
2.50 Master
45.00
2.50
1.00
59
3.50 Bachelor
35.00
3.00
3.00
60
2.75 Master
33.00
2.33
1.00
61
2.50 Bachelor
34.00
2.50
4.00
62
3.25 Master
27.00
4.17
2.00
63
3.75 Master
38.00
4.50
5.00
64
3.50 Bachelor
39.00
5.00
5.00
65
3.50 Bachelor
39.00
5.00
5.00
66
1.75 Master
42.00
1.83
2.50
67
3.50 Bachelor
31.00
4.50
5.00
68
1.00 Bachelor
34.00
1.17
1.00
69
4.25 Bachelor
34.00
5.00
5.00
70
4.50 Bachelor
25.00
4.83
5.00
71
4.00 Master
31.00
4.33
2.00
72
4.75 Bachelor
23.00
4.83
4.00
73
5.00 Bachelor
31.00
5.00
5.00
74
5.00 Bachelor
30.00
4.83
5.00
75
4.50 Bachelor
24.00
5.00
5.00
76
4.25 Bachelor
29.00
4.67
4.50
77
3.50 Master
32.00
3.83
3.50
78
1.25 Master
25.00
2.50
1.00
79
4.00 Bachelor
23.00
4.17
3.50
80
1.00 Master
31.00
2.67
5.00
81
4.00 Bachelor
31.00
4.00
4.00
82
4.00 Bachelor
34.00
3.67
3.50
83
4.50 Bachelor
24.00
5.00
4.00
84
3.50 Bachelor
28.00
4.33
5.00
85
4.00 Bachelor
22.00
4.00
3.50
86
3.75 Bachelor
32.00
4.67
5.00
87
3.50 Bachelor
35.00
4.50
4.50
88
4.25 Bachelor
31.00
3.67
3.50
89
4.00 Bachelor
27.00
3.50
3.00
90
5.00 Bachelor
27.00
4.83
5.00
91
5.00 Bachelor
30.00
5.00
5.00
92
4.00 Bachelor
24.00
3.50
3.50
93
4.75 Bachelor
37.00
3.83
3.50
94
4.75 Bachelor
28.00
3.67
2.50
95
4.75 Bachelor
25.00
4.67
2.00
96
4.75 Bachelor
39.00
5.00
5.00
97
3.50 Master
38.00
4.17
4.00
98
4.00 Master
33.00
4.00
3.00
99
5.00 Bachelor
36.00
3.83
5.00
100
3.75 Bachelor
32.00
4.50
4.50
101
2.50 Master
38.00
4.00
2.50
102
3.25 Bachelor
32.00
2.67
2.50
103
2.50 Bachelor
30.00
3.50
4.00
104
4.50 Bachelor
34.00
4.17
4.00
105
2.00 Master
31.00
3.17
3.00
106
3.50 Master
30.00
3.50
3.00
107
2.50 Master
49.00
3.17
4.00
108
3.50 Bachelor
41.00
4.83
4.50
109
3.25 Master
34.00
4.33
4.50
110
1.00 Master
37.00
2.67
5.00
111
3.25 Bachelor
27.00
3.17
4.00
112
4.25 Bachelor
25.00
5.00
4.50
113
4.50 Bachelor
26.00
4.50
4.50
114
3.25 Bachelor
30.00
2.83
4.50
115
4.75 Master
25.00
4.17
4.50
116
3.75 Master
25.00
4.17
5.00
117
3.75 Master
35.00
3.67
4.00
118
5.00 Bachelor
23.00
4.17
5.00
119
3.75 Master
33.00
3.83
4.00
120
3.25 Master
40.00
3.83
4.00
121
3.25 Master
53.00
4.50
4.00
122
3.00 Master
40.00
3.67
4.50
123
1.50 Master
28.00
3.33
4.00
124
3.75 Master
59.00
3.67
1.00
125
1.75 Master
38.00
4.33
5.00
126
3.00 Master
43.00
5.00
5.00
127
4.75 Master
32.00
4.67
5.00
128
2.25 Master
30.00
4.00
5.00
129
4.00 Bachelor
36.00
4.17
4.50
130
1.00 Master
33.00
3.17
4.50
131
3.25 Master
35.00
3.67
4.50
132
2.50 Master
49.00
1.83
3.50
133
4.00 Master
35.00
2.83
5.00
134
4.50 Master
30.00
3.33
3.50
135
5.00 Master
27.00
5.00
5.00
136
4.00 Master
30.00
3.17
4.50
137
1.00 Bachelor
26.00
2.00
1.50
138
3.25 Bachelor
26.00
3.00
3.50
139
4.50 Master
38.00
5.00
5.00
140
4.00 Bachelor
29.00
4.50
3.00
141
3.00 Master
32.00
2.67
1.00
142
3.75 Bachelor
47.00
4.00
5.00
143
3.25 Master
29.00
1.67
3.00
144
3.25 Master
47.00
3.67
4.00
145
2.25 Master
37.00
1.83
2.50
146
5.00 Bachelor
25.00
5.00
5.00
147
2.00 Bachelor
38.00
3.17
3.50
148
3.50 Master
33.00
2.83
4.50
149
4.00 Bachelor
48.00
4.00
4.00
150
3.75 Master
30.00
4.50
2.50
151
3.00 Master
40.00
3.33
2.50
152
2.75 Master
46.00
3.83
3.50
153
3.25 Master
39.00
4.33
2.50
154
1.50 Bachelor
34.00
2.17
1.00
155
4.75 Bachelor
57.00
5.00
4.50
156
4.00 Bachelor
29.00
2.50
2.50
157
3.00 Master
28.00
4.83
4.50
158
3.00 Master
31.00
3.17
5.00
159
3.00 Master
38.00
3.00
5.00
160
3.50 Master
31.00
3.00
3.00
161
3.00 Master
39.00
3.17
2.50
162
4.25 Bachelor
32.00
5.00
5.00
163
3.50 Master
43.00
2.67
2.00
164
4.00 Master
49.00
4.50
5.00
165
4.00 Master
44.00
4.17
4.00
166
1.75 Master
32.00
3.83
3.50
167
1.00 Master
34.00
3.33
5.00
168
1.25 Master
42.00
3.33
2.50
169
2.75 Master
34.00
2.83
3.50
170
2.00 Master
43.00
2.83
3.00
171
4.00 Master
40.00
2.50
4.50
172
4.75 Master
50.00
4.67
5.00
173
3.25 Master
29.00
4.00
5.00
174
3.50 Master
42.00
2.50
5.00
175
3.25 Master
24.00
2.50
2.00
176
2.00 Master
41.00
3.17
4.00
177
2.75 Master
32.00
2.00
4.00
178
3.75 Master
37.00
4.83
5.00
179
4.00 Master
32.00
3.67
4.00
180
4.75 Bachelor
37.00
4.83
5.00
181
4.25 Bachelor
33.00
4.67
4.50
182
1.50 Bachelor
28.00
2.83
1.00
183
3.00 Bachelor
31.00
3.33
3.50
184
1.00 Bachelor
26.00
2.67
2.00
185
3.00 Bachelor
36.00
3.67
4.00
186
1.00 Bachelor
37.00
3.50
3.00
187
2.75 Master
34.00
4.83
4.50
188
2.00 Master
29.00
4.50
5.00
189
4.50 Master
28.00
4.50
5.00
190
2.00 Master
33.00
3.50
5.00
191
5.00 Master
36.00
4.67
3.50
192
4.50 Master
39.00
1.67
3.00
193
4.00 Bachelor
42.00
3.67
4.50
194
4.50 Master
28.00
4.67
5.00
195
2.25 Master
29.00
3.17
2.50
196
4.75 Master
33.00
5.00
5.00
197
3.00 Master
36.00
4.33
5.00
198
2.75 Bachelor
38.00
3.83
5.00
199
3.25 Master
34.00
2.50
4.00
200
2.75 Master
45.00
2.83
2.50
Table 1:
Teller ID
EducationLevel
Age
WorkingConditions
JobStability
1 Master
35
3.8
3.5
2 Bachelor
65
4.6
2.8
3 Master
28
3.8
3.2
4 Master
56
2.9
5
5 Bachelor
60
3.5
4
Q5_15pts
The given data set was collected using a cross-sectional survey for bank tellers. The sample size is 200.
Please test if Education Level, Age, Working Conditions, and Job Stability have a significant association with
sampled tellers.
In answering the given questions (a-h), write all the required/related statistics, values, etc. even if the analys
Use “Bachelor” as the reference category in Education Level (Bachelor = 0 and Master = 1)
a) Investigate the multicollinearity issue in detail (2pts).
Answer:
b) Write the proposed model? Is the model reliable?(1pts) why? (1pts)
Answer:
d) write the equation with estimated coefficients after the analysis (2pts)
Answer:
e) Interpret the estimated coefficients (3pts)
Answer:
f) Test if education level, age, working conditions, and job stability have positive or negative statistically signific
performance (1.5pts).
Which variable is the most important factor on non-financial performance? Why?
Answer:
g) Interpret R and Adj-R2.(2pts)
Answer:
h) Predict the nonfinancial performance of the given tellers based on the information
Answer:
NonFinancialPerformance
bank tellers. The sample size is 200.
Stability have a significant association with Non-Financial performance of the
ated statistics, values, etc. even if the analysis results are provided as tables.
helor = 0 and Master = 1)
have positive or negative statistically significant relationships with Non-Financial
rmance? Why? (0.5pts)
on the information provided in Table 1. (2pts)
Q6_10 pts
Word count:
1
1
1
=
3
With at least 500 words, answer the given questions (A, B, and C). There is no right or wrong answer.
a) Discuss the advantages of a 7-week fast-paced program in studying a data analytics course. Place your answer in the box
b) Discuss the disadvantages of a 7-week fast-paced program in studying a data analytics course.
c) Would you prefer a 7-week fast-paced or a 14-week program in studying a data analytics course? Why?
(please do not delete this row)
ong answer.
rse. Place your answer in the box below.
cs course? Why?
Purchase answer to see full
attachment
