University of the District of Columbia Linear Regression Excel Worksheet Basic Subject Material: linear regression Readings: Chapter 10R as necessary Home | Course Hero
University of the District of Columbia Linear Regression Excel Worksheet Basic Subject Material: linear regression
Readings: Chapter 10R as necessary Homework due Next Meeting:
Instructor Comments
Linear regression is one of the most powerful statistical tools. It has numerous applications in business. It can be especially useful when trying to determine cause and effect situations, as is explained in the text. These kind of situations can show the cause and effect relationship between number of employees and profit center workload requirements, which can be used to justified staffing levels. Understaffed managers will have a difficult time reaching their performance objectives and will have a difficult time retaining overworked employees. They will also have difficulty justifying their staffing requirement budgets if they cannot provide a statistically sound reason for why their requested staffing levels are needed.
This module reviews linear regression, and introduces (to most students) the concepts of stepwise regression, and the Spearman test. The case studies are realistic examples of how linear regression can be used at work.
First Case 10 Linear Regression is on page 188 of the textbook
Case 10 Part 2 – Stepwise Regression is on page 190 of the textbook.
Video 1
https://www.viddler.com/embed/8f8cf973/?f=1&player…
Video 2
https://www.viddler.com/embed/489084a3/?f=1&player…
Video 3
https://www.viddler.com/embed/7ab6a6f2/?f=1&player.. ARMAND’S PIZZA SALES
$250
y = 5x + 60
R² = 0.9027
Quarterly Sales in $1000s
$200
$150
$100
$50
$0
0
5
10
15
20
Student Population in 1000s
25
30
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.950122955
R Square
0.90273363
Adjusted R Square
0.890575334
Standard Error
13.82931669
Observations
10
ANOVA
df
SS
Regression
Residual
Total
1
8
9
Coefficients
Intercept
X Variable 1
60
5
14200
1530
15730
Standard Error
t Stat
P-value
9.22603481 6.503336 0.000187
0.580265238 8.616749 2.55E-05
RESIDUAL OUTPUT
Observation
Predicted Y
1
2
3
4
5
6
7
8
9
10
70
90
100
100
120
140
160
160
170
190
MS
F
14200 74.24837
191.25
Residuals
-12
15
-12
18
-3
-3
-3
9
-21
12
$300
Y
$200
$100
Significance F
2.54887E-05
Lower 95%
38.72472558
3.661905962
Upper 95%
81.27527442
6.338094038
Lower 95.0%
38.72472558
3.661905962
Upper 95.0%
81.27527442
6.338094038
X Variable 1 Line Fit Plot
$300
Y
$200
Y
$100
Predicted Y
$0
0
10
20
X Variable 1
30
ARMAND’S PIZZA
$250
Student
Population
Restaurant in 1000s
1
2
2
6
3
8
4
8
5
12
6
16
7
20
8
20
9
22
10
26
Quarterly
Sales in
$1000s
$58
$105
$88
$118
$117
$137
$157
$169
$149
$202
$130
$200
$150
$100
$50
$0
0
average sales
5
Armand’s Pizza
y = 5x + 60
R² = 0.9027
10
15
20
25
30
Price in
Sq Feet in Hundreds $Thousands
26
518
27
548
33
588
29
592
29
650
34
760
30
914
40
1046
22
430
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.828732
R Square 0.686796
Adjusted R Square
0.642053
Standard Error
119.2415
Observations
9
ANOVA
df
Regression
Residual
Total
SS
1 218249.8
7 99529.76
8 317779.6
Coefficients
Standard Error
Intercept -281.833 246.6247
Sq Feet in Hundreds
31.78704 8.113357
MS
F
Significance F
218249.8 15.34967 0.005765
14218.54
t Stat
P-value Lower 95%Upper 95%Lower 95.0%
Upper 95.0%
-1.14276 0.290712 -865.008 301.3414 -865.008 301.3414
3.917865 0.005765
12.602 50.97208
12.602 50.97208
PUSHUPS FOR MALE SOLDIERS
Age
18
18
22
21
20
19
30
34
24
25
Pushups
52
48
51
47
48
39
33
32
39
42
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.81948
R Square 0.671548
Adjusted R Square
0.630491
Standard Error
4.368889
Observations
10
ANOVA
df
Regression
Residual
Total
SS
MS
F
1 312.2025 312.2025 16.35665
8 152.6975 19.08719
9
464.9
Coefficients
Standard Error t Stat
P-value
Intercept 68.66497 6.470396 10.61217 5.44E-06
Age
-1.10671 0.273644 -4.04434 0.003713
Significance F
0.003713
Lower 95%Upper 95%Lower 95.0%
Upper 95.0%
53.74421 83.58573 53.74421 83.58573
-1.73773 -0.47568 -1.73773 -0.47568
# Pushups
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
0
0.2
Pushups Chart
0.2
0.4
0.6
Age
0.8
1
SPEARMAN TEST BOOK PAGE 210 FOR DATA
FISHER TEST (SIMPLE LINEAR REGRE
1
18
64
1
Actual
Pushups
Rank
1
2
20
58
2
2
0
0
3
21
54
3
3
0
0
4
22
52
4
4
0
0
6
26
51
6
5
1
1
5
24
50
5
6
-1
1
7
29
48
7
7
0
0
8
32
44
8
8
0
0
10
44
40
10
9
1
1
9
38
38
9
10
-1
1
Soldier #
Age
Actual
Pushups
Age Rank
Rank
Difference d
d2
0
0
2
SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
ANOVA
Regression
Residual
Total
4
Sum of d =>
n=
10
AGE V. PUSHUPS
Intercept
Age
70
60
50
40
30
y = -0.8656x + 73.617
R² = 0.8468
20
10
0
0
5
10
15
20
25
30
35
40
45
50
ST (SIMPLE LINEAR REGRESSION)
egression Statistics
0.920192425
0.8467541
0.827598362
3.289540413
10
df
1
8
9
SS
478.331391
86.56860902
564.9
MS
F
Significance F
478.331391 44.20368042
0.000161044
10.82107613
Coefficients
Standard Error
t Stat
P-value
73.6174812
3.715874091 19.81161886 4.38841E-08
-0.865601504
0.130193346 -6.648584844 0.000161044
Lower 95%
Upper 95%
65.04866018 82.18630222
-1.165827897 -0.565375111
SPEARMAN TEST (NONPARAMETRIC CORRELATION)
numerator =
denominator =
rs =
Lower 95.0% Upper 95.0%
65.04866018 82.18630222
-1.165827897 -0.565375111
24
990
0.975758
TABLE OF CONTENTS
THE STRUCTURE OF THIS BOOK ……………………………………………………………………………………………………………………………… 4
FOR THE PROFESSOR …………………………………………………………………………………………………………………………………………………… 5
FOR THE READER …………………………………………………………………………………………………………………………………………………………. 7
CHAPTER 1 OVERVIEW AND BASICS CASE STUDY …………………………………………………………………………………………….21
CHAPTER 1R OVERVIEW AND BASICS ……………………………………………………………………………………………………………………23
CHAPTER 2 PRESENTING STATISTICAL DATA CASE STUDY …………………………………………………………………………..46
CHAPTER 2R PRESENTATING STATISTICAL DATA …………………………………………………………………………………………….48
CHAPTER 3 PROBABILITY IN BUSINESS CASE STUDY ……………………………………………………………………………………….61
CHAPTER 3R PROBABILITY IN BUSINESS………………………………………………………………………………………………………………65
CHAPTER 4 NORMAL DISTRIBUTIONS CASE STUDY …………………………………………………………………………………………82
CHAPTER 4R NORMAL DISTRIBUTIONS ………………………………………………………………………………………………………………..88
CHAPTER 5 USE OF SAMPLING IN BUSINESS CASE STUDY ………………………………………………………………………….. 108
CHAPTER 5R USE OF SAMPLING IN BUSINESS …………………………………………………………………………………………………. 110
CHAPTER 6 BUSINESS APPLICATIONS OF SAMPLING CASE STUDY…………………………………………………………… 114
CHAPTER 6R BUSINESS APPLICATIONS OF SAMPLING …………………………………………………………………………………. 116
CHAPTER 7 HYPOTHESIS TESTING CASE STUDY ……………………………………………………………………………………………. 132
CHAPTER 7R HYPOTHESIS TESTING …………………………………………………………………………………………………………………… 135
CHAPTER 8 SOME NONPARAMETRIC STATISTICS ………………………………………………………………………………………….. 150
CHAPTER 8 SOME NONPARAMETRIC STATISTICS CASE STUDY AND GRADING RUBRIC …………………… 164
CHAPTER 9 BINOMIAL DISTRIBUTIONS IN BUSINESS CASE STUDY AND GRADING RUBRIC …………… 169
CHAPTER 9R BINOMIAL DISTRIBUTIONS IN BUSINESS ………………………………………………………………………………… 171
CHAPTER 10 LINEAR REGRESSION AND ITS USE IN BUSINESS CASE STUDY …………………………………………. 188
CHAPTER 10 STEPWISE REGRESSION CASE AND GRADING RUBRIC ……………………………………………………….. 191
CHAPTER 10R LINEAR REGRESSION …………………………………………………………………………………………………………………… 192
CHAPTER 11 ANOVA COMBINED CASE STUDY AND GRADING RUBRIC …………………………………………………. 225
CHAPTER 11R ANALYSIS OF VARIANCE (ANOVA) …………………………………………………………………………………………… 227
CHAPTER 12 INTRODUCTION TO BUSINESS FORECASTING ………………………………………………………………………. 239
CHAPTER 12 BUSINESS FORECASTING AND GRADING RUBRIC ………………………………………………………………… 260
CHAPTER 13 CONDUCTING A STATISTICAL STUDY ………………………………………………………………………………………. 262
BIBLIOGRAPHY …………………………………………………………………………………………………………………………………………………………. 265
APPENDICES ………………………………………………………………………………………………………………………………………………………………. 266
3
APPENDIX A Z TABLE ……………………………………………………………………………………………………………………………………………. 268
APPENDIX B T TABLE …………………………………………………………………………………………………………………………………………….. 269
APPENDIX C MANNWHITNEY TEST TABLES …………………………………………………………………………………………………. 270
APPENDIX D CHI-SQUARE TABLE……………………………………………………………………………………………………………………….. 272
APPENDIX E BINOMIAL DISTRIBUTION TABLE ……………………………………………………………………………………………… 273
APPENDIX F COMBINATORICS TABLE ………………………………………………………………………………………………………………. 274
APPENDIX G STATISTICAL TESTS FLOW CHART …………………………………………………………………………………………….. 275
APPENDIX H F-TEST TABLE (5% SIGNIFICANCE LEVEL) ……………………………………………………………………………… 277
APPENDIX H F-TEST TABLE (1% SIGNIFICANCE LEVEL) ……………………………………………………………………………… 278
4
THE STRUCTURE OF THIS BOOK
This book and course are designed to offer the student several benefits compared to other ways
it could be presented.
First of all, the book is free of charge. It is downloadable and the material is yours to keep. It may
be changed by the authors from semester to semester or as technology changes, but those changes
will occur between semesters for future students. Please provide feedback to the authors if you have
an improvement to recommend for future students.
Second, each early chapter is presented in two parts and later chapters are in a single part. For most
chapters, the first part is a real world case study, which assumes that you are familiar with the
course material already. For example, there is a Chapter 2, Presenting Statistical Data which begins
with a case study. Your grade for each case study will be determined on your presentation and
solution. For each case study, you may work on it alone, or in collaboration with others — as is most
comfortable with you. This mimics the real world where analytics professionals are given problems
to solve at work and often collaborate with others, or consult additional academic material, to reach
the best solution. That being said, each student must turn in work that is original to them and not
copied from others. The second part of most chapters is a review of introductory material
corresponding to the case study. For example, Chapter 2s case study is followed by a Chapter 2R
(R meaning review). You are encouraged to review the material in the R chapter sections as you
require. Your instructor, or course, is free to clarify any of these requirements for his or her class.
Some chapters are new material and therefore do not have an R designation.
Finally, there is a Chapter 0 which covers the basic mathematics skills needed for the course. You
will probably already be able to correctly solve all the problems in this (non-graded) quiz, but if you
need further assistance please contact your instructor.
4
FOR THE PROFESSOR
Most statistics classes we have taught were attended by a wide diversity of students, including
diversity in their abilities and interests in mathematics. Many students genuinely enjoyed
mathematics, and many took statistics only because it was a curriculum requirement. Of course,
there were many students in between.
In our experience, statistics books selected for either the first course or (usually) the only course in
statistics that students will take in their university curricula have grown and detail over the years.
Today, introductory books are full-sized and can be well over 850 pages. We place ourselves into the
position of the general student, faced with statistics for the first and only time, and buying 850 pages
of formulas, graphs, tables, new symbols, and new vocabulary. This can be quite daunting. It almost
initiates the question What will we ever use this for? Also, many those pages will not be in the
curriculum and are therefore unneeded for the course.
This book is written for the undergraduate or graduate student taking the second course of business
statistics, or for the otherwise interested reader. This book has significantly fewer pages than most
other statistics books. Most students will need just one book in their degree programs and believe
that the topics here represent the most important ones for their management or staff officer portfolio
of skills. There are also a few more topics in this book that will be covered in an undergraduate
course. We wanted to provide some optional extra topics so that those readers can have some
references at work for some situations that probably will eventually arise in their management and
staff officer careers. These should be self-explanatory. The intention here is to give a few optional
techniques to help address more advanced situations when they arise, and not to attempt to start to
provide a desktop encyclopedia which is suitable for several statistics courses.
We have written the material in as plain English as we are able to do. We use the Socratic principle
as often as possible and hope that many, if not most, of the concepts here can be seen intuitively if
presented in that light rather than derived through formula transformations. Modern spreadsheets
allow the use of brute force solution techniques, which may seem intuitively clear, rather than
traditional short-cut formulas which may have been welcome before computer technology but are
not intuitively clear. We also have used as many everyday examples as possible. Our experience
from teaching using this approach is that students completing this material should be just as
competent, if not more competent, in applying z-tests, t-tests, regression, ANOVA, presentation
graphics, and other statistical methods to management problems as with approaches in the behemoth
books.
5
One philosophy of mathematics states that mathematics is the study of patterns, or structuralism
(Stewart Shapiro). We use this philosophy often, focusing on the underlying patterns. It seems that
if the patterns are taught, then the formulas will often become intuitive. There are no proofs given,
but there are many demonstrations or discussions that are designed to be convincing to the general
student, although not necessarily found to be totally robust by the pure mathematician. For example,
a lot of time is spent developing the standard deviation pattern, rather than presenting it as heres
the formula, because the standard deviation is so important for improved investment analysis and
understanding of margins of error. On the other hand, we do not rigorously develop the Central
Limit Theorem to prefer to take the time to show students how to use the t-test to solve business
problems.
We hope that there is little or nothing here that good students will memorize just to pass a midterm,
and then let dissolve from short term memory thereafter. If you see some things missing here that
might be in other textbooks, perhaps those are things that our experience indicates fall into the
quickly dissolve after the midterm category and are not really necessary to become a better
manager. What are presented then, we hope, will be methodologies stored in long-term memory and
used for becoming a better manager.
The purpose of Chapter 0 is to convey to students what mathematics skills are required to start this
course. It is designed to convey an easily measurable standard prerequisite. It is certainly optional,
but it was very helpful to us as we frequently used it for every statistics class. Students not having
these mathematics skills could understand this quickly and drop the course quickly without financial
or academic penalty.
One final note about use of computers in Statistics classes. Most of the statistics-related problems
encountered at work are solved using the computer, often using Excel. A statistics text should
acquaint students with how to use software to support solution of statistics problems. This test will
show a lot of Excel-based solutions.
6
FOR THE READER
The Purpose of this Book
The main purpose of this book is to make you a better manager or staff officer. [1] If you master the
concepts in this book, you will be a far better manager than most of your peers. You will also be
much better able to distinguish facts from opinions discussed in staff meetings, better able to manage
your retirement account, better able to justify causes and effects, and better able to evaluate the
performance of subordinates and vendors. To support this, the writing style will be conversational
as much as possible. Often, we will use the first person and use plain English throughout the book
whenever possible. This is our way of trying to make statistics easier to understand. To be sure, you
will find that much of statistics is intuitive. Honest.
One of the standards in this book is that management decisions should be made based on the data
(whenever possible). Continuing, when all affected people see the data and the analysis, then
everyone will draw the same conclusion. [2] For example, suppose that ten expert software
developers receive a notebook containing the requirements for new software. These experts are
asked to review the book and come back next week to a meeting with an estimate of how much the
software will cost to develop, and how long it will take to develop. It most cases, this staffing
exercise will produce ten different results. However, in some organizations all experts would come
back with the same cost and schedule estimates. This is what we mean by when we all see the data
and the analysis, we all draw the same conclusion.
This is an important management quality. When an employee files a grievance for unfair
performance evaluation, it is important that this standard was met. After examining the performance
data, the manager, the union, upper management, and even an arbitrator should all arrive at the same
performance evaluation. If this situation is not the case, then the manager has not performed his or
her job well (in our opinion) and may lose the grievance. Management should never lose a grievance
or arbitration, and employees should arrive at the same conclusion as management.
This book has significantly fewer pages than most other introductory statistics books. Most students
will need just one book in their degree programs and believe that the topics here represent the most
important ones for your management or staff officer portfolio of skills. There are also a few more
topics in this book that will be covered in an undergraduate course. We wanted to provide you with
some optional extra topics so that you can have some references at work for some situations that
probably will eventually arise in your management and staff officer career. These should be selfexplanatory. The intention here is to give you opti…
Purchase answer to see full
attachment
