Business Statistics Using Excel: A Complete Course in Data Analytics

Cover....1

Half Title....2

Title....4

Copyright....5

Dedication....6

Contents....8

List of Figures....17

List of Tables....36

Glossary....42

1 Introduction....48

1.1 Real-World Problems....48

1.2 Decision Support System....49

Mathematical Models....49

Exact Algorithm....50

Heuristics....50

1.3 Business Statistics....50

1.4 Types of Research....51

1.4.1 Exploratory Research....51

1.4.2 Conclusive Research....53

1.4.3 Modelling Research....55

1.5 Steps of Research....56

1.5.1 Problem Definition....56

1.5.2 Objectives of the Research....56

1.5.3 Research Design....57

1.5.4 Data Collection....58

1.5.5 Data Analysis....58

1.5.6 Interpretation of Results....59

1.5.7 Validation of Results....59

1.6 Statistical Techniques....59

Summary....62

Keywords....64

Review Questions....64

References....64

2 Introduction to Excel....65

2.1 Introduction....65

2.2 Excel....66

2.2.1 Excel Templates....66

2.2.2 Arithmetic Operators in Excel....67

2.2.3 Changing Width of Cells....67

2.2.4 Operations in Excel Using Simple Formulas....67

2.2.5 Formatting of Excel Templates....71

2.2.6 Adding Values in a Range of Cells by Including Cells in Formulas....71

2.2.7 Adding Values in a Range of Cells Using the @SUM Function....72

2.2.8 Copying Formulas....74

2.2.9 SUMIF Function....77

2.2.10 SUMIFS Command....78

2.2.11 Absolute References to Cells While Copying Formulas....80

2.2.12 COMBIN Command....82

2.2.13 FACT Command....83

2.2.14 Invoking the Data Analysis Button in Excel....84

2.2.15 MIN Function....84

2.2.16 MINA Function....85

2.2.17 MAX Function....86

2.2.18 MAXA Function....88

2.2.19 MAXIFS Function....88

2.2.20 MINIFS Function....91

2.2.21 ROUND Function....93

2.2.22 INT Function....95

2.2.23 ROUNDDOWN Function....96

2.2.24 ROUNDUP Function....97

2.2.25 SORT Function....97

2.2.26 RANK.EQ Function....99

2.2.27 RANK.AVG Function....101

2.2.28 Hide and Unhide Commands....103

2.2.29 Filter Function....105

2.2.30 PROB Function in Excel 2019....110

Summary....111

Keywords....112

Review Questions....112

References....115

3 Count, Frequency, and Histogram Functions....117

3.1 Introduction....117

3.2 Count Functions....117

3.2.1 COUNT Function....118

3.2.2 COUNTA Function....120

3.2.3 COUNTBLANK Function....123

3.2.4 COUNTIF Function....129

3.2.5 COUNTIFS Function....130

3.3 Frequency....136

3.4 Histograms....141

Summary....147

Keywords....147

Review Questions....148

References....150

4 Average Functions....151

4.1 Introduction....151

4.2 AVERAGE Function....151

4.3 AVEDEV Function....158

4.4 AVERAGEA Function....165

4.5 AVERAGEIF Function....171

4.6 AVERAGEIFS Function....175

4.6.1 Weighted Average....179

4.6.2 Arithmetic Mean of Grouped Data With Frequencies....183

Summary....187

Keywords....188

Review Questions....188

References....190

5 Median and Mode....191

5.1 Introduction....191

5.2 Median....191

5.2.1 Median of Ungrouped Data Using Median Function....192

5.2.2 Median of Grouped Data With Frequencies Using Excel Sheets....195

5.3 Mode....200

5.3.1 Mode of Ungrouped Data Using Mode Function....201

5.3.2 Mode of Grouped Data With Frequencies Using Excel Sheets....209

5.4 Percentile....213

5.4.1 PERCENTILE.EXC Function....214

5.4.2 PERCENTILE.INC Function....216

5.5 Quartile....218

5.5.1 QUARTILE.EXC Function....218

5.5.2 QUARTILE.INC Function....221

