OfficeTrainingHub

Introducing the Advanced Financial Modeler Designation

How to use this Platform

Before we begin

50 Excel Keyboard Shortcuts for Windows

50 Excel Keyboard Shortcuts for Mac

Ask the Trainer

Overview

Download the Exercise File

Working with Numbers and Formats

Rounding time in Excel (Part 1 of 2)

Rounding time in Excel (Part 2 of 2)

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

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

Working with Date and Time in Excel

Rounding Time in Excel (1 of 2)

Rounding Time in Excel (2 of 2)

Recap of Rounding Functions and Techniques

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)

Chapter Quiz

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

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

Chapter Quiz

Download Exercise Files

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

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

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 Exercise Files

Using the FLAG Modeling Tool

Using the MASK Modeling Tool

Using the AND function

Using the OR function

Comparing the AND, OR and XOR functions

Using the XOR function

Using the NOT Function

Using the IF Function

Using the IFS Function

Boolean Logic and the IF Function

Using the SWITCH Function

Chapter Quiz

Evaluation & Digital Certificate