Excel Data Analysis: Modeling and Simulation

Preface
	Why Does the World Need-Excel Data Analysis, Modeling, and Simulation?
	Who Benefits from This Book?
	Key Features of This Book
	Second Edition
Acknowledgements
Contents
About the Author
Chapter 1: Introduction to Spreadsheet Modeling
	1.1 Introduction
	1.2 What´s an MBA to do?
	1.3 Why Model Problems?
	1.4 Why Model Decision Problems with Excel?
	1.5 The Feng Shui of Spreadsheets
	1.6 A Spreadsheet Makeover
		1.6.1 Julia´s Business Problem-A Very Uncertain Outcome
		1.6.2 Ram´s Critique
		1.6.3 Julia´s New and Improved Workbook
	1.7 Summary
	Key Terms
	Problems and Exercises
Chapter 2: Presentation of Quantitative Data: Data Visualization
	2.1 Introduction
	2.2 Data Classification
	2.3 Data Context and Data Orientation
		2.3.1 Data Preparation Advice
	2.4 Types of Charts and Graphs
		2.4.1 Ribbons and the Excel Menu System
		2.4.2 Some Frequently Used Charts
		2.4.3 Specific Steps for Creating a Chart
	2.5 An Example of Graphical Data Analysis and Presentation
		2.5.1 Example-Tere´s Budget for the 2nd Semester of College
		2.5.2 Collecting Data
		2.5.3 Summarizing Data
		2.5.4 Analyzing Data
		2.5.5 Presenting Data
	2.6 Some Final Practical Graphical Presentation Advice
	2.7 Summary
	Key Terms
	Problems and Exercises
Chapter 3: Analysis of Quantitative Data
	3.1 Introduction
	3.2 What Is Data Analysis?
	3.3 Data Analysis Tools
	3.4 Data Analysis for Two Data Sets
		3.4.1 Time Series Data: Visual Analysis
		3.4.2 Cross-Sectional Data: Visual Analysis
		3.4.3 Analysis of Time Series Data: Descriptive Statistics
		3.4.4 Analysis of Cross-Sectional Data: Descriptive Statistics
	3.5 Analysis of Time Series Data: Forecasting/Data Relationship Tools
		3.5.1 Graphical Analysis
		3.5.2 Linear Regression
		3.5.3 Covariance and Correlation
		3.5.4 Other Forecasting Models
		3.5.5 Findings
	3.6 Analysis of Cross-Sectional Data: Forecasting/Data Relationship Tools
		3.6.1 Findings
	3.7 Summary
	Key Terms
	Problems and Exercises
Chapter 4: Presentation of Qualitative Data-Data Visualization
	4.1 Introduction-What Is Qualitative Data?
	4.2 Essentials of Effective Qualitative Data Presentation
		4.2.1 Planning for Data Presentation and Preparation
	4.3 Data Entry and Manipulation
		4.3.1 Tools for Data Entry and Accuracy
		4.3.2 Data Transposition to Fit Excel
		4.3.3 Data Conversion with the Logical IF
		4.3.4 Data Conversion of Text from Non-Excel Sources
	4.4 Data Queries with Sort, Filter, and Advanced Filter
		4.4.1 Sorting Data
		4.4.2 Filtering Data
		4.4.3 Filter
		4.4.4 Advanced Filter
	4.5 An Example
	4.6 Summary
	Key Terms
	Problems and Exercises
Chapter 5: Analysis of Qualitative Data
	5.1 Introduction
	5.2 Essentials of Qualitative Data Analysis
		5.2.1 Dealing with Data Errors
	5.3 PivotChart or PivotTable Reports
		5.3.1 An Example
		5.3.2 PivotTables
		5.3.3 PivotCharts
	5.4 TiendaMía.com Example: Question 1
	5.5 TiendaMía.com Example: Question 2
	5.6 Summary
	Key Terms
	Problems and Exercises
