End User Applications Category Banner Image

Microsoft Excel Specialist - Financial Modelling

Length 1 day

Course overview

View dates &
book now

Course locations >>

Why study this course

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.

Note: Excel 2016, Excel 2019, Excel 2021, and Excel 365 are all desktop application versions of Microsoft Excel. Excel 2016, Excel 2019, and Excel 2021 are the perpetual, bought-outright, stand-alone versions of the software; Excel 365 is the subscription-based version. Anyone working with any of these versions will be able to successfully complete this course. Throughout this course you will be using the desktop app; the course does not cover use of the web/online app.

Nexacu Public Schedule

Nexacu is part of the Lumify Group, offering you the largest public schedule of end user applications and professional development training in Australia, New Zealand, and the Philippines. You can now access the schedule of courses and book, by clicking on the button below.

Book now!

Request Course Information

What you’ll learn

At the completion of this course you should be able to:

Apply lessons learnt to business scenarios
Protect your models from undesired changes
Use Excel functions and features essential for model building
Apply best practice modelling techniques

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps courses

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 mathematics, 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

Mathematical Functions

Understanding Mathematical 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 assumes the user already has an intermediate level knowledge of Microsoft Excel.

Terms & Conditions

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.