Principles of Financial Modelling
Model Design and Best Practices Using Excel and VBA
1st Edition
Published on 11. May 2018
Book
Hardback
544 pages
978-1-118-90401-5 (ISBN)
Description
The comprehensive, broadly-applicable, real-world guide to financial modelling
Principles of Financial Modelling - Model Design and Best Practices Using Excel and VBAcovers the full spectrum of financial modelling tools and techniques in order to provide practical skills that are grounded in real-world applications. Based on rigorously-tested materials created for consulting projects and for training courses, this book demonstrates how to plan, design and build financial models that are flexible, robust, transparent, and highly applicable to a wide range of planning, forecasting and decision-support contexts. This book integrates theory and practice to provide a high-value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. Highlights of its content include extensive coverage of:
Model design and best practices, including the optimisation of data structures and layout, maximising transparency, balancing complexity with flexibility, dealing with circularity, model audit and error-checking
Sensitivity and scenario analysis, simulation, and optimisation
Data manipulation and analysis
The use and choice of Excel functions and functionality, including advanced functions and those from all categories, as well as of VBA and its key areas of application within financial modelling
The companion website provides approximately 235 Excel files (screen-clips of most of which are shown in the text), which demonstrate key principles in modelling, as well as providing many examples of the use of Excel functions and VBA macros. These facilitate learning and have a strong emphasis on practical solutions and direct real-world application.
For practical instruction, robust technique and clear presentation, Principles of Financial Modelling is the premier guide to real-world financial modelling from the ground up. It provides clear instruction applicable across sectors, settings and countries, and is presented in a well-structured and highly-developed format that is accessible to people with different backgrounds.
Principles of Financial Modelling - Model Design and Best Practices Using Excel and VBAcovers the full spectrum of financial modelling tools and techniques in order to provide practical skills that are grounded in real-world applications. Based on rigorously-tested materials created for consulting projects and for training courses, this book demonstrates how to plan, design and build financial models that are flexible, robust, transparent, and highly applicable to a wide range of planning, forecasting and decision-support contexts. This book integrates theory and practice to provide a high-value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. Highlights of its content include extensive coverage of:
Model design and best practices, including the optimisation of data structures and layout, maximising transparency, balancing complexity with flexibility, dealing with circularity, model audit and error-checking
Sensitivity and scenario analysis, simulation, and optimisation
Data manipulation and analysis
The use and choice of Excel functions and functionality, including advanced functions and those from all categories, as well as of VBA and its key areas of application within financial modelling
The companion website provides approximately 235 Excel files (screen-clips of most of which are shown in the text), which demonstrate key principles in modelling, as well as providing many examples of the use of Excel functions and VBA macros. These facilitate learning and have a strong emphasis on practical solutions and direct real-world application.
For practical instruction, robust technique and clear presentation, Principles of Financial Modelling is the premier guide to real-world financial modelling from the ground up. It provides clear instruction applicable across sectors, settings and countries, and is presented in a well-structured and highly-developed format that is accessible to people with different backgrounds.
More details
Other editions
Additional editions
E-Book
03/2018
1st Edition
Wiley-ISTE
€67.99
Available for download
E-Book
03/2018
1st Edition
Wiley-ISTE
€67.99
Available for download
Previous edition
Michael Rees
Financial Modelling in Practice
A Concise Guide for Intermediate and Advanced Level with CD ROM
Book
10/2008
Wiley
€130.50
Shipment within 10-20 days
Person
MICHAEL REES, D.PHIL., MBA, operates globally to help senior executives to solve their most complex problems in the areas of decision support, business strategy, value-creation, risk assessment, and optimisation. He combines practical experience from top firms with an exceptional analytic record, and is among the world's leading authors and instructors in the field of financial and risk modelling. His special interest is in cases where issues in strategy, business economics, and valuation are best addressed using practical advanced quantitative approaches.
He has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark.
He has approximately 30 years' business and finance experience, in many sectors, including oil, gas, energy and resources, private equity, health care, biotechnology, chemicals, construction, engineering, and insurance.
He has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark.
He has approximately 30 years' business and finance experience, in many sectors, including oil, gas, energy and resources, private equity, health care, biotechnology, chemicals, construction, engineering, and insurance.
Content
Preface xxv
About the Author xxvii
About the Website xxix
Part One Introduction to Modelling, Core Themes and Best Practices 1
Chapter 1 Models of Models 3
Introduction 3
Context and Objectives 3
The Stages of Modelling 3
Backward Thinking and Forward Calculation Processes 4
Chapter 2 Using Models in Decision Support 7
Introduction 7
Benefits of Using Models 7
Providing Numerical Information 7
Capturing Influencing Factors and Relationships 7
Generating Insight and Forming Hypotheses 8
Decision Levers, Scenarios, Uncertainties, Optimisation, Risk Mitigation and Project Design 8
Improving Working Processes, Enhanced Communications and Precise Data Requirements 9
Challenges in Using Models 9
The Nature of Model Error 9
Inherent Ambiguity and Circularity of Reasoning 10
Inconsistent Scope or Alignment of Decision and Model 10
The Presence on Biases, Imperfect Testing, False Positives and Negatives 11
Balancing Intuition with Rationality 11
Lack of Data or Insufficient Understanding of a Situation 12
Overcoming Challenges: Awareness, Actions and Best Practices 13
Chapter 3 Core Competencies and Best Practices: Meta-themes 15
Introduction 15
Key Themes 15
Decision-support Role, Objectives, Outputs and Communication 16
Application Knowledge and Understanding 17
Skills with Implementation Platform 17
Defining Sensitivity and Flexibility Requirements 18
Designing Appropriate Layout, Input Data Structures and Flow 20
Ensuring Transparency and Creating a User-friendly Model 20
Integrated Problem-solving Skills 21
Part Two Model Design and Planning 23
Chapter 4 Defining Sensitivity and Flexibility Requirements 25
Introduction 25
Key Issues for Consideration 25
Creating a Focus on Objectives and Their Implications 26
Sensitivity Concepts in the Backward Thought and Forward Calculation
Processes 26
Time Granularity 30
Level of Detail on Input Variables 30
Sensitising Absolute Values or Variations from Base Cases 31
Scenarios Versus Sensitivities 32
Uncertain Versus Decision Variables 33
Increasing Model Validity Using Formulae 34
Chapter 5 Database Versus Formulae-driven Approaches 37
Introduction 37
Key Issues for Consideration 37
Separating the Data, Analysis and Presentation (Reporting) Layers 37
The Nature of Changes to Data Sets and Structures 39
Focus on Data or Formulae? 40
Practical Example 42
Chapter 6 Designing the Workbook Structure 47
Introduction 47
Designing Workbook Models with Multiple Worksheets 47
Linked Workbooks 47
Multiple Worksheets: Advantages and Disadvantages 48
Generic Best Practice Structures 49
The Role of Multiple Worksheets in Best Practice Structures 49
Type I: Single Worksheet Models 50
Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50
Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51
Further Comparative Comments 51
Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52
Multi-sheet or "Three Dimensional" Formulae 53
Using Excel's Data/Consolidation Functionality 54
Consolidating from Several Sheets into a Database Using a Macro 55
User-defined Functions 56
Part Three Model Building, Testing and Auditing 57
Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59
Introduction 59
Approaches to Identifying the Drivers of Complexity 59
Taking the Place of a Model Auditor 59
Example: Creating Complexity in a Simple Model 60
Core Elements of Transparent Models 61
Optimising Audit Paths 62
Creating Short Audit Paths Using Modular Approaches 63
Creating Short Audit Paths Using Formulae Structure and Placement 67
Optimising Logical Flow and the Direction of the Audit Paths 68
Identifying Inputs, Calculations and Outputs: Structure and Formatting 69
The Role of Formatting 70
Colour-coding of Inputs and Outputs 70
Basic Formatting Operations 73
Conditional Formatting 73
Custom Formatting 75
Creating Documentation, Comments and Hyperlinks 76
Chapter 8 Building Robust and Transparent Formulae 79
Introduction 79
General Causes of Mistakes 79
Insufficient Use of General Best Practices Relating to Flow, Formatting,
Audit Paths 79
Insufficient Consideration Given to Auditability and Other Potential Users 79
Overconfidence, Lack of Checking and Time Constraints 80
Sub-optimal Choice of Functions 80
Inappropriate Use or Poor Implementation of Named Ranges, Circular
References or Macros 80
Examples of Common Mistakes 80
Referring to Incorrect Ranges or To Blank Cells 80
Non-transparent Assumptions, Hidden Inputs and Labels 82
Overlooking the Nature of Some Excel Function Values 82
Using Formulae Which are Inconsistent Within a Range 83
Overriding Unforeseen Errors with IFERROR 84
Models Which are Correct in Base Case but Not in Others 85
Incorrect Modifications when Working with Poor Models 85
The Use of Named Ranges 85
Mechanics and Implementation 86
Disadvantages of Using Named Ranges 86
Advantages and Key Uses of Named Ranges 90
Approaches to Building Formulae, to Testing, Error Detection and Management 91
Checking Behaviour and Detecting Errors Using Sensitivity Testing 91
Using Individual Logic Steps 93
Building and Splitting Compound Formulae 94
Using Absolute Cell Referencing Only Where Necessary 96
Limiting Repeated or Unused Logic 96
Using Breaks to Test Calculation Paths 97
Using Excel Error Checking Rules 97
Building Error-checking Formulae 98
Handling Calculation Errors Robustly 100
Restricting Input Values Using Data Validation 100
Protecting Ranges 101
Dealing with Structural Limitations: Formulae and Documentation 102
Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105
Introduction 105
Key Considerations 105
Direct Arithmetic or Functions, and Individual Cells or Ranges? 105
IF Versus MIN/MAX 107
Embedded IF Statements 109
Short Forms of Functions 111
Text Versus Numerical Fields 112
SUMIFS with One Criterion 112
Including Only Specific Items in a Summation 113
AGGREGATE and SUBTOTAL Versus Individual Functions 114
Array Functions or VBA User-defined Functions? 115
Volatile Functions 115
Effective Choice of Lookup Functions 116
Chapter 10 Dealing with Circularity 117
Introduction 117
The Drivers and Nature of Circularities 117
Circular (Equilibrium or Self-regulating) Inherent Logic 117
Circular Formulae (Circular References) 118
Generic Types of Circularities 119
Resolving Circular Formulae 119
Correcting Mistakes that Result in Circular Formulae 120
Avoiding a Logical Circularity by Modifying the Model Specification 120
Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121
Resolving a Circularity Using Iterative Methods 122
Iterative Methods in Practice 123
Excel's Iterative Method 123
Creating a Broken Circular Path: Key Steps 125
Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126
Practical Example 128
Using Excel Iterations to Resolve Circular References 129
Using a Macro to Resolve a Broken Circular Path 129
Algebraic Manipulation: Elimination of Circular References 130
Altered Model 1: No Circularity in Logic or in Formulae 130
Altered Model 2: No Circularity in Logic in Formulae 131
Selection of Approach to Dealing with Circularities: Key Criteria 131
Model Accuracy and Validity 132
Complexity and Transparency 133
Non-convergent Circularities 134
Potential for Broken Formulae 138
Calculation Speed 140
Ease of Sensitivity Analysis 140
Conclusions 141
Chapter 11 Model Review, Auditing and Validation 143
Introduction 143
Objectives 143
(Pure) Audit 143
Validation 144
Improvement, Restructuring or Rebuild 145
Processes, Tools and Techniques 146
Avoiding Unintentional Changes 146
Developing a General Overview and Then Understanding the Details 147
Testing and Checking the Formulae 151
Using a Watch Window and Other Ways to Track Values 151
Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153
Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155
Introduction 155
Overview of Sensitivity-related Techniques 155
DataTables 156
Overview 156
Implementation 157
Limitations and Tips 157
Practical Applications 160
Example: Sensitivity of Net Present Value to Growth Rates 160
Example: Implementing Scenario Analysis 160
Chapter 13 Using GoalSeek and Solver 163
Introduction 163
Overview of GoalSeek and Solver 163
Links to Sensitivity Analysis 163
Tips, Tricks and Limitations 163
Practical Applications 164
Example: Breakeven Analysis of a Business 165
Example: Threshold Investment Amounts 166
Example: Implied Volatility of an Option 167
Example: Minimising Capital Gains Tax Liability 167
Example: Non-linear Curve Fitting 169
Chapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171
Introduction 171
Practical Applications 172
Example: Running Sensitivity Analysis Using a Macro 172
Example: Running Scenarios Using a Macro 173
Example: Using a Macro to Run Breakeven Analysis with GoalSeek 173
Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175
Chapter 15 Introduction to Simulation and Optimisation 177
Introduction 177
The Links Between Sensitivity and Scenario Analysis,
Simulation and Optimisation 177
The Combinatorial Effects of Multiple Possible Input Values 177
Controllable Versus Non-controllable: Choice Versus
Uncertainty of Input Values 178
Practical Example: A Portfolio of Projects 179
Description 179
Optimisation Context 180
Risk or Uncertainty Context Using Simulation 180
Further Aspects of Optimisation Modelling 182
Structural Choices 182
Uncertainty 183
Integrated Approaches to Optimisation 183
Modelling Issues and Tools 184
Chapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187
Introduction 187
The Meaning, Origins and Uses of Monte Carlo Simulation 187
Definition and Origin 187
Limitations of Sensitivity and Scenario Approaches 188
Key Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189
The Nature of Model Outputs 190
The Applicability of Simulation Methods 190
Key Process and Modelling Steps in Risk Modelling 191
Risk Identification 191
Risk Mapping and the Role of the Distribution of Input Values 191
The Modelling Context and the Meaning of Input Distributions 192
The Effect of Dependencies Between Inputs 192
Random Numbers and the Required Number of Recalculations or Iterations 193
Using Excel and VBA to Implement Risk and Simulation Models 194
Generation of Random Samples 194
Repeated Recalculations and Results Storage 195
Example: Cost Estimation with Uncertainty and Event Risks Using Excel/VBA 196
Using Add-ins to Implement Risk and Simulation Models 196
Benefits of Add-ins 196
Example: Cost Estimation with Uncertainty and Event Risks Using @RISK 197
Part Five Excel Functions and Functionality 199
Chapter 17 Core Arithmetic and Logical Functions 201
Introduction 201
Practical Applications 201
Example: IF, AND, OR, NOT 202
Example: MIN, MAX, MINA, MAXA 204
Example: MINIFS and MAXIFS 204
Example: COUNT, COUNTA, COUNTIF and Similar Functions 205
Example: SUM, AVERAGE, AVERAGEA 206
Example: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206
Example: PRODUCT 207
Example: SUMPRODUCT 209
Example: SUBTOTAL 209
Example: AGGREGATE 210
Example: IFERROR 212
Example: SWITCH 215
Chapter 18 Array Functions and Formulae 217
Introduction 217
Functions and Formulae: Definitions 217
Implementation 217
Advantages and Disadvantages 218
Practical Applications: Array Functions 218
Example: Capex and Depreciation Schedules Using TRANSPOSE 218
Example: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218
Example: Cost Allocation Using Matrix Multiplication Using MMULT 219
Example: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220
Example: Summing Powers of Integers from 1 Onwards 222
Practical Applications: Array Formulae 225
Example: Finding First Positive Item in a List 225
Example: Find a Conditional Maximum 226
Example: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227
Chapter 19 Mathematical Functions 229
Introduction 229
Practical Applications 229
Example: EXP and LN 229
Example: ABS and SIGN 232
Example: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233
Example: MROUND, CEILING.MATH and FLOOR.MATH 235
Example: MOD 236
Example: SQRT and POWER 236
Example: FACT and COMBIN 237
Example: RAND() 238
Example: SINE, ASIN, DEGREES and PI() 239
Example: BASE and DECIMAL 241
Chapter 20 Financial Functions 243
Introduction 243
Practical Applications 243
Example: FVSCHEDULE 244
Example: FV and PV 244
Example: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246
Example: NPV and IRR for a Buy or Lease Decision 248
Example: SLN, DDB and VDB 250
Example: YIELD 252
Example: Duration of Cash Flows 252
Example: DURATION and MDURATION 253
Example: PDURATION and RRI 254
Other Financial Functions 255
Chapter 21 Statistical Functions 257
Introduction 257
Practical Applications: Position, Ranking and Central Values 258
Example: Calculating Mean and Mode 258
Example: Dynamic Sorting of Data Using LARGE 260
Example: RANK.EQ 261
Example: RANK.AVG 262
Example: Calculating Percentiles 262
Example: PERCENTRANK-type Functions 263
Practical Applications: Spread and Shape 264
Example: Generating a Histogram of Returns Using FREQUENCY 265
Example: Variance, Standard Deviation and Volatility 267
Example: Skewness and Kurtosis 271
Example: One-sided Volatility (Semi-deviation) 272
Practical Applications: Co-relationships and Dependencies 273
Example: Scatter Plots (X-Y Charts) and Measuring Correlation 274
Example: More on Correlation Coefficients and Rank Correlation 275
Example: Measuring Co-variances 277
Example: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277
Practical Applications: Probability Distributions 280
Example: Likelihood of a Given Number of Successes of an Oil Exploration Process 282
Example: Frequency of Outcomes Within One or Two Standard Deviations 283
Example: Creating Random Samples from Probability Distributions 283
Example: User-defined Inverse Functions for Random Sampling 284
Example: Values Associated with Probabilities for a Binomial Process 285
Example: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285
Example: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287
Example: Confidence Intervals for the Standard Deviation Using Chi-squared 289
Example: Confidence Interval for the Slope of Regression Line (or Beta) 289
Practical Applications: More on Regression Analysis and Forecasting 291
Example: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291
Example: Using LINEST to Perform Multiple Regression 292
Example: Using LOGEST to Find Exponential Fits 293
Example: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294
Example: Linear Forecasting Using FORECAST.LINEAR 295
Example: Forecasting Using the FORECAST.ETS Set of Functions 296
Chapter 22 Information Functions 299
Introduction 299
Practical Applications 300
Example: In-formula Comments Using ISTEXT, ISNUMBER or N 300
Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300
Example: Detecting Consistency of Data in a Database 301
Example: Consistent use of "N/A" in Models 301
Example: Applications of the INFO and CELL Functions: An Overview 303
Example: Creating Updating Labels that Refer to Data or Formulae 303
Example: Showing the User Which Recalculation Mode the File Is On 305
Example: Finding the Excel Version Used and Creating Backward Compatible Formulae 305
Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306
Chapter 23 Date and Time Functions 307
Introduction 307
Practical Applications 308
Example: Task Durations, Resource and Cost Estimation 308
Example: Keeping Track of Bookings, Reservations or Other Activities 308
Example: Creating Precise Time Axes 309
Example: Calculating the Year and Month of a Date 309
Example: Calculating the Quarter in Which a Date Occurs 310
Example: Creating Time-based Reports and Models from Data Sets 311
Example: Finding Out on What Day of the Week You Were Born 311
Example: Calculating the Date of the Last Friday of Every Month 311
Example: the DATEDIF Function and Completed Time Periods 312
Chapter 24 Text Functions and Functionality 313
Introduction 313
Practical Applications 314
Example: Joining Text Using CONCAT and TEXTJOIN 314
Example: Splitting Data Using the Text-to-columns Wizard 315
Example: Converting Numerical Text to Numbers 316
Example: Dynamic Splitting Text into Components I 316
Example: Dynamic Splitting Text into Components II 317
Example: Comparing LEFT, RIGHT, MID and LEN 317
Example: Dynamic Splitting Text into Components III 318
Example: Comparing FIND and SEARCH 319
Example: the UPPER and LOWER Functions 319
Example: the PROPER Function 319
Example: the EXACT Function 320
Example: Comparing REPLACE with SUBSTITUTE 320
Example: the REPT Function 320
Example: the CLEAN and TRIM Functions 321
Example: Updating Model Labels and Graph Titles 322
Example: Creating Unique Identifiers or Keys for Data Matching 323
Chapter 25 Lookup and Reference Functions 325
Introduction 325
Practical Applications: Basic Referencing Processes 326
Example: the ROW and COLUMN Functions 326
Example: the ROWS and COLUMNS Functions 327
Example: Use of the ADDRESS Function and the Comparison with CELL 327
Practical Applications: Further Referencing Processes 328
Example: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328
Example: Charts that Can Use Multiple or Flexible Data Sources 330
Example: Reversing and Transposing Data Using INDEX or OFFSET 331
Example: Shifting Cash Flows or Other Items over Time 334
Example: Depreciation Schedules with Triangle Calculations 334
Practical Applications: Combining Matching and Reference Processes 335
Example: Finding the Period in Which a Condition is Met Using MATCH 335
Example: Finding Non-contiguous Scenario Data Using Matching Keys 336
Example: Creating and Finding Matching Text Fields or Keys 336
Example: Combining INDEX with MATCH 337
Example: Comparing INDEX-MATCH with V- and HLOOKUP 338
Example: Comparing INDEX-MATCH with LOOKUP 343
Example: Finding the Closest Matching Value Using Array and Other Function Combinations 344
Practical Applications: More on the OFFSET Function and Dynamic Ranges 345
Example: Flexible Ranges Using OFFSET (I) 345
Example: Flexible Ranges Using OFFSET (II) 346
Example: Flexible Ranges Using OFFSET (III) 347
Example: Flexible Ranges Using OFFSET (IV) 347
Practical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349
Example: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349
Example: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351
Example: Other Uses of INDIRECT - Cascading Drop-down Lists 352
Practical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352
Example: Model Navigation Using Named Ranges and Hyperlinks 353
Chapter 26 Filters, Database Functions and PivotTables 355
Introduction 355
Issues Common to Working with Sets of Data 356
Cleaning and Manipulating Source Data 356
Static or Dynamic Queries 356
Creation of New Fields or Complex Filters? 357
Excel Databases and Tables 357
Automation Using Macros 359
Practical Applications: Filters 359
Example: Applying Filters and Inspecting Data for Errors or Possible Corrections 359
Example: Identification of Unique Items and Unique Combinations 362
Example: Using Filters to Remove Blanks or Other Specified Items 363
Example: Extraction of Data Using Filters 365
Example: Adding Criteria Calculations to the Data Set 365
Example: Use of Tables 366
Example: Extraction of Data Using Advanced Filters 369
Practical Applications: Database Functions 370
Example: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370
Example: Implementing a Between Query 371
Example: Implementing Multiple Queries 371
Practical Applications: PivotTables 373
Example: Exploring Summary Values of Data Sets 373
Example: Exploring Underlying Elements of the Summary Items 376
Example: Adding Slicers 376
Example: Timeline Slicers 378
Example: Generating Reports Which Ignore Errors or Other Specified Items 380
Example: Using the GETPIVOTDATA Functions 380
Example: Creating PivotCharts 382
Example: Using the Excel Data Model to Link Tables 383
Chapter 27 Selected Short-cuts and Other Features 387
Introduction 387
Key Short-cuts and Their Uses 387
Entering and Modifying Data and Formulae 388
Formatting 390
Auditing, Navigation and Other Items 391
Excel KeyTips 393
Other Useful Excel Tools and Features 393
Sparklines 393
The Camera Tool 393
Part Six Foundations of VBA and Macros 395
Chapter 28 Getting Started 397
Introduction 397
Main Uses of VBA 397
Task Automation 398
Creating User-defined Functions 398
Detecting and Reacting to Model Events 398
Enhancing or Managing the User Interface 399
Application Development 399
Core Operations 399
Adding the Developer Tab to Excel's Toolbar 399
The Visual Basic Editor 399
Recording Macros 401
Typical Adaptations Required When Using Recorded Code 402
Writing Code 403
Running Code 404
Debugging Techniques 405
Simple Examples 406
Example: Using Excel Cell Values in VBA 406
Example: Using Named Excel Ranges for Robustness and Flexibility 407
Example: Placing a Value from VBA Code into an Excel Range 408
Example: Replacing Copy/Paste with an Assignment 409
Example: A Simple User-defined Function 409
Example: Displaying a Message when a Workbook is Opened 410
Chapter 29 Working with Objects and Ranges 413
Introduction 413
Overview of the Object Model 413
Objects, Properties, Methods and Events 413
Object Hierarchies and Collections 414
Using Set. . .=. . . . 415
Using the With. . .End With Construct 415
Finding Alternatives to the Selection or Activation of Ranges and Objects 416
Working with Range Objects: Some Key Elements 416
Basic Syntax Possibilities and Using Named Ranges 416
Named Ranges and Named Variables 417
The CurrentRegion Property 417
The xlCellTypeLastCell Property 418
Worksheet Names and Code Names 419
The UsedRange Property 419
The Cells Property 420
The Offset Property 421
The Union Method 421
InputBox and MsgBox 421
Application.InputBox 422
Defining Multi-cell Ranges 422
Using Target to React to Worksheet Events 422
Using Target to React to Workbook Events 423
Chapter 30 Controlling Execution 425
Introduction 425
Core Topics in Overview 425
Input Boxes and Message Boxes 425
For. . .Next Loops 425
For Each. . . In. . .Next 426
If. . .Then 427
Select Case. . .End Select 427
GoTo 428
Do. . .While/Until. . .Loop 428
Calculation and Calculate 429
Screen Updating 432
Measuring Run Time 432
Displaying Alerts 433
Accessing Excel Worksheet Functions 433
Executing Procedures Within Procedures 434
Accessing Add-ins 435
Practical Applications 435
Example: Numerical Looping 435
Example: Listing the Names of All Worksheets in a Workbook 436
Example: Adding a New Worksheet to a Workbook 437
Example: Deleting Specific Worksheets from a Workbook 437
Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438
Chapter 31 Writing Robust Code 441
Introduction 441
Key Principles 441
From the Specific to the General 441
Adapting Recorded Code for Robustness 442
Event Code 442
Comments and Indented Text 442
Modular Code 443
Passing Arguments ByVal or ByRef 443
Full Referencing 445
Using Worksheet Code Numbers 447
Assignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447
Working with Ranges Instead of Individual Cells 448
Data Types and Variable Declaration 448
Choice of Names 449
Working with Arrays in VBA 450
Understanding Error Codes: An Introduction 451
Further Approaches to Testing, Debugging and Error-handling 452
General Techniques 452
Debugging Functions 453
Implementing Error-handling Procedures 454
Chapter 32 Manipulation and Analysis of Data Sets with VBA 455
Introduction 455
Practical Applications 455
Example: Working Out the Size of a Range 455
Example: Defining the Data Set at Run Time Based on User Input 457
Example: Working Out the Position of a Data Set Automatically 457
Example: Reversing Rows (or Columns) of Data I: Placement in a New Range 459
Example: Reversing Rows (or Columns) of Data II: In Place 460
Example: Automation of Other Data-related Excel Procedures 461
Example: Deleting Rows Containing Blank Cells 462
Example: Deleting Blank Rows 463
Example: Automating the Use of Filters to Remove Blanks or Other Specified Items 464
Example: Performing Multiple Database Queries 468
Example: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469
Chapter 33 User-defined Functions 473
Introduction 473
Benefits of Creating User-defined Functions 473
Syntax and Implementation 474
Practical Applications 475
Example: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476
Example: A Wrapper to Access the Latest Excel Function Version 477
Example: Replication of IFERROR for Compatibility with Excel 2003 478
Example: Sum of Absolute Errors 479
Example: Replacing General Excel Calculation Tables or Ranges 480
Example: Using Application.Caller to Generate a Time Axis as an Array Function 480
Example: User-defined Array Functions in Rows and Columns 482
Example: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484
Example: Sheet Reference Functions 485
Example: Statistical Moments when Frequencies Are Known 487
Example: Rank Order Correlation 489
Example: Semi-deviation of a Data Set 491
Index 493
About the Author xxvii
About the Website xxix
Part One Introduction to Modelling, Core Themes and Best Practices 1
Chapter 1 Models of Models 3
Introduction 3
Context and Objectives 3
The Stages of Modelling 3
Backward Thinking and Forward Calculation Processes 4
Chapter 2 Using Models in Decision Support 7
Introduction 7
Benefits of Using Models 7
Providing Numerical Information 7
Capturing Influencing Factors and Relationships 7
Generating Insight and Forming Hypotheses 8
Decision Levers, Scenarios, Uncertainties, Optimisation, Risk Mitigation and Project Design 8
Improving Working Processes, Enhanced Communications and Precise Data Requirements 9
Challenges in Using Models 9
The Nature of Model Error 9
Inherent Ambiguity and Circularity of Reasoning 10
Inconsistent Scope or Alignment of Decision and Model 10
The Presence on Biases, Imperfect Testing, False Positives and Negatives 11
Balancing Intuition with Rationality 11
Lack of Data or Insufficient Understanding of a Situation 12
Overcoming Challenges: Awareness, Actions and Best Practices 13
Chapter 3 Core Competencies and Best Practices: Meta-themes 15
Introduction 15
Key Themes 15
Decision-support Role, Objectives, Outputs and Communication 16
Application Knowledge and Understanding 17
Skills with Implementation Platform 17
Defining Sensitivity and Flexibility Requirements 18
Designing Appropriate Layout, Input Data Structures and Flow 20
Ensuring Transparency and Creating a User-friendly Model 20
Integrated Problem-solving Skills 21
Part Two Model Design and Planning 23
Chapter 4 Defining Sensitivity and Flexibility Requirements 25
Introduction 25
Key Issues for Consideration 25
Creating a Focus on Objectives and Their Implications 26
Sensitivity Concepts in the Backward Thought and Forward Calculation
Processes 26
Time Granularity 30
Level of Detail on Input Variables 30
Sensitising Absolute Values or Variations from Base Cases 31
Scenarios Versus Sensitivities 32
Uncertain Versus Decision Variables 33
Increasing Model Validity Using Formulae 34
Chapter 5 Database Versus Formulae-driven Approaches 37
Introduction 37
Key Issues for Consideration 37
Separating the Data, Analysis and Presentation (Reporting) Layers 37
The Nature of Changes to Data Sets and Structures 39
Focus on Data or Formulae? 40
Practical Example 42
Chapter 6 Designing the Workbook Structure 47
Introduction 47
Designing Workbook Models with Multiple Worksheets 47
Linked Workbooks 47
Multiple Worksheets: Advantages and Disadvantages 48
Generic Best Practice Structures 49
The Role of Multiple Worksheets in Best Practice Structures 49
Type I: Single Worksheet Models 50
Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50
Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51
Further Comparative Comments 51
Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52
Multi-sheet or "Three Dimensional" Formulae 53
Using Excel's Data/Consolidation Functionality 54
Consolidating from Several Sheets into a Database Using a Macro 55
User-defined Functions 56
Part Three Model Building, Testing and Auditing 57
Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59
Introduction 59
Approaches to Identifying the Drivers of Complexity 59
Taking the Place of a Model Auditor 59
Example: Creating Complexity in a Simple Model 60
Core Elements of Transparent Models 61
Optimising Audit Paths 62
Creating Short Audit Paths Using Modular Approaches 63
Creating Short Audit Paths Using Formulae Structure and Placement 67
Optimising Logical Flow and the Direction of the Audit Paths 68
Identifying Inputs, Calculations and Outputs: Structure and Formatting 69
The Role of Formatting 70
Colour-coding of Inputs and Outputs 70
Basic Formatting Operations 73
Conditional Formatting 73
Custom Formatting 75
Creating Documentation, Comments and Hyperlinks 76
Chapter 8 Building Robust and Transparent Formulae 79
Introduction 79
General Causes of Mistakes 79
Insufficient Use of General Best Practices Relating to Flow, Formatting,
Audit Paths 79
Insufficient Consideration Given to Auditability and Other Potential Users 79
Overconfidence, Lack of Checking and Time Constraints 80
Sub-optimal Choice of Functions 80
Inappropriate Use or Poor Implementation of Named Ranges, Circular
References or Macros 80
Examples of Common Mistakes 80
Referring to Incorrect Ranges or To Blank Cells 80
Non-transparent Assumptions, Hidden Inputs and Labels 82
Overlooking the Nature of Some Excel Function Values 82
Using Formulae Which are Inconsistent Within a Range 83
Overriding Unforeseen Errors with IFERROR 84
Models Which are Correct in Base Case but Not in Others 85
Incorrect Modifications when Working with Poor Models 85
The Use of Named Ranges 85
Mechanics and Implementation 86
Disadvantages of Using Named Ranges 86
Advantages and Key Uses of Named Ranges 90
Approaches to Building Formulae, to Testing, Error Detection and Management 91
Checking Behaviour and Detecting Errors Using Sensitivity Testing 91
Using Individual Logic Steps 93
Building and Splitting Compound Formulae 94
Using Absolute Cell Referencing Only Where Necessary 96
Limiting Repeated or Unused Logic 96
Using Breaks to Test Calculation Paths 97
Using Excel Error Checking Rules 97
Building Error-checking Formulae 98
Handling Calculation Errors Robustly 100
Restricting Input Values Using Data Validation 100
Protecting Ranges 101
Dealing with Structural Limitations: Formulae and Documentation 102
Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105
Introduction 105
Key Considerations 105
Direct Arithmetic or Functions, and Individual Cells or Ranges? 105
IF Versus MIN/MAX 107
Embedded IF Statements 109
Short Forms of Functions 111
Text Versus Numerical Fields 112
SUMIFS with One Criterion 112
Including Only Specific Items in a Summation 113
AGGREGATE and SUBTOTAL Versus Individual Functions 114
Array Functions or VBA User-defined Functions? 115
Volatile Functions 115
Effective Choice of Lookup Functions 116
Chapter 10 Dealing with Circularity 117
Introduction 117
The Drivers and Nature of Circularities 117
Circular (Equilibrium or Self-regulating) Inherent Logic 117
Circular Formulae (Circular References) 118
Generic Types of Circularities 119
Resolving Circular Formulae 119
Correcting Mistakes that Result in Circular Formulae 120
Avoiding a Logical Circularity by Modifying the Model Specification 120
Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121
Resolving a Circularity Using Iterative Methods 122
Iterative Methods in Practice 123
Excel's Iterative Method 123
Creating a Broken Circular Path: Key Steps 125
Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126
Practical Example 128
Using Excel Iterations to Resolve Circular References 129
Using a Macro to Resolve a Broken Circular Path 129
Algebraic Manipulation: Elimination of Circular References 130
Altered Model 1: No Circularity in Logic or in Formulae 130
Altered Model 2: No Circularity in Logic in Formulae 131
Selection of Approach to Dealing with Circularities: Key Criteria 131
Model Accuracy and Validity 132
Complexity and Transparency 133
Non-convergent Circularities 134
Potential for Broken Formulae 138
Calculation Speed 140
Ease of Sensitivity Analysis 140
Conclusions 141
Chapter 11 Model Review, Auditing and Validation 143
Introduction 143
Objectives 143
(Pure) Audit 143
Validation 144
Improvement, Restructuring or Rebuild 145
Processes, Tools and Techniques 146
Avoiding Unintentional Changes 146
Developing a General Overview and Then Understanding the Details 147
Testing and Checking the Formulae 151
Using a Watch Window and Other Ways to Track Values 151
Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153
Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155
Introduction 155
Overview of Sensitivity-related Techniques 155
DataTables 156
Overview 156
Implementation 157
Limitations and Tips 157
Practical Applications 160
Example: Sensitivity of Net Present Value to Growth Rates 160
Example: Implementing Scenario Analysis 160
Chapter 13 Using GoalSeek and Solver 163
Introduction 163
Overview of GoalSeek and Solver 163
Links to Sensitivity Analysis 163
Tips, Tricks and Limitations 163
Practical Applications 164
Example: Breakeven Analysis of a Business 165
Example: Threshold Investment Amounts 166
Example: Implied Volatility of an Option 167
Example: Minimising Capital Gains Tax Liability 167
Example: Non-linear Curve Fitting 169
Chapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171
Introduction 171
Practical Applications 172
Example: Running Sensitivity Analysis Using a Macro 172
Example: Running Scenarios Using a Macro 173
Example: Using a Macro to Run Breakeven Analysis with GoalSeek 173
Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175
Chapter 15 Introduction to Simulation and Optimisation 177
Introduction 177
The Links Between Sensitivity and Scenario Analysis,
Simulation and Optimisation 177
The Combinatorial Effects of Multiple Possible Input Values 177
Controllable Versus Non-controllable: Choice Versus
Uncertainty of Input Values 178
Practical Example: A Portfolio of Projects 179
Description 179
Optimisation Context 180
Risk or Uncertainty Context Using Simulation 180
Further Aspects of Optimisation Modelling 182
Structural Choices 182
Uncertainty 183
Integrated Approaches to Optimisation 183
Modelling Issues and Tools 184
Chapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187
Introduction 187
The Meaning, Origins and Uses of Monte Carlo Simulation 187
Definition and Origin 187
Limitations of Sensitivity and Scenario Approaches 188
Key Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189
The Nature of Model Outputs 190
The Applicability of Simulation Methods 190
Key Process and Modelling Steps in Risk Modelling 191
Risk Identification 191
Risk Mapping and the Role of the Distribution of Input Values 191
The Modelling Context and the Meaning of Input Distributions 192
The Effect of Dependencies Between Inputs 192
Random Numbers and the Required Number of Recalculations or Iterations 193
Using Excel and VBA to Implement Risk and Simulation Models 194
Generation of Random Samples 194
Repeated Recalculations and Results Storage 195
Example: Cost Estimation with Uncertainty and Event Risks Using Excel/VBA 196
Using Add-ins to Implement Risk and Simulation Models 196
Benefits of Add-ins 196
Example: Cost Estimation with Uncertainty and Event Risks Using @RISK 197
Part Five Excel Functions and Functionality 199
Chapter 17 Core Arithmetic and Logical Functions 201
Introduction 201
Practical Applications 201
Example: IF, AND, OR, NOT 202
Example: MIN, MAX, MINA, MAXA 204
Example: MINIFS and MAXIFS 204
Example: COUNT, COUNTA, COUNTIF and Similar Functions 205
Example: SUM, AVERAGE, AVERAGEA 206
Example: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206
Example: PRODUCT 207
Example: SUMPRODUCT 209
Example: SUBTOTAL 209
Example: AGGREGATE 210
Example: IFERROR 212
Example: SWITCH 215
Chapter 18 Array Functions and Formulae 217
Introduction 217
Functions and Formulae: Definitions 217
Implementation 217
Advantages and Disadvantages 218
Practical Applications: Array Functions 218
Example: Capex and Depreciation Schedules Using TRANSPOSE 218
Example: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218
Example: Cost Allocation Using Matrix Multiplication Using MMULT 219
Example: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220
Example: Summing Powers of Integers from 1 Onwards 222
Practical Applications: Array Formulae 225
Example: Finding First Positive Item in a List 225
Example: Find a Conditional Maximum 226
Example: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227
Chapter 19 Mathematical Functions 229
Introduction 229
Practical Applications 229
Example: EXP and LN 229
Example: ABS and SIGN 232
Example: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233
Example: MROUND, CEILING.MATH and FLOOR.MATH 235
Example: MOD 236
Example: SQRT and POWER 236
Example: FACT and COMBIN 237
Example: RAND() 238
Example: SINE, ASIN, DEGREES and PI() 239
Example: BASE and DECIMAL 241
Chapter 20 Financial Functions 243
Introduction 243
Practical Applications 243
Example: FVSCHEDULE 244
Example: FV and PV 244
Example: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246
Example: NPV and IRR for a Buy or Lease Decision 248
Example: SLN, DDB and VDB 250
Example: YIELD 252
Example: Duration of Cash Flows 252
Example: DURATION and MDURATION 253
Example: PDURATION and RRI 254
Other Financial Functions 255
Chapter 21 Statistical Functions 257
Introduction 257
Practical Applications: Position, Ranking and Central Values 258
Example: Calculating Mean and Mode 258
Example: Dynamic Sorting of Data Using LARGE 260
Example: RANK.EQ 261
Example: RANK.AVG 262
Example: Calculating Percentiles 262
Example: PERCENTRANK-type Functions 263
Practical Applications: Spread and Shape 264
Example: Generating a Histogram of Returns Using FREQUENCY 265
Example: Variance, Standard Deviation and Volatility 267
Example: Skewness and Kurtosis 271
Example: One-sided Volatility (Semi-deviation) 272
Practical Applications: Co-relationships and Dependencies 273
Example: Scatter Plots (X-Y Charts) and Measuring Correlation 274
Example: More on Correlation Coefficients and Rank Correlation 275
Example: Measuring Co-variances 277
Example: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277
Practical Applications: Probability Distributions 280
Example: Likelihood of a Given Number of Successes of an Oil Exploration Process 282
Example: Frequency of Outcomes Within One or Two Standard Deviations 283
Example: Creating Random Samples from Probability Distributions 283
Example: User-defined Inverse Functions for Random Sampling 284
Example: Values Associated with Probabilities for a Binomial Process 285
Example: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285
Example: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287
Example: Confidence Intervals for the Standard Deviation Using Chi-squared 289
Example: Confidence Interval for the Slope of Regression Line (or Beta) 289
Practical Applications: More on Regression Analysis and Forecasting 291
Example: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291
Example: Using LINEST to Perform Multiple Regression 292
Example: Using LOGEST to Find Exponential Fits 293
Example: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294
Example: Linear Forecasting Using FORECAST.LINEAR 295
Example: Forecasting Using the FORECAST.ETS Set of Functions 296
Chapter 22 Information Functions 299
Introduction 299
Practical Applications 300
Example: In-formula Comments Using ISTEXT, ISNUMBER or N 300
Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300
Example: Detecting Consistency of Data in a Database 301
Example: Consistent use of "N/A" in Models 301
Example: Applications of the INFO and CELL Functions: An Overview 303
Example: Creating Updating Labels that Refer to Data or Formulae 303
Example: Showing the User Which Recalculation Mode the File Is On 305
Example: Finding the Excel Version Used and Creating Backward Compatible Formulae 305
Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306
Chapter 23 Date and Time Functions 307
Introduction 307
Practical Applications 308
Example: Task Durations, Resource and Cost Estimation 308
Example: Keeping Track of Bookings, Reservations or Other Activities 308
Example: Creating Precise Time Axes 309
Example: Calculating the Year and Month of a Date 309
Example: Calculating the Quarter in Which a Date Occurs 310
Example: Creating Time-based Reports and Models from Data Sets 311
Example: Finding Out on What Day of the Week You Were Born 311
Example: Calculating the Date of the Last Friday of Every Month 311
Example: the DATEDIF Function and Completed Time Periods 312
Chapter 24 Text Functions and Functionality 313
Introduction 313
Practical Applications 314
Example: Joining Text Using CONCAT and TEXTJOIN 314
Example: Splitting Data Using the Text-to-columns Wizard 315
Example: Converting Numerical Text to Numbers 316
Example: Dynamic Splitting Text into Components I 316
Example: Dynamic Splitting Text into Components II 317
Example: Comparing LEFT, RIGHT, MID and LEN 317
Example: Dynamic Splitting Text into Components III 318
Example: Comparing FIND and SEARCH 319
Example: the UPPER and LOWER Functions 319
Example: the PROPER Function 319
Example: the EXACT Function 320
Example: Comparing REPLACE with SUBSTITUTE 320
Example: the REPT Function 320
Example: the CLEAN and TRIM Functions 321
Example: Updating Model Labels and Graph Titles 322
Example: Creating Unique Identifiers or Keys for Data Matching 323
Chapter 25 Lookup and Reference Functions 325
Introduction 325
Practical Applications: Basic Referencing Processes 326
Example: the ROW and COLUMN Functions 326
Example: the ROWS and COLUMNS Functions 327
Example: Use of the ADDRESS Function and the Comparison with CELL 327
Practical Applications: Further Referencing Processes 328
Example: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328
Example: Charts that Can Use Multiple or Flexible Data Sources 330
Example: Reversing and Transposing Data Using INDEX or OFFSET 331
Example: Shifting Cash Flows or Other Items over Time 334
Example: Depreciation Schedules with Triangle Calculations 334
Practical Applications: Combining Matching and Reference Processes 335
Example: Finding the Period in Which a Condition is Met Using MATCH 335
Example: Finding Non-contiguous Scenario Data Using Matching Keys 336
Example: Creating and Finding Matching Text Fields or Keys 336
Example: Combining INDEX with MATCH 337
Example: Comparing INDEX-MATCH with V- and HLOOKUP 338
Example: Comparing INDEX-MATCH with LOOKUP 343
Example: Finding the Closest Matching Value Using Array and Other Function Combinations 344
Practical Applications: More on the OFFSET Function and Dynamic Ranges 345
Example: Flexible Ranges Using OFFSET (I) 345
Example: Flexible Ranges Using OFFSET (II) 346
Example: Flexible Ranges Using OFFSET (III) 347
Example: Flexible Ranges Using OFFSET (IV) 347
Practical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349
Example: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349
Example: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351
Example: Other Uses of INDIRECT - Cascading Drop-down Lists 352
Practical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352
Example: Model Navigation Using Named Ranges and Hyperlinks 353
Chapter 26 Filters, Database Functions and PivotTables 355
Introduction 355
Issues Common to Working with Sets of Data 356
Cleaning and Manipulating Source Data 356
Static or Dynamic Queries 356
Creation of New Fields or Complex Filters? 357
Excel Databases and Tables 357
Automation Using Macros 359
Practical Applications: Filters 359
Example: Applying Filters and Inspecting Data for Errors or Possible Corrections 359
Example: Identification of Unique Items and Unique Combinations 362
Example: Using Filters to Remove Blanks or Other Specified Items 363
Example: Extraction of Data Using Filters 365
Example: Adding Criteria Calculations to the Data Set 365
Example: Use of Tables 366
Example: Extraction of Data Using Advanced Filters 369
Practical Applications: Database Functions 370
Example: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370
Example: Implementing a Between Query 371
Example: Implementing Multiple Queries 371
Practical Applications: PivotTables 373
Example: Exploring Summary Values of Data Sets 373
Example: Exploring Underlying Elements of the Summary Items 376
Example: Adding Slicers 376
Example: Timeline Slicers 378
Example: Generating Reports Which Ignore Errors or Other Specified Items 380
Example: Using the GETPIVOTDATA Functions 380
Example: Creating PivotCharts 382
Example: Using the Excel Data Model to Link Tables 383
Chapter 27 Selected Short-cuts and Other Features 387
Introduction 387
Key Short-cuts and Their Uses 387
Entering and Modifying Data and Formulae 388
Formatting 390
Auditing, Navigation and Other Items 391
Excel KeyTips 393
Other Useful Excel Tools and Features 393
Sparklines 393
The Camera Tool 393
Part Six Foundations of VBA and Macros 395
Chapter 28 Getting Started 397
Introduction 397
Main Uses of VBA 397
Task Automation 398
Creating User-defined Functions 398
Detecting and Reacting to Model Events 398
Enhancing or Managing the User Interface 399
Application Development 399
Core Operations 399
Adding the Developer Tab to Excel's Toolbar 399
The Visual Basic Editor 399
Recording Macros 401
Typical Adaptations Required When Using Recorded Code 402
Writing Code 403
Running Code 404
Debugging Techniques 405
Simple Examples 406
Example: Using Excel Cell Values in VBA 406
Example: Using Named Excel Ranges for Robustness and Flexibility 407
Example: Placing a Value from VBA Code into an Excel Range 408
Example: Replacing Copy/Paste with an Assignment 409
Example: A Simple User-defined Function 409
Example: Displaying a Message when a Workbook is Opened 410
Chapter 29 Working with Objects and Ranges 413
Introduction 413
Overview of the Object Model 413
Objects, Properties, Methods and Events 413
Object Hierarchies and Collections 414
Using Set. . .=. . . . 415
Using the With. . .End With Construct 415
Finding Alternatives to the Selection or Activation of Ranges and Objects 416
Working with Range Objects: Some Key Elements 416
Basic Syntax Possibilities and Using Named Ranges 416
Named Ranges and Named Variables 417
The CurrentRegion Property 417
The xlCellTypeLastCell Property 418
Worksheet Names and Code Names 419
The UsedRange Property 419
The Cells Property 420
The Offset Property 421
The Union Method 421
InputBox and MsgBox 421
Application.InputBox 422
Defining Multi-cell Ranges 422
Using Target to React to Worksheet Events 422
Using Target to React to Workbook Events 423
Chapter 30 Controlling Execution 425
Introduction 425
Core Topics in Overview 425
Input Boxes and Message Boxes 425
For. . .Next Loops 425
For Each. . . In. . .Next 426
If. . .Then 427
Select Case. . .End Select 427
GoTo 428
Do. . .While/Until. . .Loop 428
Calculation and Calculate 429
Screen Updating 432
Measuring Run Time 432
Displaying Alerts 433
Accessing Excel Worksheet Functions 433
Executing Procedures Within Procedures 434
Accessing Add-ins 435
Practical Applications 435
Example: Numerical Looping 435
Example: Listing the Names of All Worksheets in a Workbook 436
Example: Adding a New Worksheet to a Workbook 437
Example: Deleting Specific Worksheets from a Workbook 437
Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438
Chapter 31 Writing Robust Code 441
Introduction 441
Key Principles 441
From the Specific to the General 441
Adapting Recorded Code for Robustness 442
Event Code 442
Comments and Indented Text 442
Modular Code 443
Passing Arguments ByVal or ByRef 443
Full Referencing 445
Using Worksheet Code Numbers 447
Assignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447
Working with Ranges Instead of Individual Cells 448
Data Types and Variable Declaration 448
Choice of Names 449
Working with Arrays in VBA 450
Understanding Error Codes: An Introduction 451
Further Approaches to Testing, Debugging and Error-handling 452
General Techniques 452
Debugging Functions 453
Implementing Error-handling Procedures 454
Chapter 32 Manipulation and Analysis of Data Sets with VBA 455
Introduction 455
Practical Applications 455
Example: Working Out the Size of a Range 455
Example: Defining the Data Set at Run Time Based on User Input 457
Example: Working Out the Position of a Data Set Automatically 457
Example: Reversing Rows (or Columns) of Data I: Placement in a New Range 459
Example: Reversing Rows (or Columns) of Data II: In Place 460
Example: Automation of Other Data-related Excel Procedures 461
Example: Deleting Rows Containing Blank Cells 462
Example: Deleting Blank Rows 463
Example: Automating the Use of Filters to Remove Blanks or Other Specified Items 464
Example: Performing Multiple Database Queries 468
Example: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469
Chapter 33 User-defined Functions 473
Introduction 473
Benefits of Creating User-defined Functions 473
Syntax and Implementation 474
Practical Applications 475
Example: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476
Example: A Wrapper to Access the Latest Excel Function Version 477
Example: Replication of IFERROR for Compatibility with Excel 2003 478
Example: Sum of Absolute Errors 479
Example: Replacing General Excel Calculation Tables or Ranges 480
Example: Using Application.Caller to Generate a Time Axis as an Array Function 480
Example: User-defined Array Functions in Rows and Columns 482
Example: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484
Example: Sheet Reference Functions 485
Example: Statistical Moments when Frequencies Are Known 487
Example: Rank Order Correlation 489
Example: Semi-deviation of a Data Set 491
Index 493