MS Excel for Finance Professionals

Training Goals & Objectives

Excel is so intuitive that professionals can start using it with little or no training, but its real value come when one take a professional training on MS Excel for Finance Professionals.

Training facilitates the participants to learn the latest trends and the right ways for solving their day to day problems fast and with accuracy. This course is designed for the users having intermediate level skill in using Excel, and who want to enhance their skills to the next level, so they can get best output of their time.

Apart from quick tips & tricks, advanced formulas & functions, macros and using Excel for creating maintaining Financial Workbooks, this course further impart skill and knowledge to the participants on creating slick dashboards with accurate data representation using Excel.

Learning outcomes

This training will enable participants to understand the best in class, ways of using Microsoft Excel. Following are the key learning outcomes of this session:

  1. Learn Core Excel Functions & Features
  2. PivotTables & Slicer
  3. Charts & Sparkline
  4. Creating Dashboards
  5. Understand & Create Macros

Training Program

Course contents of the program are as under: 

Pre Session Activities

Networking & Participant Introduction

Course introductions & Overview

Microsoft Excel Primer

  • Excel Structure
  • Understanding Excel Capabilities as a Financial Tool.
  • 360⁰ Tour of Excel’s Intermediate + Features
  • Dealing with Excel Errors, related to Financial
  • Calculations
  • Auditing a worksheet.
  • Verification of calculations

Example: Pre-created “Financial Projection Model” will be used

Learn 13 Core Functions

  • Mathematical Functions (SUM, MAX, MIN, COUNT, COUNTA, AVERAGE)
  • Understanding Logical Functions (IF)
  • Lookups with (VLOOKUP)
  • Preventing Errors (IFERROR)
  • Conditional Calculations using (SUMIFS, COUNTIFS)
  • Versatile Aggregate Function (SUBTOTAL)
  • Array Function intro (SUMPRODUCT)

Example: Budget Variance Report

Basic Financial Worksheet’s Arithmetic

  • Simple & Compound Interest
  • Nominal vs. Effective Interest Rates
  • Basic Financial Conversions
  • Time Value of Money
  • Understanding Time Value of Money
  • Working with PV, FV
  • Using NPV (Net Present Value) Function
  • Using IRR (Internal rate of return) Function
  • Further Cash Flow Function (XNPV, XIRR)
  • Using MIRR (Modified Internal Rate of Return)

Example: Asset Valuation using PV, NPV, IRR

Amortization Table

  • PMT, RATE, NPER, IPMT, PPMT
  • Understanding Basic Amortization Calculations
  • Creating Interactive Amortization Table
  • Using Forms Controls

Example: Amortization Table

Lookup & Reference Functions Revisited

  • A step ahead with VLOOKUP; Simple VLOOKUP,
  • Multi-worksheet, Multi-list, Nested VLOOKUP
  • Understanding HLOOKUP
  • MATCH / INDEX as a universal replacement to
  • VLOOKUP/HLOOKUP
  • Significance of Financial Functions
  • Example: HR Department Data

Formatting Sheets

  • Cell formatting Insights
  • Effective use of conditional formatting
  • Using Shapes / Symbols in your Worksheets

Example: Daily Transactional Data

Analyzing Financial Statements an Excel Perspective

  • Common Size FS Analysis
  • Horizontal Analysis
  • Vertical Analysis
  • Ratio Analysis in Excel
  • Comparing Financial Statements
  • BEP Using Goal Seek
  • Evaluating your Data using Basic Statistical Functions.

Example: Analyzing two Software Giants using Common Sized Statements

Working with Multiple Worksheet/ WorkBooks

  • Working with Linked Workbooks
  • Saving Linked Workbooks
  • Source and Destination Workbook Concept
  • Updating, Breaking, Modifying Links
  • Consolidations
  • Inserting Hyperlinks
  • Consolidating Workbooks

Example: Consolidations Departmental 3 Core Financial Statement.

Working with Lists & Tables

  • Understanding Excel Lists
  • Sorting Data
  • Subtotaling and Outlining Data
  • Analyzing & Extracting Data with Filters
  • Validating Data Entries
  • Using Advanced Filters
  • Using Query
  • Understanding Excel Tables
  • Table In-Built Features: Formatting, Filter, Formula, Totals
  • Using Tables with Slicers

Example: Working with Chart of Accounts

Analyzing Data using Pivot Tables

  • Creating & Managing PivotTables
  • Using Slicers with PivotTables
  • Creating PivotChart Reports
  • Formatting PivotTable
  • Sorting / Grouping PivotTables

Dynamic Sensitivity Analysis using Excel’s“ What if ?” utilities

  • Using Simple Formulas
  • Using Data Table – Single Variable
  • Using Data Table – Single Variable/Multi column
  • Using Data Table – Two Variable
  • Scenario Manager

Case Study: Budget & Forecast of Sales

Using Graphics to Support Your Data

  • Basic Chart Types & their usage.
  • Creating Line Chart
  • Column / Bar Chart
  • Using Multiple Chart Types & Multiple Y axis
  • Pie Chart
  • X / Y or Scatter Chart
  • Common requirements when using charts
  • Date Functions
  • Form Controls Revisited

Example: Working with Chart of Accounts 

Printing, Protecting & Sharing Workbooks

  • 5 Common Printing Problems & their solutions
  • Best Practice when you Share your Workbooks
  • Actions you should take when you save
  • File Protection
  • Workbook Protection
  • Worksheet Protection
  • Sharing and Track Changes

Example: Quarterly Report

Using Macros

  • What is Macro?
  • Recording / Running / Editing a Macro
  • Creating Subroutines and Functions

Example: Dynamic Pivot Tables

Using Slicers

  • Using Slicer with Simple Tables
  • User Slicer Pivot Table Filter
  • Create a Slicer based on an existing Pivot Table
  • Understanding different options of slicer
  • Different Formatting Styles of Slicer
  • Connect Slicer with multiple Pivots

Example: Regional Sales Data

Using Sparklines and tiny charts

  • Introduction to Tiny Charts & Their Reporting Power
  • Creating Sparklines
  • Understanding Types of Sparklines: Line, Column & Win / Loss
  • Formatting Sparklines for Maximum Impact

Example: Monthly Sales Data

Introduction to Dashboard Reports

  • Business Reporting where Dashboard fits in it
  • Understanding Different Types of Dashboard Reports
  • Typical Business Dashboard Components

Example: Regional Sales Data Dashboard

Certificate Distribution Ceremony