Using Excel for Business and Financial Modelling : A Practical Guide.

Cover -- Title Page -- Copyright -- Contents -- Preface -- Book Overview -- Acknowledgements -- Chapter 1 What is Financial Modelling? -- What's the Difference Between a Spreadsheet and a Financial Model? -- Types and Purposes of Financial Models -- Tool Selection -- Is Excel Really the Best Option? -- Evaluating Modelling Tools -- 32-Bit versus 64-Bit Microsoft Excel -- What Skills do You Need to be a Good Financial Modeller? -- Spreadsheet and Technical Excel Skills -- Industry Knowledge -- Accounting Knowledge -- Business Knowledge -- Aesthetic Design Skills -- Communication and Language Skills -- Numeracy Skills -- Ability to Think Logically -- The "Ideal" Financial Modeller -- What's the Typical Background for a Financial Modeller? -- Training Courses -- Summary -- Chapter 2 Building a Model -- Model Design -- Practical Example 1: Assumptions Layout -- Practical Example 2: Summary Categorisation -- The Golden Rules for Model Design -- Separate Inputs, Calculations, and Results, where Possible -- Use Each Column for the Same Purpose -- Use One Formula per Row or Column -- Refer to the Left and Above -- Use Multiple Worksheets -- Include Documentation Sheets -- Design Issues -- The Workbook Anatomy of a Model -- Workbook Anatomy Issues -- Project Planning Your Model -- How Long Does it Take to Build a Financial Model? -- Building a Model Under Pressure -- Model Layout Flowcharting -- Practical Exercise: Model Design Customer Support Pricing Model -- Steps to Building a Model -- The Streamlined Version -- The Team Version -- Information Requests -- Version-Control Documentation -- File Structure -- Summary -- Chapter 3 Best-Practice Principles of Modelling -- Document Your Assumptions -- Linking, Not Hardcoding -- Enter Data Only Once -- Avoid Bad Habits -- Use Consistent Formulas -- Format and Label Clearly.

Methods and Tools of Assumptions Documentation -- In-Cell Comments -- "Red Triangle" Comments -- Footnoting -- Hyperlinks -- Hardcoded Text -- Linked Dynamic Text Assumptions Documentation -- Practical Exercise 1 -- Practical Exercise 2 -- Practical Exercise 3 -- What Makes a Good Model? -- Standards in Financial Modelling -- Summary -- Chapter 4 Financial Modelling Techniques -- The Problem With Excel -- Error Avoidance Strategies -- Avoiding Simple Formula Errors -- How Long Should a Formula Be? -- Linking to External Files -- Why You Should Use Named Ranges in External Links -- Dealing with Links and the Potential Errors They Can Cause -- Building Error Checks -- Error-Check Exercise -- Allowing Tolerance for Error -- Error-Check Alerts -- Avoid Error Displays in Formulas -- Circular References -- How to Fix Circular References -- Circular References in Interest Calculations -- Enabling Iterative Calculations -- Summary -- Chapter 5 Using Excel in Financial Modelling -- Formulas and Functions in Excel -- Choose the Simplest Solution -- Excel Versions -- What is the Difference Between a Perpetual and a Subscription-Based Licence? -- Excel Version Compatibility -- Handy Excel Shortcuts -- Windows Shortcuts -- Mac Shortcuts -- Cell Referencing Best Practices -- Relative and Absolute Referencing -- Mixed Referencing -- Named Ranges -- Why Use a Named Range? -- Basic Excel Functions -- Sum() -- Max() -- Min() -- Average() -- Combining Basic Functions -- Nesting: Combining Simple Functions to Create Complex Formulas -- Logical Functions -- IF Statement -- Nesting Logical Functions -- AND Statement -- OR Statement -- Nested IF Functions -- Summary -- Chapter 6 Functions for Financial Modelling -- Aggregation Functions -- COUNTIF -- SUMIF -- AVERAGEIF -- COUNTIFS -- SUMIFS -- AVERAGEIFS -- LOOKUP Functions -- VLOOKUP -- HLOOKUP (Horizontal Lookup).

