FMM Financial Modelling Master

Principle of Occam’s razor

Interrelationships within a model

Logical arrangement of the parts

Setting toggles

Model design and structure

Attributes of good Excel models

Financial

Date and time

Statistical

Lookup & reference

Database

Text

Logical

Information

Formula errors vs user errors

Warning signs

Using the F-Keys and combinations of F-Keys.

Name ranges using Create Names

Making range names more informative

Editing name ranges

Data validation

The Analysis ToolPak

Using formula auditing

Basic, intermediate and advanced functions

Arguments in advanced functions

Dealing with errors

Boolean logic

Change nested IF’s to something powerful

Array formulas

Using array formulas with the offset function

Calculating geometric return using arrays

Annualizing geometric returns

The power and manipulation of looking up data

Powering the offset with the match function and drop down menus

Combining the Index function with the match function

Strengthening the If function with (Min, Max, Choose, Offset, And, Or functions)

Conditional addition and counting functions

Manipulating date functions in complex examples

When to use DAYS360 in a model

Solving problems with DAYS360

Working with parts of numbers

Working with numbers in a direction

Using the IS-suit (isnumber, istext, isblank, iserror, iserr, islogical, isna, isnontext, isref) with logical formulas

Going through a spreadsheet that contains worked examples of all 40 functions

Understanding net present value

Understanding internal rate of return

Going through a worked example of a project cash flow

Problems with the IRR calculation

Multiple IRR

Shooting yourself in the foot with incorrect assumptions

NPV

XNPV

IRR

MIRR

XIRR

Forecasting guidelines

Modelling the connection between the income statement and the Balance Sheet

Assumptions margin

Two ways to balance the balance sheet

Using plugs to balance the balance sheet

Surplus funds and the necessity to finance

Modelling the provision for taxes

Static vs dynamic analysis

Effect of surplus income from surplus funds

Flows in the Cash Flow Statement (Operations, Investment, Financing)

Structuring a cash sweep

Modelling payment of successive debt tranches

Calculation of the post-sweep debt numbers

Cash flow variation for cash sweep

Reality checks

Adding an error trapping formula

Using conditional formatting

Finding cells that have conditional formats

Structuring the input sheet vs output sheets

Modelling common size statements

Smoothing techniques in forecasting

Simple and multiple regression analysis

Sensitivity analysis in forecasting

Efficiency ratios

Profitability ratios

Leverage ratios

Coverage ratios

Installing the solver add-in

Goal seek

Solver parameters

Decision variables and constraints

Tolerance levels

Solving problems with integer constraints

Using Solver for a working capital management model

Using Solver for a capital budgeting model

Using Solver for an inventory policy model

Using Solver for a cash management model

Using Solver for a capacity planning model

Introduction to Pivot tables

Creating a Pivot Table report

Categorizing raw data

The Pivot Table wizard

Percentage of column, percentage of raw, and percentage of previous

Top and bottom 10 feature

Creating formulas in pivot tables

Retrieving data from external sources including access and internet

Linking Pivot Tables to MySQL

Pivot charts manipulation

Building one variable Data Tables

Building two variables Data Tables

Combine data from different sources in one pivot table

Combine huge amount of data in the most optimal way

Connect to databases

Private calculated members

Calculations library

Limitations

View Pivot Table MDX

Filtering Pivot Table to a list

Changing Pivot Table Defaults

Searching in OLAPS

Distributing Pivot Tables

Best practice

Introduction to Crystal Ball

Introduction to probability distributions and their importance in decision making

Stochastic vs static models

Building a stochastic model

Moving away from “Best, Worst and Normal forecasts” to more dynamic solutions

Monte Carlo simulation versus “what if” scenarios

Incorporating decision rules into Monte Carlo simulations

What is a macro?

Creating a simple macro

Changing multiple properties at once

Assigning a shortcut key to a macro

Looking inside a macro

Objects, properties and methods

Navigating the Visual Basic Auditor

Manipulating recorded properties

Eliminating repeated objects in a recorded macro

The Select…Selection structure

The With Selection structure

Making long statements more readable

Designate a trusted location for macros

Looping

Branching

Automating spreadsheets

Retriveing data from non-Excel sources

Recording a macro that runs other macros

Task one: automating transfer of data from a non-Excel source

Task two: automating data filling

Task three: automating column addition

Task four: automating data base update

Task five: recording a macro that runs other macros

Handling pop up messages automatically

Simplifying the subroutine statements

Finding your ways in VBA even if you are not a programmer

Creating loops

For Each Loop

For Loop

Do Loop

Managing large loops

Set a breakpoint

Set a temporary breakpoint

Show progress in a loop

Using a custom function from a worksheet

Introduction to Project Explorer

Adding arguments to a custom function

Making a function volatile

Making arguments optional

Using a custom function from a macro

Developing and storing a new add-in

Making use of the hundreds of freely available custom functions in the Internet

Coding in VBA

Best practice order structure

Analyzing the problem and translating it into a macro

Explaining the parameters of the table

Setting the required calculations

Designing the code

Testing the code

Analyzing potential errors

Running the macro in different settings

Creating a custom function for the loan amortization table