Using Excel for Business and Financial Modelling E-Book

Danielle Stein Fairhurst

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 570

Veröffentlichungsjahr: 2019

Beschreibung

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.

Leseprobe

Table of Contents

Cover

Preface

BOOK OVERVIEW

ACKNOWLEDGEMENTS

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

NOTES

CHAPTER 2: Building a Model

MODEL DESIGN

THE GOLDEN RULES FOR MODEL 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

NOTE

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

NOTES

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 MATCH

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

GROUPING

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

About the Author

About the Website

DOWNLOADABLE RESOURCES

Index

End User License Agreement

List of Tables

Chapter 3

TABLE 3.1 Comparison of In-Cell Comment Methods

Chapter 4

TABLE 4.1 Why External Links are Not Used

Chapter 5

TABLE 5.1 Useful Windows Keyboard Shortcuts for Financial Modellers

Chapter 6

TABLE 6.1 Advantages and Disadvantages of LOOKUP Functions

Chapter 9

TABLE 9.1 Depreciation for a $200,000 Piece of Machinery

Chapter 12

TABLE 12.1 What Type of Chart Best Fits Your Data?

List of Illustrations

Chapter 2

FIGURE 2.1 Model Layout Option 1

FIGURE 2.2 Model Layout Option 2

FIGURE 2.3 Model Categorisation Option 1

FIGURE 2.4 Model Categorisation Option 2

FIGURE 2.5 Model Structure

FIGURE 2.6 Using the Styles Menu to Format Input Cells

FIGURE 2.7 Commonly Used Formatting for Inconsistent Formulas

FIGURE 2.8 Completed Customer Support Pricing Model

FIGURE 2.9 Sample Flowchart of Model Calculations

FIGURE 2.10 Sample File Structure

Chapter 3

FIGURE 3.1 Assumptions Documentation Using In-Cell Comment

FIGURE 3.2 Assumptions Documentation Using Data Validation Input Messages

FIGURE 3.3 Data Validation Input Message Dialog Box

FIGURE 3.4 Example of Manual Footnoting in Excel

FIGURE 3.5 Hardcoded Assumptions Documentation

FIGURE 3.6 Linking Text Using Ampersand

FIGURE 3.7 Linked Dynamic Assumptions Documentation

FIGURE 3.8 Format Cells Dialog Box

FIGURE 3.9 Practical Commentary Exercise

FIGURE 3.10 Practical Commentary Exercise (Completed)

Chapter 4

FIGURE 4.1 Common Excel Error in Modelling

FIGURE 4.2 Clicking Elsewhere Instead of Using the Enter Key Can Cause Errors

FIGURE 4.3 Showing Formula Source Cells

FIGURE 4.4 Methodology Documentation

FIGURE 4.5 Sense-Checking Methodology Using the Sum Total

FIGURE 4.6 Very Long Formula Broken into Several Lines

FIGURE 4.7 Formula Linking to a Closed External File

FIGURE 4.8 Revenue Calculated Two Different Ways

FIGURE 4.9 Breaking Links on Edit Links Dialog Box

FIGURE 4.10 Summary Report

FIGURE 4.11 Error-Check Example

FIGURE 4.12 Error-Check Alert Formula

FIGURE 4.13 Formula Creating a Circular Reference

FIGURE 4.14 Circular Reference Notification in the Status Bar

FIGURE 4.15 Finding the Circular Reference Auditing Tool in the Ribbon

FIGURE 4.16 Finding the Circular Reference Auditing Tool in the Ribbon in Excel...

FIGURE 4.17 Circular Reference in Interest Calculations

FIGURE 4.18 Enabling Iterative Calculations

FIGURE 4.19 Enabling Iterative Calculations in Excel for Mac

Chapter 5

FIGURE 5.1 Insert Function Dialog Box

FIGURE 5.2 Formula Builder in Excel for Mac

FIGURE 5.3 Compounding Growth Rate Calculation Using a Helper Row

FIGURE 5.4 Compounding Growth Rate Calculation Without a Helper Row

FIGURE 5.5 Shortcut Keys Shown After Pressing the ALT Key

FIGURE 5.6 Relative Cell Referencing

FIGURE 5.7 Absolute Cell Referencing

FIGURE 5.8 Copied Absolute Cell Referencing

FIGURE 5.9 Mixed Referencing Exercise

FIGURE 5.10 Answer to Mixed Referencing Exercise

FIGURE 5.11 The Name Box

FIGURE 5.12 Finding a Named Range Using the Name Box

FIGURE 5.13 Using the SUM Function

FIGURE 5.14 Using the MAX Function

FIGURE 5.15 Using the MIN Function

FIGURE 5.16 Using the AVERAGE Function

FIGURE 5.17 Combining Functions to Calculate the Deviation

FIGURE 5.18 Using an IF Statement for Decision Analysis

FIGURE 5.19 Using an IF Statement to Create a Spend Schedule

FIGURE 5.20 Spot Checking the Block of Data Using the F2 Shortcut Key

FIGURE 5.21 Completed Nested IF and AND Formula

FIGURE 5.22 Showing the IF Insert Function Dialog Box on a Nested Formula

FIGURE 5.23 Showing the AND Insert Function Dialog Box on a Nested Formula

FIGURE 5.24 Volume Pricing Table

FIGURE 5.25 Highlight and Copy IF Statement

FIGURE 5.26 Completed Nested IF Function

FIGURE 5.27 IFS Insert Function Dialog Box

Chapter 6

FIGURE 6.1 Sales List

FIGURE 6.2 COUNTIF Insert Function Dialog Box

FIGURE 6.3 Completed COUNTIF Function

FIGURE 6.4 SUMIF Insert Function Dialog Box

FIGURE 6.5 Completed SUMIF Function

FIGURE 6.6 Incorrect SUMIF Calculation

FIGURE 6.7 COUNTIFS Dialog Box

FIGURE 6.8 Completed COUNTIFS Function

FIGURE 6.9 Completed SUMIFS Function

FIGURE 6.10 AVERAGEIFS Insert Function Dialog Box

