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 contact us 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 |
