The Essentials of Financial Modeling in Excel E-Book

Michael Rees

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 405

Veröffentlichungsjahr: 2023

Beschreibung

A concise and practical guide to financial modeling in Excel In The Essentials of Financial Modeling in Excel: A Concise Guide to Concepts and Methods, veteran quantitative modeling and business analysis expert Dr. Michael Rees delivers a practical and hands-on introduction to financial modeling in Excel. The author offers readers a well-structured and strategic toolkit to learn modeling from scratch, focusing on the core economic concepts and the structures commonly required within Excel models. Divided into six parts, the book discusses the use of models and the factors to consider when designing and building models so that they can be as powerful as possible, yet simple. . Readers will also find: * The foundational structures and calculations most frequently used in modeling, including growth- and ratio-based methods, corkscrews, and waterfall analysis * Walkthroughs of economic modeling, measurement, and evaluation, and the linking of these to the decision criteria. These include breakeven and payback analysis, compounding, discounting, calculation of returns, loan calculations, and others * Structured approaches for modeling in corporate finance, including financial statement modeling, cash flow valuation, cost of capital, and ratio analysis * Techniques to implement sensitivity and scenario analysis * Core aspects of statistical analysis, including data preparation, manipulation, and integration * The use of approximately 100 Excel functions within example modeling contexts * Further Topics Sections, which introduce advanced aspects of many areas, in order to provide further benefit to more advance readers, whilst presenting the truly essential topics separately. Examples of these include introductions to PowerQuery and PowerPivot, as well as advanced waterfall structures An invaluable, all-in-one blueprint for learning financial modeling in Excel, this book is ideal for beginning and intermediate financial professionals and students seeking to build and reinforce essential topics in financial modeling.

Leseprobe

Table of Contents

Cover

Title Page

Copyright

Dedication

About This Book

The Author

Part One: Introduction to Modeling

1 Modeling and Its Uses

1.1 WHAT IS A MODEL?

1.2 WHAT ARE MODELS USED FOR?

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

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

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

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

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

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

8 Corkscrews and Reverse Corkscrews

8.1 INTRODUCTION

8.2 CLASSICAL CORKSCREWS

8.3 BENEFITS AND FURTHER USES

8.4 REVERSE CORKSCREWS

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

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

11 Breakeven and Payback Analysis

11.1 INTRODUCTION

11.2 SINGLE‐PERIOD BREAKEVEN ANALYSIS: PRICES AND VOLUMES

11.3 BREAKEVEN TIME AND PAYBACK PERIODS

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

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

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

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 V: Corporate Finance and Valuation

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

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

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

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

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

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

Index

End User License Agreement

List of Illustrations

Chapter 1

Figure 1.1 Influence Diagram of a Simple Revenue Model

Figure 1.2 Excel Model That Contains Formulas but No Values

Figure 1.3 Excel Model with Input Cells Populated with Values

Figure 1.4 Input Cells with Color‐Coding

Figure 1.5 Using a Model to Compare Sales Revenues for Business Design Optio...

Chapter 2

Figure 2.1 Basic “Go/No Go” Decision with Sub‐Options

Figure 2.2 Using the Decision to Design the Model That Supports the Decision

Figure 2.3 Using a Sensitivity‐Based Thought Process to Define Model Variabl...

Chapter 3

Figure 3.1 Core Menu Tabs

Figure 3.2 The Home Tab (left‐hand‐side only)

Figure 3.3 The Formulas Tab (left‐hand‐side only)

Figure 3.4 Example of the SUM Function

Figure 3.5 The Insert Function Menu

Figure 3.6 The IF Function and Its Arguments

Figure 3.7 Entering the UNIQUE Function in a Single Cell

Figure 3.8 The Dynamic Output Range of the UNIQUE Function

Figure 3.9 Using # To Refer to a Dynamic Output Range

Figure 3.10 The Calculation Options on the Formulas Tab

Figure 3.11 Effect of Changes to Input Values in Manual Setting

Figure 3.12 Accessing the Menu Using KeyTips

Figure 3.13 Selecting a Range to be Copied

Figure 3.14 Results After Pasting

Figure 3.15 The Adjusted and Completed Model

Figure 3.16 The Paste Special Menu

Figure 3.17 Central Costs Allocated According to Trips

