Download our guide to end user applications

Microsoft Excel Specialist - Financial Modelling

  • Length 1 day
Course overview
View dates &
book now
Register interest

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.

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.


Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

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.

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.


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.


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.