FIGURE 6.11 Sense Checking the AVERAGEIFS Function

FIGURE 6.12 Completed Filtered Sales Report

FIGURE 6.13 SUMIFS Function with a Minimum Filter

FIGURE 6.14 AVERAGEIFS Function with a Minimum Filter

FIGURE 6.15 VLOOKUP Insert Function Dialog Box

FIGURE 6.16 Creating a Named Range

FIGURE 6.17 Using the F3 Shortcut to Paste Name into a Formula

FIGURE 6.18 Completed VLOOKUP Function

FIGURE 6.19 MATCH Function Dialog Box

FIGURE 6.20 Use of the HLOOKUP Function

FIGURE 6.21 HLOOKUP Insert Function Dialog Box

FIGURE 6.22 LOOKUP Function Option

FIGURE 6.23 LOOKUP Insert Function Dialog Box

FIGURE 6.24 Completed Pricing Calculation

FIGURE 6.25 Sample Data

FIGURE 6.26 INDEX Function Options

FIGURE 6.27 INDEX Insert Function Dialog Box

FIGURE 6.28 Completed INDEX Function Using Named Ranges in Separate Workbooks

FIGURE 6.29 Example Data

FIGURE 6.30 OFFSET Insert Function Dialog Box

FIGURE 6.31 Calculating a Dynamic Cash Flow Using the OFFSET Function

FIGURE 6.32 OFFSET Function with Error and Text Values

FIGURE 6.33 Completed Dynamic Cash Flow Using a Nested OFFSET Formula

FIGURE 6.34 Inserting a Linear Trend Line

FIGURE 6.35 Linear Trend Line

FIGURE 6.36 FORECAST Insert Function Dialog Box

FIGURE 6.37 Completed Line Chart Showing Results of FORECAST

FIGURE 6.38 Select the Forecast Sheet Button to Bring Up the Create Forecast Wo...

FIGURE 6.39 Completed Forecast Sheet with FORECAST.ETS Function

FIGURE 6.40 CHOOSE Insert Function Dialog Box

FIGURE 6.41 Using a Formula to Calculate Dates

FIGURE 6.42 Using the EOMONTH Function

FIGURE 6.43 The EOMONTH Function Calculates a Leap Year Correctly

FIGURE 6.44 The WEEKDAY Function Returns the Day of the Week

FIGURE 6.45 The MONTH Function Used to Aggregate Data

FIGURE 6.46 Using a DAY Function to Return the Calendar Day of the Month

FIGURE 6.47 Shortcut Date Formatting Drop-Down

FIGURE 6.48 Using the NPV Function

FIGURE 6.49 Using the IRR Function

FIGURE 6.50 IRR Calculation with Multiple Results

FIGURE 6.51 XNPV Function

FIGURE 6.52 Loan Template

FIGURE 6.53 PMT Insert Function Dialog Box

FIGURE 6.54 IPMT Function

FIGURE 6.55 Loan with Completed PMT, IPMT, and PPMT Functions

FIGURE 6.56 Principal (PPMT) and Interest (IPMT) Components of Loan Repayments ...

Chapter 7

FIGURE 7.1 Formatting in the Ribbon

FIGURE 7.2 Showing the Current Date and Time Using the =NOW() Function

FIGURE 7.3 Format Cells Dialog Box

FIGURE 7.4 Changing the Date Format

FIGURE 7.5 Sample Report

FIGURE 7.6 Custom Formatting Using the Format Cells Dialog Box

FIGURE 7.7 Using the ROUND Function to Truncate Values

FIGURE 7.8 Applying Conditional Formatting

FIGURE 7.9 Applying Data Bars

FIGURE 7.10 Accessing Icon Sets

FIGURE 7.11 Sample Report without Formatting

FIGURE 7.12 Sample Report Using Colour Scales

FIGURE 7.13 Edit Formatting Rule to Hide Icons

FIGURE 7.14 Applying Multiple Types of Conditional Formatting to the Same Range

FIGURE 7.15 Choosing Sparklines from the Ribbon

FIGURE 7.16 Sparklines Dialog Box

FIGURE 7.17 Completed Report with Sparklines

FIGURE 7.18 Edit Sparklines Dialog Box

FIGURE 7.19 Hidden and Empty Cell Settings Dialog Box

FIGURE 7.20 Model with Customised Display Settings

FIGURE 7.21 Minimising the Ribbon

FIGURE 7.22 A Worksheet with Restricted Work Area

FIGURE 7.23 Data Validation Comment

FIGURE 7.24 Creating a Customised Error Message

FIGURE 7.25 Customised Popup Error Message

FIGURE 7.26 Creating a Drop-Down List

FIGURE 7.27 Enter the Source Data Range

FIGURE 7.28 Completed Drop-Down List

FIGURE 7.29 Creating a Drop-Down List Using a Named Range

FIGURE 7.30 The Developer Tab

FIGURE 7.31 Insert Controls Icon

FIGURE 7.32 Form Controls in Excel for Mac

FIGURE 7.33 Inserting the Checkbox

FIGURE 7.34 Selecting Format Control to Assign Checkbox Options

FIGURE 7.35 Format Control Dialog Box

FIGURE 7.36 Checkboxes in a Financial Model

FIGURE 7.37 Randomly Arranged Checkboxes

FIGURE 7.38 Inserting the Option Button

FIGURE 7.39 Option Button in Excel for Mac

FIGURE 7.40 Worksheet with Option Button

FIGURE 7.41 Inserting the Spinner

FIGURE 7.42 Completed Spin Button in a Financial Model

FIGURE 7.43 Format Control Dialog Box

FIGURE 7.44 Completed Combo Box

FIGURE 7.45 Inserting the Combo Box

FIGURE 7.46 Drawing the Combo Box

FIGURE 7.47 Scenario Source Data

FIGURE 7.48 Using the Combo Box Output in a Formula

FIGURE 7.49 Checked Checkboxes Drive Calculation

FIGURE 7.50 Unselected Checkboxes Drive Calculation

