internal comparison purposes

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. 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?

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 3 decimal places!
5. SEE LAST PAGE, SUMMARY POSITION (following the River Run Wellness Sample).
6. You only need to submit the output table. No Raw data.

1

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

regression output.

• 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

3

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.

4

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:

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

Grouped by Columns (If asked.)

Click New Worksheet Ply

5

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.