List of Figures |
|
xv | |
List of Tables |
|
xxi | |
Foreword |
|
xxxi | |
1 Introduction and objectives |
|
1 | (14) |
|
1.1 Why write this book? Who might find it useful? Why five volumes? |
|
|
1 | (1) |
|
1.1.1 Why write this series? Who might find it useful? |
|
|
1 | (1) |
|
|
2 | (1) |
|
1.2 Features you'll find in this book and others in this series |
|
|
2 | (5) |
|
|
3 | (1) |
|
1.2.2 The lighter side (humour) |
|
|
3 | (1) |
|
|
3 | (1) |
|
|
3 | (1) |
|
1.2.5 Discussions and explanations with a mathematical slant for Formula-philes |
|
|
4 | (1) |
|
1.2.6 Discussions and explanations without a mathematical slant for Formula-phobes |
|
|
5 | (1) |
|
|
5 | (1) |
|
|
6 | (1) |
|
1.2.9 Useful Microsoft Excel functions and facilities |
|
|
6 | (1) |
|
1.2.10 References to authoritative sources |
|
|
7 | (1) |
|
|
7 | (1) |
|
1.3 Overview of chapters in this volume |
|
|
7 | (1) |
|
1.4 Elsewhere in the 'Working Guide to Estimating & Forecasting' series |
|
|
8 | (5) |
|
1.4.1 Volume I: Principles, Process and Practice of Professional Number Juggling |
|
|
9 | (1) |
|
1.4.2 Volume II: Probability, Statistics and Other Frightening Stuff |
|
|
10 | (1) |
|
1.4.3 Volume III: Best Fit Lines and Curves, and Some Mathe-Magical Transformations |
|
|
11 | (1) |
|
1.4.4 Volume IV Learning, Unlearning and Re-learning curves |
|
|
11 | (1) |
|
1.4.5 Volume V Risk, Opportunity, Uncertainty and Other Random Models |
|
|
12 | (1) |
|
1.5 Final thoughts and musings on this volume and series |
|
|
13 | (1) |
|
|
14 | (1) |
2 Linear and nonlinear properties (!) of straight lines |
|
15 | (30) |
|
2.1 Basic linear properties |
|
|
15 | (6) |
|
2.1.1 Inter-relation between slope and intercept |
|
|
18 | (1) |
|
2.1.2 The difference between two straight lines is a straight line |
|
|
19 | (2) |
|
2.2 The Cumulative Value (nonlinear) property of a linear sequence |
|
|
21 | (22) |
|
2.2.1 The Cumulative Value of a Discrete Linear Function |
|
|
21 | (5) |
|
2.2.2 The Cumulative Value of a Continuous Linear Function |
|
|
26 | (8) |
|
2.2.3 Exploiting the Quadratic Cumulative Value of a straight line |
|
|
34 | (9) |
|
|
43 | (1) |
|
|
44 | (1) |
3 Trendsetting with some Simple Moving Measures |
|
45 | (68) |
|
3.1 Going all trendy: The could and the should |
|
|
45 | (3) |
|
3.1.1 When should we consider trend smoothing? |
|
|
45 | (2) |
|
3.1.2 When is trend smoothing not appropriate? |
|
|
47 | (1) |
|
|
48 | (33) |
|
3.2.1 Use of Moving Averages |
|
|
49 | (1) |
|
3.2.2 When not to use Moving Averages |
|
|
49 | (1) |
|
3.2.3 Simple Moving Average |
|
|
50 | (4) |
|
3.2.4 Weighted Moving Average |
|
|
54 | (4) |
|
3.2.5 Choice of Moving Average Interval: Is there a better way than guessing? |
|
|
58 | (8) |
|
3.2.6 Can we take the Moving Average of a Moving Average? |
|
|
66 | (2) |
|
3.2.7 A creative use for Moving Averages - A case of forward thinking |
|
|
68 | (2) |
|
3.2.8 Dealing with missing data |
|
|
70 | (1) |
|
3.2.9 Uncertainty Range around the Moving Average |
|
|
71 | (10) |
|
|
81 | (4) |
|
3.3.1 Choosing the Moving Median Interval |
|
|
83 | (1) |
|
3.3.2 Dealing with missing data |
|
|
84 | (1) |
|
3.3.3 Uncertainty Range around the Moving Median |
|
|
84 | (1) |
|
3.4 Other Moving Measures of Central Tendency |
|
|
85 | (4) |
|
3.4.1 Moving Geometric Mean |
|
|
87 | (1) |
|
3.4.2 Moving Harmonic Mean |
|
|
87 | (1) |
|
|
88 | (1) |
|
3.5 Exponential Smoothing |
|
|
89 | (7) |
|
3.5.1 An unfortunate dichotomy |
|
|
89 | (3) |
|
3.5.2 Choice of Smoothing Constant, or Choice of Damping Factor |
|
|
92 | (2) |
|
3.5.3 Uses for Exponential Smoothing |
|
|
94 | (1) |
|
3.5.4 Double and Triple Exponential Smoothing |
|
|
95 | (1) |
|
3.6 Cumulative Average and Cumulative Smoothing |
|
|
96 | (14) |
|
3.6.1 Use of Cumulative Averages |
|
|
97 | (4) |
|
3.6.2 Dealing with missing data |
|
|
101 | (2) |
|
3.6.3 Cumulative Averages with batch data |
|
|
103 | (1) |
|
3.6.4 Being slightly more creative - Cumulative Average on a sliding scale |
|
|
103 | (2) |
|
3.6.5 Cumulative Smoothing |
|
|
105 | (5) |
|
|
110 | (2) |
|
|
112 | (1) |
4 Simple and Multiple Linear Regression |
|
113 | (98) |
|
4.1 What is Regression Analysis? |
|
|
113 | (9) |
|
4.1.1 Least Squares Best Fit |
|
|
115 | (5) |
|
4.1.2 Two key sum-to-zero properties of Least Squares |
|
|
120 | (2) |
|
4.2 Simple Linear Regression |
|
|
122 | (7) |
|
4.2.1 Simple Linear Regression using basic Excel functions |
|
|
123 | (2) |
|
4.2.2 Simple Linear Regression using the Data Analysis Add-in Tool Kit in Excel |
|
|
125 | (2) |
|
4.2.3 Simple Linear Regression using advanced Excel functions |
|
|
127 | (2) |
|
4.3 Multiple Linear Regression |
|
|
129 | (9) |
|
4.3.1 Using categorical data in Multiple Linear Regression |
|
|
131 | (2) |
|
4.3.2 Multiple Linear Regression using the Data Analysis Add-in Tool Kit in Excel |
|
|
133 | (3) |
|
4.3.3 Multiple Linear Regression using advanced Excel functions |
|
|
136 | (2) |
|
4.4 Dealing with Outliers in Regression Analysis? |
|
|
138 | (2) |
|
4.5 How good is our Regression? Six key measures |
|
|
140 | (39) |
|
4.5.1 Coefficient of Determination (R-Square): A measure of linearity?! |
|
|
141 | (8) |
|
4.5.2 F-Statistic: A measure of chance occurrence |
|
|
149 | (7) |
|
4.5.3 t-Statistics: Measures of Relevance or Significant Contribution |
|
|
156 | (6) |
|
4.5.4 Regression through the origin |
|
|
162 | (9) |
|
4.5.5 Role of common sense as a measure of goodness of fit |
|
|
171 | (1) |
|
4.5.6 Coefficient of Variation as a measure of tightness of fit |
|
|
172 | (2) |
|
4.5.7 White's Test for heteroscedasticity...and, by default, homoscedasticity |
|
|
174 | (5) |
|
4.6 Prediction and Confidence Intervals - Measures of uncertainty |
|
|
179 | (14) |
|
4.6.1 Prediction Intervals and Confidence Intervals: What's the difference? |
|
|
180 | (2) |
|
4.6.2 Calculating Prediction Limits and Confidence Limits for Simple Linear Regression |
|
|
182 | (3) |
|
4.6.3 Calculating Prediction Limits and Confidence Limits for Multi-Linear Regression |
|
|
185 | (8) |
|
|
193 | (16) |
|
4.7.1 Backward Elimination |
|
|
197 | (4) |
|
|
201 | (5) |
|
4.7.3 Backward or Forward Selection -Which should we use? |
|
|
206 | (2) |
|
4.7.4 Choosing the best model when we are spoilt for choice |
|
|
208 | (1) |
|
|
209 | (1) |
|
|
210 | (1) |
5 Linear transformation: Making bent lines straight |
|
211 | (73) |
|
|
212 | (10) |
|
5.1.1 Basic properties of powers |
|
|
213 | (3) |
|
5.1.2 Basic properties of logarithms |
|
|
216 | (6) |
|
5.2 Basic linear transformation: Four Standard Function types |
|
|
222 | (22) |
|
|
223 | (2) |
|
5.2.2 Logarithmic Functions |
|
|
225 | (5) |
|
5.2.3 Exponential Functions |
|
|
230 | (3) |
|
|
233 | (4) |
|
5.2.5 Transforming with Microsoft Excel |
|
|
237 | (5) |
|
5.2.6 Is the transformation really better, or just a mathematical sleight of hand? |
|
|
242 | (2) |
|
5.3 Advanced linear transformation: Generalised Function types |
|
|
244 | (13) |
|
5.3.1 Transforming Generalised Logarithmic Functions |
|
|
245 | (4) |
|
5.3.2 Transforming Generalised Exponential Functions |
|
|
249 | (1) |
|
5.3.3 Transforming Generalised Power Functions |
|
|
250 | (3) |
|
5.3.4 Reciprocal Functions - Special cases of Generalised Power Functions |
|
|
253 | (1) |
|
5.3.5 Transformation options |
|
|
254 | (3) |
|
5.4 Finding the Best Fit Offset Constant |
|
|
257 | (14) |
|
5.4.1 Transforming Generalised Function Types into Standard Functions |
|
|
259 | (1) |
|
5.4.2 Using the Random-Start Bisection Method (Technique) |
|
|
260 | (3) |
|
5.4.3 Using Microsoft Excel's Goal Seek or Solver |
|
|
263 | (8) |
|
5.5 Straightening out Earned Value Analysis...or EVM Disintegration |
|
|
271 | (8) |
|
|
271 | (3) |
|
5.5.2 Taking a simpler perspective |
|
|
274 | (5) |
|
5.6 Linear transformation based on Cumulative Value Disaggregation |
|
|
279 | (2) |
|
|
281 | (2) |
|
|
283 | (1) |
6 Transforming Nonlinear Regression |
|
284 | (96) |
|
6.1 Simple Linear Regression of a linear transformation |
|
|
284 | (16) |
|
6.1.1 Simple Linear Regression with a Logarithmic Function |
|
|
288 | (3) |
|
6.1.2 Simple Linear Regression with an Exponential Function |
|
|
291 | (7) |
|
6.1.3 Simple Linear Regression with a Power Function |
|
|
298 | (1) |
|
6.1.4 Reversing the transformation of Logarithmic, Exponential and Power Functions |
|
|
299 | (1) |
|
6.2 Multiple Linear Regression of a multi-linear transformation |
|
|
300 | (23) |
|
6.2.1 Multi-linear Regression using linear and linearised Logarithmic Functions |
|
|
302 | (10) |
|
6.2.2 Multi-Linear Regression using linearised Exponential and Power Functions |
|
|
312 | (11) |
|
6.3 Stepwise Regression and multi-linear transformations |
|
|
323 | (10) |
|
6.3.1 Stepwise Regression by Backward Elimination with linear transformations |
|
|
323 | (7) |
|
6.3.2 Stepwise Regression by Forward Selection with linear transformations |
|
|
330 | (3) |
|
6.4 Is the Best Fit really the better fit? |
|
|
333 | (4) |
|
6.5 Regression of Transformed Generalised Nonlinear Functions |
|
|
337 | (22) |
|
6.5.1 Linear Regression of a Transformed Generalised Logarithmic Function |
|
|
342 | (6) |
|
6.5.2 Linear Regression of a Transformed Generalised Exponential Function |
|
|
348 | (3) |
|
6.5.3 Linear Regression of a Transformed Generalised Power Function |
|
|
351 | (6) |
|
6.5.4 Generalised Function transformations: Avoiding the pitfalls and tripwires |
|
|
357 | (2) |
|
6.6 Pseudo Multi-linear Regression of Polynomial Functions |
|
|
359 | (19) |
|
6.6.1 Offset Quadratic Regression of the Cumulative of a straight line |
|
|
361 | (7) |
|
6.6.2 Example of a questionable Cubic Regression of three linear variables |
|
|
368 | (10) |
|
|
378 | (1) |
|
|
379 | (1) |
7 Least Squares Nonlinear Curve Fitting without the logs |
|
380 | (27) |
|
7.1 Curve Fitting by Least Squares...without the logarithms |
|
|
381 | (25) |
|
7.1.1 Fitting data to Discrete Probability Distributions |
|
|
381 | (10) |
|
7.1.2 Fitting data to Continuous Probability Distributions |
|
|
391 | (8) |
|
7.1.3 Revisiting the Gamma Distribution Regression |
|
|
399 | (7) |
|
|
406 | (1) |
|
|
406 | (1) |
8 The ups and downs of Time Series Analysis |
|
407 | (63) |
|
8.1 The bits and bats...and buts of a Time Series |
|
|
408 | (3) |
|
8.1.1 Conducting a Time Series Analysis |
|
|
411 | (1) |
|
8.2 Alternative Time Series Models |
|
|
411 | (4) |
|
8.2.1 Additive/Subtractive Time Series Model |
|
|
412 | (1) |
|
8.2.2 Multiplicative Time Series Model |
|
|
413 | (2) |
|
8.3 Classical Decomposition: Determining the underlying trend |
|
|
415 | (22) |
|
8.3.1 See-Saw...Regression flaw? |
|
|
416 | (4) |
|
8.3.2 Moving Average Seasonal Smoothing |
|
|
420 | (2) |
|
8.3.3 Cumulative Average Seasonal Smoothing |
|
|
422 | (2) |
|
8.3.4 What happens when our world is not perfect? Do any of these trends work? |
|
|
424 | (6) |
|
8.3.5 Exponential trends and seasonal funnels |
|
|
430 | (6) |
|
|
436 | (1) |
|
8.4 Determining the seasonal variations by Classical Decomposition |
|
|
437 | (6) |
|
8.4.1 The Additive/Subtractive Model |
|
|
438 | (2) |
|
8.4.2 The Multiplicative Model |
|
|
440 | (3) |
|
8.5 Multi-Linear Regression: A holistic approach to Time Series? |
|
|
443 | (18) |
|
8.5.1 The Additive/Subtractive Linear Model |
|
|
444 | (5) |
|
8.5.2 The Additive/Subtractive Exponential Model |
|
|
449 | (3) |
|
8.5.3 The Multiplicative Linear Model |
|
|
452 | (4) |
|
8.5.4 The Multiplicative Exponential Model |
|
|
456 | (4) |
|
8.5.5 Multi-Linear Regression: Reviewing the options to make an informed decision |
|
|
460 | (1) |
|
8.6 Excel Solver technique for Time Series Analysis |
|
|
461 | (7) |
|
8.6.1 The Perfect World scenario |
|
|
462 | (3) |
|
8.6.2 The Real World scenario |
|
|
465 | (3) |
|
8.6.3 Wider examples of the Solver technique |
|
|
468 | (1) |
|
|
468 | (1) |
|
|
469 | (1) |
Glossary of estimating and forecasting terms |
|
470 | (19) |
Legend for Microsoft Excel Worked Example Tables in Greyscale |
|
489 | (2) |
Index |
|
491 | |