Chapter 8

FIGURE 8.1 Unhiding Rows 1 and 2

FIGURE 8.2 Unhiding Rows

FIGURE 8.3 Viewing the Source Code

FIGURE 8.4 Changing the Visibility Options in the Visual Basic Editor

FIGURE 8.5 Collapsed Grouping

FIGURE 8.6 Expanded Grouping

FIGURE 8.7 Entering an Array Formula

FIGURE 8.8 Completed Array Formula

FIGURE 8.9 Temperature Data

FIGURE 8.10 Transposed Temperature Data

FIGURE 8.11 Paste Special Dialog Box

FIGURE 8.12 Creating a TRANSPOSE Array Formula

FIGURE 8.13 Loan Repayment Calculation Using PMT Function

FIGURE 8.14 Goal Seek Dialog Box

FIGURE 8.15 Creating a Structured Reference Table

FIGURE 8.16 Source Data and PivotTable

FIGURE 8.17 PivotTable Options Dialog Box

FIGURE 8.18 Recommended PivotTables

FIGURE 8.19 Creating a PivotTable

FIGURE 8.20 Completed PivotTable with Field List

FIGURE 8.21 Grouping the Dates in a PivotTable

FIGURE 8.22 Grouping by Month

FIGURE 8.23 Filter in a PivotTable

FIGURE 8.24 Selecting Multiple Items in a Filter

FIGURE 8.25 Slicer in a PivotTable

FIGURE 8.26 Increasing the Number of Columns in a Slicer

FIGURE 8.27 Macro Launch Button

FIGURE 8.28 Accessing Record Macro from the Ribbon

FIGURE 8.29 Naming the Recorded Macro

FIGURE 8.30 Viewing the Recorded Macro in the VBA Editor

FIGURE 8.31 Macro-Free Workbook Warning

Chapter 9

FIGURE 9.1 Fixed, Compounding Exercise

FIGURE 9.2 Completed Fixed, Compounding Exercise

FIGURE 9.3 Fixed, Non-compounding Exercise

FIGURE 9.4 Relative, Compounding Exercise and FVSCHEDULE Function

FIGURE 9.5 Relative, Non-compounding Exercise

FIGURE 9.6 Complex Escalation

FIGURE 9.7 Completed Staff Calculation Using Exponential Growth Rates

FIGURE 9.8 Assumption Documentation in Growth Calculations

FIGURE 9.9 Comparison of Interest Rates

FIGURE 9.10 Comparison of Rates with Changed Compounding Periods

FIGURE 9.11 Method 1: Cumulative Total Using Inconsistent Formulas

FIGURE 9.12 Method 2: Cumulative Totals Using a Consistent Formula

FIGURE 9.13 Manually Calculating the Payback Year

FIGURE 9.14 Completed Simple Payback Calculation

FIGURE 9.15 Payback Period Workings

FIGURE 9.16 Completed Payback Calculation

FIGURE 9.17 WACC Calculation Layout

FIGURE 9.18 WACC Completed Calculation

FIGURE 9.19 Completed Flat-Tiered Pricing Calculation

FIGURE 9.20 Tiered Personal Australian Tax Calculation Example

FIGURE 9.21 Completed Progressive Tiered Calculation

FIGURE 9.22 Comparison of Different Depreciation Methods

FIGURE 9.23 Asset Depreciation Example

FIGURE 9.24 Calculating Fixed Declining Depreciation

FIGURE 9.25 Calculating Double Declining Depreciation

FIGURE 9.26 Calculating Sum of the Years' Digits Depreciation

FIGURE 9.27 Completed Depreciation Calculation on Fixed Assets

FIGURE 9.28 Break-Even Calculations

FIGURE 9.29 Chart Showing Break-Even Point

FIGURE 9.30 Break-Even Number of Units Using a Formula

FIGURE 9.31 Using Goal Seek to Calculate Break-Even Point

FIGURE 9.32 Completed Break-Even Goal Seek

FIGURE 9.33 Break-Even Goal Seek with Changed Inputs

Chapter 10

FIGURE 10.1 Trace Precedents in Formula Auditing Icons in the Ribbon

FIGURE 10.2 Using Trace Precedents on a Formula

FIGURE 10.3 Error-Checking Tools Showing Error in Sum Formula

FIGURE 10.4 Editing Error-Checking Options

FIGURE 10.5 Error-Checking Feature

FIGURE 10.6 Tracing the Error Source

FIGURE 10.7 Evaluate Formula Dialog Box

FIGURE 10.8 Viewing the Value of Part of the Formula

FIGURE 10.9 Show Formulas Option in the Ribbon

FIGURE 10.10 Disabling Direct Editing in Cells

FIGURE 10.11 Double-Clicking with Direct Editing Disabled

FIGURE 10.12 Accessing Inspect Workbook

FIGURE 10.13 Save File in .xlsx Excel Workbook Format

Chapter 11

FIGURE 11.1 Data Validation Drop-Down Box

FIGURE 11.2 Combo Box Drop-Down

FIGURE 11.3 Scenario Manager Dialog Box

FIGURE 11.4 Scenario Manager Example

FIGURE 11.5 Loan Calculation Layout

FIGURE 11.6 One-Variable Data Table

FIGURE 11.7 Completed One-Variable Data Table

FIGURE 11.8 Two-Variable Data Table

FIGURE 11.9 Completed Two-Variable Data Table

FIGURE 11.10 Data Table with Colour Scales

FIGURE 11.11 Completed Data Table Using Advanced Conditional Formatting

FIGURE 11.12 Highlighting Selected Interest Scenario Using Conditional Formatti...

FIGURE 11.13 Conditional Formatting Rule Dialog Box

FIGURE 11.14 Conditional Formatting Rule Manager Dialog Box

FIGURE 11.15 Conditional Formatting Rule Showing Intersection of Inputs

FIGURE 11.16 Model Layout for Drop-Down Scenario Method

FIGURE 11.17 Model Scenario Inputs

FIGURE 11.18 Data Validation Drop-Down List Dialog Box

