Course description

Learn to Harness the Value of Data

This is a highly intensive course covering the fundamentals of Microsoft Excel required by every Analyst. These skills are central to becoming more productive completing typical day to day task required for your work. This course also gives you the Excel skills required for more advanced analysis and reporting tasks such as financial modeling and reporting and analytics with power pivot.

Course Curriculum

Here's what you'll learn...

  • 1

    Welcome to the Course!

    • Course Overview

      FREE PREVIEW
    • 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

  • 2

    1. Working with Text, Dates & Time

  • 3

    2. Using Formatting Styles in your Model

  • 4

    3. Working with Math & Rounding Functions in Excel

    • Outline

      FREE PREVIEW
    • Download the Exercise Files

    • Overview

      FREE PREVIEW
    • Introduction

    • Using ROUND, ROUNDUP & ROUNDDOWN Functions

    • Working with Basic Rounding Functions

    • Using MROUND, CEILING & FLOOR Functions

    • Solving complex problems with Custom Rounding

      FREE PREVIEW
    • 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

  • 5

    4. Mastering Lookup Functions in Excel

    • Overview

      FREE PREVIEW
    • Download the Exercise Files

    • Introduction

      FREE PREVIEW
    • Using VLOOKUP

    • Using MATCH

    • Using VLOOKUP & MATCH

    • Using HLOOKUP

    • Using HLOOKUP & MATCH

    • Using INDEX

    • Using INDEX & MATCH

    • Using OFFSET

      FREE PREVIEW
    • 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

  • 6

    5. Time Value of Money Calculations

    • Overview

      FREE PREVIEW
    • Download the Exercise Files

    • Preparing the Template

    • Real vs Nominal Terms

    • Building an Inflation Index

    • Introducing TVM Calculations

      FREE PREVIEW
    • 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

  • 7

    6. Using Excel's Scenario Manager Tools

    • Overview

    • Download the Exercise Files

    • Case Study: Overview of the Retirement Problem

      FREE PREVIEW
    • 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

  • 8

    7. Managing Scenarios with the Data Validation Tool

    • 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

    • Understanding the INDEX Function

      FREE PREVIEW
    • How to prepare Scenario Data for LOOKUP with the INDEX Function

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

  • 9

    8. Managing Scenarios with Form Controls

    • Download the Exercise Files

    • The Big Flaw with Data Validations

    • Activating Form Controls in Excel

    • Using Form Controls to automate Scenarios in a Model

    • Form Control Best practices

      FREE PREVIEW
    • Using INDEX to Populate Scenario Data from Form Controls

    • Chapter Quiz

  • 10

    9. Introduction to Sensitivity Analysis

    • 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 - Understanding the Logic

      FREE PREVIEW
    • Building a Two Input Sensitivity Table - Implementation

    • How to make your Sensitivity Tables Dynamic

    • Best Practice Formatting for Sensitivity Tables

  • 11

    10. Sensitivity Analysis with Data Validation & Form Controls

    • 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

  • 12

    11. Mastering logical calculations in Excel

    • 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

  • 13

    Evaluation & Course Certificate

    • Evaluation & Digital Certificate

Learning Outcomes

Microsoft Excel Fundamentals for Analysts is a highly intensive certificate course, it is structured as a blended course: online pre-work, face-to-face classroom, a mock exam and a preparation guide for the AFM Exams for those registered to take it.

  • Identify and apply the essential Excel functions, tools and operators every modeller should know.

  • Design a modern model structure based on pre-determined model outputs & requirements

  • Apply best practices in capital structure modelling; Debt schedules, Cash Flow Sweeps, Equity and dividend calculations.

Featured Testimonial

“It was an interesting course loaded with revealing new techniques/short cuts to performing works on excel. The trainers were on point, delivering with deep knowledge of the excel package. ”

Chiejina Chike

“I was really impressed with the simplicity of the lessons and the ease of learning. Looking forward to taking more courses.”

Abdulwadud Mohammed

“He is very good and attentive, delivery mechanism is very effective”

Tolu Bamidele

“The facilitator is very well equipped to train, and he demonstrated the ability by taking time to fully explain the subject matter very well”

Oluwakemi Dada

“He passed the message across clearly and concisely, had so much interest in his participants”

Ibikunle Olatunji

“i especially enjoyed the unconventional yet the vibrant way of teaching and how the message was passed across ”

esther ibuonoh

Other courses for you

  • $99.00

    $99.00Report Automation in Modern Excel

    Buy Now
  • $99.00

    $99.00Reporting Automation with PivotTables and Charts in Excel

    Buy Now
  • $199.00

    $199.00Advanced Financial Modeler Exam - Step by Step Guide

    Buy Now

Some of our Clients

Call to action

Get started now

What People Have to Say...

“The course is very explanatory Will Help Entrepreneurs Check Pit Falls And Cut Costs. ”

Graham Odiakosa

“It’S A "Value For Money"Programme. I Think Am Most Bless In The Class Because I Got More Than I Bargained For.”

Dolapo Oni

“The Course Content Have Good Illustration Of Practical Exercises Which Are Useful For Reporting ”

Adamu Musa

“The Course Content Met My Expectation To Be More Knowledgeable In Reporting And Forecasting/Budgeting ”

Chioma Okoroafor

Instructor

David Brown

FCA, Microsoft MVP

David has worked with and taught Excel and financial modelling to over 4,000 people in the last 22 years. He is the Managing Partner of dbrownconsulting where he runs the Consulting Division.He is a Fellow of the Institute of Chartered Accountant of Nigeria & a Financial Analyst with over 16 years experience working as a Business intelligence, Financial Modelling & Tax Consultant with Arthur Andersen, KPMG & dbrownconsulting.He is also an International Consultant to the World Bank on Oil Revenue Modelling and is a registered financial adviser.David attained the Master Trainer & Master Instructional Designer designation from the Association for Talent Development (ATD).He is passionate about Financial Modelling, Business Intelligence and Analytics especially with Microsoft Power BI and Excel and regularly host meetups and speaker event in Nigeria and around the world.