{"product_id":"principles-of-financial-modelling-isbn-9781118904015","title":"Principles of Financial Modelling","description":"\u003cp\u003e\u003cb\u003eThe comprehensive, broadly-applicable, real-world guide to financial modelling\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003ci\u003ePrinciples of Financial Modelling \u003c\/i\u003e– \u003ci\u003eModel Design and Best Practices Using Excel and VBA\u003c\/i\u003ecovers 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:\u003c\/p\u003e \u003cul\u003e \u003cli\u003eModel 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\u003c\/li\u003e \u003cli\u003eSensitivity and scenario analysis, simulation, and optimisation\u003c\/li\u003e \u003cli\u003eData manipulation and analysis\u003c\/li\u003e \u003cli\u003eThe 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\u003c\/li\u003e \u003c\/ul\u003e \u003cp\u003eThe 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.\u003c\/p\u003e \u003cp\u003eFor practical instruction, robust technique and clear presentation, \u003ci\u003ePrinciples of Financial Modelling\u003c\/i\u003e 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.\u003c\/p\u003e \u003cp\u003ePreface xxv\u003c\/p\u003e \u003cp\u003eAbout the Author xxvii\u003c\/p\u003e \u003cp\u003eAbout the Website xxix\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart One Introduction to Modelling, Core Themes and Best Practices 1\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 1 Models of Models 3\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 3\u003c\/p\u003e \u003cp\u003eContext and Objectives 3\u003c\/p\u003e \u003cp\u003eThe Stages of Modelling 3\u003c\/p\u003e \u003cp\u003eBackward Thinking and Forward Calculation Processes 4\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 2 Using Models in Decision Support 7\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 7\u003c\/p\u003e \u003cp\u003eBenefits of Using Models 7\u003c\/p\u003e \u003cp\u003eProviding Numerical Information 7\u003c\/p\u003e \u003cp\u003eCapturing Influencing Factors and Relationships 7\u003c\/p\u003e \u003cp\u003eGenerating Insight and Forming Hypotheses 8\u003c\/p\u003e \u003cp\u003eDecision Levers, Scenarios, Uncertainties, Optimisation,  Risk Mitigation and Project Design 8\u003c\/p\u003e \u003cp\u003eImproving Working Processes, Enhanced Communications and Precise Data Requirements 9\u003c\/p\u003e \u003cp\u003eChallenges in Using Models 9\u003c\/p\u003e \u003cp\u003eThe Nature of Model Error 9\u003c\/p\u003e \u003cp\u003eInherent Ambiguity and Circularity of Reasoning 10\u003c\/p\u003e \u003cp\u003eInconsistent Scope or Alignment of Decision and Model 10\u003c\/p\u003e \u003cp\u003eThe Presence on Biases, Imperfect Testing, False Positives and Negatives 11\u003c\/p\u003e \u003cp\u003eBalancing Intuition with Rationality 11\u003c\/p\u003e \u003cp\u003eLack of Data or Insufficient Understanding of a Situation 12\u003c\/p\u003e \u003cp\u003eOvercoming Challenges: Awareness, Actions and Best Practices 13\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 3 Core Competencies and Best Practices: Meta-themes 15\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 15\u003c\/p\u003e \u003cp\u003eKey Themes 15\u003c\/p\u003e \u003cp\u003eDecision-support Role, Objectives, Outputs and Communication 16\u003c\/p\u003e \u003cp\u003eApplication Knowledge and Understanding 17\u003c\/p\u003e \u003cp\u003eSkills with Implementation Platform 17\u003c\/p\u003e \u003cp\u003eDefining Sensitivity and Flexibility Requirements 18\u003c\/p\u003e \u003cp\u003eDesigning Appropriate Layout, Input Data Structures and Flow 20\u003c\/p\u003e \u003cp\u003eEnsuring Transparency and Creating a User-friendly Model 20\u003c\/p\u003e \u003cp\u003eIntegrated Problem-solving Skills 21\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart Two Model Design and Planning 23\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 4 Defining Sensitivity and Flexibility Requirements 25\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 25\u003c\/p\u003e \u003cp\u003eKey Issues for Consideration 25\u003c\/p\u003e \u003cp\u003eCreating a Focus on Objectives and Their Implications 26\u003c\/p\u003e \u003cp\u003eSensitivity Concepts in the Backward Thought and Forward Calculation\u003c\/p\u003e \u003cp\u003eProcesses 26\u003c\/p\u003e \u003cp\u003eTime Granularity 30\u003c\/p\u003e \u003cp\u003eLevel of Detail on Input Variables 30\u003c\/p\u003e \u003cp\u003eSensitising Absolute Values or Variations from Base Cases 31\u003c\/p\u003e \u003cp\u003eScenarios Versus Sensitivities 32\u003c\/p\u003e \u003cp\u003eUncertain Versus Decision Variables 33\u003c\/p\u003e \u003cp\u003eIncreasing Model Validity Using Formulae 34\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 5 Database Versus Formulae-driven Approaches 37\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 37\u003c\/p\u003e \u003cp\u003eKey Issues for Consideration 37\u003c\/p\u003e \u003cp\u003eSeparating the Data, Analysis and Presentation (Reporting) Layers 37\u003c\/p\u003e \u003cp\u003eThe Nature of Changes to Data Sets and Structures 39\u003c\/p\u003e \u003cp\u003eFocus on Data or Formulae? 40\u003c\/p\u003e \u003cp\u003ePractical Example 42\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 6 Designing the Workbook Structure 47\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 47\u003c\/p\u003e \u003cp\u003eDesigning Workbook Models with Multiple Worksheets 47\u003c\/p\u003e \u003cp\u003eLinked Workbooks 47\u003c\/p\u003e \u003cp\u003eMultiple Worksheets: Advantages and Disadvantages 48\u003c\/p\u003e \u003cp\u003eGeneric Best Practice Structures 49\u003c\/p\u003e \u003cp\u003eThe Role of Multiple Worksheets in Best Practice Structures 49\u003c\/p\u003e \u003cp\u003eType I: Single Worksheet Models 50\u003c\/p\u003e \u003cp\u003eType II: Single Main Formulae Worksheet, and Several Data Worksheets 50\u003c\/p\u003e \u003cp\u003eType III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51\u003c\/p\u003e \u003cp\u003eFurther Comparative Comments 51\u003c\/p\u003e \u003cp\u003eUsing Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52\u003c\/p\u003e \u003cp\u003eMulti-sheet or “Three Dimensional” Formulae 53\u003c\/p\u003e \u003cp\u003eUsing Excel’s Data\/Consolidation Functionality 54\u003c\/p\u003e \u003cp\u003eConsolidating from Several Sheets into a Database Using a Macro 55\u003c\/p\u003e \u003cp\u003eUser-defined Functions 56\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart Three Model Building, Testing and Auditing 57\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 7 Creating Transparency: Formula Structure, Flow and Format 59\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 59\u003c\/p\u003e \u003cp\u003eApproaches to Identifying the Drivers of Complexity 59\u003c\/p\u003e \u003cp\u003eTaking the Place of a Model Auditor 59\u003c\/p\u003e \u003cp\u003eExample: Creating Complexity in a Simple Model 60\u003c\/p\u003e \u003cp\u003eCore Elements of Transparent Models 61\u003c\/p\u003e \u003cp\u003eOptimising Audit Paths 62\u003c\/p\u003e \u003cp\u003eCreating Short Audit Paths Using Modular Approaches 63\u003c\/p\u003e \u003cp\u003eCreating Short Audit Paths Using Formulae Structure and Placement 67\u003c\/p\u003e \u003cp\u003eOptimising Logical Flow and the Direction of the Audit Paths 68\u003c\/p\u003e \u003cp\u003eIdentifying Inputs, Calculations and Outputs: Structure and Formatting 69\u003c\/p\u003e \u003cp\u003eThe Role of Formatting 70\u003c\/p\u003e \u003cp\u003eColour-coding of Inputs and Outputs 70\u003c\/p\u003e \u003cp\u003eBasic Formatting Operations 73\u003c\/p\u003e \u003cp\u003eConditional Formatting 73\u003c\/p\u003e \u003cp\u003eCustom Formatting 75\u003c\/p\u003e \u003cp\u003eCreating Documentation, Comments and Hyperlinks 76\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 8 Building Robust and Transparent Formulae 79\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 79\u003c\/p\u003e \u003cp\u003eGeneral Causes of Mistakes 79\u003c\/p\u003e \u003cp\u003eInsufficient Use of General Best Practices Relating to Flow, Formatting,\u003c\/p\u003e \u003cp\u003eAudit Paths 79\u003c\/p\u003e \u003cp\u003eInsufficient Consideration Given to Auditability and Other Potential Users 79\u003c\/p\u003e \u003cp\u003eOverconfidence, Lack of Checking and Time Constraints 80\u003c\/p\u003e \u003cp\u003eSub-optimal Choice of Functions 80\u003c\/p\u003e \u003cp\u003eInappropriate Use or Poor Implementation of Named Ranges, Circular\u003c\/p\u003e \u003cp\u003eReferences or Macros 80\u003c\/p\u003e \u003cp\u003eExamples of Common Mistakes 80\u003c\/p\u003e \u003cp\u003eReferring to Incorrect Ranges or To Blank Cells 80\u003c\/p\u003e \u003cp\u003eNon-transparent Assumptions, Hidden Inputs and Labels 82\u003c\/p\u003e \u003cp\u003eOverlooking the Nature of Some Excel Function Values 82\u003c\/p\u003e \u003cp\u003eUsing Formulae Which are Inconsistent Within a Range 83\u003c\/p\u003e \u003cp\u003eOverriding Unforeseen Errors with IFERROR 84\u003c\/p\u003e \u003cp\u003eModels Which are Correct in Base Case but Not in Others 85\u003c\/p\u003e \u003cp\u003eIncorrect Modifications when Working with Poor Models 85\u003c\/p\u003e \u003cp\u003eThe Use of Named Ranges 85\u003c\/p\u003e \u003cp\u003eMechanics and Implementation 86\u003c\/p\u003e \u003cp\u003eDisadvantages of Using Named Ranges 86\u003c\/p\u003e \u003cp\u003eAdvantages and Key Uses of Named Ranges 90\u003c\/p\u003e \u003cp\u003eApproaches to Building Formulae, to Testing, Error Detection and Management 91\u003c\/p\u003e \u003cp\u003eChecking Behaviour and Detecting Errors Using Sensitivity Testing 91\u003c\/p\u003e \u003cp\u003eUsing Individual Logic Steps 93\u003c\/p\u003e \u003cp\u003eBuilding and Splitting Compound Formulae 94\u003c\/p\u003e \u003cp\u003eUsing Absolute Cell Referencing Only Where Necessary 96\u003c\/p\u003e \u003cp\u003eLimiting Repeated or Unused Logic 96\u003c\/p\u003e \u003cp\u003eUsing Breaks to Test Calculation Paths 97\u003c\/p\u003e \u003cp\u003eUsing Excel Error Checking Rules 97\u003c\/p\u003e \u003cp\u003eBuilding Error-checking Formulae 98\u003c\/p\u003e \u003cp\u003eHandling Calculation Errors Robustly 100\u003c\/p\u003e \u003cp\u003eRestricting Input Values Using Data Validation 100\u003c\/p\u003e \u003cp\u003eProtecting Ranges 101\u003c\/p\u003e \u003cp\u003e\u003cb\u003eDealing with Structural Limitations: Formulae and Documentation 102\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eChapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105\u003c\/p\u003e \u003cp\u003eIntroduction 105\u003c\/p\u003e \u003cp\u003eKey Considerations 105\u003c\/p\u003e \u003cp\u003eDirect Arithmetic or Functions, and Individual Cells or Ranges? 105\u003c\/p\u003e \u003cp\u003eIF Versus MIN\/MAX 107\u003c\/p\u003e \u003cp\u003eEmbedded IF Statements 109\u003c\/p\u003e \u003cp\u003eShort Forms of Functions 111\u003c\/p\u003e \u003cp\u003eText Versus Numerical Fields 112\u003c\/p\u003e \u003cp\u003eSUMIFS with One Criterion 112\u003c\/p\u003e \u003cp\u003eIncluding Only Specific Items in a Summation 113\u003c\/p\u003e \u003cp\u003eAGGREGATE and SUBTOTAL Versus Individual Functions 114\u003c\/p\u003e \u003cp\u003eArray Functions or VBA User-defined Functions? 115\u003c\/p\u003e \u003cp\u003eVolatile Functions 115\u003c\/p\u003e \u003cp\u003eEffective Choice of Lookup Functions 116\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 10 Dealing with Circularity 117\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 117\u003c\/p\u003e \u003cp\u003eThe Drivers and Nature of Circularities 117\u003c\/p\u003e \u003cp\u003eCircular (Equilibrium or Self-regulating) Inherent Logic 117\u003c\/p\u003e \u003cp\u003eCircular Formulae (Circular References) 118\u003c\/p\u003e \u003cp\u003eGeneric Types of Circularities 119\u003c\/p\u003e \u003cp\u003eResolving Circular Formulae 119\u003c\/p\u003e \u003cp\u003eCorrecting Mistakes that Result in Circular Formulae 120\u003c\/p\u003e \u003cp\u003eAvoiding a Logical Circularity by Modifying the Model Specification 120\u003c\/p\u003e \u003cp\u003eEliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121\u003c\/p\u003e \u003cp\u003eResolving a Circularity Using Iterative Methods 122\u003c\/p\u003e \u003cp\u003eIterative Methods in Practice 123\u003c\/p\u003e \u003cp\u003eExcel’s Iterative Method 123\u003c\/p\u003e \u003cp\u003eCreating a Broken Circular Path: Key Steps 125\u003c\/p\u003e \u003cp\u003eRepeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126\u003c\/p\u003e \u003cp\u003ePractical Example 128\u003c\/p\u003e \u003cp\u003eUsing Excel Iterations to Resolve Circular References 129\u003c\/p\u003e \u003cp\u003eUsing a Macro to Resolve a Broken Circular Path 129\u003c\/p\u003e \u003cp\u003eAlgebraic Manipulation: Elimination of Circular References 130\u003c\/p\u003e \u003cp\u003eAltered Model 1: No Circularity in Logic or in Formulae 130\u003c\/p\u003e \u003cp\u003eAltered Model 2: No Circularity in Logic in Formulae 131\u003c\/p\u003e \u003cp\u003eSelection of Approach to Dealing with Circularities: Key Criteria 131\u003c\/p\u003e \u003cp\u003eModel Accuracy and Validity 132\u003c\/p\u003e \u003cp\u003eComplexity and Transparency 133\u003c\/p\u003e \u003cp\u003eNon-convergent Circularities 134\u003c\/p\u003e \u003cp\u003ePotential for Broken Formulae 138\u003c\/p\u003e \u003cp\u003eCalculation Speed 140\u003c\/p\u003e \u003cp\u003eEase of Sensitivity Analysis 140\u003c\/p\u003e \u003cp\u003eConclusions 141\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 11 Model Review, Auditing and Validation 143\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 143\u003c\/p\u003e \u003cp\u003eObjectives 143\u003c\/p\u003e \u003cp\u003e(Pure) Audit 143\u003c\/p\u003e \u003cp\u003eValidation 144\u003c\/p\u003e \u003cp\u003eImprovement, Restructuring or Rebuild 145\u003c\/p\u003e \u003cp\u003eProcesses, Tools and Techniques 146\u003c\/p\u003e \u003cp\u003eAvoiding Unintentional Changes 146\u003c\/p\u003e \u003cp\u003eDeveloping a General Overview and Then Understanding the Details 147\u003c\/p\u003e \u003cp\u003eTesting and Checking the Formulae 151\u003c\/p\u003e \u003cp\u003eUsing a Watch Window and Other Ways to Track Values 151\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 12 Sensitivity and Scenario Analysis: Core Techniques 155\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 155\u003c\/p\u003e \u003cp\u003eOverview of Sensitivity-related Techniques 155\u003c\/p\u003e \u003cp\u003eDataTables 156\u003c\/p\u003e \u003cp\u003eOverview 156\u003c\/p\u003e \u003cp\u003eImplementation 157\u003c\/p\u003e \u003cp\u003eLimitations and Tips 157\u003c\/p\u003e \u003cp\u003ePractical Applications 160\u003c\/p\u003e \u003cp\u003eExample: Sensitivity of Net Present Value to Growth Rates 160\u003c\/p\u003e \u003cp\u003eExample: Implementing Scenario Analysis 160\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 13 Using GoalSeek and Solver 163\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 163\u003c\/p\u003e \u003cp\u003eOverview of GoalSeek and Solver 163\u003c\/p\u003e \u003cp\u003eLinks to Sensitivity Analysis 163\u003c\/p\u003e \u003cp\u003eTips, Tricks and Limitations 163\u003c\/p\u003e \u003cp\u003ePractical Applications 164\u003c\/p\u003e \u003cp\u003eExample: Breakeven Analysis of a Business 165\u003c\/p\u003e \u003cp\u003eExample: Threshold Investment Amounts 166\u003c\/p\u003e \u003cp\u003eExample: Implied Volatility of an Option 167\u003c\/p\u003e \u003cp\u003eExample: Minimising Capital Gains Tax Liability 167\u003c\/p\u003e \u003cp\u003eExample: Non-linear Curve Fitting 169\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 171\u003c\/p\u003e \u003cp\u003ePractical Applications 172\u003c\/p\u003e \u003cp\u003eExample: Running Sensitivity Analysis Using a Macro 172\u003c\/p\u003e \u003cp\u003eExample: Running Scenarios Using a Macro 173\u003c\/p\u003e \u003cp\u003eExample: Using a Macro to Run Breakeven Analysis with GoalSeek 173\u003c\/p\u003e \u003cp\u003eExample: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 15 Introduction to Simulation and Optimisation 177\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 177\u003c\/p\u003e \u003cp\u003eThe Links Between Sensitivity and Scenario Analysis,\u003c\/p\u003e \u003cp\u003eSimulation and Optimisation 177\u003c\/p\u003e \u003cp\u003eThe Combinatorial Effects of Multiple Possible Input Values 177\u003c\/p\u003e \u003cp\u003eControllable Versus Non-controllable: Choice Versus\u003c\/p\u003e \u003cp\u003eUncertainty of Input Values 178\u003c\/p\u003e \u003cp\u003ePractical Example: A Portfolio of Projects 179\u003c\/p\u003e \u003cp\u003eDescription 179\u003c\/p\u003e \u003cp\u003eOptimisation Context 180\u003c\/p\u003e \u003cp\u003eRisk or Uncertainty Context Using Simulation 180\u003c\/p\u003e \u003cp\u003eFurther Aspects of Optimisation Modelling 182\u003c\/p\u003e \u003cp\u003eStructural Choices 182\u003c\/p\u003e \u003cp\u003eUncertainty 183\u003c\/p\u003e \u003cp\u003eIntegrated Approaches to Optimisation 183\u003c\/p\u003e \u003cp\u003eModelling Issues and Tools 184\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 187\u003c\/p\u003e \u003cp\u003eThe Meaning, Origins and Uses of Monte Carlo Simulation 187\u003c\/p\u003e \u003cp\u003eDefinition and Origin 187\u003c\/p\u003e \u003cp\u003eLimitations of Sensitivity and Scenario Approaches 188\u003c\/p\u003e \u003cp\u003eKey Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189\u003c\/p\u003e \u003cp\u003eThe Nature of Model Outputs 190\u003c\/p\u003e \u003cp\u003eThe Applicability of Simulation Methods 190\u003c\/p\u003e \u003cp\u003eKey Process and Modelling Steps in Risk Modelling 191\u003c\/p\u003e \u003cp\u003eRisk Identification 191\u003c\/p\u003e \u003cp\u003eRisk Mapping and the Role of the Distribution of Input Values 191\u003c\/p\u003e \u003cp\u003eThe Modelling Context and the Meaning of Input Distributions 192\u003c\/p\u003e \u003cp\u003eThe Effect of Dependencies Between Inputs 192\u003c\/p\u003e \u003cp\u003eRandom Numbers and the Required Number of Recalculations or Iterations 193\u003c\/p\u003e \u003cp\u003eUsing Excel and VBA to Implement Risk and Simulation Models 194\u003c\/p\u003e \u003cp\u003eGeneration of Random Samples 194\u003c\/p\u003e \u003cp\u003eRepeated Recalculations and Results Storage 195\u003c\/p\u003e \u003cp\u003eExample: Cost Estimation with Uncertainty and Event Risks Using Excel\/VBA 196\u003c\/p\u003e \u003cp\u003eUsing Add-ins to Implement Risk and Simulation Models 196\u003c\/p\u003e \u003cp\u003eBenefits of Add-ins 196\u003c\/p\u003e \u003cp\u003eExample: Cost Estimation with Uncertainty and Event Risks Using @RISK 197\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart Five Excel Functions and Functionality 199\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 17 Core Arithmetic and Logical Functions 201\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 201\u003c\/p\u003e \u003cp\u003ePractical Applications 201\u003c\/p\u003e \u003cp\u003eExample: IF, AND, OR, NOT 202\u003c\/p\u003e \u003cp\u003eExample: MIN, MAX, MINA, MAXA 204\u003c\/p\u003e \u003cp\u003eExample: MINIFS and MAXIFS 204\u003c\/p\u003e \u003cp\u003eExample: COUNT, COUNTA, COUNTIF and Similar Functions 205\u003c\/p\u003e \u003cp\u003eExample: SUM, AVERAGE, AVERAGEA 206\u003c\/p\u003e \u003cp\u003eExample: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206\u003c\/p\u003e \u003cp\u003eExample: PRODUCT 207\u003c\/p\u003e \u003cp\u003eExample: SUMPRODUCT 209\u003c\/p\u003e \u003cp\u003eExample: SUBTOTAL 209\u003c\/p\u003e \u003cp\u003eExample: AGGREGATE 210\u003c\/p\u003e \u003cp\u003eExample: IFERROR 212\u003c\/p\u003e \u003cp\u003eExample: SWITCH 215\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 18 Array Functions and Formulae 217\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 217\u003c\/p\u003e \u003cp\u003eFunctions and Formulae: Definitions 217\u003c\/p\u003e \u003cp\u003eImplementation 217\u003c\/p\u003e \u003cp\u003eAdvantages and Disadvantages 218\u003c\/p\u003e \u003cp\u003ePractical Applications: Array Functions 218\u003c\/p\u003e \u003cp\u003eExample: Capex and Depreciation Schedules Using TRANSPOSE 218\u003c\/p\u003e \u003cp\u003eExample: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218\u003c\/p\u003e \u003cp\u003eExample: Cost Allocation Using Matrix Multiplication Using MMULT 219\u003c\/p\u003e \u003cp\u003eExample: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220\u003c\/p\u003e \u003cp\u003eExample: Summing Powers of Integers from 1 Onwards 222\u003c\/p\u003e \u003cp\u003ePractical Applications: Array Formulae 225\u003c\/p\u003e \u003cp\u003eExample: Finding First Positive Item in a List 225\u003c\/p\u003e \u003cp\u003eExample: Find a Conditional Maximum 226\u003c\/p\u003e \u003cp\u003eExample: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 19 Mathematical Functions 229\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 229\u003c\/p\u003e \u003cp\u003ePractical Applications 229\u003c\/p\u003e \u003cp\u003eExample: EXP and LN 229\u003c\/p\u003e \u003cp\u003eExample: ABS and SIGN 232\u003c\/p\u003e \u003cp\u003eExample: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233\u003c\/p\u003e \u003cp\u003eExample: MROUND, CEILING.MATH and FLOOR.MATH 235\u003c\/p\u003e \u003cp\u003eExample: MOD 236\u003c\/p\u003e \u003cp\u003eExample: SQRT and POWER 236\u003c\/p\u003e \u003cp\u003eExample: FACT and COMBIN 237\u003c\/p\u003e \u003cp\u003eExample: RAND() 238\u003c\/p\u003e \u003cp\u003eExample: SINE, ASIN, DEGREES and PI() 239\u003c\/p\u003e \u003cp\u003eExample: BASE and DECIMAL 241\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 20 Financial Functions 243\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 243\u003c\/p\u003e \u003cp\u003ePractical Applications 243\u003c\/p\u003e \u003cp\u003eExample: FVSCHEDULE 244\u003c\/p\u003e \u003cp\u003eExample: FV and PV 244\u003c\/p\u003e \u003cp\u003eExample: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246\u003c\/p\u003e \u003cp\u003eExample: NPV and IRR for a Buy or Lease Decision 248\u003c\/p\u003e \u003cp\u003eExample: SLN, DDB and VDB 250\u003c\/p\u003e \u003cp\u003eExample: YIELD 252\u003c\/p\u003e \u003cp\u003eExample: Duration of Cash Flows 252\u003c\/p\u003e \u003cp\u003eExample: DURATION and MDURATION 253\u003c\/p\u003e \u003cp\u003eExample: PDURATION and RRI 254\u003c\/p\u003e \u003cp\u003eOther Financial Functions 255\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 21 Statistical Functions 257\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 257\u003c\/p\u003e \u003cp\u003ePractical Applications: Position, Ranking and Central Values 258\u003c\/p\u003e \u003cp\u003eExample: Calculating Mean and Mode 258\u003c\/p\u003e \u003cp\u003eExample: Dynamic Sorting of Data Using LARGE 260\u003c\/p\u003e \u003cp\u003eExample: RANK.EQ 261\u003c\/p\u003e \u003cp\u003eExample: RANK.AVG 262\u003c\/p\u003e \u003cp\u003eExample: Calculating Percentiles 262\u003c\/p\u003e \u003cp\u003eExample: PERCENTRANK-type Functions 263\u003c\/p\u003e \u003cp\u003ePractical Applications: Spread and Shape 264\u003c\/p\u003e \u003cp\u003eExample: Generating a Histogram of Returns Using FREQUENCY 265\u003c\/p\u003e \u003cp\u003eExample: Variance, Standard Deviation and Volatility 267\u003c\/p\u003e \u003cp\u003eExample: Skewness and Kurtosis 271\u003c\/p\u003e \u003cp\u003eExample: One-sided Volatility (Semi-deviation) 272\u003c\/p\u003e \u003cp\u003ePractical Applications: Co-relationships and Dependencies 273\u003c\/p\u003e \u003cp\u003eExample: Scatter Plots (\u003ci\u003eX–Y \u003c\/i\u003eCharts) and Measuring Correlation 274\u003c\/p\u003e \u003cp\u003eExample: More on Correlation Coefficients and Rank Correlation 275\u003c\/p\u003e \u003cp\u003eExample: Measuring Co-variances 277\u003c\/p\u003e \u003cp\u003eExample: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277\u003c\/p\u003e \u003cp\u003ePractical Applications: Probability Distributions 280\u003c\/p\u003e \u003cp\u003eExample: Likelihood of a Given Number of Successes of an Oil Exploration Process 282\u003c\/p\u003e \u003cp\u003eExample: Frequency of Outcomes Within One or Two Standard Deviations 283\u003c\/p\u003e \u003cp\u003eExample: Creating Random Samples from Probability Distributions 283\u003c\/p\u003e \u003cp\u003eExample: User-defined Inverse Functions for Random Sampling 284\u003c\/p\u003e \u003cp\u003eExample: Values Associated with Probabilities for a Binomial Process 285\u003c\/p\u003e \u003cp\u003eExample: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285\u003c\/p\u003e \u003cp\u003eExample: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287\u003c\/p\u003e \u003cp\u003eExample: Confidence Intervals for the Standard Deviation Using Chi-squared 289\u003c\/p\u003e \u003cp\u003eExample: Confidence Interval for the Slope of Regression Line (or Beta) 289\u003c\/p\u003e \u003cp\u003ePractical Applications: More on Regression Analysis and Forecasting 291\u003c\/p\u003e \u003cp\u003eExample: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291\u003c\/p\u003e \u003cp\u003eExample: Using LINEST to Perform Multiple Regression 292\u003c\/p\u003e \u003cp\u003eExample: Using LOGEST to Find Exponential Fits 293\u003c\/p\u003e \u003cp\u003eExample: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294\u003c\/p\u003e \u003cp\u003eExample: Linear Forecasting Using FORECAST.LINEAR 295\u003c\/p\u003e \u003cp\u003eExample: Forecasting Using the FORECAST.ETS Set of Functions 296\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 22 Information Functions 299\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 299\u003c\/p\u003e \u003cp\u003ePractical Applications 300\u003c\/p\u003e \u003cp\u003eExample: In-formula Comments Using ISTEXT, ISNUMBER or N 300\u003c\/p\u003e \u003cp\u003eExample: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300\u003c\/p\u003e \u003cp\u003eExample: Detecting Consistency of Data in a Database 301\u003c\/p\u003e \u003cp\u003eExample: Consistent use of “N\/A” in Models 301\u003c\/p\u003e \u003cp\u003eExample: Applications of the INFO and CELL Functions: An Overview 303\u003c\/p\u003e \u003cp\u003eExample: Creating Updating Labels that Refer to Data or Formulae 303\u003c\/p\u003e \u003cp\u003eExample: Showing the User Which Recalculation Mode the File Is On 305\u003c\/p\u003e \u003cp\u003eExample: Finding the Excel Version Used and Creating Backward Compatible Formulae 305\u003c\/p\u003e \u003cp\u003eExample: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 23 Date and Time Functions 307\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 307\u003c\/p\u003e \u003cp\u003ePractical Applications 308\u003c\/p\u003e \u003cp\u003eExample: Task Durations, Resource and Cost Estimation 308\u003c\/p\u003e \u003cp\u003eExample: Keeping Track of Bookings, Reservations or Other Activities 308\u003c\/p\u003e \u003cp\u003eExample: Creating Precise Time Axes 309\u003c\/p\u003e \u003cp\u003eExample: Calculating the Year and Month of a Date 309\u003c\/p\u003e \u003cp\u003eExample: Calculating the Quarter in Which a Date Occurs 310\u003c\/p\u003e \u003cp\u003eExample: Creating Time-based Reports and Models from Data Sets 311\u003c\/p\u003e \u003cp\u003eExample: Finding Out on What Day of the Week You Were Born 311\u003c\/p\u003e \u003cp\u003eExample: Calculating the Date of the Last Friday of Every Month 311\u003c\/p\u003e \u003cp\u003eExample: the DATEDIF Function and Completed Time Periods 312\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 24 Text Functions and Functionality 313\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 313\u003c\/p\u003e \u003cp\u003ePractical Applications 314\u003c\/p\u003e \u003cp\u003eExample: Joining Text Using CONCAT and TEXTJOIN 314\u003c\/p\u003e \u003cp\u003eExample: Splitting Data Using the Text-to-columns Wizard 315\u003c\/p\u003e \u003cp\u003eExample: Converting Numerical Text to Numbers 316\u003c\/p\u003e \u003cp\u003eExample: Dynamic Splitting Text into Components I 316\u003c\/p\u003e \u003cp\u003eExample: Dynamic Splitting Text into Components II 317\u003c\/p\u003e \u003cp\u003eExample: Comparing LEFT, RIGHT, MID and LEN 317\u003c\/p\u003e \u003cp\u003eExample: Dynamic Splitting Text into Components III 318\u003c\/p\u003e \u003cp\u003eExample: Comparing FIND and SEARCH 319\u003c\/p\u003e \u003cp\u003eExample: the UPPER and LOWER Functions 319\u003c\/p\u003e \u003cp\u003eExample: the PROPER Function 319\u003c\/p\u003e \u003cp\u003eExample: the EXACT Function 320\u003c\/p\u003e \u003cp\u003eExample: Comparing REPLACE with SUBSTITUTE 320\u003c\/p\u003e \u003cp\u003eExample: the REPT Function 320\u003c\/p\u003e \u003cp\u003eExample: the CLEAN and TRIM Functions 321\u003c\/p\u003e \u003cp\u003eExample: Updating Model Labels and Graph Titles 322\u003c\/p\u003e \u003cp\u003eExample: Creating Unique Identifiers or Keys for Data Matching 323\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 25 Lookup and Reference Functions 325\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 325\u003c\/p\u003e \u003cp\u003ePractical Applications: Basic Referencing Processes 326\u003c\/p\u003e \u003cp\u003eExample: the ROW and COLUMN Functions 326\u003c\/p\u003e \u003cp\u003eExample: the ROWS and COLUMNS Functions 327\u003c\/p\u003e \u003cp\u003eExample: Use of the ADDRESS Function and the Comparison with CELL 327\u003c\/p\u003e \u003cp\u003ePractical Applications: Further Referencing Processes 328\u003c\/p\u003e \u003cp\u003eExample: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328\u003c\/p\u003e \u003cp\u003eExample: Charts that Can Use Multiple or Flexible Data Sources 330\u003c\/p\u003e \u003cp\u003eExample: Reversing and Transposing Data Using INDEX or OFFSET 331\u003c\/p\u003e \u003cp\u003eExample: Shifting Cash Flows or Other Items over Time 334\u003c\/p\u003e \u003cp\u003eExample: Depreciation Schedules with Triangle Calculations 334\u003c\/p\u003e \u003cp\u003ePractical Applications: Combining Matching and Reference Processes 335\u003c\/p\u003e \u003cp\u003eExample: Finding the Period in Which a Condition is Met Using MATCH 335\u003c\/p\u003e \u003cp\u003eExample: Finding Non-contiguous Scenario Data Using Matching Keys 336\u003c\/p\u003e \u003cp\u003eExample: Creating and Finding Matching Text Fields or Keys 336\u003c\/p\u003e \u003cp\u003eExample: Combining INDEX with MATCH 337\u003c\/p\u003e \u003cp\u003eExample: Comparing INDEX-MATCH with V- and HLOOKUP 338\u003c\/p\u003e \u003cp\u003eExample: Comparing INDEX-MATCH with LOOKUP 343\u003c\/p\u003e \u003cp\u003eExample: Finding the Closest Matching Value Using Array and Other Function Combinations 344\u003c\/p\u003e \u003cp\u003ePractical Applications: More on the OFFSET Function and Dynamic Ranges 345\u003c\/p\u003e \u003cp\u003eExample: Flexible Ranges Using OFFSET (I) 345\u003c\/p\u003e \u003cp\u003eExample: Flexible Ranges Using OFFSET (II) 346\u003c\/p\u003e \u003cp\u003eExample: Flexible Ranges Using OFFSET (III) 347\u003c\/p\u003e \u003cp\u003eExample: Flexible Ranges Using OFFSET (IV) 347\u003c\/p\u003e \u003cp\u003ePractical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349\u003c\/p\u003e \u003cp\u003eExample: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349\u003c\/p\u003e \u003cp\u003eExample: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351\u003c\/p\u003e \u003cp\u003eExample: Other Uses of INDIRECT – Cascading Drop-down Lists 352\u003c\/p\u003e \u003cp\u003ePractical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352\u003c\/p\u003e \u003cp\u003eExample: Model Navigation Using Named Ranges and Hyperlinks 353\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 26 Filters, Database Functions and PivotTables 355\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 355\u003c\/p\u003e \u003cp\u003eIssues Common to Working with Sets of Data 356\u003c\/p\u003e \u003cp\u003eCleaning and Manipulating Source Data 356\u003c\/p\u003e \u003cp\u003eStatic or Dynamic Queries 356\u003c\/p\u003e \u003cp\u003eCreation of New Fields or Complex Filters? 357\u003c\/p\u003e \u003cp\u003eExcel Databases and Tables 357\u003c\/p\u003e \u003cp\u003eAutomation Using Macros 359\u003c\/p\u003e \u003cp\u003ePractical Applications: Filters 359\u003c\/p\u003e \u003cp\u003eExample: Applying Filters and Inspecting Data for Errors or Possible Corrections 359\u003c\/p\u003e \u003cp\u003eExample: Identification of Unique Items and Unique Combinations 362\u003c\/p\u003e \u003cp\u003eExample: Using Filters to Remove Blanks or Other Specified Items 363\u003c\/p\u003e \u003cp\u003eExample: Extraction of Data Using Filters 365\u003c\/p\u003e \u003cp\u003eExample: Adding Criteria Calculations to the Data Set 365\u003c\/p\u003e \u003cp\u003eExample: Use of Tables 366\u003c\/p\u003e \u003cp\u003eExample: Extraction of Data Using Advanced Filters 369\u003c\/p\u003e \u003cp\u003ePractical Applications: Database Functions 370\u003c\/p\u003e \u003cp\u003eExample: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370\u003c\/p\u003e \u003cp\u003eExample: Implementing a Between Query 371\u003c\/p\u003e \u003cp\u003eExample: Implementing Multiple Queries 371\u003c\/p\u003e \u003cp\u003ePractical Applications: PivotTables 373\u003c\/p\u003e \u003cp\u003eExample: Exploring Summary Values of Data Sets 373\u003c\/p\u003e \u003cp\u003eExample: Exploring Underlying Elements of the Summary Items 376\u003c\/p\u003e \u003cp\u003eExample: Adding Slicers 376\u003c\/p\u003e \u003cp\u003eExample: Timeline Slicers 378\u003c\/p\u003e \u003cp\u003eExample: Generating Reports Which Ignore Errors or Other Specified Items 380\u003c\/p\u003e \u003cp\u003eExample: Using the GETPIVOTDATA Functions 380\u003c\/p\u003e \u003cp\u003eExample: Creating PivotCharts 382\u003c\/p\u003e \u003cp\u003eExample: Using the Excel Data Model to Link Tables 383\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 27 Selected Short-cuts and Other Features 387\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 387\u003c\/p\u003e \u003cp\u003eKey Short-cuts and Their Uses 387\u003c\/p\u003e \u003cp\u003eEntering and Modifying Data and Formulae 388\u003c\/p\u003e \u003cp\u003eFormatting 390\u003c\/p\u003e \u003cp\u003eAuditing, Navigation and Other Items 391\u003c\/p\u003e \u003cp\u003eExcel KeyTips 393\u003c\/p\u003e \u003cp\u003eOther Useful Excel Tools and Features 393\u003c\/p\u003e \u003cp\u003eSparklines 393\u003c\/p\u003e \u003cp\u003eThe Camera Tool 393\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart Six Foundations of VBA and Macros 395\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 28 Getting Started 397\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 397\u003c\/p\u003e \u003cp\u003eMain Uses of VBA 397\u003c\/p\u003e \u003cp\u003eTask Automation 398\u003c\/p\u003e \u003cp\u003eCreating User-defined Functions 398\u003c\/p\u003e \u003cp\u003eDetecting and Reacting to Model Events 398\u003c\/p\u003e \u003cp\u003eEnhancing or Managing the User Interface 399\u003c\/p\u003e \u003cp\u003eApplication Development 399\u003c\/p\u003e \u003cp\u003eCore Operations 399\u003c\/p\u003e \u003cp\u003eAdding the Developer Tab to Excel’s Toolbar 399\u003c\/p\u003e \u003cp\u003eThe Visual Basic Editor 399\u003c\/p\u003e \u003cp\u003eRecording Macros 401\u003c\/p\u003e \u003cp\u003eTypical Adaptations Required When Using Recorded Code 402\u003c\/p\u003e \u003cp\u003eWriting Code 403\u003c\/p\u003e \u003cp\u003eRunning Code 404\u003c\/p\u003e \u003cp\u003eDebugging Techniques 405\u003c\/p\u003e \u003cp\u003eSimple Examples 406\u003c\/p\u003e \u003cp\u003eExample: Using Excel Cell Values in VBA 406\u003c\/p\u003e \u003cp\u003eExample: Using Named Excel Ranges for Robustness and Flexibility 407\u003c\/p\u003e \u003cp\u003eExample: Placing a Value from VBA Code into an Excel Range 408\u003c\/p\u003e \u003cp\u003eExample: Replacing Copy\/Paste with an Assignment 409\u003c\/p\u003e \u003cp\u003eExample: A Simple User-defined Function 409\u003c\/p\u003e \u003cp\u003eExample: Displaying a Message when a Workbook is Opened 410\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 29 Working with Objects and Ranges 413\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 413\u003c\/p\u003e \u003cp\u003eOverview of the Object Model 413\u003c\/p\u003e \u003cp\u003eObjects, Properties, Methods and Events 413\u003c\/p\u003e \u003cp\u003eObject Hierarchies and Collections 414\u003c\/p\u003e \u003cp\u003eUsing Set. . .=. . . . 415\u003c\/p\u003e \u003cp\u003eUsing the With. . .End With Construct 415\u003c\/p\u003e \u003cp\u003eFinding Alternatives to the Selection or Activation of Ranges and Objects 416\u003c\/p\u003e \u003cp\u003eWorking with Range Objects: Some Key Elements 416\u003c\/p\u003e \u003cp\u003eBasic Syntax Possibilities and Using Named Ranges 416\u003c\/p\u003e \u003cp\u003eNamed Ranges and Named Variables 417\u003c\/p\u003e \u003cp\u003eThe CurrentRegion Property 417\u003c\/p\u003e \u003cp\u003eThe xlCellTypeLastCell Property 418\u003c\/p\u003e \u003cp\u003eWorksheet Names and Code Names 419\u003c\/p\u003e \u003cp\u003eThe UsedRange Property 419\u003c\/p\u003e \u003cp\u003eThe Cells Property 420\u003c\/p\u003e \u003cp\u003eThe Offset Property 421\u003c\/p\u003e \u003cp\u003eThe Union Method 421\u003c\/p\u003e \u003cp\u003eInputBox and MsgBox 421\u003c\/p\u003e \u003cp\u003eApplication.InputBox 422\u003c\/p\u003e \u003cp\u003eDefining Multi-cell Ranges 422\u003c\/p\u003e \u003cp\u003eUsing Target to React to Worksheet Events 422\u003c\/p\u003e \u003cp\u003eUsing Target to React to Workbook Events 423\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 30 Controlling Execution 425\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 425\u003c\/p\u003e \u003cp\u003eCore Topics in Overview 425\u003c\/p\u003e \u003cp\u003eInput Boxes and Message Boxes 425\u003c\/p\u003e \u003cp\u003eFor. . .Next Loops 425\u003c\/p\u003e \u003cp\u003eFor Each. . . In. . .Next 426\u003c\/p\u003e \u003cp\u003eIf. . .Then 427\u003c\/p\u003e \u003cp\u003eSelect Case. . .End Select 427\u003c\/p\u003e \u003cp\u003eGoTo 428\u003c\/p\u003e \u003cp\u003eDo. . .While\/Until. . .Loop 428\u003c\/p\u003e \u003cp\u003eCalculation and Calculate 429\u003c\/p\u003e \u003cp\u003eScreen Updating 432\u003c\/p\u003e \u003cp\u003eMeasuring Run Time 432\u003c\/p\u003e \u003cp\u003eDisplaying Alerts 433\u003c\/p\u003e \u003cp\u003eAccessing Excel Worksheet Functions 433\u003c\/p\u003e \u003cp\u003eExecuting Procedures Within Procedures 434\u003c\/p\u003e \u003cp\u003eAccessing Add-ins 435\u003c\/p\u003e \u003cp\u003ePractical Applications 435\u003c\/p\u003e \u003cp\u003eExample: Numerical Looping 435\u003c\/p\u003e \u003cp\u003eExample: Listing the Names of All Worksheets in a Workbook 436\u003c\/p\u003e \u003cp\u003eExample: Adding a New Worksheet to a Workbook 437\u003c\/p\u003e \u003cp\u003eExample: Deleting Specific Worksheets from a Workbook 437\u003c\/p\u003e \u003cp\u003eExample: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 31 Writing Robust Code 441\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 441\u003c\/p\u003e \u003cp\u003eKey Principles 441\u003c\/p\u003e \u003cp\u003eFrom the Specific to the General 441\u003c\/p\u003e \u003cp\u003eAdapting Recorded Code for Robustness 442\u003c\/p\u003e \u003cp\u003eEvent Code 442\u003c\/p\u003e \u003cp\u003eComments and Indented Text 442\u003c\/p\u003e \u003cp\u003eModular Code 443\u003c\/p\u003e \u003cp\u003ePassing Arguments ByVal or ByRef 443\u003c\/p\u003e \u003cp\u003eFull Referencing 445\u003c\/p\u003e \u003cp\u003eUsing Worksheet Code Numbers 447\u003c\/p\u003e \u003cp\u003eAssignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447\u003c\/p\u003e \u003cp\u003eWorking with Ranges Instead of Individual Cells 448\u003c\/p\u003e \u003cp\u003eData Types and Variable Declaration 448\u003c\/p\u003e \u003cp\u003eChoice of Names 449\u003c\/p\u003e \u003cp\u003eWorking with Arrays in VBA 450\u003c\/p\u003e \u003cp\u003eUnderstanding Error Codes: An Introduction 451\u003c\/p\u003e \u003cp\u003eFurther Approaches to Testing, Debugging and Error-handling 452\u003c\/p\u003e \u003cp\u003eGeneral Techniques 452\u003c\/p\u003e \u003cp\u003eDebugging Functions 453\u003c\/p\u003e \u003cp\u003eImplementing Error-handling Procedures 454\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 32 Manipulation and Analysis of Data Sets with VBA 455\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 455\u003c\/p\u003e \u003cp\u003ePractical Applications 455\u003c\/p\u003e \u003cp\u003eExample: Working Out the Size of a Range 455\u003c\/p\u003e \u003cp\u003eExample: Defining the Data Set at Run Time Based on User Input 457\u003c\/p\u003e \u003cp\u003eExample: Working Out the Position of a Data Set Automatically 457\u003c\/p\u003e \u003cp\u003eExample: Reversing Rows (or Columns) of Data I: Placement in a New Range 459\u003c\/p\u003e \u003cp\u003eExample: Reversing Rows (or Columns) of Data II: In Place 460\u003c\/p\u003e \u003cp\u003eExample: Automation of Other Data-related Excel Procedures 461\u003c\/p\u003e \u003cp\u003eExample: Deleting Rows Containing Blank Cells 462\u003c\/p\u003e \u003cp\u003eExample: Deleting Blank Rows 463\u003c\/p\u003e \u003cp\u003eExample: Automating the Use of Filters to Remove Blanks or Other Specified Items 464\u003c\/p\u003e \u003cp\u003eExample: Performing Multiple Database Queries 468\u003c\/p\u003e \u003cp\u003eExample: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 33 User-defined Functions 473\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroduction 473\u003c\/p\u003e \u003cp\u003eBenefits of Creating User-defined Functions 473\u003c\/p\u003e \u003cp\u003eSyntax and Implementation 474\u003c\/p\u003e \u003cp\u003ePractical Applications 475\u003c\/p\u003e \u003cp\u003eExample: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476\u003c\/p\u003e \u003cp\u003eExample: A Wrapper to Access the Latest Excel Function Version 477\u003c\/p\u003e \u003cp\u003eExample: Replication of IFERROR for Compatibility with Excel 2003 478\u003c\/p\u003e \u003cp\u003eExample: Sum of Absolute Errors 479\u003c\/p\u003e \u003cp\u003eExample: Replacing General Excel Calculation Tables or Ranges 480\u003c\/p\u003e \u003cp\u003eExample: Using Application.Caller to Generate a Time Axis as an Array Function 480\u003c\/p\u003e \u003cp\u003eExample: User-defined Array Functions in Rows and Columns 482\u003c\/p\u003e \u003cp\u003eExample: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484\u003c\/p\u003e \u003cp\u003eExample: Sheet Reference Functions 485\u003c\/p\u003e \u003cp\u003eExample: Statistical Moments when Frequencies Are Known 487\u003c\/p\u003e \u003cp\u003eExample: Rank Order Correlation 489\u003c\/p\u003e \u003cp\u003eExample: Semi-deviation of a Data Set 491\u003c\/p\u003e \u003cp\u003eIndex 493\u003c\/p\u003e \u003cp\u003e\u003cb\u003eMICHAEL REES, D.PHIL., MBA,\u003c\/b\u003e 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.\u003c\/p\u003e \u003cp\u003eHe has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from\u003c\/p\u003e","brand":"Wiley","offers":[{"title":"Default Title","offer_id":47989851652325,"sku":"NP9781118904015","price":105.0,"currency_code":"USD","in_stock":false}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/1842\/7735\/files\/9781118904015.jpg?v=1761785674","url":"https:\/\/k12savings.com\/products\/principles-of-financial-modelling-isbn-9781118904015","provider":"K12savings","version":"1.0","type":"link"}