SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Using Excel for Business Analysis - Supplementary Materials

Welcome to the website to accompany the book titled Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals (Second Edition) by Danielle Stein Fairhurst.

If you have a first edition copy of the book, Using Excel for Business Analysis, please click here for the supplementary material.

The supplementary content for Using Excel for Business Analysis consists of several documents which supplement the information in the book, including additional instructions, materials, downloads, and models.  Each chapter (except chapter 1) has its own Excel file which contains a soft copy of the many screenshots shown throughout the book, and their applicable page numbers.  Using these files, you'll be able to recreate many of the modelling situations, tools and functions described in the book.  Several Excel model exercises and sample models have also been included.

Please also visit www.plumsolutions.com.au/elearning if you would like to register for the Plum Solutions online courses that corresponds to the book.  These accompanying files have been made freely available by the author, but they might not make sense without the book.  If you wish to purchase a copy of Using Excel for Business Analysis, please visit www.plumsolutions.com.au/book.

Details of the supplementary material contained in this folder has been outlined below:

  • Excel 2003 Instructions.pdf a detailed document containing instructions for how to use tools for which instructions differ between versions - for those still using Excel 2003
  • Additional Material.pdf:  basically stuff we couldn't fit in the book!
    - Basic Formatting (Chapter 7)
    - Break Even Analysis (Chapter 9)
    - How to Create a Basic Line Chart (Chapter 12)

Chapter 1.  What is Financial Modelling

Online Resources.pdf:  a list of great free online resources including a list of websites, blogs, and tutorials for Excel and financial modelling (p18)

Chapter 2.  Building a Model

File Name

Figures

Page Numbers

Tools/Functions Covered

Chapter 2.xlsx 2.1-2.2 p32 Assumptions layout
  2.3-2.4 p33 Assumptions Categorisation
  2.5 p36 Model Structure
  2.6 p39 Using the Styles menu to format input cells
  2.7 p40 Formatting for inconsistent formulas
  2.8 p42 Flowchart of model calculations
  2.9 p52 Sample file structure

Chapter 3.  Best Practice Principles of Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chapter 3.xlsx 3.1-3.2 p60-p61 Assumptions Documentation 
  3.3 p62 Data Validation
  3.4 p64 Manual Footnoting
  3.5 p67 Hard Coded Assumptions Documentation
  3.6-3.8 p68-p69 Linking Text
  3.9-3.10 P70 Practical Commentary Exercise
Model Assessment Checklist.xlsx   P70  

Chapter 4.  Financial Modelling Techniques

File Name

Figures

Page Numbers

Tools/Functions Covered

Chapter 4.xlsx 4.1-4.2 p74-p75 Common Errors 
  4.3 p77 Data Validation
  4.4 p79 Methodology Documentation
  4.5 p81 Sense-Checking Methodology
  4.6 p82 Long Formulas
  4.7 p82 Formula Linking to external workbooks
  4.8 p83 Revenue Calculations
  4.9 p86 Break Links Dialog Box
  4.10 p87 Summary Report
  4.11 p89 Error-Check Example
  4.12 p90 Error Check Alert Formula
  4.13-4.17 p91-p94 Circular Reference
  4.18 p95 Iterative Calculations

 

Chapter 5.  Using Excel in Financial Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chapter 5.xlsx 5.1 p98 Function Wizard  
  5.2 p99 Formula Builder for Mac
  5.3 - 5.4 p100 Compounding Growth
  5.5 p104 Shortcut Keys
  5.6 p109 SUM
  5.7 p110 MAX
  5.8 p111 MIN
  5.9 p111 AVERAGE
  5.10 p112 Combining functions
  5.11 p114 IF Statement
  5.12 p117 Volume Pricing Table
  5.13 p117 Highlight and Copy IF Statement
  5.14 p118 Completed Nested IF function
  5.15 – 5.19 p119-p123 Cell Referencing
  5.20 p124 Name Box
  5.21 p125 Named Range
