SPECIALISTS IN FINANCIAL MODELLING
Select Page

# 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

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)

### 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

### 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.9-3.10 P70 Practical Commentary Exercise
Model Assessment Checklist.xlsx P70

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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
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

### 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