Summary....221

Keywords....223

Review Questions....223

References....225

6 Measures of Variation....226

6.1 Introduction....226

6.2 Range Using Excel Sheets....227

6.3 Quartile Deviation....228

6.3.1 Quartile Deviation of Ungrouped Data Using Quartile Function....231

6.3.2 Quartile Deviation of Grouped Data With Frequency Using Excel Sheets....232

6.4 Average Deviation Using Excel Sheets....237

6.5 Standard Deviation....241

6.5.1 Standard Deviation of Ungrouped Data Using STDEV Function....243

6.5.2 Standard Deviation of Ungrouped Data With Frequencies....245

6.5.3 Standard Deviation of Grouped Data With Frequencies....250

6.5.4 STDEVA/STDEVPA Functions....254

6.5.5 STDEVP or STDEV.P Function....256

6.5.6 STDEVPA Function....259

6.6 Variance of Ungrouped Data....262

6.6.1 VAR.S Function....263

6.6.2 VAR.P Function....263

6.6.3 VARA Function....265

6.6.4 VARPA Function....267

6.7 Coefficient of Variation Using Excel Sheets....267

Summary....270

Keywords....271

Review Questions....272

References....275

7 Measures of Skewness....276

7.1 Introduction....276

7.2 Pearson’s Coefficient of Skewness Using Excel Sheets....277

7.3 Bowley’s Coefficient of Skewness Using Excel Sheets....284

7.4 Kurtosis....288

7.4.1 Mesokurtic Kurtosis....292

7.4.2 Leptokurtic Kurtosis....296

7.4.3 Platykurtic Kurtosis....297

Summary....301

Keywords....302

Review Questions....303

References....304

8 Probability Distributions....305

8.1 Introduction....305

8.2 Binomial Distribution BINOM.DIST Function....305

8.2.1 BINOM.DIST Function....307

8.2.2 BINOM.DIST.RANGE Function....309

8.2.3 BINOM.INV Function....310

8.3 Poisson Distribution Using Poisson.Dist Function....311

8.4 Exponential Distribution Using Expon.Dist Function....314

8.5 Normal Distribution Using NORM.DIST, NORM.INV, NORM.S.DIST, and NORM.S.INV Functions....318

Excel Commands for Normal Distribution....319

Excel Commands to Get Value of Random Variable for a Given Cumulative Probability....320

Excel Commands for Standard Normal Distribution....321

Excel Commands to Get Value of Standard Normal Variable for a Given Cumulative Probability....321

8.6 Uniform Distribution Using Excel Sheets....327

8.7 t-Distribution (Sampling Distribution of Mean When Normal Population Variance Is Unknown) USING T.DIST, T.DIST.2T, T.DIST.RT, T.INV, and T.INV.2T Functions....329

Excel Formulas to Get the Value of a Random Variable for a Given Probability....331

8.8 Confidence Interval When Sample Size Is Large....333

8.9 Confidence Interval When Sample Size Is Small....336

Summary....338

Keywords....338

Review Questions....339

References....341

9 Sampling Distributions of Mean and Variance....342

9.1 Introduction....342

9.2 Sampling Distributions for Mean....342

9.2.1 Sampling Distribution of Mean When the Population Is Infinite Using NORM.S.DIST and NORM.S.INV Functions....343

9.2.2 Sampling Distribution of Mean When the Population Is Finite Using Excel Sheets....345

9.2.3 Sampling Distribution of Mean When Normal Population Variance Is Unknown (t Distribution) Using T.DIST, T.INV, T.DIST.2T, T.DIST.RT, and T.INV.2T Functions....350

9.3 Sampling Distributions of Variance....357

9.3.1 Chi-Square Distribution Using CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, and CHISQ.INV.RT....357

9.3.2 F Distributions Using F.DIST, F.DIST.RT, F.INV, and F.INV.RT Functions....363

Summary....368

Keywords....369

Review Questions....370

References....371

10 Testing Hypotheses....372

10.1 Introduction....372

