Microsoft Excel Specialist - Financial Modelling

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