A spreadsheet Approach to Business Quantitative Methods
by
John Flaherty
Ric Lombardo
Paul Morgan
Basil deSilva
David Wilson
with contributions by:
William McCluskey
Richard Borst
Lloyd Williams
Hugh Williams
For further information contact:
John Flaherty
School of Marketing, RMIT
239 Bourke Street,
Melbourne, 3000
Australia
Phone 61 3 9925 5546
FAX: 61 3 9925 5559
Email: john.flaherty @rmit.edu.au
A Spreadsheet Approach to Business Quantitative Methods
This textbook represents a major revision of the previous
textbook "Quantitative Methods in Property" by the same
authors.
Several new chapters have been added and much of the existing
material has been refined and improved upon. A significant
improvement with the new edition is the explicit use of the
Excel spreadsheet throughout.
Some of the new chapters added include:
Selected Features and Functions in Microsoft Excel
The key Excel features used throughout the text and presented
in this chapter to focus attention of a number of some of the
more recent enhancements to the spreadsheet. A brief
introduction to the VBA programming language is included to
suggest ways of further harnessing the power of the
spreadsheet.
Simulation
The use of simulation as a decision and planning tool is
becoming more prevalent. Spreadsheets are ideal to introduce
many simulation concepts and to demonstrate their usefulness
in a wide variety of areas. When more specialised simulation
tools are required users can move to Excel plug-ins such as
@R1SK (not discussed in the text).
Geographic Information Systems
GIS is now a widely used tool in the property profession and
in the wider business community. Applications to department
store location are discussed in the text.
Supplementary resources
Solutions manual for all exercises
Web site containing examples and applications using Excel
Part I Building the Foundations
1. Mathematical Preliminaries
2. Selected Features and Functions in Microsoft Excel
3. Matrix Algebra
4. Introduction to Statistics
Part II Basic Statistical Concepts
5. Probability and Mathematical Expectation
6. Probability Distributions
7. Elements of Hypotheses Testing
8. Nonparametric Statistics
9. Analysis of Variance
Part III Regression and Time Series Models
10. Introduction to Regression Analysis
11. Multiple Regression
12. Data Problems and Residual Analysis in Regression
13. Time Series Forecasting
14. Advanced Time Series Models
PART IV Multivariate Analysis
15. Principal Component Analysis
16. Factor Analysis
Part V Heuristics and Optimisation
17. Simulation
18. Linear Programming
19. Transportation, Assignment, and Transshipment Problems
20. Network Analysis / Project Management
21. Dynamic Programming
22. Decision Theory and Expected Utility
23. Markov Chains and Input Output Analysis
24. Inventory Models
25. Queuing Theory
26. Artificial Neural Networks
27. Geographic Information Systems
Appendix - Answers to Selected Problems
- Statistical Tables
Index
A Spreadsheet Approach to Business Quantitative Methods
PART I Building the Foundations
Mathematical Preliminaries
1.1 Review of Basic Algebra
1.2 Functions
1.3 Exponents and Logarithms
1.4 The Mathematics of Finance
1.5 Introduction to Calculus
1.6 Maximization and Minimization of Functions
1.7 Summation Notation
References
Exercises
2. Selected Features and Functions in Microsoft Excel
2.1 Introduction
2.2 Using Excelto Chart Data
2.3 The Analysis ToolPak
2.4 Lookup and Reference Functions
2.5 Hyperlinks
2.6 Offset Function
2.7 Data Tables and Array Formulae
2.8 Using the Trend Function
2.9 Introductiont o Solver
2.10 GoalSeek
2.11 Recording a Macro
2.12 Creating Your Own Functions
2.12 Some Common Limitations in Excel
References
Exercises
3. Matrix Algebra
3.1 Elements of Matrix Algebra
3.2 Matrix Operations
3.3 Matrix Algebra Using Excel
3.4 The Determinant
3.5 Matrix Inversion
3.6 Rank, Traceand Orthogonality
3.7 Eigenvalues and Eigenvectors
3.8 Examples of Matrix Algebra Using Excel
References
Exercises
4.Introduction to Statistics
4.1 The Role of Statistics
4.2 Descriptive Statistics
4.3 Measures of Central Tendency
4.4 Dispersion of the Data Around the Mean
4.5 Measures of Relative Standing
4.6 Skewness and Kurtosis
References
Exercises
Part II Basic Statistical Concepts
5. Probability and Mathematical Expectation
5.1 Elementary Set Theory
5.2 Basic Counting Methods
5.3 Basic Concepts in Probability
5.4 Conditional Probability and Bayes' Theorem
5.5 Jointly Distributed Random Variables
5.6 The Expected Value of a Random Variable
5.7 Coefficient of Correlation
5.8 Elements of Portfolio Theory
Appendix - Mathematical Expectation Formulae
References
Exercises
6. Probability Distributions
6.1. Random Variable
6.2. Probability Distribution
6.3. Discrete Distribution - Binomial & Poission.
6.4. The Normal Distribution
6.5 The Exponential Distribution
6.6 The Erlang Distribution
6.7 The Chi-square Distribution
6.8 The t-distribution
6.9 The F-distribution
6.10 Sampling and the CLT References Exercises
7. Elements of Hypotheses Testing and Interval Estimation
7.1 Tests of Hypotheses for the Population Mean
7.2 Tests of Hypotheses for the Population Proportion
7.3 The P-value approach to hypotheses testing
7.4 Confidence Interval Estimation
7.5 Other Approaches to Testing Hypotheses
7.6 Quality Control (include Acceptance Sampling)
References
Exercises
8. Nonparametric Statistics
8.1 Introduction
8.2 DataTypes
8.3 Measurement Scales
8.4 Parametric Methods
8.5 NonParametric vs Parametric Methods
8.6 Contingency Tables
8.7 Wilcoxon Signed -Rank Test
8.8 Mann-Whitney Test
References
Exercises
9. Analysis of Variance
9.1 Introduction
9.2 ANOVA - Single Factor
9.3 ANOVA - Two Factor without Replication
9.4 ANOVA - Two Factor with Replication
9.5 Conclusion
ANOVA EXERCISES
Part III Regression and Yime Series Models
10. Regression Analysis
10.1 Introduction
10.2 Bivariate Regression
10.3 The Gauss-Markov Theorem and OLS Assumptions
10.4 Simple Regression_An Illustrative Example
10.5 Regression using Matrix Algebra
10.6 The Capital Asset Pricing Model
10.7 Estimating the Regression Equation Using Excel
References
Exercises
11. Multiple Regression
11.1 The Multiple Regression Model
11.2 An Example of Multiple Regression
11.3 Validation of the Equation
11.4 Analysis of Variance for the Regression
11.5 Model Selection Criteria
11.6 Dummy Variables In Regression
11.7 Comparing Two Regressions
11.8 Non Linear Regression Models
Appendix - Properties of the General Linear Model
References
Exercises
12. Data Problems and Residual Analysis in Regression
12.1 Multicollinearity
12.2 Autocorrelation
12 3 Heteroscedasticity
12.4 Residual Analysis to Detect Outliers
12.5 The Logit Model
12.6 Carrying out a Regression Project
References
Exercises
13. Time Series Forecasting
13.1 Introduction
13.2 Decomposition of a Time Series
13.3 Calculating a Seasonal Index Using Dummy Variables
13.4 Moving Average Smoothing Methods
13.5 ExponentialSmoothingMethods
13.6 Model Validation
References
Exercises
14. Advanced Time Series Models
14.1 Autocorrelation Analysis
14.2 Unit Roots and Cointegration
14.3 Box-Jenkins ARIMA Models
References
Exercises
Part IV Multivariate Analysis
1 5. Principal Component Analysis
15.1 Population Principal Components
15.2 Principal Components and Standardised Variables
15.3 Sample Principal Components
15.4 Application of Principal Components to Linear Regression
References Exercises
16. Factor Analysis
16.1 The Orthogonal Factor Model
16.2 MethodsofEstimation
16.3 Factor Rotation
References
Exercises
Part V Heuristics and Optimisation
1 7. Simulation
17.1 Introduction
17.2 Simulating The Price of Residential Units
17.3 Freezing Randomly Generated Numbers
17.4 Using The Spreadsheet to Generate Distributions Creating
Other Distributions Using Excel
17.5 Simulating Demand Using a Data Table
17.6 Monte Carlo Simulation of the Least Squares Model
References Exercises
18. Linear Programming
18.1 Introduction
18.2 The General LP Problem
18.3 Maximisation, Minimisation and the Duel
18.4 TheSimplexMethod
18.5 The Dual Simplex Algorithm
18.6 LP Applications in Property References Exercises
1 9. Transportation, Assignment, and Transshipment Problems
20. Network Analysis / Project Management
20.1 Basic Terminology
20.2 Shortest Route Problem
20.3 CPM and PERT
20.4 Earliest Time, Latest Time and Activity Slack
20.5 Activity Time Statistics and Project Completion Times
20.6 Time/Coat Tradeoff and Project Crashing References
Exercises
21. Dynamic Programming
21.1 Dynamic Programming for a Multistage Problem
21.2 Recursion and Bellman's Principle of Optimality
21.3 Formal Notation of Dynamic Programming
21.4 A DP Problem with a Multiplicative Recursive Relation
21.5 Other Types of DP Problems
References
Exercises
22. Decision Theory and Expected Utility
22.1 Basic Elements of The Decision Making Process
22.2 Maximin Criterion
22.3 Minimax Regret Criterion
22.4 Expected Monetary Value
22.5 Expected Utility Analysis
22.6 Prospect theory
22.7 Conclusion
References & Exercises
23. Markov Chains and Input Output Analysis
23.1 Input-Output Analysis
23.2 Markov Chain Analysis
23.3 The Use of Markov Chains for Optimal Decision Making
23.4 The Application of Markov Chains to Input Output Analysis
23.5 Using Excel and Minitab
References & Exercises
2 4. Inventory Models
24.1 Introduction
24.2 Derivation of the EOQ Formula
24.3 Quantity Discounts
24.4 When to Place an Order and Safety Stock
24.5 Lead Time Variation
24.6 Some Illustrative Examples
References & Exercises
2 5. Queuing Theory
26. Artificial Neural Networks and Genetic Algorithms
(McCloskey/Borst)
26.1 Introduction
26.2 Main Elements of a Neural Network System
26.3 Case Study
2 7 Geographic Information Systems (Lloyd & Hugh Williams)
Appendix - Answers to Selected Problems
- Statistical Tables
Index