10.2 Tests Concerning Single Mean When Mean and Variance of the Populations Are Known and the Size of the Population Is Finite Using the Norm.S.Dist Function....373

10.3 Tests Concerning Difference Between Two Means When the Variances of the Populations Are Known and the Sample Sizes Are Large Using the Z Test: Two Sample for Means, T.DIST, T.DIST.RT, and T.DIST.2T Functions....381

10.4 Tests Concerning Single Mean When the Variance of the Population Is Unknown and the Sample Size Is Small Using T.DIST, T.DIST.RT, and T.DIST.2T Functions....387

10.5 Tests Concerning Difference Between Two Means When the Variances of the Population Are Unknown and the Sample Size Is Small Using T-Test: Two-Sample Assuming Equal Variance....392

10.6 Tests Concerning Difference Between Two Means When the Variances of the Population Are Unknown and the Sample Size Is Small Using T-Test: Two-Sample Assuming Unequal Variance....404

10.7 Paired T Test Using T-Test: Paired Two-Sample for Means Function....410

Summary....414

Keywords....415

Review Questions....415

References....419

11 Chi-Square Test....420

11.1 Introduction....420

11.2 Chi-Square Test for Checking Independence of Categorised Data Using Excel Sheets and CHISQ.DIST.RT Function....420

11.2.1 Directions for the Test....422

11.3 Goodness of Fit Test Using Excel Sheets and CHISQ.DIST.RT Function....423

Summary....436

Keywords....436

Review Questions....436

References....438

12 Nonparametric Tests....439

12.1 Introduction....440

12.2 One-Sample Sign Tests....440

12.2.1 One-Tailed One-Sample Sign Test When Sample Size Is Small Using Excel Sheets and BINOM.DIST Function....440

12.2.2 Two-Tailed One-Sample Sign Test When Sample Size Is Small Using Excel Sheets and BINOM.DIST....441

12.2.3 One-Tailed One-Sample Sign Test When Sample Size Is Large Using Excel Sheets and NORM.S.DIST Function....443

12.2.4 Two-Tailed One-Sample Sign Test When Sample Size Is Large Using Excel Sheets and NORM.S.DIST Function....448

12.3 Test Using Excel Sheets....450

12.4 Run Test for Randomness....456

12.4.1 Run Test for Small Samples Using Excel Sheets and COUNTIF Functions....457

12.4.2 Run Test for Large Samples Using Excel Sheets, COUNTIF, and NORM.S.DIST Functions....458

12.5 Two-Sample Tests....460

12.5.1 Two-Sample Sign Test....461

12.5.2 Two-Sample Sign Test for Large Samples....467

12.6 Median Test Using Excel Sheets, COUNTIF, COMBIN, and CHISQ.DIST.RT Functions....477

12.7 Mann-Whitney U Test Using Excel Sheets and NORM.S.DIST Function....485

12.8 K-Sample Tests....489

12.8.1 K-Sample Median Test Using Excel Sheets, COUNTIF, COMBIN, and CHISQ.DIST.RT Functions....490

12.8.2 Kruskal–Wallis Test (H Test) Using Excel Sheets....495

Summary....499

Keywords....500

Review Questions....500

References....505

13 Correlation and Covariance....506

13.1 Introduction....506

13.2 Correlation....506

13.3 Correlation Coefficient of Ungrouped Data Using Excel Sheets and Correl Function....507

13.3.1 Testing Guidelines....508

13.3.2 Using CORREL Function....508

13.3.3 Correlation Coefficient Using Correlation Function....510

13.4 Correlation Coefficient of Grouped Data Using Excel Sheets....516

13.5 Rank Correlation Using Excel Sheets and T.DIST.RT Function....520

13.6 Auto-Correlation Using Excel Sheets....522

13.7 Covariance Using Covariance.P/Covariance.S Functions....527

Summary....535

Keywords....535

Review Questions....536

References....537

14 Forecasting....539

14.1 Introduction....539

14.2 Methods of Forecasting....539

14.3 Linear Regression....540

14.4 Linear Regression Using Excel Regression Function....541

14.5 Moving Average Method Using Moving Average Function in Excel....546

