BIS 155 WEEK 7 ILAB
Follow below link to get this tutorial:
Contact us at:
Week 7 iLab BIS 155
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.
Software Citation Requirements
This course uses open-source software, which must be cited when
used for any student work. Citation requirements are on the Open Source Applications page.
Please review the installation instruction files to complete your assignment.
Please review the installation instruction files to complete your assignment.
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 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.
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
3.
Click 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
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
No comments:
Post a Comment