End User Applications Category Banner Image

Microsoft Excel Specialist - Financial Modelling

  • Length 1 day
Course overview
View dates &
book now

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

With Lumify Group's acquisition of Nexacu, we're pleased to now offer you the largest public schedule of end user applications training in Australia and New Zealand. As we move to consolidate our End User offering with Nexacu, as an interim measure you can now access the schedule of the most closely aligned courses and book, by clicking on the link below.

Nexacu powered by Lumify - master logo

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 Partner Cloud Logo

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's and New Zealand’s first and largest Microsoft Gold Learning Solutions Partner. Join more than 5,000 students who attend our quality Microsoft courses every year.


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.


Request Course Information

Personalise your schedule with Lumify USchedule

Interested in a course that we have not yet scheduled? Get in touch, and ask for your preferred date and time. We can work together to make it happen.



Offers

Continue your learning experience online with Lumify Plus
Lumify Plus (formerly DDLS Plus) is your online learning pathway to extend knowledge beyond courses. Get resources to help you practice what you learned and prepare for future courses, exams and certifications.