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

ISBN-13: 9781118904015 / Angielski / Twarda / 2018 / 544 str.

Rees, Michael

Opis

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.

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.

Spis treści

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

CHAPTER 2 Using Models in Decision Support 7

CHAPTER 3 Core Competencies and Best Practices: Meta–themes 15

PART TWO Model Design and Planning 23

CHAPTER 4 Defining Sensitivity and Flexibility Requirements 25

CHAPTER 5 Database Versus Formulae–driven Approaches 37

CHAPTER 6 Designing the Workbook Structure 47

PART THREE Model Building, Testing and Auditing 57

CHAPTER 7 Creating Transparency: Formula Structure, Flow and Format 59

CHAPTER 8 Building Robust and Transparent Formulae 79

CHAPTER 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105

CHAPTER 10 Dealing with Circularity 117

CHAPTER 11 Model Review, Auditing and Validation 143

PART FOUR Sensitivity and Scenario Analysis, Simulation and Optimisation 153

CHAPTER 12 Sensitivity and Scenario Analysis: Core Techniques 155

CHAPTER 13 Using GoalSeek and Solver 163

CHAPTER 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171

CHAPTER 15 Introduction to Simulation and Optimisation 177

CHAPTER 16 The Modelling of Risk and Uncertainty, and Using Simulation 187

PART FIVE Excel Functions and Functionality 199

CHAPTER 17 Core Arithmetic and Logical Functions 201

CHAPTER 18 Array Functions and Formulae 217

CHAPTER 19 Mathematical Functions 229

CHAPTER 20 Financial Functions 243

CHAPTER 21 Statistical Functions 257

CHAPTER 22 Information Functions 299

CHAPTER 23 Date and Time Functions 307

CHAPTER 24 Text Functions and Functionality 313

CHAPTER 25 Lookup and Reference Functions 325

CHAPTER 26 Filters, Database Functions and PivotTables 355

CHAPTER 27 Selected Short–cuts and Other Features 387

PART SIX Foundations of VBA and Macros 395

CHAPTER 28 Getting Started 397

CHAPTER 29 Working with Objects and Ranges 413

CHAPTER 30 Controlling Execution 425

CHAPTER 31 Writing Robust Code 441

CHAPTER 32 Manipulation and Analysis of Data Sets with VBA 455

CHAPTER 33 User–defined Functions 473

Index 493

O autorze

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.

Blurb

The Comprehensive, Proven, Real–World Guide to Financial Modelling

Principles of Financial Modelling Model Design and Best Practices Using Excel and VBA 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, based on rigorously–tested materials created for consulting projects and for training courses. It shows how to build models to maximise transparency, and to use appropriate Excel functions and VBA functionality. It also summarises key themes and best practices, and covers the optimisation of data structures and layout.

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

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.