Business Statistics using Excel

505 Pages·2013·16.7 MB·English

by  Glyn Davis

business statistics using Excel®
Davis59517.indb i 2/7/2013 4:37:08 PM
This page intentionally left blank
business statistics
using Excel®
Second edition
Glyn Davis & Branko Pecar
1
Davis59517.indb iii 2/7/2013 4:37:10 PM
1
Great Clarendon Street, Oxford, OX2 6DP,
United Kingdom
Oxford University Press is a department of the University of Oxford.
It furthers the University’s objective of excellence in research, scholarship,
and education by publishing worldwide. Oxford is a registered trade mark of
Oxford University Press in the UK and in certain other countries
© Glyn Davis and Branko Pecar 2013
Th e moral rights of the authors have been asserted
First Edition copyright 2010
Impression: 1
All rights reserved. No part of this publication may be reproduced, stored in
a retrieval system, or transmitted, in any form or by any means, without the
prior permission in writing of Oxford University Press, or as expressly permitted
by law, by licence or under terms agreed with the appropriate reprographics
rights organization. Enquiries concerning reproduction outside the scope of the
above should be sent to the Rights Department, Oxford University Press, at the
address above
You must not circulate this work in any other form
and you must impose this same condition on any acquirer
British Library Cataloguing in Publication Data
Data available
ISBN 978–0–19–965951–7
Printed in Italy by
L.E.G.O. S.p.A.—Lavis TN
Links to third party websites are provided by Oxford in good faith and
for information only. Oxford disclaims any responsibility for the materials
contained in any third party website referenced in this work.
Davis59517.indb iv 2/7/2013 4:37:10 PM
Preface
Aims of the book
It has long been recognized that the development of modular undergraduate programmes
coupled with a dramatic increase in student numbers has led to a reconsideration of
teaching practices. Th is statement is particularly true in the teaching of statistics and, in
response, a more supportive learning process has been developed. A classic approach to
teaching statistics, unless one is teaching a class of future professional statisticians, can be
diffi cult and is often met with very little enthusiasm by the majority of students. A more
supportive learning process based on method application rather than method derivation
is clearly needed. Th e authors thought that by relying on some commonly available tools,
Microsoft Excel 2010 in particular, such an approach would be possible. To this eff ect, a
new programme relying on the integration of workbook based open learning materials
with information technology tools has been adopted. Th e current learning and assess-
ment structure may be defi ned as follows:
(a) To help students ‘bridge the gap’ between school and university
(b) To enable a student to be confi dent in handling numerical data
(c) To enable students to appreciate the role of statistics as a business decision-making
tool
(d) To provide a student with the knowledge to use Excel 2010 to solve a range of
statistical problems.
Th is book is aimed at students who require a general introduction to business statistics
that would normally form a foundation-level business school module. Th e learning mate-
rial in this book requires minimal input from a lecturer and can be used as a self-instruc-
tion guide. Furthermore, three online workbooks are available; two to help students with
Excel and practise numerical skills, and an advanced workbook to help undertake facto-
rial experiment analysis using Excel 2010.
Th e growing importance of spreadsheets in business is emphasized throughout the text
by the use of the Excel spreadsheet. Th e use of software in statistics modules is more or
less mandatory at both diploma and degree level, and the emphasis within the text is on
the use of Excel 2010 to undertake the required calculations.
How to use the book eff ectively
Th e sequence of chapters has been arranged so that there is a progressive accumulation
of knowledge. Each chapter guides students step by step through the theoretical and
spreadsheet skills required. Chapters also contain exercises that give students the chance
to check their progress.
Davis59517.indb v 2/7/2013 4:37:10 PM
vi
Preface
Hints on using the book
(a) Be patient and work slowly and methodically, especially in the early stages when
progress may be slow.
(b) Do not omit or ‘jump around’ between chapters; each chapter builds upon
knowledge and skills gained previously. You may also fi nd that the Excel
applications described earlier in the book are required to develop applications in
later chapters.
(c) Try not to compare your progress with others too much. Fastest is not always best!
(d) Don’t try to achieve too much in one session. Time for rest and refl ection is
important.
(e) Mistakes are part of learning. Do not worry about them. Th e more you repeat
something, the fewer mistakes you will make.
(f) Make time to complete the exercises, especially if you are learning on your own.
Th ey are your best guide to your progress.
(g) Th e visual walkthroughs have been developed to solve a particular statistical
problem using Excel. If you are not sure about the Excel solution then use the visual
walkthrough (fl ash movies) as a reminder.
Davis59517.indb vi 2/7/2013 4:37:10 PM
Brief contents
How to use this book xiv
How to use the Online Resource Centre xvi
1 Visualizing and presenting data 1
2 Data descriptors 58
3 Introduction to probability 107
4 Probability distributions 135
5 Sampling distributions and estimating 185
6 Introduction to parametric hypothesis testing 243
7 Chi-square and non-parametric
hypothesis testing 296
8 Linear correlation and regression analysis 343
9 Time series data and analysis 406
Glossary 468
Index 477
Davis59517.indb vii 2/7/2013 4:37:11 PM
Detailed contents
How to use this book xiv
How to use the Online Resource Centre xvi
1 Visualizing and presenting data 1
Overview 1
Learning objectives 2
1.1 Th e diff erent types of data variable 2
1.2 Tables 3
1.2.1 What a table looks like 4
1.2.2 Creating a frequency distribution 6
1.2.3 Types of data 10
1.2.4 Creating a table using Excel PivotTable 11
1.2.5 Principles of table construction 21
1.3 Graphical representation of data 21
1.3.1 Bar charts 22
1.3.2 Pie charts 27
1.3.3 Histograms 31
1.3.4 Histograms with unequal class intervals 40
1.3.5 Frequency polygon 42
1.3.6 Scatter and time series plots 47
1.3.7 Superimposing two sets of data onto one graph 51
Techniques in practice 54
Summary 56
Key terms 57
Further reading 57
2 Data descriptors 58
Overview 58
Learning objectives 59
2.1 Measures of central tendency 59
2.1.1 Mean, median, and mode 59
2.1.2 Percentiles and quartiles 63
2.1.3 Averages from frequency distributions 67
2.1.4 Weighted averages 77
2.2 Measures of dispersion 80
2.2.1 Th e range 82
2.2.2 Th e interquartile range and semi-interquartile range (SIQR) 82
2.2.3 Th e standard deviation and variance 83
2.2.4 Th e coeffi cient of variation 88
Davis59517.indb viii 2/7/2013 4:37:11 PM
ix
Detailed contents
2.2.5 Measures of skewness and kurtosis 89
2.3 Exploratory data analysis 94
2.3.1 Five-number summary 94
2.3.2 Box plots 96
2.3.3 Using the Excel ToolPak add-in 100
Techniques in practice 102
Summary 104
Key terms 105
Further reading 105
3 Introduction to probability 107
Overview 107
Learning objectives 107
3.1 Basic ideas 107
3.2 Relative frequency 109
3.3 Sample space 112
3.4 Th e probability laws 114
3.5 Th e general addition law 115
3.6 Conditional probability 117
3.7 Statistical independence 120
3.8 Probability tree diagrams 123
3.9 Introduction to probability distributions 124
3.10 Expectation and variance for a probability distribution 127
Techniques in practice 131
Summary 133
Key terms 133
Further reading 133
4 Probability distributions 135
Overview 135
Learning objectives 135
4.1 Continuous probability distributions 136
4.1.1 Introduction 136
4.1.2 Th e normal distribution 136
4.1.3 Th e standard normal distribution (Z distribution) 140
4.1.4 Checking for normality 149
4.1.5 Other continuous probability distributions 153
4.1.6 Probability density function and cumulative
distribution function 154
4.2 Discrete probability distributions 155
4.2.1 Introduction 155
4.2.2 Binomial probability distribution 155
Davis59517.indb ix 2/7/2013 4:37:11 PM