Using Excel for Business and Financial Modelling, 3rd Edition

Using Excel for Business and Financial Modelling, 3rd Edition

by Danielle Stein Fairhurst
Released April 2019
Publisher(s): Wiley
ISBN: 9781119520382

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

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

Start your free trial

Book description

A hands-on guide to using Excel in the business context

First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away.

Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques.

• Provides the perfect mix of practice and theory

• Helps you become a DIY Excel modelling specialist

• Includes updates for Excel 2019/365 and Excel for Mac

• May be used as an accompaniment to the author’s online and face-to-face training courses

Many people are often overwhelmed by the hundreds of tools in Excel, and this book gives clarity to the ones you need to know in order to perform your job more efficiently. This book also demystifies the technical, design, logic and financial skills you need for business and financial modelling.

Table of contents

  1. Cover
  2. Preface
    1. BOOK OVERVIEW
    2. ACKNOWLEDGEMENTS
  3. CHAPTER 1: What is Financial Modelling?
    1. WHAT'S THE DIFFERENCE BETWEEN A SPREADSHEET AND A FINANCIAL MODEL?
    2. TYPES AND PURPOSES OF FINANCIAL MODELS
    3. TOOL SELECTION
    4. WHAT SKILLS DO YOU NEED TO BE A GOOD FINANCIAL MODELLER?
    5. THE “IDEAL” FINANCIAL MODELLER
    6. SUMMARY
    7. NOTES
  4. CHAPTER 2: Building a Model
    1. MODEL DESIGN
    2. THE GOLDEN RULES FOR MODEL DESIGN
    3. DESIGN ISSUES
    4. THE WORKBOOK ANATOMY OF A MODEL
    5. PROJECT PLANNING YOUR MODEL
    6. MODEL LAYOUT FLOWCHARTING
    7. STEPS TO BUILDING A MODEL
    8. INFORMATION REQUESTS
    9. VERSION-CONTROL DOCUMENTATION
    10. SUMMARY
  5. CHAPTER 3: Best-Practice Principles of Modelling
    1. DOCUMENT YOUR ASSUMPTIONS
    2. LINKING, NOT HARDCODING
    3. ENTER DATA ONLY ONCE
    4. AVOID BAD HABITS
    5. USE CONSISTENT FORMULAS
    6. FORMAT AND LABEL CLEARLY
    7. METHODS AND TOOLS OF ASSUMPTIONS DOCUMENTATION
    8. LINKED DYNAMIC TEXT ASSUMPTIONS DOCUMENTATION
    9. WHAT MAKES A GOOD MODEL?
    10. SUMMARY
    11. NOTE
  6. CHAPTER 4: Financial Modelling Techniques
    1. THE PROBLEM WITH EXCEL
    2. ERROR AVOIDANCE STRATEGIES
    3. HOW LONG SHOULD A FORMULA BE?
    4. LINKING TO EXTERNAL FILES
    5. BUILDING ERROR CHECKS
    6. CIRCULAR REFERENCES
    7. SUMMARY
    8. NOTES
  7. CHAPTER 5: Using Excel in Financial Modelling
    1. FORMULAS AND FUNCTIONS IN EXCEL
    2. EXCEL VERSIONS
    3. HANDY EXCEL SHORTCUTS
    4. CELL REFERENCING BEST PRACTICES
    5. NAMED RANGES
    6. BASIC EXCEL FUNCTIONS
    7. LOGICAL FUNCTIONS
    8. NESTING LOGICAL FUNCTIONS
    9. SUMMARY
  8. CHAPTER 6: Functions for Financial Modelling
    1. AGGREGATION FUNCTIONS
    2. LOOKUP FUNCTIONS
    3. NESTING INDEX AND MATCH
    4. OFFSET FUNCTION
    5. REGRESSION ANALYSIS
    6. CHOOSE FUNCTION
    7. WORKING WITH DATES
    8. FINANCIAL PROJECT EVALUATION FUNCTIONS
    9. LOAN CALCULATIONS
    10. SUMMARY
  9. CHAPTER 7: Tools for Model Display
    1. BASIC FORMATTING
    2. CUSTOM FORMATTING
    3. CONDITIONAL FORMATTING
    4. SPARKLINES
    5. BULLETPROOFING YOUR MODEL
    6. CUSTOMISING THE DISPLAY SETTINGS
    7. FORM CONTROLS
    8. SUMMARY
  10. CHAPTER 8: Tools for Financial Modelling
    1. HIDING SECTIONS OF A MODEL
    2. GROUPING
    3. ARRAY FORMULAS
    4. GOAL SEEKING
    5. STRUCTURED REFERENCE TABLES
    6. PIVOTTABLES
    7. MACROS
    8. SUMMARY
  11. CHAPTER 9: Common Uses of Tools in Financial Modelling
    1. ESCALATION METHODS FOR MODELLING
    2. UNDERSTANDING NOMINAL AND EFFECTIVE (REAL) RATES
    3. CALCULATING A CUMULATIVE SUM (RUNNING TOTALS)
    4. HOW TO CALCULATE A PAYBACK PERIOD
    5. WEIGHTED AVERAGE COST OF CAPITAL (WACC)
    6. BUILDING A TIERING TABLE
    7. MODELLING DEPRECIATION METHODS
    8. BREAK-EVEN ANALYSIS
    9. SUMMARY
  12. CHAPTER 10: Model Review
    1. REBUILDING AN INHERITED MODEL
    2. IMPROVING MODEL PERFORMANCE
    3. AUDITING A FINANCIAL MODEL
    4. SUMMARY
    5. APPENDIX: QA LOG
  13. CHAPTER 11: Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling
    1. WHAT ARE THE DIFFERENCES BETWEEN SCENARIO, SENSITIVITY, AND WHAT-IF ANALYSES?
    2. OVERVIEW OF SCENARIO ANALYSIS TOOLS AND METHODS
    3. ADVANCED CONDITIONAL FORMATTING
    4. COMPARING SCENARIO METHODS
    5. ADDING PROBABILITY TO A DATA TABLE
    6. SUMMARY
  14. CHAPTER 12: Presenting Model Output
    1. PREPARING AN ORAL PRESENTATION FOR MODEL RESULTS
    2. PREPARING A GRAPHIC OR WRITTEN PRESENTATION FOR MODEL RESULTS
    3. CHART TYPES
    4. WORKING WITH CHARTS
    5. HANDY CHARTING HINTS
    6. DYNAMIC NAMED RANGES
    7. CHARTING WITH TWO DIFFERENT AXES AND CHART TYPES
    8. BUBBLE CHARTS
    9. CREATING A DYNAMIC CHART
    10. WATERFALL CHARTS
    11. SUMMARY
  15. About the Author
  16. About the Website
    1. DOWNLOADABLE RESOURCES
  17. Index
  18. End User License Agreement

Product information

  • Title: Using Excel for Business and Financial Modelling, 3rd Edition
  • Author(s): Danielle Stein Fairhurst
  • Release date: April 2019
  • Publisher(s): Wiley
  • ISBN: 9781119520382