FIGURE 11.19 Data Validation Drop-Down List

FIGURE 11.20 Completed Data Validation Drop-Down Box Model with Scenario Formul...

FIGURE 11.21 Combo Box Format Control Dialog Box

FIGURE 11.22 Model with Combo Box Drop-Down

FIGURE 11.23 Completed Combo Box Model

FIGURE 11.24 Combo Box Drop-Down with Horizontally Oriented Source Data

FIGURE 11.25 Model Layout for Data Validation Scenario Method

FIGURE 11.26 Creating a Two-Variable Data Table

FIGURE 11.27 Completed Data Table

FIGURE 11.28 Completed Probability-Weighted Predicted Outcome

Chapter 12

FIGURE 12.1 Line Chart with Multiple Series

FIGURE 12.2 Chart on Two Different Axes and Chart Types (Combo Chart)

FIGURE 12.3 Comparison of Single Series Chart Types

FIGURE 12.4 Comparison of Multi-series Chart Types

FIGURE 12.5 Donut Chart

FIGURE 12.6 Combination Chart

FIGURE 12.7 Map Chart

FIGURE 12.8 Bubble Chart

FIGURE 12.9 Editing from the Chart

FIGURE 12.10 Adding Chart Elements in Excel for Mac

FIGURE 12.11 Pie Chart Depicting Units Sold Data

FIGURE 12.12 Changing the Data Source to Depict Expenses

FIGURE 12.13 Single-Series Column Chart

FIGURE 12.14 Double-Series Clustered Column Chart

FIGURE 12.15 Edit Series Dialog Box with Incorrect Series Values Data

FIGURE 12.16 Using a Chart Template

FIGURE 12.17 Changing the Hidden and Empty Cells Option

FIGURE 12.18 Line Chart with Data Table

FIGURE 12.19 New Data Not Included in Formula

FIGURE 12.20 New Data Included in Named Range, and Formula

FIGURE 12.21 Having a Title in Column A Will Expand the Named Range by One Row

FIGURE 12.22 Chart with Variable Number of Tenants

FIGURE 12.23 Chart with Fixed Number of Tenants

FIGURE 12.24 Create Three Dynamic Named Ranges, One for Each Series in the Char...

FIGURE 12.25 Referring to the Named Range in the Chart Series

FIGURE 12.26 Selecting Non-consecutive Ranges by Holding Down the Control Key

FIGURE 12.27 Insert Chart Dialog Box

FIGURE 12.28 Completed Combo Chart

FIGURE 12.29 Data Shown in a Two-Dimensional Chart

FIGURE 12.30 Inserting the Bubble Chart

FIGURE 12.31 Changing the Labels

FIGURE 12.32 Completed Bubble Chart

FIGURE 12.33 Creating the “Active Range”

FIGURE 12.34 Creating a Chart Based on the Active Range

FIGURE 12.35 Completed Dynamic Chart

FIGURE 12.36 Completed Dynamic Chart with Linked Text Box

FIGURE 12.37 Completed Company Profit Waterfall

FIGURE 12.38 Cumulative Bar Chart

FIGURE 12.39 Creating a Waterfall Chart

FIGURE 12.40 Editing the Source Data Range of the Waterfall Chart

FIGURE 12.41 Setting the Total Column

FIGURE 12.42 Completed Waterfall Chart

Guide

Cover

Table of Contents

Begin Reading

Pages

ii

iii

iv

v

xi

xii

xiii

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

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

182

183

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

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

256

257

258

259

260

261

262

263

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

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

397

399

400

401

403

404

405

406

407

408

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers' professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.

For a list of available titles, visit our Web site at www.WileyFinance.com.

Using Excel for Business and Financial Modelling

A practical guide

Third Edition

DANIELLE STEIN FAIRHURST

This third edition first published 2019© 2019 John Wiley & Sons Ltd

Edition HistoryJohn Wiley & Sons Ltd (1e, 2012 and 2e, 2015)

Registered officeJohn Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, United Kingdom

For details of our global editorial offices, for customer services and for information about how to apply for permission to reuse the copyright material in this book please see our website at www.wiley.com.

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 the UK Copyright, Designs and Patents Act 1988, without the prior permission of the publisher.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Designations used by companies to distinguish their products are often claimed as trademarks. All brand names and product names used in this book are trade names, service marks, trademarks or registered trademarks of their respective owners. The publisher is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. It is sold on the understanding that the publisher is not engaged in rendering professional services and neither the publisher nor the author shall be liable for damages arising herefrom. If professional advice or other expert assistance is required, the services of a competent professional should be sought.

Library of Congress Cataloging-in-Publication Data is Available

ISBN 978-1-119-52038-2 (paperback) ISBN 978-1-119-52037-5 (ePDF)ISBN 978-1-119-52035-1 (epub) ISBN 978-1-119-52034-4 (Obook)

Cover Design: WileyCover Image: © Mike Fairhurst

For Mike

Preface

This book was written from course materials compiled over many years of training in analytical courses in Australia and globally—most frequently courses such as Financial Modelling in Excel, Data Analysis & Reporting in Excel, and Budgeting & Forecasting in Excel, both as face-to-face workshops and online courses. The common theme is the use of Microsoft Excel, and I've refined the content to suit the hundreds of participants and their questions over the years. This content has been honed and refined by the many participants in these courses, who are my intended readers. This book is aimed at you, the many people who seek financial analysis training (either by attending a seminar or self-paced by reading this book), because you are seeking to improve your skills to perform better in your current role, or to get a new and better job. When I started financial modelling in the early 1990s, it was not called financial modelling—it was just “using Excel for business analysis”, which is what I called the first two editions of this book. The title was edited in the third edition to “Using Excel for Business and Financial Modelling”. It was only just after the new millennium that the term financial modelling gained popularity in its own right and became a required skill often listed on analytical job descriptions. This book spends quite a bit of time in Chapter 1 defining the meaning of a financial model, as it's often thought to be something that is far more complicated than it actually is. Many analysts and financial professionals I've met are building financial models already without realising it, but they do themselves a disservice by not calling their models, “models”!

