MASTER FINANCIAL MODELING SPECIALIST(MFMS)

COURSE DESCRIPTION

Financial Modeling is one of the most fundamental and widely sought after skills in the finance industry. It is building models on MS Excel that help analyze parameters and arrive at a business solution. The program will introduce you to spreadsheet modeling, various modeling techniques and the applications in various domains for finance. After completing the program, you will be able to build robust and dynamic financial models while mastering Excel for corporate finance, risk management, portfolio construction, fixed income and derivatives.


CERTIFICATION AWARDED

On successful completion of the program, you would be awarded the foundational certification in “MASTER FINANCIAL MODELING SPECIALIST (MFMS)” by AAFM and EduEdgePro.


COURSE COLLATERALS AND HIGHLIGHTS

 Fast Facts

 Certified by :
 American Academy of Financial Management and  EduEdgePro

 Broad Coverage:
  • Foundations of excel
  • Excel Formulae
  • Data Analysis, Visualization and Security Tools
  • Programming using Macros
  • Financial Statement Modeling
  • Application of financial modeling in Banking, Derivatives and Fixed Income

Mode of Delivery

Price

Duration
Offline Training
(Classroom)

Rs. 35,000 plus
taxes

80 Hours


E-Learning


Rs. 10,000 plus
taxes

40 Hours


Customized In-house
Group Training
Contact us


Customized


  • Pre-requisite material on concepts of Derivatives, Fixed Income, Risk Management & Portfolio Management
  • Black Scholes Calculator for derivatives on Excel
  • Excel Dashboards for ratio analysis
  • Excel Models on Valuation Techniques, portfolio management
  • Excel Essential Shortcuts List
  • Working with market data to calculate Value-at-Risk in Excel
  • Excel models to calculate Portfolio Value-at-Risk
  • Monte Carlo simulations using Excel-VBA and applications to VaR
  • Hedging Frameworks
  • Exhaustive reading material covering the sections outlined below
  • Course delivery by domain experts


COURSE OBJECTIVES

  • Getting familiar with excel interface and shortcuts
  • Understanding excel formula and their applicability
  • Using advanced functions for data aggregation
  • Understanding excel best practices for data representation
  • Using charts and graphs for visual impact
  • Learn various security measures for protecting a workbook
  • Learn and implement macros to automate functions and reduce manual work
  • Value and analyse firms and their financial statements in Financial Modelling
  • Learn how to build VaR models in excel
  • Build portfolio construction model in in excel
  • Build your own financial models
  • Build your own dashboards


WHO SHOULD ATTEND

This course provides a deep insight in Financial Modeling. The course is a great foundation for people who want to make a career in:

  • Investment Banking
  • Equity Research
  • Credit Modelling
  • Financial Analysis
  • Business Analysis
  • Credit Rating
  • Commercial Banking
  • Risk Management
  • Corporate Treasury
  • Financial Institutions
  • Risk Consulting
  • Global Markets and Risk
  • Model Validation
  • Quantitative Research
  • M&A Professionals
  • Fund Managers


DETAILED CURRICULUM

Section 1 - Foundations of Excel

This section was designed to introduce to the participant the basics of excel. This section will familiarize the student about the Excel application and its interface. It will introduce various elements of an excel application such as menu ribbons, address and formula bar and how to customize the quick access toolbar. You will further learn various important and necessary shortcuts that are frequently used in an excel application that will increase your productivity multifold. Shortcuts for rows and columns (insert, delete, hide), workbooks and worksheets (adding, deleting, renaming, hiding and unhiding), and other miscellaneous shortcuts would be covered in great detail.


Excel Interface Elements

  • Quick Access Toolbar
  • Menu Ribbons
  • Address Bar
  • Formula Bar
  • Status Bar and Worksheet Zoom
  • Worksheets
  • Fill Handle

