Using Excel for Business Analysis – First Edition Supplementary Materials

This supplementary content is to accompany the first edition of this book.  The second edition supplementary content is available here.

This supplementary content is to accompany the first edition of this book.  The second edition supplementary content is available here.

Welcome to the website to accompany the book titled Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals 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

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

• Excel Exercise file Chap 2.xls
  – Model design
  – Inconsistent formulas

Chapter 3

• Excel Exercise file Chap 3.xls
  – Comments
  – Input messages
  – Superscript
  – Linked Text
  – Linked Assumptions
  – Linked Commentary
• Model Assessment Checklist.xls

Chapter 4

• Excel Exercise file Chap 4.xls
  – Excel Error
  – Error Check
  – Error Check Options
  – Error Check Example
  – Error Check Alert
• Long formulas.xls  A model which demonstrates different ways of designing a formula, and different tools for rebuilding an inherited model.

Chapter 5

• Excel Exercise file Chap 5.xls
  – Formula Options
  – Basic Functions
  – Nested IF Function
  – Relative & Absolute Referencing
  – Mixed Referencing
  – Named Range
• Shortcuts (Windows Version).pdf  A printable three-page cheat-sheet of all most useful shortcuts for financial modelling in Windows Excel
• Shortcuts (Mac Version).pdf  As above, using Excel for Mac
• Excel Versions.pdf  An overview of the history and key difference between all the different version of Excel in use for business today.

Chapter 6

• Excel Exercise file Chap 6.xls
  – Aggregation
  – Incorrect SUMIF
  – COUNTIFS & SUMIFS
  – AVERAGEIF & AVERAGEIFS
  – VLOOKUP
  – HLOOKUP
  – LOOKUP
  – INDEX & MATCH
  – OFFSET
  – Cash Flow using OFFSET
  – CHOOSE
  – FORECAST
  – Date Formulas
  – Working with Dates
  – NPV & IRR
  – XNPV & XIRR
  – Loan Calculation

Chapter 7

• Excel Exercise file Chap 7.xls
  – Custom Formatting
  – Conditional Formatting
  – Colour Scales
  – Icon Sets
  – Sparklines
  – Work Area
  – DV Comment
  – DV Drop-down
  – Form Control
  – Combo Box Form Control
  – Boolean Logic
• Salary Sacrifice Calculator.xls

Chapter 8

• Excel Exercise file Chap 8.xls
  – Hiding Rows
  – Grouping
  – Array Formula
  – Completed Array
  – Transpose Array
  – Goal Seek
  – Pivot Table Data
  – Pivot Table
• Malaysian Tax Calculator.xls

Chapter 9

• Excel Exercise file Chap 9.xls
  – Escalation Methods
  – Exponential Growth Rates
  – Nominal & Effect Functions
  – Cumulative Totals
  – Simple Payback
  – Payback Workings
  – More Complex Payback
  – WACC
  – Flat Tiering
  – Progressive Tiering
  – Depreciation Calculation
  – Depreciation End of Useful Life
  – Break Even Chart
  – Break Even Formula
  – Break Even Goal Seek

Chapter 10

• Excel Exercise file Chap 10.xls
  – Trace Precedents
  – Error Checking Tools
  – Trace Error
  – Evaluating Part of the Formula
• QA Log template.pdf  A sample template which can be used during the model QA process.

Chapter 11

• Excel Exercise file Chap 11.xls
  – Scenario Manager
  – One-Variable Data Table
  – Two-Variable Data Table
  – Data Table with Colour Scales
  – Advanced Conditional Formatting
• Scenario Comparison Exercise.xls
• Scenario Comparison (Completed).xls  An exercise comparing different methods of scenario analysis; data validation drop-down, combo box drop-down, and data table.

Chapter 12

• Excel Exercise file Chap 12.xls
  – Presenting Model Results
  – Which Chart Type
  – Donut Chart
  – Other Chart Types
  – Comparison Charts
  – Dynamic Named Ranges
  – Tenancy Example
  – Two Axes
  – Bubble Chart
• Waterfall Chart.xls