However, those who are already building financial models are not necessarily following good modelling practice as they do so. Chapter 3 is dedicated to the principles of best modelling practice, which will save you a lot of time, effort, and anguish in the long run. Many of the principles of best practice are for the purpose of reducing the possibility of error in your model, and there is a whole section on strategies for reducing error in Chapter 4.

The majority of Excel users are self-taught, and therefore many users will often know highly advanced Excel tools, yet fail to understand how to use them in the context of building a financial model. This book is very detailed, so feel free to skip sections you already know. Because of the comprehensive nature of the book, much of the detailed but less commonly used content, such as instructions for the older versions of Excel, has been moved to the companion website at www.wiley.com/go/steinfairhurstrevised. There you will also find templates, checklists, and other useful materials. References to the content on the website, and many cross-references to other sections of the book, can be found throughout the book.

I'm passionate about supporting the financial modelling community, and especially about encouraging more women into what I believe is a highly rewarding and in-demand profession. There is a whole world of financial modelling awards, championships, standards, certifications, and meetup groups all over the world you can tap into to learn, network, and progress your career. If this book has piqued your interest, then I invite you to connect with me directly on LinkedIn and other social media platforms to find out more about how to get more involved in the wonderful world of financial modelling.

BOOK OVERVIEW

This book has 12 chapters, which can be grouped into three sections. Whilst they do follow on from each other with the most basic concepts at the beginning, feel free to jump directly to any of the chapters. The first section—Chapters 1 to 3—addresses the least technical topics about financial modelling in general, such as tool selection, model design, and best practice.

The second section—Chapters 4 to 8—is extremely practical and hands on. Here I have outlined all of the tools, techniques, and functions in Excel that are commonly used in financial modelling. Of course, it does not cover everything Excel can do, but it covers the “must know” tools.

The third section—Chapters 9 to 12—is the most important in my view. This covers the use of Excel in financial modelling and analysis. This is really where the book differs from other “how to” Excel books. Chapter 9 covers some commonly used techniques in modelling, such as escalation, tiering tables, and depreciation—how to actually use Excel tools for something useful! Chapter 11 covers the several different methods of performing scenarios and sensitivity analysis (basically the whole point of financial modelling to my mind). Lastly, Chapter 12 covers the often-neglected task of presenting model output. Many modellers spend days or weeks on the calculations and functionality, but fail to spend just a few minutes or hours on charts, formatting, and layout at the end of the process, even though this is what the user will see, interact with, and eventually use to judge the usefulness of the model.

ACKNOWLEDGEMENTS

This book would not have been written had it not been for the many people who have attended my training sessions, participated in online courses, and contributed to the forums. Your continual feedback and enthusiasm for the subject inspired me to write this book and it was because of you that I realised how much a book like this was needed.

The continued support of my family and network made this project possible. In particular, Mike, my husband, for his unconditional commitment and to whom this book is dedicated; my children who give me so much joy; as well as my remarkable parents and siblings, who have always inspired and encouraged me without question. I would like to give special thanks to my ever-patient assistant Susan Wilkin for her continuing dedication and diligence, as always. I could not do it without you all.

I hope you find the book both useful and enjoyable. Happy modelling!

CHAPTER 1What is Financial Modelling?

There are all sorts of complicated definitions of financial modelling, and in my experience there is quite a bit of confusion around what a financial model is exactly. A few years ago, we put together a Plum Solutions survey about the attitudes, trends, and uses of financial modelling, asking respondents “What do you think a financial model is?” Participants were asked to put down the first thing that came to mind, without any research or too much thinking about it.

I found the responses interesting, amusing, and sometimes rather disturbing.

Some answers were overly complicated and highly technical:

“Representation of behaviour/real-world observations through mathematical approach designed to anticipate range of outcomes.”

“A set of structured calculations, written in a spreadsheet, used to analyse the operational and financial characteristics of a business and/or its activities.”

“Tool(s) used to set and manage a suite of variable assumptions in order to predict the financial outcomes of an opportunity.”

“A construct that encodes business rules, assumptions, and calculations enabling information, analysis, and insight to be drawn out and supported by quantitative facts.”

“A system of spreadsheets and formulas to achieve the level of record keeping and reporting required to be informed, up-to-date, and able to track finances accurately and plan for the future.”

Some philosophical:

“A numerical story.”

Some incorrect:

“Forecasting wealth by putting money away now/investing.”

“It is all about putting data into a nice format.”

“It is just a mega huge spreadsheet with fancy formulas that are streamlined to make your life easier.”

Some ridiculous:

“Something to do with money and fashion?”

Some honest:

“I really have no idea.”

And some downright profound:

“A complex spreadsheet.”

There are many (often very complicated and long-winded) definitions available from different sources, but I actually prefer the last, very broad, but accurate description: “a complex spreadsheet”. Whilst it does need some definition, my definition of a financial model is pretty broad.

As long as a spreadsheet has financial inputs and outputs, and is dynamic and flexible, I'm happy to call it a financial model! Pretty much the whole point of financial modelling is that you change the inputs and the outputs. This is the major premise behind scenario and sensitivity analysis; this is what Excel, with its algebraic logic, was made for. Most of the time, a model will contain financial information and serve the purpose of making a financial decision, but not always. Quite often it will contain a full set of financial statements: profit and loss, cash flow, and balance sheet; but not always.

According to the more staid or traditional definitions of financial modelling, the following items would all most certainly be classified as financial models:

A business case that determines whether to go ahead with a project.

A five-year forecast showing profit and loss, cash flow, and balance sheet.

Pricing calculations to determine how much to bid for a new tender.

Investment analysis for a joint venture.

But what about other pieces of analysis that we perform as part of our roles? Can these also be called financial models? What if something does not contain financial information at all? Consider if you were to produce a spreadsheet for the following purposes:

An actual versus budget monthly variance analysis

that does not contain scenarios and for which there are no real assumptions listed.

A risk assessment

