OfficeTrainingHub

Getting Ready for Virtual Training with dbrownconsulting

How to use this Platform

Introducing the Advanced Financial Modeler Designation

Your Expectations

Model Documentation

Building Dynamic Labels

Sheet Structure

Replicating a Standard WorkSheet

Inserting HyperLinks

Building a Financial Model Template Chapter Quiz

Overview

Overview of Excel Styles

List of Styles used in Models

The Normal Style

The Input Style

The Multiples Style

The Percentage Style

The Historicals Style

The FLAG & MASK Styles

Conditional Format for FLAG & MASK Style

The Linked Cell Style

The Unit Styles

The Total Column Style

The Blank Style

The Title Style

The Subtitle style

The Header Style

The Banner Style

The Currency Style

Implementing Styles

Update the Model Template with Styles

How to Transfer Styles between Workbooks

Style Guide Clean-up

Overview

Working with Numbers and Formats

Rounding time in Excel (Part 1 of 2)

Rounding time in Excel (Part 2 of 2)

Working with Text, Dates & Time Chapter Quiz

Download the Exercise Files

Introduction

Using ROUND, ROUNDUP & ROUNDDOWN Functions

Working with Basic Rounding Functions

Using MROUND, CEILING & FLOOR Functions

Rounding Challenge

Using INT & TRUNC Functions

Using EVEN & ODD functions

Understanding Banker's Rounding

Building a Banker's Rounding User Defined Function

Creating a Banker's Rounding Formula

Recap of Rounding Functions and Techniques

Rounding Functions Chapter Quiz

Working with Cell Referencing

Download the Exercise Files

Using VLOOKUP

Using MATCH

Using VLOOKUP & MATCH

Using HLOOKUP

Using HLOOKUP & MATCH

Using INDEX

Using INDEX & MATCH

Using OFFSET & MATCH

Using CHOOSE

VLOOKUP Hidden Feature part 1 of 2

VLOOKUP Hidden Feature part 2 of 2

Mastering the OFFSET Function (Part 1 of 2)

Mastering the OFFSET Function (Part 2 of 2)

Lookup Functions Chapter Quiz

Download Exercise Files

Using the FLAG Modeling Tool

Using the MASK Modeling Tool

Using the AND function

Using the OR function

Comparing the AND, OR & XOR functions

Using the XOR function

Using the NOT function

Using the IF function

Converting the Boolean Logic into an IF Function Calculation

IFS Function

SWITCH function

Download the Exercise Files

Preparing the Template

Real vs Nominal Terms

Building an Inflation Index

Building a Retirement Model Template (Part 1 of 2)

Building a Retirement Model Template (Part 2 of 2)

Using Financial Functions in Excel for TVM Calculations

Calculating Investment Value at Retirement

Identifying Errors in TVM Calculations

Time Value of Money Chapter Quiz

Overview

Download the Exercise Files

Answering the Retirement Problem with Scenario Analysis

Goal Seek & Scenario Analysis

Setting up the Scenario Manager Tool

Recording Scenarios with the Scenario Manager Tool

Running Reports with the Scenario Manager tool

Scenario Manager Chapter Quiz

Introduction to the Data Validation Tool

Introduction to the Data Validation Tool

Setting the Stage for Data Validation

Carry out a Data Validation

Best Practice for Creating List Validations

Data Validation Settings, Input Message and Error Alert Options

Using Data Validation for Scenario Management

Create Scenarios with Data Validation Setup 1 of 2

Create Scenarios with Data Validation Setup 2 of 2

How to prepare Scenario Data for LOOKUP with the INDEX Function

How to use the INDEX Function to activate Scenarios in a Model

Introduction to the Data Validation Tool

Download Exercise Files

Download the Exercise Files

The Big Flaw with Data Validations

Activating Form Controls in Excel

Using Form Controls to automate Scenarios in a Model

Using INDEX to Populate Scenario Data from Form Controls

Managing Scenarios with Form Control Chapter Quiz

Download Exercise Files

Overview of Sensitivity Analysis - The Data Table Tool

Building a One Input Sensitivity Table - The Setup

Building a One Input Sensitivity Table - Implement

Building a Two Input Sensitivity Table - Setup the Table

Building a Two Input Sensitivity Table - Implementation

