using excel® - gbv

business statistics using Excel® Glyn Davis & Branko Pecar

OXFORD U N I V E R S I T Y PRESS

Detailed contents

Introduction to Microsoft Excel 2003

Overview

Learning Objectives

1.1 Introduction to Microsoft Excel 2003 1.1.1 Components of an Excel spreadsheet 1.1.2 Loading Excel 1.1.3 Taskpanes 1.1.4 Help 1.1.5 Saving a workbook 1.1.6 Opening an existing workbook 1.1.7 Closing a workbook 1.1.8 Switch to a new worksheet 1.1.9 Creating a new worksheet 1.1.10 Opening a new workbook l.l.ii Printing and print preview 1.1.12 What to do if you encounter a damaged Excel ffle

1.2 Entering Data and Formatting 1.2.1 Entering data in a cell 1.2.2 Modifying data in a cell 1.2.3 Entering data into a range 1.2.4 Cancelling a cell entry 1.2.5 Undoing a cell entry 1.2.6 Entering numbers 1.2.7 Entering dates or times 1.2.8 Entering text 1.2.9 Formatting a worksheet 1.2.10 Column widths and row heights 1.2.11 Naming a worksheet 1.2.12 Inserting and deleting rows and columns 1.2.13 The clear command 1.2.14 Spell checking 1.2.15 AutoFormat

1.3 Performing Calculations 1.3.1 Entering formulae 1.3.2 Understanding operators 1.3.3 Selecting cells and moving around worksheet 1.3.4 Reference operators 1.3.5 Editing a formula l .3.6 Creating and applying names 1.3.7 Copying formulae to adjacent cells 1.3.8 Using absolute and relative references

1

1

2 3 4 5 5 5 6 7 7 7 7 7 9

9 10 10 10 11 11 11 12 12 12 13 14 14 14 15 15

16 16 16 17 17 18 18 19 19

Detailed contents

1.3.9 IF function 21 1.3.10 Adding a column of numbers using AutoSum 21 1.3.11 What if analysis 22 1.3.12 Insert function 23 1.3.13 Copying formulae and values 23 1.3.14 Excel arrays 23 1.3.15 Install Excel ToolPakadd-ins 23

1.4 Presenting Results 24 1.4.1 Inserting Excel features into Microsoft Word 24 1.4.2 Inserting a chart from an Excel worksheet into Microsoft Word 25

Techniques in Practice 28 Summary 29 Key Terms 29 Further Reading 30

2 Numerical Skills Revision 31 Overview 31 Learning Obj ectives 31

2.1 Excel and Solving Algebraic Problems 32 2.1.1 Excel scientific notation 32 2.1.2 Squares 32 2.1.3 Square roots 33 2.1.4 Indices or the power of a number 34

2.2 Excel Mathematical and Statistical Functions 35 2.2.1 Mathematical functions 35 2.2.2 Excel arrays 39 2.2.3 Excel statistical functions 40 2.2.4 Excel statistical macro functions 44

2.3 Excel Financial Functions 44 2.3.1 Simple interest 45 2.3.2 Compound interest 46 2.3.3 Increasing the sum invested 49 2.3.4 Sinking funds or future value of an ordinary annuity 51 2.3.5 The concept of present value 53 2.3.6 Trust funds and loan repayments or present value of an ordinary annuity 55 2.3.7 The present value of a stream of earnings 56 2.3.8 Compound annual growth rate 58

2.4 Coordinate Geometry 61 2.4.1 The coordinates of a point 61 2.4.2 Excel and plotting straight line graphs from an equation 62 2.4.3 Linear equation parameters m and с 67 2.4.4 Use Excel to calculate the equation of the line given data points 70

Techniques in Practice 71 Summary 72 Key Terms 72 Further Reading 73 Formula Summary 73

3 Visualizing and Presenting Data Overview

Learning Objectives

3.1 The Different Types of Data Variable

3.2 Tables 3.2.1 What a table looks like 3.2.2 Creating a frequency distribution 3.2.3 Types of data 3.2.4 Creating a table using Excel Pivot Table 3.2.5 Principles of table construction

3.3 Graphical Representation of Data 3.3.1 Bar charts 3.3.2 Pie charts 3.3.3 Histograms 3.3.4 Histograms with unequal class intervals 3.3.5 Frequency polygon 3.3.6 Scatter and time series plots 3.3.7 Superimposing two sets of data onto one graph

Techniques in Practice

Summary

Key Terms

Further Reading