, where you enter the risk, assign a likelihood to that risk, and calculate the overall risk of the project using probability calculations. This does not contain any financial outputs at all.

A dashboard report

showing a balanced scorecard type of metrics reporting like headcount, quality, customer numbers, call volume, and so on. Again, there are few or no financial outputs.

See the section “Types and Purposes of Financial Models” later in this chapter for greater detail on business models that don't actually contain financial information.

Don't get hung up on whether you're building something that meets the definition of a financial model or not. As long as you've got inputs and outputs that change flexibly and dynamically, you can call it a financial model. If you're using Excel to any extent whereby you are linking cells together, chances are you're already building a financial model whether you realise it or not. The most important thing is that you are building the model (or whatever it's called) in a robust way, following the principles of best practice, which this book will teach you.

Generally, a model consists of one or more input variables along with data and formulas that are used to perform calculations, make predictions, or perform any number of solutions to business (or non-business) requirements. By changing the values of the input variables, you can do sensitivity testing and build scenarios to see what happens when the inputs change.

Sometimes managers treat models as though they are able to produce the answer to all business decisions and solve all business problems. Whilst a good model can be of significant aid, it's important to remember that models are only as good as the data they contain, and the answers they produce should not necessarily be taken at face value.

“The reliability of a spreadsheet is essentially the accuracy of the data that it produces, and is compromised by the errors found in approximately 94 percent of spreadsheets.”1 When presented with a model, the savvy manager will query all the assumptions and the way it has been built. Someone who has had some experience in building models will realise that they must be treated with caution. Models should be used as one tool in the decision-making process, rather than the definitive solution.

WHAT'S THE DIFFERENCE BETWEEN A SPREADSHEET AND A FINANCIAL MODEL?

Before we continue, let me make one thing clear: I am not partial to the use of the word spreadsheet; in fact, you'll hardly find it used at all in this book.

I've often been asked the difference between the two, and there is a fine line of definition between them. In a nutshell, an Excel spreadsheet is simply the medium that we can use to create a financial model.

At the most basic level, a financial model that has been built in Excel is simply a complex spreadsheet. By definition, a financial model is a structure that contains input data and supplies outputs. By changing the input data, we can test the results of these changes on the output results, and this sort of sensitivity analysis is most easily done in an Excel spreadsheet.

One could argue, then, that they are in fact the same thing; there is really no difference between a spreadsheet and a financial model. Others question if it really matters what we call them as long as they do the job. After all, both involve putting data into Excel, organising it, formatting, adding some formulas, and creating some usable output. There are, however, some subtle differences to note:

“

Spreadsheet” is a catch-all term for any type of information stored in Excel, including a financial model.

Therefore, a spreadsheet could really be anything: a checklist, raw data output from an accounting system, a beautifully laid out management report, or a financial model used to evaluate a new investment.

A financial model is more structured.

A model contains a set of variable assumptions, inputs, outputs, calculations, scenarios, and often includes a set of standard financial forecasts such as profit and loss, balance sheet, and cash flow, which are based on those assumptions.

A financial model is dynamic.

A model contains variable inputs, which, when changed, impact the output results. A spreadsheet might be simply a report that aggregates information from other sources and assembles it into a useful presentation. It may contain a few formulas, such as a total at the bottom of a list of expenses or average cash spent over 12 months, but the results will depend on direct inputs into those columns and rows. A financial model will always have built-in flexibility to explore different outcomes in all financial reports based on changing a few key inputs.

A spreadsheet is usually static.

Once a spreadsheet is complete, it often becomes a stand-alone report, and no further changes are made. A financial model, on the other hand, will always allow a user to change input variables and see the impact of these assumptions on the output.

A financial model will use relationships between several variables to create the financial report, and changing any or all of them will affect the output.

For example, Revenue in Month 4 could be a result of Sales Price × Quantity Sold Prior Month × Monthly Growth in Quantities Sold. In this example, three factors come into play, and the end user can explore different mixes of all three to see the results and decide which reflects their business model best.

A spreadsheet shows actual historical data, whereas a financial model contains hypothetical outcomes.

A by-product of a well-built financial model is that we can easily use it to perform scenario and sensitivity analysis. This is an important outcome of a financial model. What would happen if interest rates increase by half a basis point? How much can we discount before we start making a loss?

In conclusion, a financial model is a complex type of spreadsheet, whilst a spreadsheet is a tool that can fulfil a variety of purposes, financial models being one. The list of attributes above can identify the spreadsheet as a financial model, but in some cases we really are talking about the same thing. Take a look at the Excel files you are using. Are they dynamic, structured, and flexible, or have you simply created a static, direct input spreadsheet?

TYPES AND PURPOSES OF FINANCIAL MODELS

Models in Excel can be built for virtually any purpose—financial and non-financial, business-related or non-business-related—although the majority of models will be financial and business-related. The following are some examples of models that do not capture financial information:

Risk management.

A model that captures, tracks, and reports on project risks, status, likelihood, impact, and mitigation. Conditional formatting is often integrated to make a colourful, interactive report.

Project planning.

Models may be built to monitor progress on projects, including critical path schedules and even Gantt charts. (See the next section in this chapter, “Tool Selection”, for an analysis of whether Microsoft Project or Excel should be used for building this type of project plan.)

Key performance indicators (KPIs) and benchmarking.

Excel is the best tool for pulling together KPI and metrics reporting. These sorts of statistics are often pulled from many different systems and sources, and Excel is often the common denominator between different systems.

Dashboards.

The popularity of dashboards has increased in recent years. The dashboard is a conglomeration of different measures (sometimes financial, but often not), which are also often conveniently collated and displayed as charts and tables using Excel.

Balanced scorecards.

These help provide a more comprehensive view of a business by focusing on the operational, marketing, and developmental performance of the organisation as well as financial measures. A scorecard will display measures such as process performance, market share, or penetration, and learning and skills development, all of which are easily collated and displayed in Excel.