Figure 3.18 Formulas Used to Allocate Central Cost

Figure 3.19 The Formulas/Formula Auditing Menu

Figure 3.20 The Formula View

Figure 3.21 Using Trace Dependents and Trace Precedents

Figure 3.22 Inspecting a Formula Using the F2 Key

Figure 3.23 The Watch Window

Figure 3.24 Using the Name Manager

Figure 3.25 Simple Model with Named Inputs

Figure 3.26 The Name Box

Figure 3.27 Accessing the Go To (F5) Functionality

Figure 3.28 Diagonal Dependency Paths

Figure 3.29 Horizontal and Vertical Dependency Paths

Chapter 4

Figure 4.1 Accessing a DataTable Using Data/What‐If Analysis

Figure 4.2 Recap of Cab (Taxi) Business Profit Model

Figure 4.3 Three Raw DataTable Structures

Figure 4.4 Completing a Two‐Way DataTable

Figure 4.5 The Completed Two‐Way DataTable

Figure 4.6 The Raw DataTable Structures for DataTables with Multiple Outputs

Figure 4.7 Summary Area with Selection Menu

Figure 4.8 DataTable with Choice of Outputs to Analyze

Figure 4.9 Using Data Validation to Restrict a User's Choices to Valid Items...

Figure 4.10 Model Inputs Are Replaced by Cell References to the Scenario Cho...

Figure 4.11 Implementing the Scenario Results Using a DataTable

Figure 4.12 Simple Example of Variance Analysis

Figure 4.13 Example of Using GoalSeek

Chapter 5

Figure 5.1 Basic Growth Forecast

Figure 5.2 Historical Information and Growth Forecasting

Figure 5.3 Common Layout of Growth Forecasting

Figure 5.4 Multi‐period Forecast Using the Common Layout

Figure 5.5 Reducing the Number of Separate Input Assumptions

Figure 5.6 Full Separation of Inputs from Calculations

Figure 5.7 DataTable of Year 5 Revenues to Two Growth Assumptions

Figure 5.8 Using Inflation as a Separate Item

Figure 5.9 Comparison of Measurement and Forecasting Results

Figure 5.10 Raw Data on Growth Rates Measured by Each Method

Figure 5.11 Calculation of Total and Average Growth Using Each Method

Chapter 6

Figure 6.1 An Initial Five‐Year Model with Quarterly Periods

Figure 6.2 Summary of Five‐Year and Specified Year

Figure 6.3 Using Flag Fields to Find When a Target is Met

Figure 6.4 Using a DataTable for Items in the Summary Report

Figure 6.5 Setting a Conditional Format Rule

Figure 6.6 Dependencies without Initialization Area

Figure 6.7 Use of an Initialization Area to Be Able to Have Consistent Formu...

Chapter 7

Figure 7.1 Historical Calibration and Ratio‐Based Forecast for a Flow Item...

Figure 7.2 Historical Calibration and Ratio‐Based Forecast for a Stock Item...

Figure 7.3 Using the Days, Equivalent Method

Figure 7.4 Price Forecast for the Example Model

Figure 7.5 Sales Revenue Calculation

Figure 7.6 Calculation of Fixed and Variable Costs

Figure 7.7 Calculation of EBITDA in the Simple Model

Figure 7.8 Calculation of CapEx Using a Volume‐Based Ratio and Inflation...

Chapter 8

Figure 8.1 Framework for a Corkscrew Structure

Figure 8.2 Linking of CapEx into the Corkscrew Structure

Figure 8.3 Linking of CapEx into the Corkscrew Structure

Figure 8.4 Completion of Structure for the First Period

Figure 8.5 Completed Structure with Dependency Paths Shown

Figure 8.6 Basic Ratio Analysis of Assets to Sales

Figure 8.7 Calculation of Net Flow Items

Figure 8.8 Core Structure of a Reverse Corkscrew

Figure 8.9 Inclusion of One Flow Item

Figure 8.10 Completion of Both Flow Items

Chapter 9

Figure 9.1 General Split Using the MIN Function

Figure 9.2 Two Category Waterfall Split – Vertical Layout

Figure 9.3 Two Category Waterfall Split – Horizontal Layout

Figure 9.4 Capacities of the Multiple Layers

Figure 9.5 Completed Calculations of Multiple Layer Example

