Plum Solutions
Availability: BOOK NOW
more info

Financial Modelling in Excel

(PS)-00001

Melbourne

$880.00per person

1 Day, 9:00am - 5:00pm

Public / Open

This course is designed for business professionals who need to explore the practical usage of advanced excel functions in a financial model. By creating your own user-friendly financial model, you will learn to translate business concepts into a structured format to identify weaknesses and predict future performance.
Course Content


Financial Modelling Theory & Best Practice

  • Overview of Financial Modelling
  • Model Design, planning and steps in building your model.
  • Upgrade to the latest version of Excel.
  • The technical, design, business and industry knowledge required for financial modelling
  • Purposes of financial models

Model Tools & Functions

  • Which formula or tool is most appropriate in which modelling situation?
  • Selecting the correct formula
  • Adjusting formulas manually
  • Named ranges
  • Applying absolute and relative cell referencing and it’s importance in Financial Modelling
  • Use of LOOKUP functions, aggregation functions such as SUMIFS, COUNTIFS and nested formulas within a financial model and others
  • How to model compounding inflation
  • Calculating customer acquisition numbers from the potential pool of customers
  • Modelling market penetration in a business case

Financial Functions

  • IRR (Internal Rate of Return)
  • NPV (Net Present Value)
  • How to calculate a payback period

Modelling Techniques

  • What makes a good model?
  • Best practice in financial modelling
  • Attributes of a good model such as user-friendly and structural features

Practical Exercise: Build an individual business case using a range of financial functions and tools utilising best practice financial modelling techniques.


Error Prevention

  • Modelling techniques and strategies to reduce potential for error
  • Identifying and correcting common errors
  • Avoiding error displays in formulas
  • Creating in-built, self-balancing error checks and error alerts

Other Useful Tools

  • Hiding sections of the model
  • Using Goal Seek within Financial Models
  • Macros and Financial Modelling
  • Pivot tables and their use in Financial Modelling

Rebuilding an inherited model

  • Formula auditing
  • "Sense-checking" methodology
  • Identifying formula error
  • Dealing with links and the potential errors they can cause

Bullet-proofing your model

  • Protect data by locking cells
  • Password Protection
  • Restricting incorrect data entry with data validations
  • Using worksheet protection to prevent entry
  • Protecting your file using “Read Only” and password protection
  • Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs

Making a model user-friendly

  • Formatting
  • Inserting Navigation buttons
  • Hyperlinks

Scenarios and Sensitivity Analysis

    Manual sensitivity analysis
  • Creating drop-down switches for scenario selection
  • Adjusting inputs variables to impact outcomes
  • What-if analysis
  • Stress-testing

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


Assumption Documentation

  • Why document assumptions?
  • Linking to source referencing
  • Importance of assumptions when assessing risk
  • Mitigate liability by including appropriate caveats and key assumptions

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 key assumptions, documentation and source referencing
  • Writing operation instructions
  • Summarising model data into a presentation
You will learn how to:


  • Build a financial model from scratch, or modify and improve an inherited model
  • Select the most appropriate formula to achieve the desired outcome
  • Identify common errors in modelling
  • Mitigate errors by building in error checks
  • Prevent incorrect use of your model by protecting worksheets
  • Validate data entry by setting data entry parameters
  • Create a navigation page to help users find their way around your model
  • Develop drop-down boxes which enable a model to produce a series of results depending on scenario variable selected
  • Mitigate liability by providing assumptions
  • Write instructions for use
  • Gain an in-depth understanding of how to build a business case
  • Communicate the results of your model clearly and concisely"
This course is designed for business professionals who need to explore the practical usage of advanced excel functions in a financial model. By creating your own user-friendly financial model, you will learn to translate business concepts into a structured format to identify weaknesses and predict future performance.
Course Content


Financial Modelling Theory & Best Practice

  • Overview of Financial Modelling
  • Model Design, planning and steps in building your model.
  • Upgrade to the latest version of Excel.
  • The technical, design, business and industry knowledge required for financial modelling
  • Purposes of financial models

Model Tools & Functions

  • Which formula or tool is most appropriate in which modelling situation?
  • Selecting the correct formula
  • Adjusting formulas manually
  • Named ranges
  • Applying absolute and relative cell referencing and it’s importance in Financial Modelling
  • Use of LOOKUP functions, aggregation functions such as SUMIFS, COUNTIFS and nested formulas within a financial model and others
  • How to model compounding inflation
  • Calculating customer acquisition numbers from the potential pool of customers
  • Modelling market penetration in a business case

Financial Functions

  • IRR (Internal Rate of Return)
  • NPV (Net Present Value)
  • How to calculate a payback period

Modelling Techniques

  • What makes a good model?
  • Best practice in financial modelling
  • Attributes of a good model such as user-friendly and structural features

Practical Exercise: Build an individual business case using a range of financial functions and tools utilising best practice financial modelling techniques.


Error Prevention

  • Modelling techniques and strategies to reduce potential for error
  • Identifying and correcting common errors
  • Avoiding error displays in formulas
  • Creating in-built, self-balancing error checks and error alerts

Other Useful Tools

  • Hiding sections of the model
  • Using Goal Seek within Financial Models
  • Macros and Financial Modelling
  • Pivot tables and their use in Financial Modelling

Rebuilding an inherited model

  • Formula auditing
  • "Sense-checking" methodology
  • Identifying formula error
  • Dealing with links and the potential errors they can cause

Bullet-proofing your model

  • Protect data by locking cells
  • Password Protection
  • Restricting incorrect data entry with data validations
  • Using worksheet protection to prevent entry
  • Protecting your file using “Read Only” and password protection
  • Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs

Making a model user-friendly

  • Formatting
  • Inserting Navigation buttons
  • Hyperlinks

Scenarios and Sensitivity Analysis

    Manual sensitivity analysis
  • Creating drop-down switches for scenario selection
  • Adjusting inputs variables to impact outcomes
  • What-if analysis
  • Stress-testing

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


Assumption Documentation

  • Why document assumptions?
  • Linking to source referencing
  • Importance of assumptions when assessing risk
  • Mitigate liability by including appropriate caveats and key assumptions

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 key assumptions, documentation and source referencing
  • Writing operation instructions
  • Summarising model data into a presentation
You will learn how to:


  • Build a financial model from scratch, or modify and improve an inherited model
  • Select the most appropriate formula to achieve the desired outcome
  • Identify common errors in modelling
  • Mitigate errors by building in error checks
  • Prevent incorrect use of your model by protecting worksheets
  • Validate data entry by setting data entry parameters
  • Create a navigation page to help users find their way around your model
  • Develop drop-down boxes which enable a model to produce a series of results depending on scenario variable selected
  • Mitigate liability by providing assumptions
  • Write instructions for use
  • Gain an in-depth understanding of how to build a business case
  • Communicate the results of your model clearly and concisely"

Plum Solutions

Plum Solutions delivers training in the following course categories

  • Financial Modeling
  • Financial Services

Plum Solutions Training Courses and Open Programs in Sydney, Melbourne and Brisbane, Australia.

Short Courses and In-House Training Programs:

With Training Courses available in these categories:

Financial Modelling | Budgeting & Forecasting | Business Cases & Pricing Models | Data Analysis

Call us to book on 1300 768 368

Need Approval?