Essential Productivity Shortcuts

  • Working with Cells
  • Working with Rows and Columns
  • Other Shortcuts: Accessing Menu Elements, Referencing Shortcut, -Formatting, Inserting Current Time and Date, Save As
  • Working with Worksheets
  • Copy and Pasting

Data Input Techniques

  • Flash Fill
  • Using Fill Handle
  • Absolute and Relative Referencing
  • Working with Named Ranges
  • Inserting Comments

Section 2 - Excel Formulae

The objective of this section is to understand and implement various built-in formulae repository in excel. The participant will learn how to use mathematical and statistical formulae, text functions, date and time functions, financial functions and others. By the end of this section you will gain expertise in learning and using various lookup functions like Vlookup, Hlookup Multi conditional lookups, indirect function and many more.


Built-in formulae repository

  • Mathematical and Statistical Formulae
  • Text Functions
  • Date and Time Functions
  • Conditional Functions
  • Error Handling Functions
  • Financial Functions

Lookups and Searches

  • Working with Lookup, HLookup, VLookup
  • Use of MATCH, INDEX, OFFSET
  • Using Lookups, MATCH and INDEX together

Advanced Lookups and Searches

  • Wildcard Lookups
  • Multi conditional lookups
  • Multi-dimensional lookups
  • Reverse Lookups
  • INDIRECT Function

Section 3 - Data Analysis, Visualization and Security Tools

This section is aimed at understanding data analysis, visualization & security tools. The participants will learn building tables and analysing data. Table functions like data slicers, accessing table elements etc. are taught that will help you analyse data better. You will also learn how create pivot tables and charts. Uses and applications of functions like goal seek and solvers are explained. In this section the participants will also learn various techniques to make the data visually appealing and easier to analyse like conditional formatting, cell highlights, charts and graphs, chart formatting and many more. This section also introduces methods to protect your workbooks by using passwords, hiding formulae and making your workbook secure.


Tables and Pivot tables

  • Data Analysis using Tables
  • Accessing Table Elements
  • Data Slicers
  • Creating Pivot Tables
  • Pivot Charts
  • GETPIVOTDATA Function

What-if analysis techniques

  • Goal Seek
  • Solver
  • Scenario Manager
  • Data Tables

Data visualization

  • Conditional Formatting
  • Formulas and Cell-Highlights
  • Conditional Formatting for In-cell Charting
  • Sparklines
  • Charts and Graphs Terminology
  • Line, Scatter, and Bar Charts
  • Radar and Surface Plots
  • Waterfall, Pie, and Sunburst Charts
  • The Box and Whisper Plot and Histograms (Statistical)
  • Chart Formatting in Detail
  • Dynamic Charting and Selections
  • Heatmapping, Indexing
  • Mixing Chart Types

Security and Protection Essentials

  • Protecting Workbooks and Sheets
  • Using Passwords
  • Hiding Formulas
  • Share Workbooks

Section 4 - Programming using Macros

The objective of this section is to explain programming using macros. This program explains how macros can be used to automate tasks in Excel and how you can create simple macros of your own. You will learn how to record a macro, share macros between workbooks, automate routine work and much more. The course wraps up with a macro project that brings together each of the elements explained.


Visual Basic Editor Interface

  • Project Explorer Window
  • Properties Window
  • Coding Window
  • Toolbars

Objects in VBA

  • Understanding Objects in VBA
  • MsgBox and InputBox
  • Cell Object and its properties
  • Range Object and its properties
  • Worksheet Object and its properties
  • Workbook Object and its properties
  • Font, Formatting, Color and Colorindex properties
  • Rows and Columns

Logical and Looping constructs

  • IF THEN ELSE constructs
  • Select Case construct
  • Logical Operators
  • For loop
  • For Each loop
  • Do while and Do Until loop

User Defined Functions (UDF)

  • Uses of UDF
  • Building a basic function
  • ByRef and ByVal