Figure 9.6 Waterfall Structure for Tax Calculation

Figure 9.7 Vertical Waterfall Structured by Item

Figure 9.8 Time Axis on a Vertical Waterfall Structured by Item

Figure 9.9 Vertical Waterfall Structured by Band

Figure 9.10 Capital Return Waterfall with Single Threshold

Figure 9.11 Capital Return Waterfall with Alternative Value

Figure 9.12 Capital Return Waterfall with Alternative Value

Chapter 10

Figure 10.1 Overview of Model with Interpolated Growth Rates

Figure 10.2 The Formula Used in Cell H11

Figure 10.3 Proportional Smoothing with Flexible Period Start

Figure 10.4 Logic Flow for Each Forecast Formula

Figure 10.5 Formula Used in Cell H8

Figure 10.6 Example of the Effect of a Combined Smoothing

Figure 10.7 Triangle Inputs: Time‐Specific Purchases and Generic Time Alloca...

Figure 10.8 Time‐Specific Allocations (Step 1)

Figure 10.9 Time‐Specific Allocations (Step 2)

Figure 10.10 Triangle Outputs Feeding a Corkscrew

Chapter 11

Figure 11.1 Model Used for Single‐Period Analysis

Figure 11.2 Cost Structure as Volume is Varied

Figure 11.3 Revenue, Cost, and Profit as Volume is Varied

Figure 11.4 Thresholds and Combinations to Achieve Breakeven

Figure 11.5 Time‐Based Forecast from Sales to EBITDA

Figure 11.6 Completed Model with Forecast to Cash Flows

Figure 11.7 Completed Set of Calculations

Figure 11.8 The Formula View of the Completed Calculations

Chapter 12

Figure 12.1 Example of Compounded Interest Calculations

Figure 12.2 Example of the EFFECT Function

Figure 12.3 Effective Periodic Rates for Different Compounding Frequencies

Figure 12.4 Use of FVSCHEDULE Function

Figure 12.5 Yield Curve Bootstrapping Assumptions and Context

Figure 12.6 Yield Curve Bootstrapping Results

Chapter 13

Figure 13.1 Use of the Derived Formula to Calculate an Effective Rate Given ...

Figure 13.2 Explicit Calculation of the Effective Rate Given Repayments

Figure 13.3 Example of the PMT Function

Figure 13.4 Function Arguments for the PMT Function

Figure 13.5 Explicit Calculation of Loan Repayment Using a Corkscrew Structu...

Figure 13.6 Payment Value with Start‐of‐Period Payments

Figure 13.7 Explicit Calculation When Payment Is at the Start of Each Period

Figure 13.8 Reversal of Natural Values when Using PMT

Figure 13.9 Verification of Calculations Using Sign Reversal

Figure 13.10 Examples of the RATE, NPER, FV, and PV Functions

Figure 13.11 Rates When the Loan Period Is a Multiple of the Compounding Per...

Chapter 14

Figure 14.1 The Assumed Cash Flow Profile for a Discounting Example

Figure 14.2 The Assumed One‐Year Discount Rates

Figure 14.3 Possibilities to Calculate the Discount Factors

Figure 14.4 The Discounted Cash Flows and the Total

Figure 14.5 Constant Discount Rate with Explicit Profile

Figure 14.6 Use of the NPV Function

Figure 14.7 Valuing an Annuity by Explicit Calculation of the Cash Flows

Figure 14.8 Application of the Annuity Formulas

Figure 14.9 Input Assumptions for Two‐Stage Terminal Value Calculation

Figure 14.10 Implementation of Two‐Stage Terminal Value Calculation

Chapter 15

Figure 15.1 Percentage Returns Calculated Explicitly in a Simple Case

Figure 15.2 Returns Expressed on a Per‐Period Basis

Figure 15.3 Example with Payback Occurring in Two Periods

Figure 15.4 Inflating or Discounting Cash Flows to Achieve a Total Value of ...

Figure 15.5 Using the IRR Function

Figure 15.6 IRR with Several Periods of Investment and Payback

Chapter 16

Figure 16.1 Threshold Level for Debt‐Equity Substitution and without Taxes...

Figure 16.2 Threshold Level for Debt‐Equity Substitution and without Taxes...

