{"product_id":"principles-of-financial-modelling-9781118904015","title":"Principles of Financial Modelling","description":"\u003cb\u003eBook Synopsis\u003c\/b\u003e\u003cbr\u003e\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, balan\u003cbr\u003e\u003cbr\u003e\u003cb\u003eTable of Contents\u003c\/b\u003e\u003cbr\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\n\u003c\/li\u003e\n\u003c\/ul\u003e","brand":"John Wiley \u0026 Sons Inc","offers":[{"title":"Default Title","offer_id":49406943756631,"sku":"9781118904015","price":999.99,"currency_code":"GBP","in_stock":false}],"url":"https:\/\/bookcurl.com\/products\/principles-of-financial-modelling-9781118904015","provider":"Book Curl","version":"1.0","type":"link"}