4 Data Descriptors

Overview

Learning Objectives

4.1 Measures of Central Tendency 4.1.1 Mean, median, and mode 4.1.2 The percentile and quartile 4.1.3 Averages from frequency distributions 4.1.4 Weighted averages

4.2 Measures of Dispersion 4.2.1 The range 4.2.2 The interquartile range and semi interquartile range 4.2.3 The standard deviation and variance 4.2.4 The coefficient of variation 4.2.5 Measures of skewness and kurtosis

4.3 Exploratory Data Analysis 4.3.1 Five-number summary 4.3.2 Box plots 4.3.3 Using the Excel ToolPak add-in


Summary

Key Terms

Further Reading

Formula Summary

Probability Distributions 177

Overview 177

Learning Objectives 177

5.1 Introduction to Probability 178 5.1.1 Basic ideas 178 5.1.2 Relative frequency 179 5.1.3 The probability laws 181 5.1.4 Probability tree diagram 183 5.1.5 Introduction to probability distributions 184 5.1.6 Expectation and variance for a probability distribution 186

5.2 Continuous Probability Distributions 191 5.2.1 Introduction 191 5.2.2 The normal distribution 191 5.2.3 The standard normal distribution (Z distribution) 194 5.2.4 Checking for normality 203 5.2.5 Other continuous probability distributions 207

5.3 Discrete Probability Distributions 208 5.3.1 Introduction 208 5.3.2 Binomial probability distribution 208 5.3.3 Poisson probability distribution 217 5.3.4 Poisson approximation to the binomial distribution 225 5.3.5 Normal approximation to the binomial distribution 227 5.3.6 Normal approximation to the Poisson distribution 231 5.3.7 Other discrete probability distributions 233

Techniques in Practice 234

Summary 235

Key Terms 235

Further Reading 236

Formula Summary 236

Sampling Distributions and Estimating 238

Overview 238

Learning Objectives - 238

6.1 Introduction to the Concept of a Sample 239 6.1.1 Why sample? 239 6.1.2 Sampling terminology 240 6.1.3 Types of samples 241 6.1.4 Types of error 246

6.2 Sampling from a Population 247 6.2.1 Introduction 247 6.2.2 Population vs. sample 247 6.2.3 Sampling distributions 247 6.2.4 Sampling distribution of the mean 248 6.2.5 Sampling from a normal population 252 6.2.6 Sampling from a non-normal population 259

6.2.7 Sampling distribution ofthe proportion 264 6.2.8 Using Excel to generate a sample from a sampling distribution 267

6.3 Population Point Estimates 271 6.3.1 introduction 271 6.3.2 Types of estimate 272 6.3.3 Criteria of a good estimator 272 6.3.4 Point estimate ofthe population mean and variance 272 6.3.5 Point estimate for the population proportion and variance 275 6.3.6 Pooled estimates 277