Figure 16.3 The Leverage Effect of Debt on Returns to Equity (at Book Value)

Figure 16.4 Effect of Debt with Taxes

Figure 16.5 Effect of Debt If Charges Were Not Offset Against Taxes

Figure 16.6 Generic Effect of Leverage on Cost of Capital: Equity, Debt, and...

Figure 16.7 A Simple Example of the Calculation of the Expected Return

Chapter 17

Figure 17.1 Income Statement for Simple Model

Figure 17.2 Cash and Equity Corkscrews

Figure 17.3 The Balance Sheet for the Base Case

Figure 17.4 The Balance Sheet with a Lower Initial Capital Injection

Figure 17.5 Implementation of the Adjustment Mechanism

Figure 17.6 Completion of Statements to Reflect the Equity Injection

Figure 17.7 Example of Adding an Accounts Receivable Functionality

Chapter 18

Figure 18.1 Forecast to the NOPAT line

Figure 18.2 Calculation of the Value in the Explicit Forecast Period

Figure 18.3 Terminal Value Calculation

Figure 18.4 Total Enterprise and Equity Value

Chapter 19

Figure 19.1 Generic Example of DuPont Analysis Using Linear Scales

Figure 19.2 Variations Analysis Using Component Parts

Chapter 20

Figure 20.1 Raw Data for Input to the Statistical Functions

Figure 20.2 Examples of the Use of AGGREGATE

Figure 20.3 Using AGGREGATE with its Fourth Argument

Figure 20.4 Augmented Data Set with Month and Year Information

Figure 20.5 Use of the AVERAGEIFS Function

Figure 20.6 Data Set with Field Headers

Figure 20.7 Example of a Criteria Range for a Database Function

Figure 20.8 Function Arguments for the Database Functions

Figure 20.9 Results of Applying the Database Functions

Figure 20.10 Data Set for Correlation and Regression Analysis

Figure 20.11 X‐Y Scatter Plot with Trendline Displayed

Figure 20.12 Calculation of Slope, Correlations, and Standard Deviations

Figure 20.13 Creating a Table

Figure 20.14 The Table Design Tab

Figure 20.15 Entering a Formula That Will Refer to a Table

Figure 20.16 Completed PivotTable with a Row Structure

Figure 20.17 First Step to Insert a PivotTable

Figure 20.18 Completion of Step‐by‐Step Creation of a PivotTable

Figure 20.19 Population of the PivotTable Structure

Figure 20.20 Results of the LINEST Function

Chapter 21

Figure 21.1 Raw Data and Desired Transformation

Figure 21.2 Calculation Steps for One Item, Shown in a Column

Figure 21.3 Row Form of the Calculations and Results

Figure 21.4 Data Including Transaction Values in Local Currency

Figure 21.5 Tables with Additional Information That Need to Be Referenced

Figure 21.6 Augmented Main Table Showing Country Names

Figure 21.7 Main Table with Further Augmentation

Figure 21.8 The Completed Flat Table

Figure 21.9 Results of Appending Two Tables to Create a Third

Figure 21.10 The PowerQuery Editor

Figure 21.11 Selecting to Create a Connection Only

Figure 21.12 Queries & Connections Before Appending

Figure 21.13 Using the Table.Combine Operation

Figure 21.14 Using PowerQuery for the Full Process

Figure 21.15 Tables and Their Relationships within the Data Model

Figure 21.16 Creation of a Measure

Figure 21.17 PivotTable that Displays the Value of a PowerPivot Measure

Guide

Cover Page

Title Page

Copyright

Dedication

About This Book

The Author

Table of Contents

Begin Reading

Index

Wiley End User License Agreement

Pages

iii

iv

v

ix

x

xi

1

3

4

5

6

7

9

10

11

12

13

14

15

16

17

18

19

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

73

75

76

77

78

79

80

81

82

83

84

85

86

87

89

90

91

92

93

94

95

97

98

99

100

101

102

103

104

105

107

108

109

110

111

112

113

115

116

117

118

119

120

121

122

123

124

125

127

128

129

130

131

132

133

134

135

137

139

140

141

142

143

144

145

147

148

149

150

151

152

153

154

155

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

183

184

185

186

187

188

189

190

191

192

193

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

257

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

THE ESSENTIALS OF FINANCIAL MODELING IN EXCEL

