做数据分析的或许有兴趣: <<Excel Data Analysis - Modeling and Simulation>>
by Hector Guerrero, 2010    353pages

Contents
1 Introduction to Spreadsheet Modeling . . . . . . . . . . . . . . . . 1
1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 What’s anMBAtodo? . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Why Model Problems? . . . . . . . . . . . . . . . . . . . . . . 3
1.4 Why Model Decision Problems with Excel? . . . . . . . . . . . 3
1.5 Spreadsheet Feng Shui/Spreadsheet Engineering . . . . . . . . . 5
1.6 A Spreadsheet Makeover . . . . . . . . . . . . . . . . . . . . . 7
1.6.1 Julia’s Business Problem—A Very Uncertain Outcome . . 8
1.6.2 Ram’sCritique . . . . . . . . . . . . . . . . . . . . . . . 11
1.6.3 Julia’s New and Improved Workbook . . . . . . . . . . . 12
1.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Presentation of Quantitative Data . . . . . . . . . . . . . . . . . . . 19
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2 DataClassification . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.3 DataContext andDataOrientation . . . . . . . . . . . . . . . . 21
2.3.1 Data Preparation Advice . . . . . . . . . . . . . . . . . . 24
2.4 Types of Charts and Graphs . . . . . . . . . . . . . . . . . . . . 26
2.4.1 Ribbons and the Excel Menu System . . . . . . . . . . . 27
2.4.2 Some Frequently Used Charts . . . . . . . . . . . . . . . 29
2.4.3 Specific Steps for Creating a Chart . . . . . . . . . . . . 33
2.5 An Example of Graphical Data Analysis and Presentation . . . . 36
2.5.1 Example—Tere’s Budget for the 2nd Semester of College 38
2.5.2 CollectingData . . . . . . . . . . . . . . . . . . . . . . 40
2.5.3 SummarizingData . . . . . . . . . . . . . . . . . . . . . 40
2.5.4 AnalyzingData . . . . . . . . . . . . . . . . . . . . . . 42
2.5.5 PresentingData . . . . . . . . . . . . . . . . . . . . . . 48
2.6 Some Final Practical Graphical Presentation Advice . . . . . . . 49
2.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
3 Analysis of Quantitative Data . . . . . . . . . . . . . . . . . . . . . 55
3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.2 What isDataAnalysis? . . . . . . . . . . . . . . . . . . . . . . 56
3.3 DataAnalysisTools . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4 DataAnalysis forTwoDataSets . . . . . . . . . . . . . . . . . 60
3.4.1 TimeSeriesData—VisualAnalysis . . . . . . . . . . . . 61
3.4.2 Cross-Sectional Data—Visual Analysis . . . . . . . . . . 65
3.4.3 Analysis of Time Series Data—Descriptive Statistics . . . 67
3.4.4 Analysis of Cross-Sectional Data—Descriptive Statistics . 69
3.5 Analysis of Time Series Data—Forecasting/Data RelationshipTools  . . 72
3.5.1 Graphical Analysis . . . . . . . . . . . . . . . . . . . . . 73
3.5.2 Linear Regression . . . . . . . . . . . . . . . . . . . . . 77
3.5.3 Covariance and Correlation . . . . . . . . . . . . . . . . 82
3.5.4 Other Forecasting Models . . . . . . . . . . . . . . . . . 84
3.5.5 Findings . . . . . . . . . . . . . . . . . . . . . . . . . . 85
3.6 Analysis of Cross-Sectional Data—Forecasting/Data RelationshipTools . . 85
3.6.1 Findings . . . . . . . . . . . . . . . . . . . . . . . . . . 92
3.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
4 Presentation of Qualitative Data . . . . . . . . . . . . . . . . . . . 99
4.1 Introduction—What is Qualitative Data? . . . . . . . . . . . . . 99
4.2 Essentials of Effective Qualitative Data Presentation . . . . . . . 100
4.2.1 Planning for Data Presentation and Preparation . . . . . . 100
4.3 DataEntryandManipulation . . . . . . . . . . . . . . . . . . . 103
4.3.1 Tools for Data Entry and Accuracy . . . . . . . . . . . . 103
4.3.2 Data Transposition to Fit Excel . . . . . . . . . . . . . . 106
4.3.3 DataConversionwiththeLogical IF . . . . . . . . . . . 109
4.3.4 Data Conversion of Text from Non-Excel Sources . . . . 112
4.4 Data queries with Sort, Filter, and Advanced Filter . . . . . . . . 116
4.4.1 SortingData . . . . . . . . . . . . . . . . . . . . . . . . 116
4.4.2 Filtering Data . . . . . . . . . . . . . . . . . . . . . . . 118
4.4.3 Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
4.4.4 Advanced Filter . . . . . . . . . . . . . . . . . . . . . . 123
4.5 AnExample . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
4.6 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
5 Analysis of Qualitative Data . . . . . . . . . . . . . . . . . . . . . . 141
5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
5.2 Essentials of Qualitative Data Analysis . . . . . . . . . . . . . . 143
5.2.1 DealingwithDataErrors . . . . . . . . . . . . . . . . . 143
5.3 PivotChart or PivotTable Reports . . . . . . . . . . . . . . . . . 147
5.3.1 AnExample . . . . . . . . . . . . . . . . . . . . . . . . 148
5.3.2 PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . 150
5.3.3 PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . 157
5.4 TiendaMía.com Example—Question 1 . . . . . . . . . . . . . . 160
5.5 TiendaMía.com Example—Question 2 . . . . . . . . . . . . . . 163
5.6 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
6 Inferential Statistical Analysis of Data . . . . . . . . . . . . . . . . 177
6.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
6.2 Let the Statistical Technique Fit the Data . . . . . . . . . . . . . 179
6.3 χ2—Chi-Square Test of Independence for Categorical Data . . . 179
6.3.1 Tests of Hypothesis—Null and Alternative . . . . . . . . 180
6.4 z-Test and t-TestofCategorical and IntervalData . . . . . . . . . 184
6.5 AnExample . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
6.5.1 z-Test: 2 Sample Means . . . . . . . . . . . . . . . . . . 187
6.5.2 Is There a Difference in Scores for SC
Non-Prisoners and EB Trained SC Prisoners? . . . . . . . 188
6.5.3 t-Test: Two Samples Unequal Variances . . . . . . . . . . 191
6.5.4 Do Texas Prisoners Score Higher Than Texas Non-Prisoners? . . 191
6.5.5 Do Prisoners Score Higher Than Non-Prisoners Regardless of theState? .  . 192
6.5.6 How do Scores Differ Among Prisoners of SC and Texas
Before Special Training? . . . . 193
6.5.7 Does the EB Training Program Improve Prisoner Scores? 195
6.5.8 What If the Observations Means Are Different,
But We Do Not See Consistent Movement of Scores? . . 197
6.5.9 SummaryComments . . . . . . . . . . . . . . . . . . . . 197
6.6 ANOVA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
6.6.1 ANOVA:SingleFactorExample . . . . . . . . . . . . . 199
6.6.2 Do the Mean Monthly Losses of Reefers Suggest
That the Means are Different for the Three Ports? .. . 201
6.7 Experimental Design . . . . . . . . . . . . . . . . . . . . . . . . 202
6.7.1 Randomized Complete Block Design Example . . . . . . 205
6.7.2 Factorial Experimental Design Example . . . . . . . . . 209
6.8 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
7 Modeling and Simulation: Part 1 . . . . . . . . . . . . . . . . . . . 217
7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
7.1.1 What is a Model? . . . . . . . . . . . . . . . . . . . . . 219
7.2 How Do We Classify Models? . . . . . . . . . . . . . . . . . . . 221
7.3 AnExample ofDeterministicModeling . . . . . . . . . . . . . . 223
7.3.1 APreliminaryAnalysis of theEvent . . . . . . . . . . . 224
7.4 Understanding the Important Elements of a Model . . . . . . . . 227
7.4.1 Pre-Modeling or Design Phase . . . . . . . . . . . . . . 228
7.4.2 Modeling Phase . . . . . . . . . . . . . . . . . . . . . . 228
7.4.3 Resolution of Weather and Related Attendance . . . . . . 232
7.4.4 Attendees Play Games of Chance . . . . . . . . . . . . . 233
7.4.5 Fr.Efia’sWhat-ifQuestions . . . . . . . . . . . . . . . . 235
7.4.6 Summary ofOLPSModelingEffort . . . . . . . . . . . . 236
7.5 Model Building with Excel . . . . . . . . . . . . . . . . . . . . 236
7.5.1 Basic Model . . . . . . . . . . . . . . . . . . . . . . . . 237
7.5.2 Sensitivity Analysis . . . . . . . . . . . . . . . . . . . . 240
7.5.3 Controls fromtheFormsControlTools . . . . . . . . . . 247
7.5.4 OptionButtons . . . . . . . . . . . . . . . . . . . . . . . 248
7.5.5 ScrollBars . . . . . . . . . . . . . . . . . . . . . . . . . 250
7.6 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
8 Modeling and Simulation: Part 2 . . . . . . . . . . . . . . . . . . . 257
8.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
8.2 Types of Simulation and Uncertainty . . . . . . . . . . . . . . . 259
8.2.1 Incorporating Uncertain Processes in Models . . . . . . . 259
8.3 The Monte Carlo Sampling Methodology . . . . . . . . . . . . . 260
8.3.1 Implementing Monte Carlo Simulation Methods . . . . . 261
8.3.2 A Word About Probability Distributions . . . . . . . . . 266
8.3.3 ModelingArrivalswiththePoissonDistribution . . . . . 271
8.3.4 VLOOKUP and HLOOKUP Functions . . . . . . . . . . 273
8.4 A Financial Example—Income Statement . . . . . . . . . . . . . 275
8.5 An Operations Example—Autohaus . . . . . . . . . . . . . . . . 279
8.5.1 Status of Autohaus Model . . . . . . . . . . . . . . . . . 283
.

.
.