A Comprehensive Excel Masterclass 

Course Overview Video

Module 1 Introduction

Introduction to Time Value of Money and Cash Flow Diagrams

Cash Flow Diagrams in Excel

Types of Interest: Simple vs. Compound

Nominal and Effective Interest Rates

Terminology: Interest Vs. Discount Rates

The Power of Compounding

Basic Excel Financial functions I (FV, PV,)

Basic Excel Financial functions II (RATE, NPER)

Examples using APR, EAR, FV and PV Functions

Net Present Value (NPV, XNPV) Function

Annuity and Perpetuities

Calculating Loan Payments (PMT)

Understanding Amortization or Repayment Schedules

Creating an Amortization in Excel: Applying IPMT, PPMT Functions

Calculating Loan Balances with CUMPRINC Function

Calculating Total Interest and Principal Payments with CUMIPMT and CUMPRINC Functions

Impact of Paying Extra each Period on Loan Repayment Horizon (NPER)

Impact of Adjustable Rate Mortgages on Credit Card Loans

Introduction to Capital Budgeting

Evaluating One-Time Projects using Net Present Analysis

Capital Budgeting for Ongoing Projects – Using a specified Time Horizon

Capital Budgeting for Ongoing Projects – Equivalent Annual Worth

Internal Rate of Return Analysis (IRR, XIRR)

Incremental Cash Flow Analysis and MARR

Syllabus

Module 1 Lesson 1 Reading

Module 1 Lesson 1 Video Instructions Pt.1

Module 1 Lesson 1 Video Instructions Pt.2

Time Value of Money Exercise Completed Workbook

Basic Financial Functions Exercise Completed Workbook

Module 1 Lesson 2 Reading

Module 1 Lesson 2 Video Instructions Pt.1

Module 1 Lesson 2 Video Instructions Pt.2

Loan Analysis Exercise Completed Workbook

Mortgage Loan & Refinancing Exercise Completed Workbook

Module 1 Lesson 3 Reading

Module 1 Lesson 3 Video Instructions Pt.1

Module 1 Lesson 3 Video Instructions Pt.2

Capital Budgeting Exercise Completed Workbook

Project Evaluation and Selection Quiz - Solution Worksheet

Module 1 Summative Assessment Answers

Module 1 Summary

Time Value of Money Exercise

Basic Financial Functions Exercise

Time Value of Money Quiz

Loan Analysis Exercise

Mortgage Loan & Refinancing Exercise

Excel Functions Quiz (Excel Needed)

Excel Functions Quiz (No Excel Needed)

Capital Budgeting Exercise

Project Evaluation and Selection Quiz

Module 1 Summative Assessment

Meet and Greet Discussion

Module 2 Introduction

Overview of Pivot Tables

Guidelines for Inserting Pivot Tables and Pivot Table Structure

Understanding Variable Types and Pivot Table Structure

Filtering, Organizing and Sorting Pivot Tables

Grouping Dates and More Filtering of Pivot Tables

Pivot Tables Contextual Tabs: The Design and Analyze Tabs

The “Show Value As” Display

Creating Calculated Fields

The Power of “Show Value As” Displays to Analyze Data

Pivot Table Options, Field Headers and Buttons

Using Slicers and Timelines in Pivot Tables

Creating Pivot Charts

Pivot Tables with different levels of granularity

Pivot Tables in Google Sheets: Functional Differences (Interface, Slicers, Grouping Dates)

Chart Interface in Google Sheets

Introduction to XLOOKUP – Powerful Replacement to VLOOKUP and HLOOKUP

XLOOKUP’s Match Mode

XLOOKUP’s Wildcard Mode

Nested XLOOKUPs Overview

Dropdown lists plus XLOOKUPs vs. VLOOKUP and MATCH

UNIQUE and SORT Functions, along with Cell Referencing in Google Sheets

The FILTER Function and Dependent Dropdowns

The FILTER Function’s Power

Module 2 Lesson 1 Reading