A CONCISE GUIDE TO CONCEPTS AND METHODS

Michael Rees

This edition first published 2023

© 2023 Michael Rees

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by law. Advice on how to obtain permission to reuse material from this title is available at http://www.wiley.com/go/permissions.

The right of Michael Rees to be identified as the author of this work has been asserted in accordance with law.

Registered Offices

John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, USA

John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, UK

Editorial Office

The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, UK

For details of our global editorial offices, customer services, and more information about Wiley products visit us at www.wiley.com.

Wiley also publishes its books in a variety of electronic formats and by print‐on‐demand. Some content that appears in standard print versions of this book may not be available in other formats.

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty

While the publisher and authors have used their best efforts in preparing this work, they make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives, written sales materials or promotional statements for this work. The fact that an organization, website, or product is referred to in this work as a citation and/or potential source of further information does not mean that the publisher and authors endorse the information or services the organization, website, or product may provide or recommendations it may make. This work is sold with the understanding that the publisher is not engaged in rendering professional services. The advice and strategies contained herein may not be suitable for your situation. You should consult with a specialist where appropriate. Further, readers should be aware that websites listed in this work may have changed or disappeared between when this work was written and when it is read. Neither the publisher nor authors shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

Library of Congress Cataloging‐in‐Publication Data

Names: Rees, Michael, 1964‐ author.

Title: The essentials of financial modeling in Excel: a concise guide to concepts and methods / Michael Rees.

Description: Hoboken, NJ: John Wiley & Sons, Inc., 2023. | Includes index.

Identifiers: LCCN 2022043302 (print) | LCCN 2022043303 (ebook) | ISBN 9781394157785 (paperback) | ISBN 9781394157792 (adobe pdf) | ISBN 9781394157808 (epub)

Subjects: LCSH: Finance—Mathematical models. | Corporations—Finance—Mathematical models. | Microsoft Excel (Computer file)

Classification: LCC HG106. R439 2023 (print) | LCC HG106 (ebook) | DDC 332.0285/554—dc23/eng/20220908

LC record available at https://lccn.loc.gov/2022043302

LC ebook record available at https://lccn.loc.gov/2022043303

Cover Design: Wiley

Cover Image: © Skylines/Shutterstock

This book is dedicated to Elsa and Raphael.

About This Book

This book provides a concise introduction to financial modeling in Excel. It aims to provide readers with a well‐structured and practical tool kit to learn modeling “from the ground up.” It is unique in that it focuses on the concepts and structures that are commonly required within Excel models, rather than on Excel per se.

The book is structured into six parts (containing twenty‐one chapters in total):

Part I

introduces financial modeling and the general factors to consider when designing, building, and using models.

Part II

discusses the core features of Excel that are needed to build and use models. It covers operations and functionality, calculations and functions, and sensitivity and scenario analysis.

Part III

covers the fundamental structures and calculations that are very frequently used in modeling. This includes growth‐based forecasting, ratio‐driven calculations, corkscrew structures, waterfalls, allocations, triangles, and variations of these.

Part IV

discusses economic modeling, measurement, and evaluation. It covers the analysis of investments, interest calculations and compounding, loan calculations, returns analysis, discounting, and present values.

Part V

treats the core applications of modeling within corporate finance. It covers the cost of capital, the modeling of financial statements, cash flow valuation, and ratio analysis.

Part VI

covers statistical analysis, as well as data preparation, manipulation, and integration.

Readers will generally obtain the maximum benefit by studying the text from the beginning and working through it in order. It is intended that the reader builds from scratch the models that are shown, to reinforce the learning experience and to enhance practical skills. Of course, there may be areas which are already familiar to some readers, and which can be skim‐read. Nevertheless, the text is intended to be concise and practical, and to contain information that is potentially useful even to readers who may have some familiarity with the subject.

Although the text is focused on the essentials, at various places it briefly highlights some aspects of more advanced topics. These are described in Further Topics sections, which are situated at the end of some chapters. These sections can be skipped at the reader's discretion without affecting the comprehension of the subsequent text. Note that another of the author's works (Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA, John Wiley & Sons, 2018) discusses in detail some topics that are only briefly (or not) covered in this text (notably VBA macros, optimization, circularities, named ranges, and others). For convenience, in the current text this other text is occasionally mentioned at specific places where it contains significant additional materials related to the discussion, and is subsequently referred to as PFM.

