Business Statistics using Excel

Cover......Page 1
Contents......Page 9
How to use this book......Page 15
How to use the Online Resource Centre......Page 17
Overview......Page 20
1.1 The different types of data variable......Page 21
1.2 Tables......Page 22
1.2.1 What a table looks like......Page 23
1.2.2 Creating a frequency distribution......Page 25
1.2.3 Types of data......Page 29
1.2.4 Creating a table using Excel PivotTable......Page 30
1.3 Graphical representation of data......Page 40
1.3.1 Bar charts......Page 41
1.3.2 Pie charts......Page 46
1.3.3 Histograms......Page 50
1.3.4 Histograms with unequal class intervals......Page 59
1.3.5 Frequency polygon......Page 61
1.3.6 Scatter and time series plots......Page 66
1.3.7 Superimposing two sets of data onto one graph......Page 70
Techniques in practice......Page 73
Summary......Page 75
Further reading......Page 76
Overview......Page 77
2.1.1 Mean, median, and mode......Page 78
2.1.2 Percentiles and quartiles......Page 82
2.1.3 Averages from frequency distributions......Page 86
2.1.4 Weighted averages......Page 96
2.2 Measures of dispersion......Page 99
2.2.2 The interquartile range and semi-interquartile range (SIQR)......Page 101
2.2.3 The standard deviation and variance......Page 102
2.2.4 The coefficient of variation......Page 107
2.2.5 Measures of skewness and kurtosis......Page 108
2.3.1 Five-number summary......Page 113
2.3.2 Box plots......Page 115
2.3.3 Using the Excel ToolPak add-in......Page 119
Techniques in practice......Page 121
Summary......Page 123
Further reading......Page 124
3.1 Basic ideas......Page 126
3.2 Relative frequency......Page 128
3.3 Sample space......Page 131
3.4 The probability laws......Page 133
3.5 The general addition law......Page 134
3.6 Conditional probability......Page 136
3.7 Statistical independence......Page 139
3.8 Probability tree diagrams......Page 142
3.9 Introduction to probability distributions......Page 143
3.10 Expectation and variance for a probability distribution......Page 146
Techniques in practice......Page 150
Further reading......Page 152
Learning objectives......Page 154
4.1.2 The normal distribution......Page 155
4.1.3 The standard normal distribution (Z distribution)......Page 159
4.1.4 Checking for normality......Page 168
4.1.5 Other continuous probability distributions......Page 172
4.1.6 Probability density function and cumulative distribution function......Page 173
4.2.2 Binomial probability distribution......Page 174
4.2.3 Poisson probability distribution......Page 184
4.2.4 Poisson approximation to the binomial distribution......Page 192
4.2.5 Normal approximation to the binomial distribution......Page 194
4.2.6 Normal approximation to the Poisson distribution......Page 199
Techniques in practice......Page 201
Key terms......Page 202
Further reading......Page 203
Learning objectives......Page 204
5.1.1 Why sample?......Page 205
5.1.2 Sampling terminology......Page 206
5.1.3 Types of samples......Page 207
5.1.4 Types of error......Page 211
5.2.1 Introduction......Page 212
5.2.4 Sampling distribution of the mean......Page 213
5.2.5 Sampling from a normal population......Page 217
5.2.6 Sampling from a non-normal population......Page 223
5.2.7 Sampling distribution of the proportion......Page 229
5.2.8 Using Excel to generate a sample from a sampling probability distribution......Page 231
5.3.1 Introduction......Page 236
5.3.4 Point estimate of the population mean and variance......Page 237
5.3.5 Point estimate for the population proportion and variance......Page 241
5.3.6 Pooled estimates......Page 243
5.4.1 Introduction......Page 244
5.4.2 Confidence interval estimate of the population mean, μ (σ known)......Page 245
5.4.3 Confidence interval estimate of the population mean, μ(σ unknown, n < 30)......Page 247
5.4.4 Confidence interval estimate of the population mean, μ (σ unknown, n ≥ 30)......Page 251
5.4.5 Confidence interval estimate of a population proportion......Page 254
5.5 Calculating sample size......Page 256
Techniques in practice......Page 258
Key terms......Page 260
Further reading......Page 261
Learning objectives......Page 262
6.1.1 Hypothesis statements H[sub(0)] and H[sub(1)]......Page 263
6.1.3 One and two sample tests......Page 265
6.1.4 Choosing an appropriate statisitcal test......Page 266
6.1.6 Sampling distributions......Page 267
6.1.7 One and two tail tests......Page 268
6.1.8 Check t-test model assumptions......Page 269
6.1.10 P-values......Page 270
6.1.11 Critical test statistic......Page 271
6.2 One sample z-test for the population mean......Page 272
6.3 One sample t-test for the population mean......Page 276
6.4 Two sample z-test for the population mean......Page 280
6.5 Two sample z-test for the population proportion......Page 285
6.6 Two sample t-test for population mean (independent samples, equal variances)......Page 288
6.7.1 Two sample tests for independent samples (unequal variances)......Page 293
6.8.1 Two sample tests for dependent samples......Page 298
6.8.2 Equivalent non-parametric test: Wilcoxon matched pairs test......Page 302
6.9 F test for two population variances (variance ratio test)......Page 304
6.10 Calculating the size of the type II error and the statistical power......Page 309
Techniques in practice......Page 311
Key terms......Page 313
Further reading......Page 314
Learning objectives......Page 315
7.1 Chi-square tests......Page 316
7.1.1 Chi-square test of association......Page 317
7.1.2 Chi-square test for independent samples......Page 322
7.1.3 McNemar’s test for matched (or dependent) pairs......Page 326
7.1.4 Chi-square goodness-of-fit test......Page 331
7.2.1 Sign test......Page 337
7.2.2 Wilcoxon signed rank sum test for dependent samples (or matched pairs)......Page 343
7.2.3 Mann–Whitney U test for two independent samples......Page 350
Techniques in practice......Page 357
Summary......Page 359
Further reading......Page 360
Learning objectives......Page 362
8.1.1 Scatter plots......Page 363
8.1.2 Covariance......Page 366
8.1.3 Pearson’s correlation coefficient, r......Page 367
8.1.4 Testing the significance of linear correlation between the two variables......Page 372
8.1.5 Spearman’s rank correlation coefficient......Page 375
8.1.6 Testing the significance of Spearman’s rank correlation coefficient, r[sup(s)]......Page 377
8.2 Linear regression analysis......Page 381
8.2.2 Fit line to sample data......Page 383
8.2.3 Sum of squares defined......Page 388
8.2.4 Regression assumptions......Page 389
8.2.5 Test model reliability......Page 391
8.2.6 The use of t-test to test whether the predictor variable is a significant contributor......Page 393
8.2.7 The use of F test to test whether the predictor variable is a significant contributor......Page 397
8.2.8 Confidence interval estimate for slope β[sub(1)]......Page 401
8.2.9 Prediction interval for an estimate of Y......Page 402
8.2.10 Excel data analysis regression solution......Page 404
8.3.1 Introduction to non-linear regression......Page 409
8.3.2 Introduction to multiple regression analysis......Page 416
Techniques in practice......Page 420
Summary......Page 423
Further reading......Page 424
Learning objectives......Page 425
9.1.1 Stationary and non-stationary time series......Page 426
9.1.3 Univariate and multivariate methods......Page 428
9.1.4 Scaling the time series......Page 429
9.2 Index numbers......Page 430
9.2.1 Simple indices......Page 431
9.2.2 Aggregate indices......Page 434
9.2.3 Deflating values......Page 435
9.3 Trend extrapolation......Page 438
9.3.2 Fitting a trend to a time series......Page 439
9.3.3 Types of trends......Page 442
9.3.4 Using a trend chart function to forecast time series......Page 443
9.3.5 Trend parameters and calculations......Page 445
9.4 Moving averages and time series smoothing......Page 449
9.4.1 Forecasting with moving averages......Page 450
9.4.2 Exponential smoothing concept......Page 455
9.4.3 Forecasting with exponential smoothing......Page 457
9.5 Forecasting seasonal series with exponential smoothing......Page 464
9.6.1 Error measurement......Page 469
9.6.2 Types of errors......Page 472
9.6.3 Interpreting errors......Page 474
9.6.4 Error inspection......Page 475
9.7.1 Population and sample standard errors......Page 477
9.7.2 Standard errors in time series......Page 478
Techniques in practice......Page 482
Summary......Page 484
Further reading......Page 485
C......Page 487
D......Page 488
G......Page 489
M......Page 490
O......Page 491
Q......Page 492
S......Page 493
T......Page 494
W......Page 495
C......Page 496
D......Page 497
F......Page 498
K......Page 499
N......Page 500
P......Page 501
R......Page 502
S......Page 503
T......Page 504
Z......Page 505