.equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/BIS155_W7_iLab.html”>iLab> STEP 9: Regression

Analysis

iLab Overview

Scenario/Summary

Hopefully you will find this week’s iLab

activity fun and useful. We’ll be exploring the world of statistics from a

business perspective this week, allowing you to practice your skills with

descriptive statistics, formatting, graphs, and regression analysis.

As discussed in the lesson, the value of

statistics lies in the ability to analyze data more effectively for the purpose

of improving decision making. You might have heard the expression that

“statistics never lie, and only liars use statistics”. There is an

obvious truth in this statement, in that, depending on the questions being

asked and the data collected, the statistics can skew reality.

For example, it is true that as ice cream sales

increase, accidents at swimming pools increase. Does this mean that the more

ice cream that is sold, the more accidents it causes (correlation/causation)?

Of course not, but the data, if not interpreted correctly, could lead to false

conclusions. It just so happens that both are correlated to a rise in temperature

in the summertime. The hotter it is outside, the more kids flock to swimming

pools, leading to more accidents, and the more ice cream is sold. So you see,

although statistics are vital in the world of decision making, you have to be

wise, and ask the right questions.

Deliverables

**NOTE**

Submit your assignment to the Dropbox, located

at the top of this page. For instructions on how to use the Dropbox, read these .equella.ecollege.com/file/8ff9f27a-3772-48cf-9855-4bec4e6706bf/1/Dropbox.html”>step-by-step instructions.

(See the Syllabus

section “Due Dates for Assignments & Exams” for due dates.)

You will turn in one Excel workbook for this

iLab. The workbook will consist of nine separate worksheets, including the

Documentation sheet.

File naming convention: If your name is Jane

Doe, then your file should be named very similar to: Doe_J_Week7_iLab.xlsx.

Click to download the .equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/BIS155_W7__Lab_8_Grading_Rubric.docx”>Grading Rubric for Lab

8

Required Software

Microsoft Office: Excel

2013

Options for accessing Microsoft Excel 2013:

1.

Use a personal copy on

your PC. You can request a copy of Microsoft Office 2013 via the Student

Software Store icon on the Course Resources Page

2.

If you are a MAC user,

click to read the .equella.ecollege.com/items/2d5f4ce6-cbbd-4727-9910-eb5621ef0ed5/0/ZZZ_W2_MacSupport.html”>MAC User Information.

3.

Click .devry.edu/”>DeVry

virtual lab to access Microsoft Excel in the virtual lab.

For additional virtual lab information and tutorials on saving files, click the

iLab icon on the Course Resources page.

Lab Steps

STEP 1: Getting

Started—Worksheet Template

Please download this week’s iLab file: .equella.ecollege.com/file/1f71be11-64b2-465e-a667-b0f4510202c2/20/Week7_iLab_Statistics_Student.xlsx”>Week7_iLab_Statistics

Your first step should be to save and rename

this file according to the naming convention above.

It is recommended, as you work on this iLab,

that you save your work often.

STEP 2: Create a

Documentation Page

This will be a similar documentation page that

you have used for all prior iLabs. Please refer to instructions in iLab 1 for

detailed instructions.

Be sure to place the documentation sheet as your

first sheet.

STEP 3: Descriptive

Statistics

The **Data_1971_2000** worksheet is

already loaded with data for you, which is the actual temperatures for all of

the U.S. states between 1971 and 2001. As you can see, the data already

contains the *average* temperature for each state, in both

Fahrenheit and Celsius, along with the ranking of the states, in terms of

