Using Excel for Business and Financial Modelling: A Practical Guide

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

What Skills Do You Need to Be a Good Financial Modeller?

The “Ideal” Financial Modeller

Summary

Chapter 2: Building a Model

Model Design

The Golden Rules for Product Design

Design Issues

The Workbook Anatomy of a Model

Project Planning Your Model

Model Layout Flowcharting

Steps to Building a Model

Information Requests

Version-Control Documentation

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

Linked Dynamic Text Assumptions Documentation

What Makes a Good Model?

Summary

Chapter 4: Financial Modelling Techniques

The Problem with Excel

Error Avoidance Strategies

How Long Should a Formula Be?

Linking to External Files

Building Error Checks

Circular References

Summary

Chapter 5: Using Excel in Financial Modelling

Formulas and Functions in Excel

Excel Versions

Handy Excel Shortcuts

Cell Referencing Best Practices

Named Ranges

Basic Excel Functions

Logical Functions

Nesting Logical Functions

Summary

Chapter 6: Functions for Financial Modelling

Aggregation Functions

LOOKUP Functions

Nesting Index and Matching

OFFSET Function

Regression Analysis

Choose Function

Working with Dates

Financial Project Evaluation Functions

Loan Calculations

Summary

Chapter 7: Tools for Model Display

Basic Formatting

Custom Formatting

Conditional Formatting

Sparklines

Bulletproofing Your Model

Customising the Display Settings

Form Controls

Summary

Chapter 8: Tools for Financial Modelling

Hiding Sections of a Model

Array Formulas

Goal Seeking

Structured Reference Tables

PivotTables

Macros

Summary

Chapter 9: Common Uses of Tools in Financial Modelling

Escalation Methods for Modelling

Understanding Nominal and Effective (Real) Rates

Calculating a Cumulative Sum (Running Totals)

How to Calculate a Payback Period

Weighted Average Cost of Capital (WACC)

Building a Tiering Table

Modelling Depreciation Methods

Break-Even Analysis

Summary

Chapter 10: Model Review

Rebuilding an Inherited Model

Improving Model Performance

Auditing a Financial Model

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?

Overview of Scenario Analysis Tools and Methods

Advanced Conditional Formatting

Comparing Scenario Methods

Adding Probability to a Data Table

Summary

Chapter 12: Presenting Model Output

Preparing an Oral Presentation for Model Results

Preparing a Graphic or Written Presentation for Model Results

Chart Types

Working with Charts

Handy Charting Hints

Dynamic Named Ranges

Charting with Two Different Axes and Chart Types

Bubble Charts

Creating a Dynamic Chart

Waterfall Charts

Summary

Index