LOOKUP Function -- Nesting Index and Match -- Using INDEX and MATCH to Create a More Robust Formula -- OFFSET Function -- Using an OFFSET to Model Cash Flow -- Nesting OFFSET and COLUMN Formulas -- Regression Analysis -- Using a FORECAST or TREND Function -- Creating a Forecast with the Forecast Sheet Tool -- Choose Function -- Using a Nested CHOOSE Formula with Dates -- Working With Dates -- Handy Functions -- Date Format Dilemma -- Long and Short Date Formatting -- Financial Project Evaluation Functions -- Net Present Value -- Internal Rate of Return -- Loan Calculations -- Loan-Interest Calculation Method -- Loan Repayment According to an Amortisation Schedule -- Why Does the Interest Amount Decrease on a Fixed Interest Rate? -- Summary -- Chapter 7 Tools for Model Display -- Basic Formatting -- Custom Formatting -- Custom Currency Symbols -- Custom Formatting in Reporting -- Conditional Formatting -- To Apply Conditional Formatting -- To Remove Conditional Formatting -- Data Bars -- Icon Sets and Colour Scales -- Enhancements to Conditional Formatting -- Sparklines -- Editing Sparklines -- Bulletproofing Your Model -- Protection -- Customising the Display Settings -- Useful Display Settings -- Minimising the Ribbon -- Restrict the Work Area -- Restricting Incorrect Data Entry with Data Validations -- Using Validations to Create a Drop-Down List -- Form Controls -- Accessing Form Controls -- Showing the Developer Tab in the Ribbon -- Checkboxes -- Combo Boxes -- Boolean Logic (Binary Code) -- Form Controls versus ActiveX Controls -- Summary -- Chapter 8 Tools for Financial Modelling -- Hiding Sections of a Model -- Columns and Rows -- Sheets -- Errors Caused by Hiding -- Grouping -- Array Formulas -- Advantages and Disadvantages of Using Array Formulas -- Simple Array Formula Example -- Array Formula Uses -- Transposing Data Using an Array.

Goal Seeking -- Structured Reference Tables -- PivotTables -- Using PivotTables in Financial Models -- Other Things You Should Know About PivotTables -- Building a PivotTable -- Filtering and Using Slicers -- Macros -- Macro Settings -- Creating Macro Buttons -- Macros in Financial Modelling Case Studies -- Dangers and Pitfalls of Using Macros -- Summary -- Chapter 9 Common Uses of Tools in Financial Modelling -- Escalation Methods for Modelling -- Using Absolute (Fixed) Growth Rate -- Using Relative (Varying) Growth Rates -- Using Exponential Operations on an Absolute (Fixed) Growth Rate -- Practical Usage of Exponential Growth Rates -- Understanding Nominal and Effective (Real) Rates -- Adjusting Loan Rates with NOMINAL and EFFECT Functions -- Calculating a Cumulative Sum (Running Totals) -- How to Calculate a Payback Period -- Simple Payback Calculation -- More Complex Payback Calculation -- Weighted Average Cost of Capital (WACC) -- How to Calculate the WACC -- Building a Tiering Table -- Flat Tiering Structure -- Progressive Tiering Structure -- Modelling Depreciation Methods -- Why Depreciate? -- Depreciation Methods -- Declining Balance Value Methods -- Calculating Depreciation at the End of Useful Life -- Break-Even Analysis -- Calculating the Break-Even Point -- Charting the Break-Even Point -- Break-Even Analysis Using Goal Seek -- Summary -- Chapter 10 Model Review -- Rebuilding an Inherited Model -- Removing Redundant Assumptions and Source Data in a Model -- Formula Auditing -- Error-Checking Tools -- Inspecting the Workbook -- Improving Model Performance -- Reducing File Size -- Improving Excel Memory and File Performance -- Auditing a Financial Model -- Informal Check -- QA Procedure -- QA Logic Testing -- Input Testing -- Summary -- Appendix: QA Log.

Chapter 11 Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling -- What are the Differences Between Scenario, Sensitivity, and What-If Analyses? -- Scenarios and Sensitivity Analysis in a Business Case -- Stress Testing a Financial Model versus a Business -- Overview of Scenario Analysis Tools and Methods -- Manual Drop-Downs -- Scenario Manager -- Using Data Tables for Sensitivity Analysis -- Advanced Conditional Formatting -- Comparing Scenario Methods -- Manual Sensitivity Analysis -- Creating Scenarios Using a Two-Variable Data Table -- Adding Probability to a Data Table -- Summary -- Chapter 12 Presenting Model Output -- Preparing an Oral Presentation for Model Results -- Summarising and Displaying Model Results -- Preparing a Graphic or Written Presentation for Model Results -- Additional Tips for Charting -- Chart Types -- Choosing a Chart Type -- Summary of Common Charts and Applications -- Detailed Chart Types -- Another Example with More Data -- Working with Charts -- Changing the Type of Chart -- Changing the Source Data -- Saving a Chart as a Template -- Handy Charting Hints -- Dynamic Named Ranges -- Using a Dynamic Range Name in a Chart -- Charting with Two Different Axes and Chart Types -- Creating a Combo Chart -- Bubble Charts -- Creating a Dynamic Chart -- Additional Exercise -- Waterfall Charts -- Summary -- About the Author -- About the Website -- Downloadable Resources -- Models -- Technical Exercise Files -- Index -- EULA.

Description based on publisher supplied metadata and other sources.

Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2024. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.