Module 2 Lesson 1 Video Instructions Pt.1

Module 2 Lesson 1 Video Instructions Pt.2

Pivot Table Exercise Completed Workbook

Module 2 Lesson 2 Reading

Module 2 Lesson 2 Video Instructions Pt.1

Module 2 Lesson 2 Video Instructions Pt.2

Pivot Tables II Exercise Completed Workbook

Module 2 Lesson 3 Readings

Module 2 Lesson 3 Video Instructions Pt.1

Module 2 Lesson 3 Video Instructions Pt.2

Basic Google Sheets Usage Exercise Completed Worksheet

XLOOKUP Exercise Completed Worksheet

Pivot Tables Exercise III Completed Worksheet

Pivot Tables Exercise IV Completed Worksheet

Module 2 Summative Assessment Answers

Module 2 Summary

Pivot Table Exercise

Pivot Tables Quiz

Pivot Tables II Exercise

Basic Google Sheets Usage Exercise

XLOOKUP Exercise

Pivot Tables Exercise III

Pivot Tables Exercise IV

Module 2 Summative Assessment

Module 3 Introduction

Introduction to Mastering Excel Dashboards

Dashboard Design Checklist

Dashboard Design Basics – Getting Started

Dashboard Workbook Structure

Useful Shortcut Keys

Dashboard Design and Creation Tips

Static Dashboard Calculations I

Static Dashboard Calculations II – GETPIVOTDATA Function

Constructing Static Dashboard – Using Textboxes for Flexibility

Design Elements of Dashboards

Static Dashboard Formatting I -- Size, Fonts and Colors

Static Dashboard Formatting II – Containers and Alignment

Static Dashboard Formatting III – Object Properties & Final Touches

MATCH Function Tutorial

Using Slicers and Timelines in Pivot Tables

INDEX Function – Efficient Lookup Function

LARGE and SMALL – For sorting values

CHOOSE – An alternative to IF Statements

SUMIFS – A Review with Twists

Introduction to the Developer Tab and Form Controls

Combo Box – More Attractive Dropdown List

Check Box – Check/Uncheck Option

List Box – A Different Way to Selection from a List

Spin Button – Increase or Decrease a Value/Position in a list

Option Button – Choose Between Options

Scroll Bar – Another way to move through contents

Calculations -- Form Controls and Slicer Cell Links

Calculations -- Overall Metric Calculations

Calculations -- Monthly Charts and Dynamic Titles

Calculations -- Current vs. Previous Year Deltas

Calculations – Top 3 and Bottom 3 Products by Profitability

Construction and Design -- Shapes and Formatting

Construction and Design – Charts Edits and Shape Properties

Construction and Design – Linked Pictures

Final Touches for Dynamic Dashboard

Module 3 Lesson 1 Reading

Module 3 Lesson 1 Video Instructions Pt.1

Module 3 Lesson 1 Video Instructions Pt.2

Module 3 Lesson 2 Reading

Module 3 Lesson 2 Video Instructions Pt.1

Module 3 Lesson 2 Video Instructions Pt.2

Data Retrieval and Reporting Exercise Completed

Module 3 Lesson 3 Reading

Module 3 Lesson 3 - Section 1 Video Instructions Pt.1

Module 3 Lesson 3 - Section 1 Video Instructions Pt.2

Module 3 Lesson 3 - Section 2 Video Instructions Pt.1

Module 3 Lesson 3 - Section 2 Video Instructions Pt.2

Module 3 Lesson 3 - Section 3 Video Instructions Pt.1

Module 3 Lesson 3 - Section 3 Video Instructions Pt.2

Module 3 Lesson 3 - Section 4 Video Instructions Pt.1

Module 3 Lesson 3 - Section 4 Video Instructions Pt.2

Module 3 Summative Assessment Answers

Module 3 Summary

Dashboard Design Quiz

Data Retrieval and Reporting Exercise

Retrieval Functions Quiz

Form Controls Quiz

Module 3 Summative Assessment