Excel Versions.pdf      
Handy Excel Shortcuts.pdf      
Mac Excel Shortcuts.pdf      

Chapter 6.  Functions for Financial Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 6 – 1.
Aggregation.xlsx

6.1-6.4 p128-p130 COUNTIF Function Wizard  
  6.5-6.6 p131-132 SUMIF
  6.7-6.8 p134-p135 COUNTIFS
  6.9 p135 SUMIFS
  6.10-6.11 p137-p138 AVERAGEIFS
  6.12-6.14 p139-p141 Filtering IFS by a variable value
Chap 6 – 2.
Lookups.xlsx
6.15-6.18 p142-p145 VLOOKUP
  6.19-6.21 p147-p149 HLOOKUP
  6.22-6.24 p150 LOOKUP

Chap 6 – 3. Other
Functions.xlsx

6.25 p153 Sample Data
  6.26-6.28 p154-p156 INDEX
  6.29-6.33 p157-p161 OFFSET
  6.34-6.37 p162-p164 FORECAST
  6.38 P166 FUNCTION
Chap 6 – 4. Working
with Dates.xlsx
6.39 P167 Dates
  6.40-6.41 p167 EOMONTH
  6.42 p168 WEEKDAY
  6.43 p170 MONTH
  6.44 p170 DAY
  6.45 p172

Shortcut Date Formatting
Drop-Down

  6.46 p173 Weekday only

Chap 6 – 5. Project
Evaluation &
Loan Calcs.xlsx

6.47 p175 NPV
  6.48-6.49 p177-p178 IRR
  6.50 p179 XNPV
  6.51-6.55 p182-p186 Loan Calculation

Chapter 7. Tools for Model Display

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 7 - 1.
Formatting.xlsx
7.1 p188 Formatting
  7.2-7.4 p188-p190 Date Formatting
  7.5 p193 Sample Report
  7.6 p194 Conditional Formatting
  7.7 p195 ROUND
  7.8 p196 Applying Conditional Formatting
  7.9 p197 Data Bars
  7.10 p198 Icon Sets
  7.11 p198 Sample Report without Formatting
  7.12 p199 Colour Scales
  7.13 p199 Hiding Icons
  7.14 p200 Multiple Conditional Formatting
  7.15-7.19 p201-p203 Sparklines
  7.20 p208 Customised Display Settings

Chap 7 - 2.
Bullet Proofing.xlsx

7.21 p209 Minimising the Ribbon
  7.22 p210 Restricted Work Area
  7.23 p211 Data Validation Comment
  7.24-7.25 p212-p213 Customised Error Message
  7.26-7.29 p214-p216 Drop-Down List
Chap 7 – 3.
Form Controls.xlsx
7.30-7.37 p217-p222 Form Controls
  7.38-7.40 p222-p224 Option Button
  7.41-7.43 p225-p226 Spinner
  7.44-7.48 p227-p230 Combo Box Drop-Down
  7.49-7.50 p231 Check Boxes Drive Calculation
Lender Repayment Calculator.xlsx      

Chapter 8.  Tools for Financial Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 8 - 1.
Hiding & Grouping.xlsx
8.1-8.4 p234-p237 Hiding Rows
  8.5-8.6 p238-p239 Grouping
Chap 8 – 2. Arrays
& Goal Seek.xlsx
8.7-8.12 p242-p246 Array Formulas
  8.13-8.14 p248-p249 Goal Seeking
Chap 8 - 3. Tables.xlsx 8.15 p250 Structured Reference Table
  8.16-8.25 p251-p261 Pivot Tables
 
Chap 8 – 4. Macros.xls 8.26-8.30 p263-p268 Macros

Chapter 9.  Common Uses of Tools in Financial Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 9 - 1.
Escalation.xlsx
9.1-9.6 p274-p277 Escalation Methods
  9.7-9.8 p279-p281 Exponential Growth Rates
  9.9-9.10 p282-p283 Interest Rates
