In this course we show you how to use Microsoft Excel to build smart models and avoid potential pitfalls, working through real scenarios so you can apply your learning directly to your work.
Excel financial modelling involves designing and building calculations to aid decision making. This is a beginner-level modelling course. Learning how to build models with complexity in an accurate, robust, and transparent way is an essential skill in the modern workplace.
Request Course Information
By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.
What you’ll learn
Apply lessons learnt to business scenarios
Protect you models from undesired changes
Learn Excel functions and features essential for model building
Apply best practice modelling techniques
Microsoft Apps at Lumify Work
Lumify Work is your best choice for training and certification in any of Microsoft’s leading technologies and services. We’ve been delivering effective training across all Microsoft products for over 30 years, and are proud to be Australia and New Zealand’s first and largest Microsoft Gold Learning Solutions Partner. All Lumify Work Microsoft courses follow Microsoft Official Curriculum (MOC) and are led by Microsoft Certified Trainers. Join more than 5,000 students who attend our quality Microsoft courses every year.
From our state-of-the-art classrooms to telepresence to your offices, our instructor-led training caters to your needs.
Track Record
30 years driving innovative, award-winning learning solutions
More Courses, More Often
When you train with Lumify Work you get more courses, more often, in more locations and from more vendors.
Quality instructors and content
Expert instructors with real world experience and the latest vendor-approved in-depth course content.
Partner-Preferred Supplier
Chosen and awarded by the world’s leading vendors as preferred training partner.
Ahead of the technology curve
No matter your chosen technologies or platforms, we can help you stay one step ahead.
Train Anywhere
From our state-of-the-art classrooms to telepresence to your offices, our instructor-led training caters to your needs.
Track Record
30 years driving innovative, award-winning learning solutions
More Courses, More Often
When you train with Lumify Work you get more courses, more often, in more locations and from more vendors.
Quality instructors and content
Expert instructors with real world experience and the latest vendor-approved in-depth course content.
Partner-Preferred Supplier
Chosen and awarded by the world’s leading vendors as preferred training partner.
Ahead of the technology curve
No matter your chosen technologies or platforms, we can help you stay one step ahead.
Train Anywhere
From our state-of-the-art classrooms to telepresence to your offices, our instructor-led training caters to your needs.
Track Record
30 years driving innovative, award-winning learning solutions
More Courses, More Often
When you train with Lumify Work you get more courses, more often, in more locations and from more vendors.
Who is the course for?
This course is suitable for those who want to become familiar with and master the Microsoft Excel tools, formulas, and functions necessary for building effective models.
If you answer YES to any of the below then the course may not be suitable for you.
Do you currently build detailed and complex financial models?
Do you expect to be taught financial or accounting theory, investment analysis, bond maths, or equity analysis?
VLOOKUP and SUMIF functions – I have never used or even heard of these functions?
Course subjects
Modelling techniques
Tips for best practice structure and design
Separation of inputs, calculations and outputs
Avoiding hard coding
How to best include adjustments
Effective styling to purpose
Indicators of risk and mitigating the risk of error
Using reconciliations and zero checks.
Tips for effective reporting
Projects
Business case model build #1 - Create a Feasibility Study
Business case model build #2 - Clients have commissioned a model to forecast Cashflows of a potential investment
Business case model build #3 - You have been engaged to assess the viability of a Development Proposal, compile forecast Financial Statements
Business case model build #4 - Create a Management Dashboard and Sensitivity Analysis on a model you have built
Business case model build #5 - Create an Automated Pricing Tool to support Sales Staff
Business case model build #6 - Mini-project to automate the payout calculation for investors given set parameters
Defined Names
Understanding Defined Names
Defining Names From Worksheet Labels
Using Names in Typed Formulas
Applying Names to Existing Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Defined Names Into Formulas
Defining Names for Constant Values
Creating Names From a Selection
Scoping Names to a Worksheet
Validating Data
Understanding Data Validation
Creating a Number Range Validation
Testing a Validation
Creating an Input Message
Creating an Error Message
Creating a Drop Down List
Using Formulas as Validation Criteria
Circling Invalid Data
Removing Invalid Circles
Copying Validation Settings
Formula Referencing
Absolute Versus Relative Referencing
Relative Formulas
Problems With Relative Formulas
Creating Absolute References
Creating Mixed References
Logical Functions
Understanding Logical Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT
Controls
Understanding Types of Controls
Understanding How Controls Work
Preparing a Worksheet for Controls
Adding a Combo Box Control
Changing Control Properties
Using the Cell Link to Display the Selection
Adding a List Box Control
Adding a Scroll Bar Control
Adding a Spin Button Control
Adding Option Button Controls
Adding a Group Box Control
Adding a Check Box Control
Protecting a Worksheet With Controls
Financial Functions
Understanding Financial Functions
Using PMT
Using FV
Using NPV
Using PV
Using RATE
Using EFFECT
Using NOMINAL
Date and Time Functions
Understanding Date and Time Functions
Using NOW
Using HOUR and MINUTE
Using TODAY
Calculating Future Dates
Using DATE
Using Calendar Functions
Using WEEKDAY
Using WEEKNUM
Using WORKDAY
Using EOMONTH
Lookup Functions
Understanding Data Lookup Functions
Using VLOOKUP
Using VLOOKUP for Exact Matches
Using HLOOKUP
Using INDEX
Using Match
Complex Formulas
Scoping a Formula
Long-Hand Formulas
Preparing for Complex Formulas
Creating the Base Formula
Adding More Operations
Editing a Complex Formula
Adding More Complexity
Copying Nested Functions
Switching to Manual Recalculation
Pasting Values From Formulas
Documenting Formulas
Math Functions
Understanding Maths Functions
Using SQRT
Using ABS
Using INT
Using TRUNC
Using ROUND
Using ROUNDDOWN and ROUNDUP
Using ODD and EVEN
Using CEILINGMATH
Using MROUND
Using PRODUCT
Using SUMIF
Using SUMIFS
Using SUMPRODUCT
Protecting Data
Understanding Data Protection
Providing Total Access to Cells
Protecting a Worksheet
Working With a Protected Worksheet
Disabling Worksheet Protection
Providing Restricted Access to Cells
Password Protecting a Workbook
Opening a Password Protected Workbook
Removing a Password From a Workbook
Scenarios
Understanding Scenarios
Creating a Default Scenario
Creating Scenarios
Using Names in Scenarios
Displaying Scenarios
Creating a Scenario Summary Report
Merging Scenarios
Goal Seeking
Understanding Goal Seeking
Using Goal Seek
Solver
Understanding How Solver Works
Installing the Solver Add
Setting Solver Parameters
Adding Solver Constraints
Performing the Solver Operation
Running Solver Reports
Prerequisites
This course is suitable for those who already have an intermediate level knowledge of Microsoft Excel.
End User Seminars
Seminar-style training is conducted in short sessions of 1 to 3 hours and is ideal for larger groups of up to 30 students.
The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.
Request Course Information
By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.