The Author

Dr. Michael Rees is a leading expert in quantitative modeling and analysis for applications in business economics, finance, valuation, and risk assessment. He is Professor of Finance at Audencia Business School in Nantes (France), where he teaches subjects related to valuation, financial engineering, optimization, risk assessment, modeling, and business strategy. His earlier academic credentials include a Doctorate in Mathematical Modelling and Numerical Algorithms, and a BA with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with Distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark. Prior to his academic career, he gained over 30 years' practical experience, including in senior roles at leading firms in finance and strategy consulting (JP Morgan, Mercer Management Consulting, and Braxton Associates), as well as working as an independent consultant and trainer. His clients included companies and entrepreneurs in private equity; auditing and consulting; finance; banking and insurance; pharmaceuticals and biotechnology; oil, gas, and resources; construction; chemicals; engineering; telecommunications; transportation; the public sector; software; and training providers. In addition to this text, he is the author of Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA (2018); Business Risk and Simulation Modelling in Practice: Using Excel, VBA and @RISK (2015); and Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level (2008).

Part OneIntroduction to Modeling

2Principles of Model Design

2.1 INTRODUCTION

Modeling activity takes place within an overall context and a wider set of business processes. At a high level, the main steps to consider when planning and building a financial model for decision support are:

Identifying the decision and its structure, options, and criteria.

Mapping the elements of real‐life that should be captured, including the variables and logic flow.

Building and testing the model.

Using relevant external data.

Using the results, including presentation, graphics, sensitivity analysis, reports, and documentation.

This chapter explores these topics, discussing the core principles of each point and the main practical issues. Note that in this chapter, the discussion is still quite generic; in fact, most of the principles apply whether a model is to be built in Excel or in some other platform. However, the rest of the book (from Chapter 3 onwards) is devoted to implementing these within the Excel environment.

2.2 DECISION IDENTIFICATION, FRAMING, AND STRUCTURE

A model is generally used to support a decision process in some way. Therefore, it is important to establish what decision is being addressed, what are the objectives, and what are the constraints or limitations that must be respected.

A common failing of decision processes is known as the “fallacy of choice”: This is where what would have been the best decision option is not considered at all. Clearly, for a model to be most useful, it must also reflect the relevant decision and the most appropriate or best option(s).

Generically, one may think of a decision as having a binary structure (“go or no go?”). Most commonly, Excel models reflect this: The model represents the “go” option, whereas the “no go” option is not modeled explicitly (i.e. it is implicitly considered as being neutral or evaluating to zero).

It is also frequently the case that (within the “go” option) there are set of sub‐options which each have the same structure. That is, there is only one model, and the sub‐options are captured as scenarios (each simply using different input values). If there were major structural differences between the sub‐options then a different model would be required for each (and, in that case, they are strictly speaking not sub‐options at all). Figure 2.1 illustrates this for the situation discussed in Chapter 1 (see Figure 1.5 and the associated discussion).

Other types of decision structures include allocations or optimizations (e.g. how much capital shall we allocate to project A, and how much to project B?), multiple structurally different options (such as whether to renovate one's house, buy a new car, or go on vacation), and decision sequences (e.g. using a phased approach rather than making a single up‐front decision). These may require more advanced models and tools to properly address them. However, the core points are that the appropriate decision needs to be identified and that the model should reflect the structure of the decision situation.

Figure 2.1Basic “Go/No Go” Decision with Sub‐Options

2.3 DECISION CRITERIA AND INFORMATION NEEDS

There are many ways that a decision could be made, or a decision option selected. The least structured is using “gut feel,” which is essentially a subjective method. A more robust process is to make the criteria explicit and to evaluate these as objectively as possible (often quantitatively).

In principle it should be self‐evident that a model should be designed so that it calculates (or contains) the values of the decision criteria (or metrics) that are to be used by the decision‐maker. Figure 2.2 depicts the idealized modeling process. It starts with identifying the decision, with the nature of the decision then determining the decision criteria (metrics). These are used to determine the design requirements, allowing the model to be built so that it evaluates the criteria, with the results used to support the decision.

It is also worth noting that a “gut feel” decision process is often one where the process of decision identification is incomplete and potentially subject to the fallacy of choice. In addition, it may be considered as one in which there is a direct route from decision identification to decision‐making (i.e. a route directly downwards from the top‐left box to the bottom‐left one in Figure 2.2).

Common decision criteria used in economic analysis include measures relating to:

Breakeven analysis (such as time‐to‐breakeven and payback periods).

Figure 2.2Using the Decision to Design the Model That Supports the Decision

Returns (such as the internal rate‐of‐return, the return‐on‐capital) and net present values).