Form Controls and Event Listeners

  • Button
  • Combo Box
  • Check Box
  • Spin Button
  • List box
  • Radio Button
  • Group Box
  • Label
  • Scroll Bar
  • Properties of Form Controls
  • Understanding Event Listener

Debugging Techniques in VBA

  • Importance of Debug.Print
  • Breakpoints
  • Step Into debugging
  • Run to cursor

Section 5 - Financial Statement Modeling

This section is aimed at understanding financial statement modelling. The participants will learn data gathering, performing integrity check and integrating financial statements. The participants will also learn how to build cost and revenue sheet, asset and depreciation schedule, debt, working capital schedule using excel. By the end of this section participants will be able to build dashboards for ratio analysis, sensitivity analysis, performing sensitivity analysis and building dynamic dashboards. You will also learn calculation of WACC using excel model. The participant will also be able to build valuation models on excel.


Model Schematic and Data Gathering

  • Identifying the model structure
  • Sources of data
  • Data gathering and integrity check
  • Integrating financial statements

Assumptions and Forecasting

  • Assumption Sheet
  • Identifying key assumption drivers for Cost and Revenue
  • Building assumptions for key drivers of IS/BS
  • Building Cost and Revenue sheet
  • Building Cost and Revenue sheet
  • Building Asset and Depreciation Schedule
  • Building Debt, Working Capital and Other Income Schedule
  • Handling Circular Referencing errors

Analysis and Dashboard

  • Building Common Size Financial Statements
  • Ratio Analysis Dashboard
  • Building Scenarios
  • Scenario Analysis
  • Building Sensitivity Table
  • Sensitivity Analysis
  • Using In-cell charting techniques
  • Building Dynamic Dashboards

Beta and Cost of Capital

  • Understanding Beta
  • Levered and Unlevered Beta
  • Cost of Equity
  • Cost of Debt and Tax shield
  • Calculating WACC

Valuation Techniques

  • Creating Valuation Template
  • Concept of Terminal Value and assumptions of growth rate
  • Discount rate concept
  • Projected Cash Flows for DCF Valuation
  • FCFF and FCFE Calculation
  • Sensitivity of WACC and terminal growth rate
  • Advanced Valuation concepts
  • Valuing distressed firms
  • Valuing fast growing companies

Section 6 - Banking, Derivatives and Fixed Income Applications

This section is aimed at explaining applications of financial modelling in banking, derivatives and fixed income. The participants will learn how create CMA Spreadsheet, ALM Modeling and hedging frameworks. You will also be able to create blackscholes calculator and learn how to perform monte carlo simulation for options. Bond pricing fundamnetals, term structure modelling are also taught in excel. You will also learn application of financial modelling in risk management and learn how to build various VaR models, monte carlo simulations, default modelling. You will also learn how to build models on excel for portfolio management. By the end of this section you will be able to design your own models in various fields.


Banking and Treasury

  • CMA Application
  • ALM Modeling
  • Hedging Frameworks
  • Treasury Models

Application to Derivatives

  • Bond Pricing Fundamentals
  • Term Structure modeling
  • Scenario and Sensitivity analysis on bond pricing
  • Bootstrapping Spot Rates
  • Forward rates calculation

Application to Risk Management

  • Delta-Normal VaR Modeling
  • Historical VaR Modeling
  • Monte Carlo Simulations and VaR
  • Default Modeling
  • Credit Portfolio Modeling

Application to Portfolio Management

  • Portfolio Analytics and Mathematics
  • Portfolio Construction and Optimization
  • Efficient Frontiers and beyond
  • Quantitative Portfolio Strategy Backtester

DETAILED CASE STUDY AND EXCEL IMPLEMENTION

Let us bring our classes to you! Our in-house training are ideal for groups of 10 or more people. We can provide Off-the-shelf training in the form of our classic courses, or we can provide bespoke training, tailored to your organizational goals and objectives. Please contact us for further details.