Financial Modelling in Excel

During the course, participants will create their own financial model to take away and use for future reference. You will learn how to design and create a user-friendly model which can then be used by anyone with limited knowledge of Excel.

You will learn how to:

1. Build a financial model from scratch, or modify and improve an inherited model
2. Select the most appropriate formula to achieve the desired outcome
3. Identify common errors in modelling & mitigate errors by building in error checks
4. Prevent incorrect use of your model by protecting worksheets
5. Validate data entry by setting data entry parameters
6. Develop manual scenario selection 
7. Mitigate liability by providing assumptions
8. Gain an in-depth understanding of how to build a business case
9. Communicate the results of your model clearly and concisely

This course builds on students' existing knowledge of Excel tools and functions and incorporates these into a financial model.

Course ContentAbout Financial Modelling

• What is Financial Modelling?
  Definition of a financial model.  Can all spreadsheets be called financial models?
• Model Design
  Designing and planning the layout of your model.   Considering factors such as the purpose and audience of the model.
• Skills needed for Financial Modelling 
  The technical, design, business and industry knowledge required for financial modelling
• Best Practice in Financial Modelling
  Overview of the six points of financial modelling best practice

Excel Tools

• Excel Versions
  Upgrading to Excel 2013 and technical differences between versions. Considerations when building a model for users of different versions.
• Conditional Formatting
  Creating an automatic variance alert.  Using icon sets, colour scales and data bars to add visual interest to model outputs.
• Hiding and Grouping
  Keep your model tidy and easy to follow by hide unnecessary information or unused parts of the model whilst still following best practice guidelines
• Dos and Don’ts for Linking Between Files
  Ways to improve model integrity and reduce errors between linked files

Financial Modelling Techniques

• What Makes a Good Financial Model?
  Attributes of a good model such as user-friendly and structural features
• Strategies for Reducing Errors
  Techniques to employ during the model building process to reduce the potential for formula or logic error
• Building Error Checks
  Creating in-built, self-balancing error checks and error alerts
• Bullet-Proofing your Model
  Using worksheet protection to prevent entry, and restricting data entries using data validations.  Prevent misuse of your model by restricting incorrect inputs.
• What-if Analysis with Goal Seek
  Back-solve from a desired formula output to determine a model input using the goal seek Excel tool 
• Overview of Scenario Analysis Methods
  Technical methods of creating scenario and sensitivity analysis in Excel

Essential Formulas

• Cell Referencing & Named Ranges
  Applying absolute and relative cell referencing and understanding its importance in Financial Modelling.  Using named ranges for assumptions reference.
• Logical Nested Functions
  Using IF, OR and AND functions, and nesting these together to create simple but intelligent formulas for use within financial models
• Aggregation Functions
  Applying the COUNTIF and SUMIF functions to reports and data summaries
• Using a VLOOKUP Function
  Correctly building this much-loved and often over-used Excel function
• Tiering Tables
  Practical application of one of the more complex and widely used calculations in financial modelling, such as tax calculations and volume break discounting
• Using the FORECAST / TREND Function
  Hypothetically forecasting future data based on historical trends using simple regression analysis in Excel
• Formula Selection
  Which formula or tool is most appropriate in which modelling situation? 

Building a Business Case

Case Study: Build an individual business case using a range of financial functions and tools utilising best practice financial modelling techniques

• Calculating Staff costs
  Modelling with consistent, nested formulas to calculate costs for employees with variable start and end dates, including compounding inflation
• Forecasting customer numbers
  Calculating customer acquisition numbers from the potential pool of customers with documented assumptions. Mitigate liability by including appropriate caveats and key assumptions.
• Modelling market penetration in a business case
  Using assumed takeup rates to model market penetration
• Forecasting Product Profitability
  Assess business case feasibility cashflow forecast
• Project Evaluation
  Evaluate project feasibility with IRR (Internal Rate of Return), NPV (Net Present Value) and the payback period

Analysing & Presenting your Model

Practical: Create a best, base and worst case scenario on your model. Select from the drop-down box and watch the results change

• Scenarios and Sensitivity Analysis
  Manual sensitivity analysis, creating drop-down switches for scenario selection. Adjusting inputs variables to impact outcomes.
• Model Documentation
  Summarising key assumptions, documentation and source referencing, Writing operation instructions
• Presentation of Model Output
  Summarising results and display of findings. Communicate the results of your model clearly and concisely whilst getting the key message across to the audience. Summarising model data into a presentation

Danielle Stein Fairhurst, Principal Consultant at Plum Solutions,  is an MBA qualified business professional with many years' experience as a financial analyst.  She is the author of "Using Excel for Business Analysis: a Guide to Financial Modelling Fundamentals", Revised Edition, John Wiley & Sons, April 2015.   With her talent for financial modelling and professional approach, she helps her clients create meaningful financial models in the forms of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.

Danielle is regularly engaged as a speaker, course facilitator, financial modelling consultant and analyst. She runs regular training seminars around Australia and globally, and her custom built training courses have been described by attendees as well-presented, neatly structured, informative, practical, and extremely relevant to their everyday needs. She is available for presentations, workshops, training courses, lectures and seminars. Topics include Financial Modelling in Excel, Budgeting & Forecasting and Data Analysis & Reporting.

She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management, and has taught management accounting subjects at Sydney University. Danielle lives in Sydney with her husband and two children, and enjoys the challenge of balancing a healthy family lifestyle with her passion for business.