Ratios (such as profit/sales, or sales/assets, and so on).

In some cases, one may wish to focus on a specific item only and maximize or minimize this. For example, one may wish to choose the option which has the maximum revenues, that which has the minimum cost, or that with the minimum risk, and so on. Clearly, these criteria could lead to different decision choices. For example, in day‐to‐day life, the choice to go on the cheapest vacation possible would likely lead to a different selected vacation than if one sought to choose the vacation option by considering both the costs and benefits (such as the quality of the hotel one is staying in). Similarly, in a business context, the option that maximizes revenues may require making significant up‐front investments that would not be acceptable if criteria such as profitability or financing constraints were considered.

Note that while one may initially interpret “decision criteria” in a pure economic sense, the term should be thought of in a wider context (i.e. the full information needs of decision‐makers). These would typically also include that a sensitivity or scenario analysis (or a full risk assessment) be conducted. That is, one would aim to establish the likely ranges for the decision criteria (such as the range of value for the time‐to‐breakeven, or for the return‐on‐capital, and so on). This is discussed further in the next section.

Similarly, in practice, some decision criteria may initially be overlooked when a model is first built: It is possible that the criteria are not understood initially, or that the information needs of decision‐makers change over time after some initial results have been reviewed, or that further information about the market or competition has become available, and so on.

Finally, some decision elements (e.g. relating to ethical or moral issues) may not be able to be evaluated by quantitative analysis (i.e. cannot be included in a model). In these cases, some judgment by the decision‐maker is likely to be required. However, the core point is that when planning a model, one should take some time to reflect on a wide set of likely decision criteria that may ultimately be needed, and to build the model so that these are evaluated, at least as far as possible.

2.4 SENSITIVITY‐BASED DESIGN

Sensitivity analysis is the exploration of the changes that occur to the value of a calculated item when one or more of the input value(s) is changed. It is a key part of decision support, as it can:

Help to understand the conditions under which a decision makes sense (or not). For example, while a base case may indicate that a “go” decision is preferable (to “no go”), a sensitivity analysis could identify that this is true only if costs do not rise by more than 10%.

Establish the range of likely outcomes and generally to assess the potential upsides and downsides.

Identify the relative importance of the key input variables, and hence the effectiveness of potential management actions that could be used to maximize (or optimize) the overall result while mitigating or reducing risk.

A seemingly obvious – but often overlooked – point is that sensitivity analysis should be considered before the model is built (i.e. as a planning and design tool): If it is considered only afterwards, the model may have been built in a way which does not allow the necessary sensitivities to be run! The approach to implementing sensitivity techniques varies according to the stage within the modeling process:

At the design and planning stage, it revolves around identifying as precisely as possible the sensitivities that will need to be run later. This can help to define the variables that should be included in the model, their roles as inputs or outputs (i.e. the logic flow), as well as the level of detail or granularity that is needed.

When a model is being built, it can be used to verify and test its general behavior, notably by checking that the relationships that are present in the real‐life situation are reflected properly. It can also be used to develop and check complex calculations, by testing their results at various values (ideally a combination of simple values, extreme values, and values which are critical in how the formulas would evaluate).

Figure 2.3Using a Sensitivity‐Based Thought Process to Define Model Variables

Figure 2.3 shows a simple illustration of the use of a sensitivity thought process in model design and planning. When creating a model forecast of Sales Revenue, there could be several fundamentally different approaches to the choice of variables and the logic used. These include:

Volume multiplied by Price.

Market Size multiplied by Market Share.

Sum of the Sales Revenue per Customer (or per product, geographic region, etc.).

By considering the nature of the sensitivity analysis that will be needed when the model is complete, one should be able to see which of these is most appropriate or what variations of one of them may be required. (The process could also highlight that none of these options are suitable and that other modeling approaches should be considered.)