You are previewing Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, Edition Revised for Excel 2013.
O'Reilly logo
Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, Edition Revised for Excel 2013

Book Description

Utilise Excel 2013 capabilities to build effective financial models

Using Excel for Business Analysis, Revised Edition provides practical guidance for anyone looking to build financial models. Whether for business proposals, opportunity evaluation, financial reports, or any other business finance application, this book shows you how to design, create, and test your model, then present your results effectively using Excel 2013. The book opens with a general guide to financial modelling, with each subsequent chapter building skill upon skill until you have a real, working model of your own. Financial tools, features, and functions are covered in detail from a practical perspective, and put in context with application to real-world examples. Each chapter focuses on a different aspect of Excel modelling, including step-by-step instructions that walk you through each feature, and the companion website provides live model worksheets that give you the real hands-on practice you need to start doing your job faster, more efficiently, and with fewer errors.

Financial modelling is an invaluable business tool, and Excel 2013 is capable of supporting the most common and useful models most businesses need. This book shows you how to dig deeper into Excel's functionality to craft effective financial models and provide important information that informs good decision-making.

  • Learn financial modelling techniques and best practice

  • Master the formulas and functions that bring your model to life

  • Apply stress testing and sensitivity analysis with advanced conditionals

  • Present your results effectively, whether graphically, orally, or written

  • A deceptively powerful application, Excel supports many hundreds of tools, features, and functions; Using Excel for Business Analysis eliminates the irrelevant to focus on those that are most useful to business finance users, with detailed guidance toward utilisation and best practice.

    Table of Contents

    1. Preface
      1. BOOK OVERVIEW
      2. ACKNOWLEDGMENTS
    2. 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
    3. 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 FLOW CHARTING
      7. STEPS TO BUILDING A MODEL
      8. INFORMATION REQUESTS
      9. VERSION-CONTROL DOCUMENTATION
      10. SUMMARY
    4. CHAPTER 3 Best Practice Principles of Modelling
      1. DOCUMENT YOUR ASSUMPTIONS
      2. LINKING, NOT HARD CODING
      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
    5. 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. SUMMARY
      7. NOTES
    6. CHAPTER 5 Using Excel in Financial Modelling
      1. FORMULAS AND FUNCTIONS IN EXCEL
      2. EXCEL VERSIONS
      3. HANDY EXCEL SHORTCUTS
      4. BASIC EXCEL FUNCTIONS
      5. LOGICAL FUNCTIONS
      6. NESTING: COMBINING SIMPLE FUNCTIONS TO CREATE COMPLEX FORMULAS
      7. CELL REFERENCING BEST PRACTICES
      8. NAMED RANGES
      9. SUMMARY
    7. CHAPTER 6 Functions for Financial Modelling
      1. AGGREGATION FUNCTIONS
      2. LOOKUP FORMULAS
      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
    8. 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
    9. 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
    10. CHAPTER 9 Common Uses of Tools in Financial Modelling
      1. ESCALATION METHODS FOR MODELLING
      2. UNDERSTANDING NOMINAL AND EFFECTIVE (REAL) RATES
      3. CALCULATING CUMULATIVE 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
    11. CHAPTER 10 Model Review
      1. REBUILDING AN INHERITED MODEL
      2. IMPROVING MODEL PERFORMANCE
      3. AUDITING A FINANCIAL MODEL
      4. SUMMARY
      5. APPENDIX 10.1: QA LOG
    12. 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. SUMMARY
    13. 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
    14. About the Author
    15. About the Website
    16. Index
    17. EULA