Chat with us, powered by LiveChat Consumption of Beer Within Regions of The USA Survey Analysis | All Paper
+1(978)310-4246 credencewriters@gmail.com
Select Page

All of the directions are provided in the Fact set document. My red Id # is 822958637, this is used to differentiate between different students work. If you have any questions feel free to ask! Thank you!!!
fact_set_esp3.docx

data_set_esp3.xls

introduction_to_all_stat_packs.pdf

mandatory_standards_for_all_stat_packs.pdf

spring_2019_5.5_class_survey_data_1_.xlsx

Unformatted Attachment Preview

Excel Stat Pack 3
B-E-E-R IN THE U-S-A
One-Way Anova
The Facts
For internal comparison purposes the USA is often divided into five
distinct regions:
1.
2.
3.
4.
5.
Northeast (CT, DE, ME, MD, MA, NH, NJ, NY, PA, RI, VT)
Southeast (AL, AR, FL, GA, KY, LA, MS, NC, SC, TN, VA, WV)
Midwest (IL, IN, IA, KS, MI, MN, MO, NE, ND, OH, SD, WI)
Southwest (AZ, NM, OK, TX)
West (AL, CA, CO, HI, ID, MT, NV, OR, UT, WA, WY)
Despite any trends, shifts, and niches within the market beer remains the
most popular alcoholic drink in America. But is the consumption of beer
evenly distributed across regions or are some regions within the USA
consuming more beer than others?
Steps:
1) Pull up the Beer Consumption data*.
2) Click on the DATA TAB, select Data Analysis, and then select
ANOVA: Single Factor.
3) Fill in the entries for the dialog box as follows.
Input Range: \$A\$3:\$E\$15. (Notice this includes the cells
containing the labels.)
Grouped BY: Columns (Default choice)
Click on Labels in First Row.
Alpha: .01.
Click on output Range: \$A\$17.
Click OK.
4) Format column widths so data is legible. Format data to no more
than 4 decimal places!
5) SEE LAST PAGE: SUMMARY POSITION. Response must be typed.
*Data from https://247walst.com
1
Excel Stat Pack 3
BEACH FRONT LIVING: Bivariate Linear Regression
The Facts: Use the Coronado data to examine the relationship between the
estimated square footage of a house (ESF) and its sales price.
Steps:
1) Pull up the Coronado data.
2) Click on the DATA TAB on the toolbar, select Data Analysis, and
then select Regression.
3) Fill in the entries for the dialog box as follows.
Input Y Range: \$C\$5:\$C\$56.
Input X Range: \$B\$5:\$B\$56.
Click Labels.
Output Range: New Worksheet Ply.
Click on all 4 Residual Boxes (Residuals, Standardized Residuals,
Residual Plots, Line Fit Plots)
Click OK.
Graphs:
4) Copy and Paste your Residual Plot & Line Fit Plot to another page.
You might want to enlarge them so they present better.
5) Make sure graphs are labeled in a way that makes sense.
6) Put a trendline on the Line Fit Plot. There are different ways to
do this depending on the version of Excel. Two common ways:
A. Click inside the graph. Select “Layout” from “Chart Tools”.
Click on the “Trendline” icon and select “Linear Trendline”. To
put the equation on the chart select “More Trendline Options” then
check “Display Equation on Chart”, or -B. Click inside the graph. From the “Chart Layout” options in the
toolbar, select the graph with the trendline and fx(this will add
the equation also).
If not done automatically, to extend the trendline through the Y
axis, Right Click on the trendline, select “Format Trendline” in
the “Forecast Forward” and “Forecast Backward” put 3 (or higher).
Before Printing:
7) Adjust column widths for legibility.
8) Delete one set of the cells titled: Lower 95% and Upper 95% as
Excel repeats them.
9) You may format cells to 4 decimal places.
10)Per the Mandatory Standards document, it is okay to make a page
break between rows when you have long columns of data, but make
sure the right margin is not cutting a table (ex: anova table).
11)Turn in the Regression Output, including the Residuals & Graphs.
12)Do NOT turn in the raw (given) data for this problem!
2
Excel Stat Pack 3
Student Survey Data: Multiple Regression
Use the Class Survey Data to regress multiple X-variables onto GPA.
The steps are the same as the Beach Front Property example only now there
is more than one X-variable.
•You must use at least 3 but no more than 5 independent variables.
•One independent variable must be a dummy variable (see the Dummy
Variables page of the Class Survey file.
•Just for thought: some data categories are demographic, some are
external influences, and some non-intellectual personal traits.
•Copy & Paste your chosen variables onto a new sheet. Excel requires all
the X-variables to be in adjacent columns with the Y-variable (GPA) in
either the column before or after the X-variables.
•Do NOT select any of the Residual choices or Plots. Your output should
not contain any residuals or any graphs.
•Create a multicollinearity check and copy and paste the table below your
regression analysis output. See: Setting Up Multicollinearity Table in
sample below.
•If needed, see the River Run Wellness sample below for steps needed for
both the regression analysis and multicollinearity table.
NOTE: I am not looking for one correct model or even a good model. So it’s
not necessary to keep running different models. I am looking to see if you
can create a model and articulate your reasoning for said model along with
anything — good, bad or otherwise — about the model that might be useful
for the viewer to know, and your conclusion.
SUMMARY POSITION page:
• Response must be typed.
• Response must be in solely in your own words.
• Response must be statistically literate, keeping in mind you are
in the Fowler College of Business.
• Responses must not exceed the 1 given page. So crystalize your
thoughts, prioritize your issues, and use 11pt or 12pt font.
• Type your name where directed, sign, and date.
• Attach the Summary Position page to the FRONT of your work.
You can set cells to 4 decimals and make adjustments to make it clean.
For this example, turn in the Regression Output & Multicollinearity
Table. Do NOT turn in the raw data. Do NOT turn in residuals. Do NOT turn
in graphs.
3
Excel Stat Pack 3
THE MULTIPLE REGRESSION SAMPLE ON THE PAGES BELOW IS FOR THE MULTIPLE
REGRESSION OUTPUT CONTAINED IN THE CHAPTER 13 STUDENT WORKSHEET THAT
WAS/WILL BE COVERED IN CLASS.
4
Excel Stat Pack 3
RIVER RUN WELLNESS PROGRAM: Multiple Regression
The Facts: Soaring health care spending is a drag on the profits of
wellness programs. Studies show such programs can decrease employee
absenteeism, workplace injuries, and medical claims. Also wellness
programs have a positive ROI, by a factor ranging from \$2.50 to \$4.50
for every \$1 spent.
While employee participation in wellness programs is often voluntary,
many companies offer incentives to join. For those joining a wellness
program, companies utilize a health risk assessment, a self-reported
survey that collects baseline data on the health of an employee.
Last year River Run Processing Corp implemented a wellness program.
Worksheet 7 contains a partial list of the data River Run Corp has
collected to help them analyze their recently implemented program.
Independent variables include:
1. Gender — Male = 0; Female = 1
2. Age
3. Weight
4. Cholesterol level
5. Blood Pressure: Systolic
6. Physical Activity: Number of times per week
The dependant variable is Medical, the difference (in dollars (\$)),
in the employees’ medical cost for the first year.
Note: There are no “lettered cells” in this example.
Steps:
1) Pull up the River Run Corp Wellness Program data. The steps are
the same as the previous example, only now you will use six
independent variables (X-variables).
2) Click on the DATA TAB, select Data Analysis, and then select
Regression.
Input Y Range: H\$5:\$H\$105
Input X Range: \$B\$5:\$G\$105
Click Labels
Click New Worksheet Ply
Click on Residuals
Click on Standard Residuals
Leave Residual Plots and Line Fit Plots
empty. Click OK
5
Excel Stat Pack 3
Setting up Multicollinearity Table
3) Place cursor on the third empty cell below the last line of your
4) Return to the River Run Corp data. Click on the DATA TAB,
select Data Analysis, and then select Correlation.
Input Range: \$B\$5:\$H\$105 (All the data, Xs, Y, and
Labels) Grouped By: Columns (Default Setting)
Click on Labels in the first row
Click on Output Range and input
\$A\$109 Click OK
5) Copy and paste the correlation output (created in step 4) to the
bottom of the multiple regression Summary Output page, beneath
the RESIDUAL OUTPUT.
6
Excel Stat Pack 3
SUMMARY POSITION
Beer Consumption by Region: What is your analysis of the data?
Multiple Regression Analysis of GPA Statement:
The above is solely in my own words.
Last Name, First Name (Type):
__________________________________________________________
Signature (Pen)
_____________________________
Date
A
1
B
C
D
E
Beer Consumption by States (Gallons per Year per Adult of Drinking Age)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Northeast
20.A
28.7
33.8
20.2
23.6
40.6
20.6
21.9
26.4
23.0
32.8
Southeast
28.9
23.B
26.3
24.0
23.6
29.6
30.9
25.0
30.9
24.4
24.4
27.4
Midwest
27.4
23.6
31.C
25.7
25.3
28.4
27.7
33.3
38.3
27.3
38.2
34.3
Southwest
26.6
28.8
25.1
31.8
West
26.0
25.1
28.3
28.D
25.9
39.4
32.9
30.0
18.7
24.7
29.5
A
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
39
40
41
42
43
44
45
46
47
48
49
50
51
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
39
40
41
42
43
44
45
46
B
C
ESF
1267
1800
1725
1500
1795
1000
1800
1213
4297
1353
1194
1320
3012
1672
1200
1152
2200
707
1300
1320
1817
3908
2163
983
3079
1720
1320
1600
1689
2737
3300
1127
3400
2520
4400
4500
1750
2100
534
2057
1100
2522
1200
1665
1400
820
Sale Price
\$1,905,000
\$2,600,000
\$2,300,00A
\$1,125,000
\$1,445,00B
\$910,000
\$2,350,000
\$1,700,00C
\$3,600,000
\$1,660,00D
\$619,000
\$1,300,500
\$2,125,000
\$1,300,500
\$1,225,000
\$620,800
\$1,620,000
\$680,000
\$2,775,000
\$1,325,000
\$2,000,000
\$3,317,000
\$2,400,000
\$817,500
\$2,800,000
\$1,630,000
\$1,500,000
\$1,900,000
\$1,175,000
\$2,388,000
\$2,550,000
\$1,135,000
\$2,187,135
\$2,000,000
\$3,875,000
\$3,386,000
\$1,800,000
\$2,985,000
\$603,000
\$2,068,000
\$945,000
\$1,973,200
\$2,300,000
\$2,300,000
\$1,640,000
\$1,560,000
D
A
52
53
54
55
56
47
48
49
50
51
B
3270
3750
1816
674
1274
C
\$3,450,000
\$3,625,000
\$875,000
\$860,000
\$1,350,000
D
A
B
1
2
3
4
5 Employee
6
1
7
2
8
3
9
4
10
5
11
6
12
7
13
8
14
9
15
10
16
11
17
12
18
13
19
14
20
15
21
16
22
17
23
18
24
19
25
20
26
21
27
22
28
23
29
24
30
25
31
26
32
27
33
28
34
29
35
30
36
31
37
32
38
33
39
34
40
35
41
36
42
37
43
38
44
39
45
40
46
41
47
42
48
43
49
44
50
45
C
D
E
River Run Corp Wellness Program
F
G
H
Phys Act
4
3
5
5
5
2
0
2
3
2
0
0
3
3
3
2
7
2
2
4
3
3
3
1
4
5
3
4
2
2
2
2
4
2
0
3
4
3
3
4
3
5
3
1
2
Medical
(\$345)
(\$255)
(\$680)
(\$550)
(\$760)
\$315
\$415
(\$110)
(\$330)
(\$70)
\$130
\$560
(\$470)
(\$270)
(\$120)
(\$80)
(\$640)
(\$220)
(\$180)
(\$590)
(\$340)
(\$215)
\$230
\$205
\$0
(\$430)
\$130
\$140
\$530
\$450
(\$130)
(\$70)
(\$360)
\$60
\$510
(\$95)
\$200
(\$160)
(\$310)
(\$380)
(\$295)
\$440
\$0
(\$40)
(\$100)
Sample Only Sample Only Sample
Gender
0
0
1
1
0
1
0
1
0
0
1
1
1
1
0
0
1
0
0
1
1
0
1
0
1
0
0
1
1
0
1
1
1
0
1
0
0
0
0
1
1
0
1
0
0
Age
43
53
45
36
37
58
39
40
35
42
43
61
41
46
47
48
37
51
51
46
53
54
55
53
35
58
59
60
63
60
56
54
54
55
55
54
54
54
54
53
45
48
50
51
52
Weight
137
146
117
115
118
271
247
91
115
126
188
203
102
181
156
125
120
157
142
144
163
163
215
167
98
155
142
131
192
175
154
128
124
139
123
170
207
163
175
145
91
158
95
131
139
Chol
176
170
212
176
152
230
218
168
170
177
232
234
156
170
182
180
177
162
172
144
172
170
200
192
152
158
172
192
252
210
182
172
172
192
217
194
182
202
182
182
182
262
148
174
152
BP: Syst
121
119
117
123
117
161
155
123
117
117
155
160
119
127
129
123
117
119
119
119
121
127
159
157
117
115
125
135
163
160
131
123
115
123
155
127
125
139
135
127
117
167
119
137
129
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
A
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
B
1
1
0
0
0
0
0
0
1
1
0
0
1
1
0
1
0
1
0
1
0
0
1
1
1
0
1
0
0
0
1
0
1
0
1
0
1
0
0
0
1
1
0
1
0
0
0
1
1
0
1
C
53
54
48
47
47
47
63
39
40
63
54
54
55
56
60
41
49
59
49
52
54
54
54
65
50
39
51
46
38
50
51
52
49
46
49
52
59
55
56
49
50
52
47
48
44
55
53
51
52
55
54
D
104
139
159
147
129
169
209
153
120
199
159
158
134
135
188
127
152
178
155
127
159
162
139
214
127
155
115
128
164
133
134
129
130
134
121
142
287
144
159
148
167
188
161
181
175
159
157
96
126
158
146
E
192
182
172
157
168
182
208
172
178
196
162
166
162
172
218
160
190
194
168
172
166
232
172
252
160
162
182
152
162
160
180
144
176
162
184
164
254
172
192
184
176
174
182
172
192
217
176
180
167
212
177
F
115
115
117
117
118
117
157
123
137
155
127
127
125
121
151
127
125
163
121
131
125
135
132
177
129
125
111
127
127
129
125
125
127
117
121
125
160
127
139
115
141
131
133
127
131
137
133
127
119
127
117
G
3
3
3
3
5
4
3
3
3
0
2
2
3
3
2
3
3
2
1
2
2
2
4
2
3
2
3
3
4
4
4
2
2
3
2
3
2
1
2
2
1
2
3
3
5
2
2
2
3
1
4
H
(\$170)
(\$110)
\$50
(\$210)
(\$230)
(\$300)
\$510
(\$330)
(\$280)
\$610
(\$110)
(\$15)
(\$210)
(\$150)
\$440
(\$315)
(\$80)
\$380
(\$40)
(\$140)
(\$110)
(\$160)
(\$230)
\$580
(\$155)
(\$90)
(\$120)
(\$360)
(\$415)
(\$200)
(\$170)
(\$100)
(\$190)
(\$330)
(\$40)
(\$100)
\$410
\$20
\$110
\$0
(\$20)
(\$140)
(\$220)
(\$190)
(\$460)
\$55
(\$80)
(\$130)
(\$200)
\$130
(\$320)
102
103
104
105
A
97
98
99
100
B
0
1
0
0
C
48
50
52
54
D
138
158
149
165
E
166
202
175
188
F
125
117
125
125
G
5
4
3
3
H
(\$360)
(\$235)
(\$200)
(\$145)
Introduction to Stat Packs
of statistics. A computer can ease many of the necessary calculations,
it is not, however, a replacement for statistical knowledge.
All directions are given under the assumptions that students will be
using Excel on a Microsoft device.
All analysis will involve either the DATA ANALYSIS TOOL or STATISTICAL
FUNCTIONS.
ONCE IN EXCEL:
To access STATISTICAL FUNCTION click on the Function Wizard icon,
fx, located on the toolbar, to the immediate left of the input line.
To access the DATA ANYLSIS TOOL click DATA TAB on the toolbar.
Data Analysis is available under the Analysis Group (far right corner
of the Tool Bar.)
If you do not see Data Analysis you will need to load it into Excel.
It is free. The following applies to Excel 2007, Excel 2010, Excel
2013, and Excel 2016.
1. Click the File tab, click Options, and click the Add-Ins category.
If you’re using Excel 2007, click the Microsoft Office Button, and
then click Excel Options
2. In the Manage box select Excel Add-ins, click Go.
3. In the Add-Ins box, check the Analysis ToolPak check box, click OK.
• If Analysis ToolPak is not listed in the Add-Ins available box,
click Browse to locate it.
• If you are prompted that the Analysis ToolPak is not currently
installed on your computer, click Yes to install it.
The Data Analysis should now be loaded into Excel.
MAC USERS: Many Excel versions for MAC do NOT have the Data Analysis.
If this is the case for you and you cannot load it into your version,
complete those sections requiring Data Analysis on another computer
(such as those in the library).
Mandatory Standards for All Stat Packs
Read, Use, and Save This Document
The following applies to all Stat Pack assignments. To receive full
credit:
1. Put your name in the marked cells, copying over the words Last
Name, First Name.
2. Put your Red ID# in the marked cells, copying over the words Red
ID#.
3. Complete the Stat Pack.
4. Turn it in on time (paper copy, no email attachments).
5. Format it like a business document.
A) Format ALL graphs & tables, including but not limited to
axis titles, scales, data legends, data labels, and titles.
When printing:
B) No gridlines*
C) No Excel row and column headings*
D) No cutting off of words in cells*
E) No cutting tables by right margin, leaving column table
data hanging on a separate page.*
*Before printing, use Print Preview (if given the option or
in the document itself), Portrait Orientation and/or the NO
6. Put the printed sheets in the same order as they are presented
in the stat pack.
7. Work turned in greater than 1 in length page must be stapled.
8. Do not attach a cover sheet.
9. Do not write anything in pen, pencil, or highlighter, except
signature & date in pen if required.
10. There is no need for fancy fonts or color schemens.
ATTENTION: Where instructed to do so input your 9 digit RED ID # on
the Excel sheets. Then substitute the last four digits of your Red ID
# for the letters A, B, C, and D in the actual data sets.
A student with the Red ID # 888887654 would make the following
substitutions: A = 7, B = 6, C = 5, D = 4.
Thus, the Twisty Player’s Theatre problem for this student would
contain the following values:
(A) Cell A9: 117
(B) Cell A16: 116
(C) Cell A20: 105
(D) Cell A24: 114
When printing keep these “lettered cells” either highlighted yellow or
printed in grey scale.
A Note on Popup Boxes: At times input requirements/input wording is
slightly different across various versions of Excel. If your screen’s
popup box does not exactly match the directions use the help function
and good judgement.
Major
Accounting
Marketing
Accounting
Accounting
Accounting
Marketing
Accounting
Management
Management
Accounting
Information Systems
Accounting
Management
Accounting
Marketing
Information Systems
Marketing
Marketing
Accounting
Entrepreneurship
Marketing
Management
Entrepreneurship
Marketing
Accounting
Information Systems
Information Systems
Information Systems
Management
Accounting
Accounting
Management
Accounting
Accounting
Management
Information Systems
Marketing
Management
Marketing
Accounting
Other
Accounting
Marketing
Accounting
Information Systems
Accounting
Campus Engagement
SDSU Club
SDSU Club
No Affiliation
No Affiliation
No Affiliation
No Affiliation
SDSU Club
Fraternity/Sorority
SDSU Club
SDSU Club
No Affiliation
No Affiliation
Fraternity/Sorority
No Affiliation
No Affiliation
SDSU Club
No Affiliation
Fraternity/Sorority
SDSU Club
No Affiliation
No Affiliation
Fraternity/Sorority
No Affiliation
No Affiliation
No Affiliation
SDSU Club
No Affiliation
SDSU Club
No Affiliation
No Affiliation
No Affiliation
No Affiliation
Fraternity/Sorority
No Affiliation
No Affiliation
Fraternity/Sorority
No Affiliation
No Affiliation
Fraternity/Sorority
No Affiliation
No Affiliation
No Affiliation
No Affiliation
SDSU Club
No Affiliation
No Affiliation
No. of Units
15
12
13
15
15
11
13
14
12
17
15
6
14
13
13
12
14
19
14
18
17
15
13
15
9
12
6
12
18
12
13
13
14
15
13
16
14
13
13
6
13
13
10
15
15
13
Work Hours per Week
0
0
15
16
20
30
0
10
20
0
10
32
0
10
25
30
10
23
30
0
40
12
0
35
40
30
40
0
40
0
40
28
15
35
30
30
60
48
12
40
15
0
40
0
0
12
Sleep Hrs per Night
50
8
49
45.5
6
50
56
56
35
40
42
49
56
56
42
6.5
56
46
40
49
49
42
45
7
30
46.5
49
38.5
32
45
8
42
7
58
56
40
45.5
30
49
56
46
55
45
6
50
49
Accounting
Marketing
Management
Accounting
No Affiliation
No Affiliation
No Affiliation
No Affiliation
13
11
12
15
18
28
40
0
7
7
5
49
Daily Commute Time
0.75
1
30
3.75
1
1
0.5
0
1
0
2.5
1
0.5
2
2
1
1
0.5
0.25
2
1
1
2.25
1
5.25
2.25
1.5
0.5
1
1.5
3
2
0.5
3
1
2
2
3
1
1
1.25
1.5
2.5
3
2
1.25
Known Work Prospect?
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
No
Yes
No
No
No
No
Yes
No
Yes
No
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
No
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Parent(s) College Degree?
Yes
No
Yes
No
No
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
No
Yes
No
No
No
No
No
Yes
No
Yes
Yes
No
Yes
Yes
Yes
Yes
No
No
No
Yes
Yes
No
Yes
Gender
Male
Male
Male
Female
Male
Female
Male
Female
Male
Male
Female
Female
Male
Male
Female
Male
Female
Female
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Female
Male
Male
Female
Female
Female
Female
Male
Male
Female
Female
Male
Female
Male
2
2.5
1.25
12
Yes
Yes
Yes
No
No
Yes
No
No
Male
Male
Female
Female
Last Semester Rating
5
1
4
3
3
3
3
4
3
2
2
3
3
3
3
3
3
3
4
4
4
4
2
4
2
4
4
4
3
2
3
3
5
4
3
4
3
3
3
3
4
4
3
3
4
3
This Semester Rating
5
5
4
4
4
5
4
4
4
4
1
4
2
4
3
3
5
5
5
4
4
5
4
4
4
4
5
3
4
3
3
4
3
4
4
4
4
1
4
5
5
4
5
4
5
3
Happy W/ Major Persistence
5
5
4
3
4
4
4
5
3
4
5
5
3
4
3
4
3
2