Principles of Financial Modelling

Principles of Financial Modelling

by Michael Rees
Released July 2018
Publisher(s): Wiley
ISBN: 9781118904015

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book 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.

Table of contents

  1. Cover
  2. Title Page
  3. Preface
  4. About the Author
  5. About the Website
  6. PART One: Introduction to Modelling, Core Themes and Best Practices
    1. CHAPTER 1: Models of Models
      1. INTRODUCTION
      2. CONTEXT AND OBJECTIVES
      3. THE STAGES OF MODELLING
      4. BACKWARD THINKING AND FORWARD CALCULATION PROCESSES
    2. CHAPTER 2: Using Models in Decision Support
      1. INTRODUCTION
      2. BENEFITS OF USING MODELS
      3. CHALLENGES IN USING MODELS
    3. CHAPTER 3: Core Competencies and Best Practices: Meta-themes
      1. INTRODUCTION
      2. KEY THEMES
  7. PART Two: Model Design and Planning
    1. CHAPTER 4: Defining Sensitivity and Flexibility Requirements
      1. INTRODUCTION
      2. KEY ISSUES FOR CONSIDERATION
    2. CHAPTER 5: Database Versus Formulae-driven Approaches
      1. INTRODUCTION
      2. KEY ISSUES FOR CONSIDERATION
      3. PRACTICAL EXAMPLE
    3. CHAPTER 6: Designing the Workbook Structure
      1. INTRODUCTION
      2. GENERIC BEST PRACTICE STRUCTURES
      3. USING INFORMATION FROM MULTIPLE WORKSHEETS: CHOICE (EXCLUSION) AND CONSOLIDATION (INCLUSION) PROCESSES
  8. PART Three: Model Building, Testing and Auditing
    1. CHAPTER 7: Creating Transparency: Formula Structure, Flow and Format
      1. INTRODUCTION
      2. APPROACHES TO IDENTIFYING THE DRIVERS OF COMPLEXITY
      3. OPTIMISING AUDIT PATHS
      4. IDENTIFYING INPUTS, CALCULATIONS AND OUTPUTS: STRUCTURE AND FORMATTING
      5. CREATING DOCUMENTATION, COMMENTS AND HYPERLINKS
    2. CHAPTER 8: Building Robust and Transparent Formulae
      1. INTRODUCTION
      2. GENERAL CAUSES OF MISTAKES
      3. EXAMPLES OF COMMON MISTAKES
      4. THE USE OF NAMED RANGES
      5. APPROACHES TO BUILDING FORMULAE, TO TESTING, ERROR DETECTION AND MANAGEMENT
    3. CHAPTER 9: Choosing Excel Functions for Transparency, Flexibility and Efficiency
      1. INTRODUCTION
      2. KEY CONSIDERATIONS
    4. CHAPTER 10: Dealing with Circularity
      1. INTRODUCTION
      2. THE DRIVERS AND NATURE OF CIRCULARITIES
      3. RESOLVING CIRCULAR FORMULAE
      4. ITERATIVE METHODS IN PRACTICE
      5. PRACTICAL EXAMPLE
      6. SELECTION OF APPROACH TO DEALING WITH CIRCULARITIES: KEY CRITERIA
    5. CHAPTER 11: Model Review, Auditing and Validation
      1. INTRODUCTION
      2. OBJECTIVES
      3. PROCESSES, TOOLS AND TECHNIQUES
  9. PART Four: Sensitivity and Scenario Analysis, Simulation and Optimisation
    1. CHAPTER 12: Sensitivity and Scenario Analysis: Core Techniques
      1. INTRODUCTION
      2. OVERVIEW OF SENSITIVITY-RELATED TECHNIQUES
      3. DATATABLES
      4. PRACTICAL APPLICATIONS
    2. CHAPTER 13: Using GoalSeek and Solver
      1. INTRODUCTION
      2. OVERVIEW OF GOALSEEK AND SOLVER
      3. PRACTICAL APPLICATIONS
    3. CHAPTER 14: Using VBA Macros to Conduct Sensitivity and Scenario Analyses
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    4. CHAPTER 15: Introduction to Simulation and Optimisation
      1. INTRODUCTION
      2. THE LINKS BETWEEN SENSITIVITY AND SCENARIO ANALYSIS, SIMULATION AND OPTIMISATION
      3. PRACTICAL EXAMPLE: A PORTFOLIO OF PROJECTS
      4. FURTHER ASPECTS OF OPTIMISATION MODELLING
    5. CHAPTER 16: The Modelling of Risk and Uncertainty, and Using Simulation
      1. INTRODUCTION
      2. THE MEANING, ORIGINS AND USES OF MONTE CARLO SIMULATION
      3. KEY PROCESS AND MODELLING STEPS IN RISK MODELLING
      4. USING EXCEL AND VBA TO IMPLEMENT RISK AND SIMULATION MODELS
      5. USING ADD-INS TO IMPLEMENT RISK AND SIMULATION MODELS
  10. PART Five: Excel Functions and Functionality
    1. CHAPTER 17: Core Arithmetic and Logical Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    2. CHAPTER 18: Array Functions and Formulae
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS: ARRAY FUNCTIONS
      3. PRACTICAL APPLICATIONS: ARRAY FORMULAE
    3. CHAPTER 19: Mathematical Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    4. CHAPTER 20: Financial Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
      3. OTHER FINANCIAL FUNCTIONS
    5. CHAPTER 21: Statistical Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS: POSITION, RANKING AND CENTRAL VALUES
      3. PRACTICAL APPLICATIONS: SPREAD AND SHAPE
      4. PRACTICAL APPLICATIONS: CO-RELATIONSHIPS AND DEPENDENCIES
      5. PRACTICAL APPLICATIONS: PROBABILITY DISTRIBUTIONS
      6. PRACTICAL APPLICATIONS: MORE ON REGRESSION ANALYSIS AND FORECASTING
    6. CHAPTER 22: Information Functions
      1. INTRODUCTION
    7. CHAPTER 23: Date and Time Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    8. CHAPTER 24: Text Functions and Functionality
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    9. CHAPTER 25: Lookup and Reference Functions
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS: BASIC REFERENCING PROCESSES
      3. PRACTICAL APPLICATIONS: FURTHER REFERENCING PROCESSES
      4. PRACTICAL APPLICATIONS: COMBINING MATCHING AND REFERENCE PROCESSES
      5. PRACTICAL APPLICATIONS: MORE ON THE OFFSET FUNCTION AND DYNAMIC RANGES
      6. PRACTICAL APPLICATIONS: THE INDIRECT FUNCTION AND FLEXIBLE WORKBOOK OR DATA STRUCTURES
      7. PRACTICAL EXAMPLES: USE OF HYPERLINKS TO NAVIGATE A MODEL, AND OTHER LINKS TO DATA SETS
    10. CHAPTER 26: Filters, Database Functions and PivotTables
      1. INTRODUCTION
      2. ISSUES COMMON TO WORKING WITH SETS OF DATA
      3. PRACTICAL APPLICATIONS: FILTERS
      4. PRACTICAL APPLICATIONS: DATABASE FUNCTIONS
      5. PRACTICAL APPLICATIONS: PIVOTTABLES
    11. CHAPTER 27: Selected Short-cuts and Other Features
      1. INTRODUCTION
      2. KEY SHORT-CUTS AND THEIR USES
      3. OTHER USEFUL EXCEL TOOLS AND FEATURES
  11. PART Six: Foundations of VBA and Macros
    1. CHAPTER 28: Getting Started
      1. INTRODUCTION
      2. MAIN USES OF VBA
      3. CORE OPERATIONS
      4. SIMPLE EXAMPLES
    2. CHAPTER 29: Working with Objects and Ranges
      1. INTRODUCTION
      2. OVERVIEW OF THE OBJECT MODEL
      3. WORKING WITH RANGE OBJECTS: SOME KEY ELEMENTS
    3. CHAPTER 30: Controlling Execution
      1. INTRODUCTION
      2. CORE TOPICS IN OVERVIEW
      3. PRACTICAL APPLICATIONS
    4. CHAPTER 31: Writing Robust Code
      1. INTRODUCTION
      2. KEY PRINCIPLES
      3. FURTHER APPROACHES TO TESTING, DEBUGGING AND ERROR-HANDLING
    5. CHAPTER 32: Manipulation and Analysis of Data Sets with VBA
      1. INTRODUCTION
      2. PRACTICAL APPLICATIONS
    6. CHAPTER 33: User-defined Functions
      1. INTRODUCTION
      2. BENEFITS OF CREATING USER-DEFINED FUNCTIONS
      3. SYNTAX AND IMPLEMENTATION
      4. PRACTICAL APPLICATIONS
  12. Index
  13. End User License Agreement

Product information

  • Title: Principles of Financial Modelling
  • Author(s): Michael Rees
  • Release date: July 2018
  • Publisher(s): Wiley
  • ISBN: 9781118904015