Chap 9 - 2. Cumulative
Calculations & Payback.xlsx
9.11-9.12 p283-p284 Cumulative Totals
  9.13-9.14 p285-p286 Payback
  9.15-9.16 p288-p289 Complex Payback
Chap 9 – 3. WACC.xlsx 9.17-9.18 p292 WACC
Chap 9 – 4. Tiering.xlsx 9.19 p294 Flat Tiering
  9.20-9.21 p295-p296 Progressive Tiering
Chap 9 – 5. Depn.xlsx 9.22-9.27 p296-p306 Depreciation Methods
Chap 9 – 6. Break
Even.xlsx
9.28-9.33 p308-p312 Break Even Calculations
Break Even Chart.pdf      

Chapter 10.  Model Review

File Name

Figures

Page Numbers

Tools/Functions Covered

Chapter 10a.xlsx 10.1-10.2 p317 Trace Precedents
  10.3-10.5 p319-p320 Error Checking
  10.6 p321 Trace Error
Chapter 10b.xlsx 10.7-10.8 p321-p322 Evaluate Formula
  10.9 p323 Show Formula Options
  10.10-10.11 p324 Disabling Direct Editing in Cells
  10.12 p325 Change File type
QA Log.pdf      

Chapter 11.  Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 11 – 1. Scenario Tools.xlsx 11.1-11.2 p341 Drop-Down Boxes
  11.3-11.4 p342-p343 Scenario Manager
Chap 11 – 2. Data Tables.xlsx 11.5 p345 Loan Calculation Layout
  11.6-11.7 p345-p346 One-Variable Data Table
  11.8-11.9 p347-p348 Two-Variable Data Table
  11.10 p348 Data Table with Colour Scales
  11.11-11.15 p349-p353 Advanced Conditional Formatting
Chap 11 – 3. Scenario Comparison.xlsx 11.16-11.20 p354-p358 Drop-Down Scenario Method
  11.21-11.24 p359-p362 Combo Box Drop-Down
  11.25 p363 Data Validation Scenario Method
  11.26-11.27 p364 Two-Variable Data Table

Chapter 12.  Presenting Model Output

File Name

Figures

Page Numbers

Tools/Functions Covered

Chap 12 – 1. Presenting Data Graphically.xlsx 12.1 P371 Line Chart
  12.2 P371 Combo Chart
Chap 12 – 2. Chart Types.xlsx 12.3 P374 Single-Series Chart Types
  12.4 p374 Multiseries Chart Types
  12.5 p378 Donut Chart
  12.6 p378 Combination Chart
  12.7 p380 Bubble Chart
Chap 12 – 3. Working with charts.xlsx 12.8-12.9 p381-p382 Pie Charts
  12.10-12.11 p383 Column Charts
  12.12 p384 Edit Series Dialog Box
  12.13 p385 Save as Template Option
  12.14 p386 Using a Chart Template
  12.15 p387 Changing the Hidden
and Empty Cells Option
  12.16 p388 Line Chart with Data Table
Chap 12 – 4. Dynamic
Range Name Example.xlsx
12.17-12.19 p389-p391 Dynamic Named Ranges
Chap 12 – 5. Tenancy.xlsx 12.20 p392-p396 Tenancy
Chap 12 – 6. Combo Chart.xlsx 12.25-12.27 p396-p399 Combo Charts
Chap 12 – 7. Bubble.xlsx 12.29-12.32 P401-p405 Two-Dimensional Chart
Chap 12 – 8. Dynamic Chart.xlsx 12.33 – 12.34 p406 Active Range
  12.35 P408 Completed Dynamic Chart
  12.36 P409 Completed Dynamic Chart
with Linked Text Box
Chap 12 – 9. Waterfall Chart.xlsx 12.37 p410 Completed Company
Profit Waterfall
  12.38-12.46 p411-p418 Basic Waterfall Chart
  12.47-12.48 p418-p419 Complex Waterfall Chart