How to make your Sensitivity Tables Dynamic

Best Practice Formatting for Sensitivity Tables

Download Exercise Files

Sensitivity Analysis with Data Validations - The Setup

Sensitivity Analysis on with Data Validations - Implementation

Sensitivity Analysis with Form Controls - Introduction

Sensitivity Analysis with Form Controls - The Setup

Sensitivity Analysis with Form Controls - Implementation

Download the Exercise Files

Importance of timing in a model

Excel Tools used to manage timing in a model

Logical operators in a model

How to implement logical operations in a model

Logical functions in excel

MOD function

Tools needed for excellent timing in a model

Identifying a single timing period

Types of time periodicities in a model

Managing time periodicities in a model

Download the Exercise Files

Quarter identifier

Start Mask

End Mask

Combining start and End Mask

Equity contribution

Download the Exercise Files

Amortizing loan as an example

PMT Function

Principal repayment and interest payment

Counter

Loan period mask

Determining Loan ending period

Using -AND- Function

Using flag to determine loan inflow

Using mask to determine Repayment

Download the Exercise Files

Identifying a cascading timing period

Waterfall depreciation method

Automating Depreciation schedule

Using And function to Automate depreciation

Download the Exercise Files

Building a Capex Schedule

Building a Depreciation Schedule

Using a Matrix Formula to build a Depreciation Schedule (Part 1 of 2)

Using a Matrix Formula to build a Depreciation Schedule (Part 2 of 2)

Using the OFFSET Function to Calculate Depreciation (Part 1 of 3)

Using the OFFSET Function to Calculate Depreciation (Part 2 of 3)

Using the OFFSET Function to Calculate Depreciation (Part 3 of 3)

Welcome

AFM Course Brochure

Exercise Files Download

Session_01 Details

Download Exercise File

FAST Standard

Download Exercise Files

Introducing Modeling Mechanics

Understanding Growth Formulas

Using Growth Formulas

Understanding Account Drivers

Using Account Drivers

Smart Techniques and Shortcuts

Building Working Capital Assumptions

Building Working Capital Schedule

Welcome to Session_02

Using of Timings in Your Model

Download the Exercise Files

Revenue & Profit Determination

Cost Classification Methodologies

Hendersen Manufacturing - Sample Case Study from AFM Exam

Building the Revenue Schedule

Building the Cost Schedule

Welcome to Session_03

Exercise Files

The Financial Statement

Defining Accounting and Finance

The Business & Accounting Cycle

Introducing the REAL Framework for Accounting

The Structure of the Balance Sheet

The 5 Box Balance Sheet Layout

The Structure of the Income Statement

Welcome to Session_04

Exercise Files

Welcome to Session_05

Download Session Files

The 3s Modeling Framework

Welcome to Session

Download Session Files

Welcome to Session_07

Download Session Files

Welcome to Session_08

Download Session Files

Welcome to Session_09

Download Session Files

Credit Enhancements

What is a Cash Flow Waterfall

5 Steps to implement a cashflow waterfall with cash sweeps

Download Exercise file

Setting up your sheet structure

Inputting Assumptions

Building the Debt Schedules

Modeling the Cash sweep

Modelling the Default

Calculating DSCR and PLCR

Identifying Key Metric and charts

Linking your critical scenario inputs

Building Scenario Controls

Download Exercise file

Calculating Debt Service using DSCR

Building out Principal Repayment Schedule

Debt Sculpting to get the Maximum Debt Capacity

Understanding other Constraints in Debt Sculpting

Welcome to Session_10

Session Files

AFM Exam Structure (from FMI)

AFM Exam Planner Tool by dbrownconsulting

The AFM Exam Experience

Planning for the Exams

AFM Body of Knowledge (from FMI)

Skills Required for the AFM Exams

AFM Skills Checklist (From FMI)

Exam Study time required

AFM Study Guide (From FMI)

Sample Exams & Models (From FMI)

The 3s Modeling Framework

50 Excel Keyboard Shortcuts for Windows

50 Excel Keyboard Shortcuts for Mac

Positive vs Negative Numbers in Models

Building the Cost of Sales Schedule

Completing Tax Calculations

Modeling the Cash Sweep

Handling Cash Sweeps in a Model

Instructions

Evaluation & Digital Certificate