As with many Excel models, most of these could be more accurately created and maintained in a purpose-built piece of software, but quite often the data for these kinds of reports is stored in different systems, and the most practical tool for pulling the data together and displaying it in a dynamic monthly report is Excel.

Although purists would not classify these as financial models, the way that they have been built should still follow the fundamentals of financial modelling best practices, such as linking and assumptions documentation. How we classify these models is therefore simply a matter of semantics, and not particularly important. Going back to our original definition of financial modelling, it is a structure (usually in Excel) that contains inputs and outputs and is flexible and dynamic.

TOOL SELECTION

In this book, we will use Excel exclusively, as that is most appropriate for the kind of financial analysis we are performing when creating financial models. We often hear it said that Excel is the “second-best solution” to a problem. There is usually a better, more efficient piece of software that will also provide a solution, but we often default to the “Swiss army knife” of software, Excel, to get the job done. Why do many financial modelling analysts use Excel almost exclusively, when they know that better solutions exist? At Plum Solutions, our philosophy is also one of using only “plain vanilla” Excel, without relying on any other third-party software, for several reasons:

No extra licences, costly implementation, or software download is required.

The software can be installed on almost any computer.

Little training is needed, as most users have some familiarity with the product, which means other people will be able to drive and understand your model.

It is a very flexible tool. If you can imagine it, you can probably do it in Excel (within reason, of course).

Excel can report, model, and contrast virtually any data, from any source, all in one report.

But most importantly, Excel is commonly used across all industries, countries, and organisations, meaning that the Excel skills you have are highly transferrable.

What this last point means to you is that if you have good financial modelling skills in Excel, these skills are going to make you more in demand, especially if you are considering changing industries or roles or getting a job in another country. In fact, one of the best things you can do for your career is to improve your Excel skills. Becoming an expert developer on a proprietary piece of software is useful, but becoming a highly skilled Excel expert will stand you in good stead throughout your career.

Excel has its limitations, of course, and Excel's main downfall is the ease with which users can make errors in their models. Therefore, a large part of financial modelling best practice relates to reducing the possibility of errors. See Chapter 3, “Best Practice Principles of Modelling”, and “Error Avoidance Strategies” in Chapter 4 for details on errors and how to avoid them.

The other issue with using Excel is capacity; we simply run out of rows, especially in this age of “Big Data”. Microsoft worked hard to keep Excel relevant by introducing Power Pivot, which was a free add-in when it was first introduced in Excel 2010 and is now native to later versions. Power Pivot can handle much bigger data than plain Excel, which gets around Excel's capacity limitations.

For more information on the different capabilities of Excel, see the section on “Excel Versions” in Chapter 5.

Is Excel Really the Best Option?

Before jumping straight in and creating your solution in Excel, it is worth considering that some solutions may be better built in other software, so take a moment to contemplate your choice of software before designing a solution. There are many other forms of modelling software on the market, and it might be worth considering other options besides Excel. There are also a number of Excel add-ins provided by third parties that can be used to create financial models and perform financial analysis. The best choice depends on the solution you require.

The overall objective of a financial model determines the output as well as the calculations or processing of input required by the model. Financial models are built for the purpose of providing timely, accurate, and meaningful information to assist in the financial decision-making process. As a result, the overall objective of the model depends on the specific decisions that are to be made based on the model's output.

As different modelling tools lend themselves to different solutions or output, before selecting a modelling tool it is important to determine precisely what solution is required based on the identified model objective.

Evaluating Modelling Tools

Once the overall objective of the model has been established, a financial modelling tool that will best suit the business requirements can be chosen.

To determine which financial modelling tool would best meet the identified objective, the following must be considered:

The output required from the model, based on who will use it and the particular decisions to be made.

The volume, complexity, type, and source of input data, particularly relating to the number of interdependent variables and the relationships between them.

The complexity of calculations or processing of input to be performed by the model.

The level of computer literacy of the users, as they should ideally be able to manipulate the model without the assistance of a specialist.

The cost versus benefit set off for each modelling tool.

As with all software, financial modelling programs can either be purchased as a package or developed in-house. Whilst purchasing software as a package is a cheaper option, in a very complex industry, in-house development of specific modelling software may be necessary in order to provide adequate solutions. In this instance, one would need to engage a qualified specialist to plan and develop appropriate modelling software.

Which package you choose depends on the solution you require. Customer relationship management (CRM) data lends itself very well to a database, whereas something that requires complex calculations, such as those in many financial models, is more appropriately dealt with in Excel.

Excel is often described as a “band-aid solution”, because it is such a flexible tool that we can use to perform almost any process—albeit not as fast or as well as fully customised software, but it will get the job done until a long-term solution is found: “Spreadsheets will always fill the void between what a business needs today and the formal installed systems.”.2

Budgeting and Forecasting    Many budgets and forecasts are built using Excel, but most major general ledger systems have additional modules available that are built specifically for budgeting and forecasting. These tools provide a much easier, quicker method of creating budgets and forecasts that is less error-prone than using templates. However, there are surprisingly few companies that have a properly integrated, fully functioning budgeting and forecasting system, and the fallback solution is almost always Excel.

There are several reasons why many companies use Excel templates over a full budgeting and forecasting solution, whether they are integrated with their general ledger system or not:

A full solution can be expensive and time-consuming to implement properly.

Integration with the general ledger system means a large investment in a particular modelling system, which is difficult to change later.

Even if a system is not in place, invariably some analysis will need to be undertaken in Excel, necessitating that at least part of the process is built using Excel templates.

Microsoft Office Tools: Power Excel and Access    “Plain vanilla” Excel (and by this, I mean no add-ins) is the most commonly used tool and the one we are focusing on in this book. There are also other Microsoft tools, both outside and within Excel, that could also serve to create the solution, depending on the requirements. Any version of Excel released from Excel 2010 onwards contains access to the tools we sometimes refer to as “Power Excel”. The introduction of these tools was the most exciting thing to happen in the Excel world in a long time, and it has truly changed the landscape for Excel users. Note, however, that at the time of writing, none of these tools are yet available for a Mac.

The Power Excel suite consists of:

