Muutke küpsiste eelistusi

Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA [Kõva köide]

3.75/5 (4 hinnangut Goodreads-ist)
(Independent Consultant)
  • Formaat: Hardback, 544 pages, kõrgus x laius x paksus: 252x178x36 mm, kaal: 1089 g
  • Sari: The Wiley Finance Series
  • Ilmumisaeg: 11-May-2018
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 111890401X
  • ISBN-13: 9781118904015
Teised raamatud teemal:
Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBASuurem pilt
  • Formaat: Hardback, 544 pages, kõrgus x laius x paksus: 252x178x36 mm, kaal: 1089 g
  • Sari: The Wiley Finance Series
  • Ilmumisaeg: 11-May-2018
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 111890401X
  • ISBN-13: 9781118904015
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781118904015.html
Märksõnad:
The comprehensive, broadly-applicable, real-world guide to financial modelling

Financial Modelling in Practice, Second Edition covers the full spectrum of financial modelling tools and techniques to provide practical skills grounded in real-world scenarios. Based on rigorously-tested training materials, this book demonstrates the construction and operation of a range of financial models with clear instruction on model design, Excel functionality, VBA coding, risk analysis, real options modelling and more. This second edition has been significantly updated to align with the latest version of Excel and the current financial modelling climate, including new guidance on sensitivity analysis as a model design tool and risk analysis as an extension of sensitivity analysis. The companion website provides access to worked examples and pre-formatted spreadsheets to facilitate learning and implementation, and the strong emphasis on practical solutions allows direct real-world application.

The number of people tasked with financial modelling duties has increased significantly in recent years, yet comprehensive, broadly-applicable references are rare. This book brings together concept and practice to provide a high value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic.

  • Utilize Excel at a more advanced level, including VBA modelling
  • Learn unique tips and hints for data manipulation and analysis
  • Understand financial statement modelling for valuation
  • Master simulation, risk modelling and optimization

Financial modelling is invaluable to a broad range of financial decisions, including corporate finance valuation, portfolio composition, cash flow analysis, budgeting, forecasting and more. This book provides clear instruction applicable across sectors, settings and countries, presented in a well-structured and highly-developed format accessible to people with very different backgrounds. For practical instruction, robust technique and clear presentation,Financial Modelling in Practice is the premier guide to real-world financial modelling from the ground up.

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