Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA

PART I. Introduction to Modelling, Core Themes and Best Practices
1. Models of Models 3
2. Using Models in Decision Support 7
3. Core Competencies and Best Practices: Meta-themes 15

PART II. Model Design and Planning 23
4. Defining Sensitivity and Flexibility Requirements 25
5. Database Versus Formulae-driven Approaches 37
6. Designing the Workbook Structure 47

PART III. Model Building, Testing and Auditing 57
7. Creating Transparency: Formula Structure, Flow and Format 59
8. Building Robust and Transparent Formulae 79
9. Choosing Excel Functions for Transparency, Flexibility and Efficiency 105
10. Dealing with Circularity 117
11. Model Review, Auditing and Validation 143

PART IV. Sensitivity and Scenario Analysis, Simulation and Optimisation 153
12. Sensitivity and Scenario Analysis: Core Techniques 155
13. Using GoalSeek and Solver 163
14. Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171
15. Introduction to Simulation and Optimisation 177
16. The Modelling of Risk and Uncertainty, and Using Simulation 187

PART V. Excel Functions and Functionality 199
17. Core Arithmetic and Logical Functions 201
18. Array Functions and Formulae 217
19. Mathematical Functions 229
20. Financial Functions 243
21. Statistical Functions 257
22. Information Functions 299
23. Date and Time Functions 307
24. Text Functions and Functionality 313
25. Lookup and Reference Functions 325
26. Filters, Database Functions and PivotTables 355
27. Selected Short-cuts and Other Features 387

PART VI. Foundations of VBA and Macros 395
28. Getting Started 397
29. Working with Objects and Ranges 413
30. Controlling Execution

31. Writing Robust Code
32. Manipulation and Analysis of Data Sets with VBA
33. User-defined Functions