6.4 Population Confidence Intervals 278 6.4.1 Introduction 278 6.4.2 Confidence interval estimate ofthe population mean, |i(o" known) 278 6.4.3 Confidence interval estimate of the population mean,

(l (a unknown, n < 30) 280 6.4.4 Confidence interval estimate ofthe population mean,

\i (a unknown, n > 30) 284 6.4.5 Confidence interval estimate of a population proportion 286

6.5 Calculating Sample Sizes 288


Summary 292

Key Terms 293

Further Reading 293

Formula Summary 294

Introduction to Parametric Hypothesis Testing 297 Overview 297


7.1 Hypothesis Testing Rationale 298 7.1.1 Hypothesis statements H0 and Hj 298 7.1.2 Parametric vs. non-parametric tests of difference 300 7.1.3 One and two sample tests 300 7.1.4 Choosing an appropriate test 300 7.1.5 Significance level 301 7.1.6 Sampling distributions 302 7.1.7 One and two tail tests 303 7.1.8 Checkt test model assumptions 305 7.1.9 Types of error 305 7.1.10 P-values 306 7.1.11 Critical test statistic 306

7.2 One Sample Z Test for the Population Mean 307

7.3 One Sample T Test for the Population Mean 312

7.4 Two Sample Z Test for the Population Mean 317

7.5 Two Sample Z Test for the Population Proportion 322

7.6 Two Sample T Test for Population Mean (Independent Samples,

Equal Variances) 326

7.7 Two Sample Tests for Population Mean (Independent Samples,

Unequal Variances) 331 7.7.1 Two sample t test for independent samples

(unequal variances) 331 7.7.2 Equivalent non-parametric test: Mann-Whitney U test 335

7.8 Two Sample Tests for Population Mean

(Dependent or Paired Samples) 336 7.8.1 Two sample t test for dependent samples 336

7.8.2 Equivalent non-parametric test: Wilcoxon matched pairs test 340

7.9 F Test for Two Population Variances (Variance Ratio Test) 341


Summary 348

Key Terms 349

Further Reading 349

Formula Summary 350

Chi Square and Non-Parametric Hypothesis Testing 352

Overview

Learning Objectives

8.1 Chi Square Tests 8.1.1 Tests of association 8.1.2 Test differences in proportions for two samples

8.2 Non-Parametric (or Distribution Free) Tests 8.2.1 The sign test 8.2.2 Wilcoxon signed rank sum (or matched pairs) test 8.2.3 Mann-Whitney U test for two independent samples


Summary

Key Terms

Further Reading

Formula Summary

352

352

353 354 359

368 369 374 381

388

389

390

391

391

Factorial Experiments 393 Overview 393


9.1 Introduction 393

9.2 Single-Factor Experiments 394 9.2.1 Single-factor ANOVA (or one-way ANOVA) 395 9.2.2 Kruskal-Wallis test 411

9.3 Two-Factor Experiments with No Replication 418

9.3.1 Two-way ANOVA with no repeated measures (or one-factor with repeated measures) 419

9.3.2 Friedman's test 429

9.4 Two-Factor Experiments with Equal Replication 435 9.4.1 Two-way ANOVA with equal replication 435 9.4.2 Friedman's test with equal replicates ineachcell 443


Summary 447

Key Terms 448

Further Reading 448

Formula Summary 449

Linear Correlation and Regression Analysis 454 Overview 454


10.1 Linear Correlation Analysis 455 10.1.1 Scatter plot 455 10.1.2 Covariance 457 10.1.3 Pearson's correlation coefficient, r 459 10.1.4 Testing the significance of Pearson's correlation coefficient, r 463 10.1.5 Spearman's rank correlation coefficient, rs 466 10.1.6 Testing the significance of Spearman's rank correlation

coefficient, r 468

10.2 Linear Regression Analysis 472 10.2.1 Construct scatter plot to identify model 473 10.2.2 Fit line to sample data 474 10.2.3 Sum of squares defined 478 10.2.4 Regression assumptions 478 10.2.5 Test model reliability 481 10.2.6 Test whether the predictor variable is a significant

contributor, t test 486 10.2.7 Test whether the predictor variable is a significant

contributor, F test 489 10.2.8 Confidence interval estimate for slope ßj 491 10.2.9 Prediction interval for an estimate of Y 493 10.2.10 Excel ToolPak Regression solution 494

10.3 Some Advanced Topics in Regression Analysis 499 10.3.1 Introduction to non-linear regression 499 10.3.2 Introduction to multiple regression analysis 505


Summary 512

KeyTerms 513

Further Reading 514

Formula Summary 514

Detailed contents

11 Time Series Data and Analysis 518

Overview 518

Learning Obj ectives 518

11.1 Introduction to Time Series Data 519 11.1.1 Stationary and non-stationary time series 519 11.1.2 Seasonal time series 521 11.1.3 Univariate and multivariate methods 521 11.1.4 Scaling the time series 522

11.2 Index Numbers 525 11.2.1 Simple indices 526 11.2.2 Aggregate indices 528 11.2.3 Deflating values 529

11.3 Trend Extrapolation 531 11.3.1 A trend component 532 11.3.2 Fitting a trend to a time series 533 11.3.3 Types of trends 534 11.3.4 Using a trend chart function to forecast time series 535 11.3.5 Trend parameters and calculations 537

11.4 Moving Averages and Time Series Smoothing 540 11.4.1 Forecasting with moving averages 541 11.4.2 Exponential smoothing concept 545 11.4.3 Forecasting with exponential smoothing 547

11.5 Seasonal Models 551

11.6 Forecasting Errors 556 11.6.1 Error measurement 556 11.6.2 Types of errors 558 11.6.3 Interpreting errors 560 11.6.4 Error inspection 561

11.7 Confidence Intervals 563 11.7.1 Population and sample standard errors 563 11.7.2 Standard errors in time series 565


Summary 571

Key Terms 571

Further Reading 572

Formula Summary 573

Student Exercises Answers 577

Glossary 595

Index 601

using excel® - gbv

Documents