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