Part One Introduction to Modeling
Chapter 1 Modeling and Its Uses
1.1 What Is a Model?
1.2 What Are Models Used For?
Chapter 2 Principles of Model Design
2.1 Introduction
2.2 Decision Identification, Framing, and Structure
2.3 Decision Criteria and Information Needs
2.4 Sensitivity-Based Design
2.5 Data and Data Sources
2.6 Model Mapping and Approximations
2.7 Building and Testing
2.8 Results Presentation
2.9 Biases
Part Two Essentials of Excel
Chapter 3 Menus, Operations, Functions, and Features
3.1 Introduction
3.2 Structure and Menus
3.3 Calculations Using Arithmetic
3.4 Function Basics
3.5 A Core Function Set
3.6 Further Properties and Uses of Functions
3.7 Calculation Settings and Options
3.8 KeyTips and Shortcuts
3.9 Absolute and Relative Referencing
3.10 Auditing and Logic Tracing
3.11 Named Ranges
3.12 Best Practices: Overview
3.13 Best Practices: Flow
3.14 Best Practices: Time Axis
3.15 Best Practices: Multiple Worksheets
3.16 Best Practices: Formatting
3.17 Model Testing, Checking, and Error Management
3.18 Graphs and Charts
Chapter 4 Sensitivity and Scenario Analysis
4.1 Introduction
4.2 Basic or Manual Sensitivity Analysis
4.3 Automating Sensitivity Analysis: An Introduction
4.4 Using DataTables
4.5 Checking the Results, Limitations, and Tips
4.6 Creating Flexibility in the Outputs That Are Analyzed
4.7 Scenario Analysis
4.8 Variations Analysis
4.9 Using GoalSeek
4.10 Further Topics: Optimization, Risk, Uncertainty, and Simulation
Part Three General Calculations and Structures
Chapter 5 Growth Calculations for Forecasting
5.1 Introduction
5.2 Growth Measurement and Forecasting
5.3 Logic Reversals. 5.4 Forecasting Structures in Practice
5.5 Simplifying the Sensitivity Analysis and Reducing the Number of Parameters
5.6 Dealing with Inflation
5.7 Conversions for Model Periods
5.8 Further Topics: Logarithmic and Exponential Growth
Chapter 6 Modular Structures and Summary Reports
6.1 Introduction
6.2 Motivation for Summary Areas and Their Placement
6.3 Example I: Summaries and Conditional Summaries
6.4 Example II: Targets, Flags, and Matching
6.5 Sensitivity Analysis
6.6 Comments on Formatting
6.7 Initialization Areas
Chapter 7 Scaling and Ratio-driven Forecasts
7.1 Introduction
7.2 Basic Uses
7.3 Links to Length of Model Periods
7.4 Days' Equivalent Approaches
7.5 Example I: Forecasting from Revenues to EBITDA
7.6 Using Ratio-Based Forecasting Effectively
7.7 Example II: Ratio-Based Forecasting of Capital Items
7.8 Further Topics: Links to General Ratio Analysis
Chapter 8 Corkscrews and Reverse Corkscrews
8.1 Introduction
8.2 Classical Corkscrews
8.3 Benefits and Further Uses
8.4 Reverse Corkscrews
Chapter 9 Waterfall Allocations
9.1 Introduction
9.2 Example I: Cost Sharing
9.3 Example II: Tax Calculations
9.4 Options for Layout and Structure
9.5 Further Topics: Waterfalls for Sharing Capital Returns or Carried Interest
Chapter 10 Interpolations and Allocations
10.1 Introduction
10.2 Example I: Linear Smoothing
10.3 Example II: Proportional Smoothing
10.4 Uses of Tapering and Interpolation
10.5 Triangles
10.6 Further Topics: Triangles
Part Four Economic Foundations and Evaluation
Chapter 11 Breakeven and Payback Analysis
11.1 Introduction
11.2 Single-Period Breakeven Analysis: Prices and Volumes
11.3 Breakeven Time and Payback Periods
Chapter 12 Interest Rates and Compounding
12.1 Introduction. 12.2 Stated Rates and Calculations without Compounding
12.3 Compounding Types and Effective Rates
12.4 Conversion of Effective Rates for Periods of Different Lengths
12.5 Average Effective Rates
12.6 Implied Rates and Bootstrapping
Chapter 13 Loan Repayment Calculations
13.1 Introduction
13.2 Effective Rates for Interest-Only Repayments
13.3 Aligning Model Periods with Interest Repayments
13.4 Constant Repayment Loans Using the PMT Function
13.5 Constant Repayment Loans: Other Functions
13.6 Periods of Different Lengths
Chapter 14 Discounting, Present Values, and Annuities
14.1 Introduction
14.2 The Time Value of Money
14.3 Calculation Options for Present Values
14.4 Annuities and Perpetuities
14.5 Multi-Period Approaches and Terminal Values
14.6 Further Topics I: Mathematics of Annuities
14.7 Further Topics II: Cash Flow Timing
Chapter 15 Returns and Internal Rate of Return
15.1 Introduction
15.2 Single Investments and Paybacks
15.3 Multiple Paybacks: Average Returns and the Internal Rate of Return
15.4 Using Economic Metrics to Guide Investment Decisions
15.5 Properties and Comparison of NPV and IRR
Part Five Corporate Finance and Valuation
Chapter 16 The Cost of Capital
16.1 Introduction
16.2 Returns, Costs, and Opportunity Costs of Capital
16.3 The Role of Risk in Determining the Cost of Capital
16.4 The Properties and Benefits of Debt
16.5 The Financing Mix and The Weighted Average Cost of Capital
16.6 Modigliani-Miller and Leverage Adjustments
16.7 The Capital Asset Pricing Model
16.8 Further Topics: Derivation of Leveraging and Deleveraging Formulas
Chapter 17 Financial Statement Modeling
17.1 Introduction
17.2 Financial Statement Essentials
17.3 Key Challenges in Building Integrated Financial Statement Models. 17.4 Forecasting of the Integrated Statements: A Simple Example
17.5 The Dynamic Financing Adjustment Mechanism
17.6 Generalizing the Model Features and Capabilities
17.7 Steps and Principles in Building a Financial Statement Model
17.8 Further Topics: Avoiding Circularities
Chapter 18 Corporate Valuation Modeling
18.1 Introduction
18.2 Overview of Valuation Methods
18.3 Principles of Cash Flow Valuation
18.4 Free Cash Flow for Enterprise Valuation
18.5 The Role of the Explicit Forecast
18.6 Example: Explicit Forecast with Terminal Value Calculation
18.7 Further Topics I: Enterprise Value Based on Free Cash Flow and Equivalences
18.8 Further Topics II: Value-Driver Formulas
18.9 Further Topics III: Implied Cost of Equity
Chapter 19 Ratio Analysis
19.1 Introduction
19.2 Use and Principles
19.3 Ratios for Profitability and Valuation
19.4 Ratios Relating to Operations and Efficiency
19.5 Ratios for Liquidity and Leverage
19.6 DuPont Analysis
19.7 Variations Analysis within the DuPont Framework
19.8 Further Topics: Portfolios and The Piotroski F-Score
Part Six Data and Statistical Analysis
Chapter 20 Statistical Analysis and Measures
20.1 Introduction
20.2 Data Structures in Excel and The Impact on Functionality
20.3 Averages and Spread
20.4 The AGGREGATE Function
20.5 Conditional Aggregations
20.6 Database Functions
20.7 Correlations, Covariance, and Regression
20.8 Excel Tables
20.9 Pivot Tables
20.10 Further Topics: More on Averages, Correlations, and Confidence Intervals
Chapter 21 Data Preparation: Sourcing, Manipulation, and Integration
21.1 Introduction
21.2 Modeling Considerations
21.3 Overview of Data Manipulation Process
21.4 Cleaning Excel Data Sets
21.5 Integration of Excel Data Sets. 21.6 Further Topics I: Introduction to PowerQuery - Appending Tables
21.7 Further Topics II: Introduction to PowerQuery - Data Manipulation
21.8 Further Topics III: Introduction to PowerPivot and the Data Model