Chapter 6: Inferential Statistical Analysis of Data
	6.1 Introduction
	6.2 Let the Statistical Technique Fit the Data
	6.3 χ2-Chi-Square Test of Independence for Categorical Data
		6.3.1 Tests of Hypothesis-Null and Alternative
	6.4 z-Test and t-Test of Categorical and Interval Data
	6.5 An Example
		6.5.1 z-Test: 2 Sample Means
		6.5.2 Is There a Difference in Scores for SC Non-prisoners and EB Trained SC Prisoners?
		6.5.3 t-Test: Two Samples Unequal Variances
		6.5.4 Do Texas Prisoners Score Higher than Texas Non-prisoners?
		6.5.5 Do Prisoners Score Higher Than Non-prisoners Regardless of the State?
		6.5.6 How Do Scores Differ Among Prisoners of SC and Texas Before Special Training?
		6.5.7 Does the EB Training Program Improve Prisoner Scores?
		6.5.8 What If the Observations Means Are the Same, But We Do Not See Consistent Movement of Scores?
		6.5.9 Summary Comments
	6.6 Confidence Intervals for Sample Statistics
		6.6.1 What Are the Ingredients of a Confidence Interval?
		6.6.2 A Confidence Interval Example
		6.6.3 Single Sample Hypothesis Tests Are Similar to Confidence Intervals
	6.7 ANOVA
		6.7.1 ANOVA: Single Factor Example
		6.7.2 Do the Mean Monthly Losses of Reefers Suggest That the Means Are Different for the Three Ports?
	6.8 Experimental Design
		6.8.1 Randomized Complete Block Design Example
		6.8.2 Factorial Experimental Design Example
	6.9 Summary
	Key Terms
	Problems and Exercises
Chapter 7: Modeling and Simulation: Part 1
	7.1 Introduction
		7.1.1 What Is a Model?
	7.2 How Do We Classify Models?
	7.3 An Example of Deterministic Modeling
		7.3.1 A Preliminary Analysis of the Event
	7.4 Understanding the Important Elements of a Model
		7.4.1 Pre-modeling or Design Phase
		7.4.2 Modeling Phase
		7.4.3 Resolution of Weather and Related Attendance
		7.4.4 Attendees Play Games of Chance
		7.4.5 Fr. Efia´s What-if Questions
		7.4.6 Summary of OLPS Modeling Effort
	7.5 Model Building with Excel
		7.5.1 Basic Model
		7.5.2 Sensitivity Analysis
		7.5.3 Controls from the Forms Control Tools
		7.5.4 Option Buttons
		7.5.5 Scroll Bars
	7.6 Summary
	Key Terms
	Problems and Exercises
Chapter 8: Modeling and Simulation: Part 2
	8.1 Introduction
	8.2 Types of Simulation and Uncertainty
		8.2.1 Incorporating Uncertain Processes in Models
	8.3 The Monte Carlo Sampling Methodology
		8.3.1 Implementing Monte Carlo Simulation Methods
		8.3.2 A Word About Probability Distributions
		8.3.3 Modeling Arrivals with the Poisson Distribution
		8.3.4 VLOOKUP and HLOOKUP Functions
	8.4 A Financial Example-Income Statement
	8.5 An Operations Example-Autohaus
		8.5.1 Status of Autohaus Model
		8.5.2 Building the Brain Worksheet
		8.5.3 Building the Calculation Worksheet
		8.5.4 Variation in Approaches to Poisson Arrivals: Consideration of Modeling Accuracy
		8.5.5 Sufficient Sample Size
		8.5.6 Building the Data Collection Worksheet
		8.5.7 Results
	8.6 Summary
	Key Terms
	Problems and Exercises
Chapter 9: Solver, Scenarios, and Goal Seek Tools
	9.1 Introduction
	9.2 Solver-Constrained Optimization
	9.3 Example-York River Archaeology Budgeting
		9.3.1 Formulation
		9.3.2 Formulation of YRA Problem
		9.3.3 Preparing a Solver Worksheet
		9.3.4 Using Solver
		9.3.5 Solver Reports
		9.3.6 Some Questions for YRA
	9.4 Scenarios
		9.4.1 Example 1-Mortgage Interest Calculations
		9.4.2 Example 2-An Income Statement Analysis
	9.5 Goal Seek
		9.5.1 Example 1-Goal Seek Applied to the PMT Cell
		9.5.2 Example 2-Goal Seek Applied to the CUMIPMT Cell
	9.6 Summary
	Key Terms
	Problems and Exercises