Course curriculum

  • 1

    Introduction

    • Course introduction

    • Learning objectives

    • Download Excel files and course notes

  • 2

    Excel Template Setup

    • Template setup learning objectives

    • Dynamic dates and time periods with IF statements

    • Dynamic totals and averages with the OFFSET function

    • IF statement and OFFSET function exercise

    • Scenarios with the VLOOKUP and CHOOSE functions

    • Dynamic outputs with IF statements

    • Template formatting

    • Interactive exercise 1

  • 3

    Financial Statements

    • Financial statements learning objectives

    • Debt schedule with PMT, IPMT, and IF functions

    • XNPV and XIRR with DATE and IF functions

    • Adding OFFSET to XNPV and XIRR

    • Formatting with sections and grouping

    • PMT, IPMT, XNPV and XIRR exercise

  • 4

    Financial Analysis

    • Financial analysis learning objectives

    • Summary data with VLOOKUP

    • INDEX function

    • MATCH function

    • INDEX MATCH MATCH

    • Goal seek (what-if analysis)

    • Dynamic totals with INDIRECT and SUM functions

    • Interactive exercise 2

    • CELL function

    • COUNT and COUNTA functions

    • Creating table of contents with CELL function

    • RIGHT, CELL, LEN, and FIND functions

    • Combining RIGHT, CELL, LEN, and FIND

    • Combining IF with AND, OR formulas

    • Interactive exercise 3

  • 5

    Tables

    • Tables in Excel learning objectives

    • Download pivot table files

    • Setting up a data table

    • Linking and formatting the data table

    • Setting up a pivot table

    • Pivot table field settings

    • Pivot table filters

    • Pivot table calculated fields

    • Pivot table slicer and timeline

    • Interactive exercise 4

  • 6

    Charts

    • Charts in Excel learning objectives

    • Combination column and line charts

    • Stacked column chart

    • IFERROR function

    • Dynamic charts

    • Dynamic charts continued

    • Learn more about charts

    • Additional Resource: Waterfall chart template download

    • Interactive exercise 5

  • 7

    Macros

    • Macros learning objectives

    • Recording macros

    • Form control

    • Interactive exercise 6

  • 8

    Final Product

    • Review of the final product

    • Download the final product

    • Summary

  • 9

    Qualified Assessment

    • Qualified assessment

    • Share Your Feedback

    • Course Completion