B-E-E-R IN THE U-S-A
For internal comparison purposes the USA is often divided into five distinct regions:
1. Northeast (CT, DE, ME, MD, MA, NH, NJ, NY, PA, RI, VT)
2. Southeast (AL, AR, FL, GA, KY, LA, MS, NC, SC, TN, VA, WV)
3. Midwest (IL, IN, IA, KS, MI, MN, MO, NE, ND, OH, SD, WI)
4. Southwest (AZ, NM, OK, TX)
5. 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?
- Pull up the Beer Consumption data.
- Click on the DATA TAB, select Data Analysis, and then select
ANOVA: Single Factor.
- 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.
- Format column widths so data is legible. Format data to no more than 3 decimal places!
- SEE LAST PAGE, SUMMARY POSITION (following the River Run Wellness Sample).
- You only need to submit the output table. No Raw data.
Multiple Regression: Class Survey Data
Same steps as the C-13 SLR assignment, only here there will be more than one X-variable. [Also, River Run from the videos is below as an example.]
• Y-variable: GPA
• Use at least 3 but no more than 4 X-variables.
• Use one dummy variable (provided in the Class Survey file). If you want
to use your own D-variable https://www.youtube.com/watch?v=TBJsEb2UCPs
• Note 1 → Survey data falls into various categories: external factors,
demographic, non-intellectual personality traits, emotional measures,
and spiritual/religious dimensions.
• Note 2 → Spend time up-front thinking about what a good model might be.
Have an overarching idea for your model. Then support that idea by the
individual X-variables you select.
• Note 3 → Doing Note 2 will make writing your regression statement much
easier and much better. See Note 4 below.
• Copy & Paste your variables onto a new sheet. X-variables need to be in
adjacent columns with the Y-variable (GPA) in a column before/after.
• Do not select any of the Residual choices or Plots in the pop-up box.
• Create a multicollinearity table. See, Setting Up Multicollinearity Table
in the River Run Wellness example below. Copy & paste the table below the
• Note 4: I am not looking for one correct model or even a useful model. I
want to see if you can create a model, articulate your reasoning for your
model/variables (see note 2 above), state the ways the results support
and/or do not support your thinking, and any specific conclusion regarding
your particular outcome. Overall, your writing should display insight
into your thinking, reasoning, and understanding of multiple regression.
SUMMARY POSITION PAGE:
• Response must be in solely of your work & in your own words.
• It may help to read the CBA Written Communication Rubric posted on BB.
• You are not getting a degree in data entry or Excel, so whether a score
moves towards 15 or towards 0 will be determined to the degree to which
the submission is a well-executed, blended expression of subject matter
context and statistics.
• Use 11pt/12pt font, type your name at bottom, and do not exceed 1 page.
• Attach the Summary Position page to the FRONT of your work.
Formatting: Set cells to 3 decimals and clean up tables. Do NOT turn in the raw data. Do NOT turn in any residuals. Do NOT turn in any graphs.
Submission Order –
• Page 1: Summary Position Page
• Page 2: Anova Output Table (no raw data)
• Page 3: Multiple Regression Output Tables and Multicollinearity Matrix
THE MULTIPLE REGRESSION SAMPLE ON THE PAGES BELOW IS FOR THE MULTIPLE REGRESSION OUTPUT COVERED IN THE CHAPTER 13 LECTURE. IT IS PROVIDED HERE AS AN EXAMPLE ONLY. DO NOT SUBMIT ANYTHING FROM THIS EXAMPLE. UNDER IT’S OWN SPECIFIC HEADING, THE EXAMPLE DOES CONTAIN THE DIRECTIONS TO CREATE A MULTICOLLINEARITY TABLE.
RIVER RUN WELLNESS PROGRAM: Multiple Regression
The Facts: Soaring health care spending is a drag on the profits of many companies. So businesses have a growing interest in sponsoring 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:
- Gender — Male = 0; Female = 1
- Cholesterol level
- Blood Pressure: Systolic
- 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.
- 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
Input Y Range: H$5:$H$105
Input X Range: $B$5:$G$105
Grouped by Columns (If asked.)
Click New Worksheet Ply
Setting up Multicollinearity Table
3) Place cursor on the third empty cell below the last line of your
output (Cell A132 or thereabouts).
4) Return to the River Run Corp data. Click on the DATATAB,
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 cell outside the range of your data. Click OK
5) Copy and paste the correlation matrix (created in step 4) below
your multiple regression output tables.