Power Query (also called Get & Transform)

Power Pivot

Power BI

Power BI is technically a Power Excel tool, but it is a separate desktop tool primarily used for building dashboards and visualisations, so we won't be going into detail on it here.

Power Excel: Microsoft Power Query (Get & Transform)    First introduced as a free add-in in Excel 2010, Power Query is now an inbuilt feature which, if you're using Excel 2016 or later, can be accessed via the Get & Transform section of the Data Tab. It extracts data from various sources, such as websites or other systems, and allows you to cleanse and format the data. When you perform a series of actions, this procedure can be saved, which can be repeatedly performed each time the data is refreshed. Whilst not a modelling tool, Power Query is useful for cleansing and preparing the data, ready to use in your financial model.

Power Excel: Microsoft Power Pivot    Power Pivot extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. It will allow you to do things you couldn't do before in plain Excel, like matching data from multiple sources and pulling them together into a single report. Because it is a relational database, Power Pivot makes it easy to link together data from various sources, employing a simple “drag and drop” graphical user interface.

Marvellous as it is, we know that plain vanilla Excel stops being quite so wonderful when your data is more than 1,048,576 records long, or if the data needs to be consolidated from multiple sources. When faced with this problem, Excel users find themselves migrating to a data warehouse or other, more powerful software. Microsoft has tried to retain these users by introducing Power Pivot, which addresses these problems with added capacity and speed, yet retains the familiar Excel interface that we all know and love.

As a self-service business intelligence (BI) product, Power Pivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries. For more sophisticated users, Power Pivot can:

Create your own BI solutions without purchasing expensive software.

Manipulate large data sets quickly, even if they consist of millions of rows (Excel can't do that).

Construct complex what-if reporting systems with data modelling and data analysis expressions (DAX).

Link data from various sources quickly and easily.

Although more appropriate for data analysis than pure dynamic financial models, Power Pivot is certainly worth some consideration when you are building an Excel solution with large quantities of data. If you find that your model has the following attributes, then you should consider using Power Pivot:

Your data contains many thousands of rows and your model is starting to slow down.

PivotTables or Tables are used extensively.

Data needs to be sourced from multiple locations.

One of the great things about Power Pivot is that it is already part of your existing Microsoft licence, so there are no extra licensing costs. There are a number of differences between versions, and as this is an area of rapid change, I have no doubt that the availability of versions and features may have changed by the time this book goes to print.

The disadvantage of using Power Pivot is that although you don't need to be a BI specialist to use it, learning how to use Power Pivot is not particularly straightforward even for advanced users. We offer a number of Power Pivot and Power BI training courses at Plum Solutions through our partners, and there are many videos and online resources that can help you to get started if you decide that Power Pivot is the solution that you need.

If you are trying to decide whether your Excel skills are advanced enough to consider tackling Power Pivot, here are some questions that will help you to determine whether you are ready to take on Power Pivot. You should:

Understand and have used Excel's SUMIF function.

Have a working knowledge of filtering data in Excel (e.g., Auto or Advanced Filters).

Know how to deal with multiple criteria (e.g., SUMIFS, SUMPRODUCT, or DBASE functions).

Be able to import data from third-party databases and/or files (e.g., Access, SQL, MIS systems).

Regularly use, adapt, and modify PivotTables (see

Chapter 8

for more on PivotTables).

Have created calculated fields in PivotTables.

Have created and/or modified an Excel Table (a structured reference table, not a data table—see

Chapter 8

for more on Excel Tables).

It took a while to catch on, but Power Pivot has certainly gained in popularity to the point where it has now become almost mainstream amongst Excel users. Microsoft has devoted a lot of resources to developing the Power Pivot product, so its use can only continue to spread in the near future. It's worth investing some time in learning it: being skilful in Power Pivot may become similar to having advanced Excel skills and will be a valuable addition to your résumé, and benefit your career as an analyst.

Bear in mind, however, that Power Pivot is not primarily a financial modelling tool. It was designed for the purpose of data analysis, not financial modelling. Remember that—as we talked about at the beginning of the chapter—a financial model, by definition, has inputs and outputs, is dynamic and flexible, but a model built in Power Pivot summarises a large quantity of data into PivotTables, so, whilst not impossible, adjusting assumptions and toggling between scenarios is difficult to do in Power Pivot.

MS Access    Since the introduction of Power Pivot to the Microsoft suite of products, Access is less often used, but it's still worth a mention. There is often some resistance to using Access, and it is certainly less popular than it used to be. Prior to the release of Excel 2007, Excel users were restricted to only 65,000 rows, and many analysts and finance staff used Access as a way to get around this limit. With now over 1.1 million rows (and purportedly up to a billion rows with Power Pivot), Excel is able to handle a lot more data, so there is less need for the additional row capacity of Access. If you've been using Access over the years, you might have noticed that not very much has changed in Access between versions. It seems that Microsoft is investing more of its efforts into the new Power Excel rather than Access.

Advantages of Excel

Excel is included in most basic Microsoft Office packages (unlike Access, which often needs to be purchased separately), and therefore comes as standard on most PCs. Excel is much more flexible than Access, and calculations are much easier to perform.

It is generally faster to build a solution in Excel than in Access.

Excel has a wider knowledge base among users, and many people find it to be more intuitive. This means it is quicker and easier to train staff in Excel.

It is very easy to create flexible reports and charts in Excel.

Excel can report, model, and contrast virtually any data, from any source, all in one file.

Excel easily performs calculations on more than one row of data at a time, which Access has difficulty with.

Advantages of Access

Access can handle much larger amounts of data: Excel 2003 was limited to 65,536 rows and 256 columns, and later versions of Excel are limited to around 1.1 million rows (1,048,576 rows, to be precise) and 16,384 columns. Access's capability is much larger, and it also has a greater memory storage capacity.

Data is stored only once in Access, making it work more efficiently.

Data can be entered into Access by more than one user at a time.

Access is good at crunching and manipulating large volumes of data.

Due to Access's lack of flexibility, it is more difficult for users to make errors.