Excel Financial Modelling Course for Beginners

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 - Forecast Cashflows for investment

Business case model build #3 - Assess viability of a Development Proposal

Business case model build #4 - Create a Management Dashboard and Sensitivity Analysis

Business case model build #5 - Create an Automated Pricing Tool

Business case model build #6 - Automate payout calculation for investors

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

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

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

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