14.6 Exponential Smoothing Method of Forecasting Using Excel Sheets and Exponential Smoothing Function....550

14.7 Multiple Linear Regression Using Excel Regression Function....555

14.8 Time Series Analysis Using Excel Sheets and Regression Function....560

14.8.1 Steps of Time Series....562

Summary....570

Keywords....571

Review Questions....572

References....574

15 Analysis of Variance....575

15.1 Introduction....575

15.2 ANOVA With Single Factor (Completely Randomised Design) Using ANOVA: Single-Factor Function....579

15.3 Randomised Complete Block Design Using ANOVA: Two-Factor Without Replication Function....589

15.4 Latin Square Design Using Excel Sheets....596

15.5 Complete Factorial Experiment With Two Factors Using ANOVA: Two-Factor With Replication Function....604

15.6 Yates’ Algorithm for 2n Factorial Experiment Using Excel Sheets and F.DIST.RT Function....611

Summary....616

Keywords....617

Review Questions....617

References....620

16 Charts....621

16.1 Introduction....621

16.2 Pie Charts....622

16.3 Bar (Column) Charts....625

16.4 Multi-Bar (Columns) Charts....629

16.5 Stacked Bar Charts....633

16.6 Line Charts....636

16.7 Multiple-Line Charts....640

Summary....644

Keywords....644

Review Questions....645

References....647

17 Linear Programming....648

17.1 Introduction....648

17.2 Assumptions of Linear Programming....649

17.2.1 Linearity....649

17.2.2 Divisibility....649

17.2.3 Non-Negativity....649

17.2.4 Additivity....649

17.3 Components of Linear Programming Model....649

17.3.1 Component 1: Decision Variables....650

17.3.2 Component 2: Objective Function....650

17.3.3 Component 3: Constraints....650

17.3.4 Component 4: Non-Negativity Constraints....651

17.4 Examples of Mathematical Models....651

17.5 Graphical Method for Linear Programming....653

17.6 Simplex Method....659

17.6.1 Steps of Simplex Method....661

17.7 Solving Linear Programming Problems Using Excel....666

17.7.1 Summary of Results....678

Summary....678

Keywords....679

Review Questions....679

References....681

Annexures....682

Annexure 1: F Table for α = 0.10....682

Annexure 2: F Table for α = 0.05....684

Annexure 3: F Table for α = 0.01....685

Annexure 4: Area Under Standard Normal Distribution From Its Mean....686

Annexure 5: Values of Student’s t Statistic (tα)....686

Annexure 6: Value of Chi-Square Statistic for a Given Significance Level and Degrees of Freedom....687

Annexure 7: Critical Values of d for Kolmogorov-Smirnov One-Sample Test....688

Annexure 8: Critical Values of r of Run Test....689

Index....691

This book gives readers a hands-on understanding of Excel-assisted statistical techniques to take effective business decisions. It showcases applications of the tools and techniques of statistics for analysing business data from the domain of business statistics.

The volume provides an exhaustive introduction to the application of statistics in solving business problems and implementing data analytics for effective decision making in all kinds of business situations around the world. With an emphasis on simplicity in presentation of concepts of statistical methods and associated Excel functions, the volume explores the implementation of Excel functions through well-defined sequences of steps. It covers an array of key topics which include

• Discussions on real-world problems, decision support systems, scope of business statistics, types, and steps of research;
• Introduction to Excel and its mathematical and preliminary statistical functions; usage of different types of average functions; mean, median, and mode functions; measures of variation; measures of skewness of Excel;
• In-depth discussions on probability distributions, sampling distributions, testing of hypothesis, chi-square test, non-parametric tests of Excel;
• Extensive coverage on correlation and covariance, forecasting, analysis of variance, charts in Excel; and
• Analysis of the concept of linear programming, problem formulations, and techniques of linear programming, followed by the application in Excel.

Comprehensive in scope and simple in approach, this book will be key for students and researchers of business studies, business administration, economics, finance, commerce, data analytics/science, and computer science. This will also serve as useful guidebook for business executives and working professionals across the globe.