*’Excel’ in ***Bus**iness Analytics has been designed for aspiring business professionals who have an interest in learning the foundation of business analytics within Microsoft Excel. This book assumes no prior working knowledge of descriptive, predictive, or prescriptive analytics or even Microsoft Excel. From a learning perspective, **‘Excel’ In Business Analytics** has been developed to support active learning. Thus, the text within the book has been intentionally kept to a minimum and was written in a lean manner with the purpose of quickly acclimating readers to the importance of each topic being presented. There are easy-to-understand definitions and easy-to-find information about equations and formulas that are featured within the examples and highlighted throughout each chapter.

This edition is supported with 72 learning activity videos, which will provide more in-depth discussions about the topics featured within the manuscript. In addition, starting files are provided with the data necessary for you to work along with the videos to complete the activities presented. This hands-on approach will allow you to actively practice and further develop skills related to business analytics as you watch the videos at your own pace.

Instructors adopting this book will have access to multiple choice questions, homework assignments, and exam problems. Those interested in adopting this book should contact Sean Skinner (sskinner@kendallhunt.com) at Kendall Hunt Publishing Company for more information.

*PREFACE*

THE AUTHORS

**CHAPTER 1 DESCRIPTIVE STATISTICS—Building Excel Skills **

Professional Formatting Guidance

Paste Special

Shortcut Keys

Order of Operations

Working With Formulas

Cell Referencing

Date and Text Functions

SUMPRODUCT Function

TRANSPOSE Function

IF Statements

Conditional Formatting

INDEX and MATCH Functions

VLOOKUP Function

Working With Macros

**CHAPTER 2 DESCRIPTIVE STATISTICS—Summarizing Data **

Types of Data and Measurement Scales

Building Summary Tables

Counting the Number of Occurrences

Counting With Continuous Criteria

Summing the Values in a Range

Extreme Values

Averages

Population and Sample Means

Population and Sample Medians

Population and Sample Modes

Weighted Mean

Geometric Mean

Dispersion (aka Spread)

The Emperical Rule

**CHAPTER 3 DESCRIPTIVE STATISTICS—Data Visualization **

Pivot Tables and Pivot Charts

Qualitative Data

Quantitative Data

Bivariate Data

Box and Whisker Plots

Pareto Charts

Reference

**CHAPTER 4 INFERENTIAL STATISTICS—Probability **

Basic Definitions

Assigning Probability for One Event Experiments

Assigning Probability for Two or More Event Experiments

Principles of Counting

Probability Distributions: Definitions

The Binomial Probability Distribution

Binomial Probability Distribution Example Problem

Hypergeometric Probability Distribution

Hypergeometric Probability Distribution Example Problem

Poisson Probability Distribution

Poisson Probability Distribution Example Problem

Uniform Probability Distribution

Uniform Probability Distribution Example Problem

Exponential Probability Distribution

Exponential Probability Distribution Example Problem

Observational vs. Experimental Studies

Sampling Methods

Central Limit Theorem (CLT)

Normal Probability Distribution

Sample Size Determination for the Mean

Sample Size Determination for the Mean Example Problem

Normal Probability Distribution Example Problem

Confidence Intervals about the Mean

Control Charts

Sampling Distribution of the Proportion

Sampling Distribution of the Proportion Example Problem

Confidence Interval Estimate for the Proportion

Confidence Interval Estimate for the Proportion Example Problem

Sample Size Determination for the Proportion

Sample Size Determination for the Proportion Example Problem

Reference

**CHAPTER 5 INFERENTIAL STATISTICS—Hypothesis Testing **

One-Sample Hypothesis Tests for Means

Single-Sample Problem and Solution Example

One Sample Hypothesis Tests for Proportions

One Sample Hypothesis Tests for Proportions Example Problem

Two-Sample, Hypothesis Tests for Means

Two-Sample Problem and Solution Example

Two Sample Hypothesis Tests for Proportions

Multi-Sample or Multi-Factor Hypothesis Tests—for Means

Multi-Sample Problem and Solution Example

Two-Sample Hypothesis Tests for Equal Variances

P-Values in Hypothesis Testing

ANOVA Post Hoc Analysis

**CHAPTER 6 PREDICTIVE ANALYTICS—Regression **

Contingency Table

Correlation Analysis

Correlation Analysis Problem and Solution Example

Dealing With Missing Data and Outliers

Dealing With Text Data

Simple Regression

Simple Regression Problem and Solution Example

Multiple Regression

Multiple Regression Stepwise Procedure

Multiple Regression Problem and Solution Example

**CHAPTER 7 PREDICTIVE ANALYTICS—Time Series **

Secular Trend

Cyclical Variations

Seasonal Variations

Irregular Variations

Simple Moving Averages

Weighted Moving Averages

Forecasting Using Exponential Smoothing

Exponential Smoothing Problem and Solution Example

Autoregression

Measures of Error

Reference

**CHAPTER 8 PRESCRIPTIVE ANALYTICS—Decision Theory **

Payoff Matrix

The Maximum Expected Monetary Value (EMV) Strategy

The MAXIMIN Decision Strategy

The MAXIMAX Decision Strategy

The Regret Matrix and Expected Opportunity Loss (EOL)

The MINIMAX REGRET Decision Strategy

Expected Value of Perfect Information (EVPI)

Decision Trees

**CHAPTER 9 PRESCRIPTIVE ANALYTICS—Optimization **

Steps to Build an Optimization Model in Excel

Example Production—Transportation Type Optimization Problem

Example Production—Inventory Type Optimization Problem

*INDEX*