warmest average temperature (#1) to the lowest.

1.

Freeze the top row, so

that the column headers are visible as you scroll through the data.

2.

At the bottom of the

page, you are asked to provide the Count, Average, Median, Mode, Min, and Max

for each of the states for each of the data columns. The shaded area at the end

of the states is where these descriptive statistics should be entered.

3.

To the right of the

data, starting at approximately Texas (row 44), use the Data Analysis feature

to display the summary descriptive statistics for each temperature and the

rank. Be sure to shade and format your descriptive statistics (similar to the

shading in Step #2 above) so as to be able to read everything well.

As you read your results, you might note some

interesting results. First and foremost, note how the statistics associated

with the rankings are virtually worthless, as they really don’t provide any

insight to the data itself. This is a little of what I meant above when I

talked about some statistics are junk, and you have to be careful in how you

ask your questions and interrupt the results.

STEP 4: Bar Chart and

Summary Statistics

Using the **BarChart** worksheet,

calculate the summary statistics shown at the bottom of the data, for each of

Bottles, Cans, and Plastic.

Create a bar chart to the right of the data,

with a title of Marketing Campaign Results. You can choose the colors that you

want for each city’s results, but make sure that you show the Y-axis labels to

the right and the X-axis labels on the bottom, along with the word City as

their label.

STEP 5: Line Chart

Using the **LineChart1** worksheet,

calculate the average income for the ages listed. Then create a line chart,

with a title of Average Income by Age, with appropriate labels on the X and Y

axis.

Your chart should be placed to the right of your

data, on the same sheet.

STEP 6: Average and

Median, With Line Chart

This step is very similar to the previous

worksheet, except that there is an additional summary statistic and you are

working with multiple variables.

Calculate the average and median for both Income

and Rent. As you look at your results, do you notice the difference between the

results? Does this better explain the difference between average and median for

you?

To the right of the data, on the same sheet,

produce a line graph of the Income and Rent. Again, the color of the lines is

your choice. Use a chart heading of Average Income/Rent by Age. Be sure to show

your Income and Rent labels to the right of the chart, and a label of Age on

the X axis and Amount ($000) on the Y axis.

STEP 7: Regression

Analysis and Scatter Graph

The data here is very simple, and not really a

good example of using regression analysis, but the process behind the exercise

is the most important issue.

1.

Create a scatter chart

of the data, below the data, with a title of Revenue Growth.

2.

Draw a trendline

associated with the data points. Be sure to select the inclusion of the

Equation and R-squared values on the chart.

3.

Perform a regression

analysis on the data set. Remember to identify which of the variables are

dependent (Y axis) and independent (X axis) so as not to get confused on your

input values. Place the regression results, starting at cell I1, instead of

using the default, which is a separate sheet. Be sure to identify and highlight

on the regression data, the R-squared value, the Intercepts point, and the

slope of the line.

STEP 8: Sorting

Statistics

The data listed here is the first and last five

presidents to have died of natural causes.

Many forget that simply sorting information in a

specific format can provide meaningful information. However, before we begin

the multiple sorting exercise, simply complete the descriptive statistics at

the bottom of this group of presidents. There are multiple ways of calculating

the average age for the first and last five presidents, but for this exercise,

use the AVERAGEIF function. Because there is not a MEDIANIF function, you will

have to do this one manually with your formula (not with a calculator).

Your next step is to copy the data for each

president, excluding the ordinal column, to each of the other two categories.

For example, the column of President should start at cell G3, and at cell L3.

Once you have copied the data, reorder the data, with the second group by Age

at Death, and the third set by Year of Death.

You will most likely find this information very

interesting. Some find it strange that the average age of death of the first

five and the last five presidents was less than a year different, especially

given all the advances in medicine.

STEP 9: Regression

Analysis

**Scenario:** The owner of the Original Greek Diner has been advertising

for the past year, and is now ready to renew his contract. He needs to know if

the advertising has been effective, so your task is to take the prior year’s

data and perform a regression analysis to determine the correlation between

advertising expenditures and restaurant sales (revenues).

Using the GreekData sheet, prepare a scatter

plot graph with a title of Revenues (Y), placed to the right of the data on the

same sheet. The data must be presentable, so you might want to use an increment

of $500 for the X axis. After creating the graph, which should include the

Equation and R-squared values, create a trend line. You should notice

that there appears to be a close relationship between advertising spending and

revenues.

Your next task is to create the regression data

on a separate sheet, labeled Greek Regression. As a reminder, regression

analysis is located on the Data -> Data Analysis menu. Be sure to highlight

the R-squared value in red, the Intercept value in blue, and the X variable 1

in green.

Based on this data, and what you have learned

about regression thus far, what do you think the owner should do?

Let me throw you a curve (pun intended). Suppose

this restaurant is located near a baseball park, and it just so happens that

the days the owner advertised, there were baseball games playing on those

nights. Would you now have the same conclusions, or might you want to take the

time to collect more data?

Statistics are not perfect, but they can provide

immeasurable insight into data analysis. You just have to ask the right

questions.

Be sure to submit your work for this iLab to the

Dropbox basket labeled **Week 7: iLab 8**.