Excel Data Analysis

Table of contents

- Preface (Page 8)
- Why does the World Need Excel Data Analysis, Modeling, and Simulation ? (Page 8)
- Who Benefits from this Book? (Page 9)
- Key Features of this Book (Page 9)
- Acknowledgements (Page 10)
- Contents (Page 12)
- About the Author (Page 18)
- 1 Introduction to Spreadsheet Modeling (Page 19)
- 1.1 Introduction (Page 19)
- 1.2 Whats an MBA to do? (Page 20)
- 1.3 Why Model Problems? (Page 21)
- 1.4 Why Model Decision Problems with Excel? (Page 21)
- 1.5 Spreadsheet Feng Shui1/ Spreadsheet Engineering (Page 23)
- 1.6 A Spreadsheet Makeover (Page 25)
- 1.6.1 Julia---s Business Problem---A Very Uncertain Outcome (Page 26)
- 1.6.2 Ram's Critique (Page 29)
- 1.6.3 Julia's New and Improved Workbook (Page 30)
- 1.7 Summary (Page 34)
- Key Terms (Page 35)
- Problems and Exercises (Page 35)
- 2 Presentation of Quantitative Data (Page 37)
- 2.1 Introduction (Page 37)
- 2.2 Data Classification (Page 38)
- 2.3 Data Context and Data Orientation (Page 39)
- 2.3.1 Data Preparation Advice (Page 42)
- 2.4 Types of Charts and Graphs (Page 44)
- 2.4.1 Ribbons and the Excel Menu System (Page 45)
- 2.4.2 Some Frequently Used Charts (Page 47)
- 2.4.3 Specific Steps for Creating a Chart (Page 51)
- 2.5 An Example of Graphical Data Analysis and Presentation (Page 53)
- 2.5.1 Example'Tere's Budget for the 2nd Semester of College (Page 56)
- 2.5.2 Collecting Data (Page 58)
- 2.5.3 Summarizing Data (Page 58)
- 2.5.4 Analyzing Data (Page 60)
- 2.5.5 Presenting Data (Page 66)
- 2.6 Some Final Practical Graphical Presentation Advice (Page 67)
- 2.7 Summary (Page 69)
- Key Terms (Page 69)
- Problems and Exercises (Page 70)
- 3 Analysis of Quantitative Data (Page 73)
- 3.1 Introduction (Page 73)
- 3.2 What is Data Analysis? (Page 73)
- 3.3 Data Analysis Tools (Page 74)
- 3.4 Data Analysis for Two Data Sets (Page 78)
- 3.4.1 Time Series Data---Visual Analysis (Page 79)
- 3.4.2 Cross-Sectional Data---Visual Analysis (Page 83)
- 3.4.3 Analysis of Time Series Data---Descriptive Statistics (Page 85)
- 3.4.4 Analysis of Cross-Sectional Data---Descriptive Statistics (Page 87)
- 3.5 Analysis of Time Series DataForecasting/Data Relationship Tools (Page 90)
- 3.5.1 Graphical Analysis (Page 91)
- 3.5.2 Linear Regression (Page 95)
- 3.5.3 Covariance and Correlation (Page 100)
- 3.5.4 Other Forecasting Models (Page 102)
- 3.5.5 Findings (Page 103)
- 3.6 Analysis of Cross-Sectional DataForecasting/Data Relationship Tools (Page 103)
- 3.6.1 Findings (Page 110)
- 3.7 Summary (Page 111)
- Key Terms (Page 112)
- Problems and Exercises (Page 112)
- 4 Presentation of Qualitative Data (Page 116)
- 4.1 IntroductionWhat is Qualitative Data? (Page 116)
- 4.2 Essentials of Effective Qualitative Data Presentation (Page 117)
- 4.2.1 Planning for Data Presentation and Preparation (Page 117)
- 4.3 Data Entry and Manipulation (Page 120)
- 4.3.1 Tools for Data Entry and Accuracy (Page 120)
- 4.3.2 Data Transposition to Fit Excel (Page 123)
- 4.3.3 Data Conversion with the Logical IF (Page 126)
- 4.3.4 Data Conversion of Text from Non-Excel Sources (Page 129)
- 4.4 Data queries with Sort, Filter, and Advanced Filter (Page 130)
- 4.4.1 Sorting Data (Page 133)
- 4.4.2 Filtering Data (Page 135)
- 4.4.3 Filter (Page 135)
- 4.4.4 Advanced Filter (Page 140)
- 4.5 An Example (Page 145)
- 4.6 Summary (Page 150)
- 4.7 Key Terms (Page 152)
- 4.7 Problems and Exercises (Page 153)
- 5 Analysis of Qualitative Data (Page 157)
- 5.1 Introduction (Page 157)
- 5.2 Essentials of Qualitative Data Analysis (Page 159)
- 5.2.1 Dealing with Data Errors (Page 159)
- 5.3 PivotChart or PivotTable Reports (Page 163)
- 5.3.1 An Example (Page 164)
- 5.3.2 PivotTables (Page 166)
- 5.3.3 PivotCharts (Page 173)
- 5.4 TiendaMa.com ExampleQuestion 1 (Page 176)
- 5.5 TiendaMa.com ExampleQuestion 2 (Page 179)
- 5.6 Summary (Page 187)
- Key Terms (Page 188)
- Problems and Exercises (Page 188)
- 6 Inferential Statistical Analysis of Data (Page 192)
- 6.1 Introduction (Page 192)
- 6.2 Let the Statistical Technique Fit the Data (Page 194)
- 6.3 2 Chi-Square Test of Independence for Categorical Data (Page 194)
- 6.3.1 Tests of Hypothesis---Null and Alternative (Page 195)
- 6.4 z-Test and t-Test of Categorical and Interval Data (Page 199)
- 6.5 An Example (Page 199)
- 6.5.1 z-Test: 2 Sample Means (Page 202)
- 6.5.2 Is There a Difference in Scores for SC Non-Prisoners and EB Trained SC Prisoners? (Page 203)
- 6.5.3 t-Test: Two Samples Unequal Variances (Page 206)
- 6.5.4 Do Texas Prisoners Score Higher Than Texas Non-Prisoners? (Page 206)
- 6.5.5 Do Prisoners Score Higher Than Non-Prisoners Regardless of the State? (Page 207)
- 6.5.6 How do Scores Differ Among Prisoners of SC and Texas Before Special Training? (Page 208)
- 6.5.7 Does the EB Training Program Improve Prisoner Scores? (Page 210)
- 6.5.8 What If the Observations Means Are Different, But We Do Not See Consistent Movement of Scores? (Page 212)
- 6.5.9 Summary Comments (Page 212)
- 6.6 ANOVA (Page 212)
- 6.6.1 ANOVA: Single Factor Example (Page 214)
- 6.6.2 Do the Mean Monthly Losses of Reefers Suggest That the Means are Different for the Three Ports? (Page 216)
- 6.7 Experimental Design (Page 217)
- 6.7.1 Randomized Complete Block Design Example (Page 220)
- 6.7.2 Factorial Experimental Design Example (Page 224)
- 6.8 Summary (Page 225)
- Key Terms (Page 226)
- Problems and Exercises (Page 228)
- 7 Modeling and Simulation: Part 1 (Page 232)
- 7.1 Introduction (Page 232)
- 7.1.1 What is a Model? (Page 234)
- 7.2 How Do We Classify Models? (Page 235)
- 7.3 An Example of Deterministic Modeling (Page 238)
- 7.3.1 A Preliminary Analysis of the Event (Page 239)
- 7.4 Understanding the Important Elements of a Model (Page 242)
- 7.4.1 Pre-Modeling or Design Phase (Page 243)
- 7.4.2 Modeling Phase (Page 243)
- 7.4.3 Resolution of Weather and Related Attendance (Page 247)
- 7.4.4 Attendees Play Games of Chance (Page 248)
- 7.4.5 Fr. Efia's What-if Questions (Page 250)
- 7.4.6 Summary of OLPS Modeling Effort (Page 251)
- 7.5 Model Building with Excel (Page 251)
- 7.5.1 Basic Model (Page 252)
- 7.5.2 Sensitivity Analysis (Page 255)
- 7.5.3 Controls from the Forms Control Tools (Page 262)
- 7.5.4 Option Buttons (Page 263)
- 7.5.5 Scroll Bars (Page 265)
- 7.6 Summary (Page 267)
- Key Terms (Page 268)
- Problems and Exercises (Page 269)
- 8 Modeling and Simulation: Part 2 (Page 272)
- 8.1 Introduction (Page 272)
- 8.2 Types of Simulation and Uncertainty (Page 274)
- 8.2.1 Incorporating Uncertain Processes in Models (Page 274)
- 8.3 The Monte Carlo Sampling Methodology (Page 275)
- 8.3.1 Implementing Monte Carlo Simulation Methods (Page 276)
- 8.3.2 A Word About Probability Distributions (Page 281)
- 8.3.3 Modeling Arrivals with the Poisson Distribution (Page 286)
- 8.3.4 VLOOKUP and HLOOKUP Functions (Page 288)
- 8.4 A Financial ExampleIncome Statement (Page 289)
- 8.5 An Operations ExampleAutohaus (Page 293)
- 8.5.1 Status of Autohaus Model (Page 298)
- 8.5.2 Building the Brain Worksheet (Page 299)
- 8.5.3 Building the Calculation Worksheet (Page 301)
- 8.5.4 Variation in Approaches to Poisson Arrivals---Consideration of Modeling Accuracy (Page 303)
- 8.5.5 Sufficient Sample Size (Page 305)
- 8.5.6 Building the Data Collection Worksheet (Page 306)
- 8.5.7 Results (Page 311)
- 8.6 Summary (Page 313)
- Key Terms (Page 314)
- Problems and Exercises (Page 315)
- 9 Solver, Scenarios, and Goal Seek Tools (Page 318)
- 9.1 Introduction (Page 318)
- 9.2 SolverConstrained Optimization (Page 320)
- 9.3 ExampleYork River Archaeology Budgeting (Page 321)
- 9.3.1 Formulation (Page 323)
- 9.3.2 Formulation of YRA Problem (Page 325)
- 9.3.3 Preparing a Solver Worksheet (Page 325)
- 9.3.4 Using Solver (Page 329)
- 9.3.5 Solver Reports (Page 330)
- 9.3.6 Some Questions for YRA (Page 334)
- 9.4 Scenarios (Page 338)
- 9.4.1 Example 1---Mortgage Interest Calculations (Page 339)
- 9.4.2 Example 2---An Income Statement Analysis (Page 343)
- 9.5 Goal Seek (Page 344)
- 9.5.1 Example 1---Goal Seek Applied to the PMT Cell (Page 345)
- 9.5.2 Example 2---Goal Seek Applied to the CUMIPMT Cell (Page 346)
- 9.6 Summary (Page 347)
- Key Terms (Page 349)
- Problems and Exercises (Page 350)