Financial Modeling Course 

Overview of Excel and its versions

Understanding and creating a Financial Model template

Using referencing framework (usage of $) to effectively use formulae

Identify the relevant data using conditional formatting

Extracting the required information from string of data using Left, Right, Mid and Trim

Understanding Array functions

Getting the relevant details from multiple tables/ sheets with Lookup/ Index/ Match functions

Using Name Ranges to make formulae easy to implement and understand

Looking at multiple possibilities with Offset function

Getting the desired result with Goal Seek

Getting the desired result having multiple constraints using Solver

Summarizing the results with Sumifs/ Pivot Tables

Data Collection and Data Structuring

Preliminary Scrutiny of the Data and Information

Understand the Business Model and Operating Model

Modeling the Historical Statement

Modeling Assumptions for Future Action

Modeling Revenue Build-up – projecting the future revenues

Modeling Cost Build-up – projecting the future cost

Modeling the Asset Schedule

Modeling the Depreciation Schedule – Book and Income Tax Depreciation

Working Capital and Term Loan Modeling – Debt Infusion

Tax Modeling, MAT and MAT credit

Modeling Impact of Accumulated Losses, Current Tax and Deferred Tax on Tax Payable

Equity Modeling – Equity Infusion

Modeling Paid Up Capital and Share Premium Account

Modeling Retained Earning Schedule

Modeling the Financial Statements, Ratio Analysis

WACC and Cost of Equity Analysis

Performing Valuation using DCF (FCFF and Enterprise Value) and Comparable Analysis (Relative Valuation)

Performing Sensitivity/Scenario Analysis

Comparing the Valuation Summary using Different Parameters on football field

Meaning and Categories of M&A

Merger Motivations

Forms of Payment in a Merger Deal

Hostile versus Friendly Offer

Evaluating a Merger Bid

Case: Estimating fair acquisition price of the target company in M&A

Discounted Cash Flows

Identifying assumptions for estimating future cash flows

Making pro-forma financial estimates

Calculating FCFF, discount and terminal rate and NPV

Comparable Companies

Defining comparative value metric

Estimating takeover premium and calculating takeover price

Comparable Transactions

Comparable transactions and defining comparative value metric

Calculating the valuation of the company

Case: LinkedIn acquisition by Microsoft

Why LinkedIn and Microsoft

Finding out rationale for the transaction

Searching and researching relevant reports

Understanding the usage of 10K and 10Q

LinkedIn's Schedule 14A, LinkedIn's 10Q, Microsoft's fund raise

Collecting and projecting numbers

Merger Analysis – Key Drivers and Inputs

Projecting Microsoft and LinkedIn numbers

Find out the impact of deal announcement on share price

Creating high level merger accounting for goodwill – Pooling method

Accretion/Dilution Analysis and Modeling a Financing Plan

Modeling a Financing Plan

Performing Sensitivity Analysis on Acquirer's EPS, Contribution Analysis

Macros

Accessing VB editor, Developer tab

Creating and deleting modules

Changing security to ‘Disable all macros with notification

Recording your first macro – copy pasting values

Formatting chart with macros

Creating flexible CAPM model using Form Controls and Active X Controls

Writing your first macro – adding a new worksheet and populating content

Debugging and stepping into the VBA Code of your first macro

Correcting circular reference in IDC using VBA/macros

VBA Assignments

Case: Creating a Project model for the development of toll

Road under build, own, operate and transfer model

Creating flexible activity timeline chart – Gantt chart

Using AND function to identify the timelines

Using Webdings to create special characters

Conditional formatting to highlight the timeline

Establishing design parameter

Calculating monthly traffic handling capacity

Forecasting toll charges and monthly traffic

Creating monthly timeline using date function(s)

Calculating toll rate using min/max functions

Estimating traffic under three different scenarios: Pessimistic, Most likely and Optimistic

Adding two more scenarios: Custom and Simulated

Auditing the Project details

Converting all hardcoded numbers to different color in one go

Using Macros (Form controls) to create flexible scenarios

Creating flexibility for five different scenarios

Using OFFSET function to get the output against selected scenario

Creating flexible Project momentum

Importing traffic and toll charges using HLOOKUP

Using Date functions to create monthly, quarterly (CEILING) and yearly timelines

Importing data of relevant scenario using TRANSPOSE function

Cleaning the output using IFERROR

Creating a name manager to use a factor frequently

Projecting Power and Insurance expenses

Modeling CAPEX, Government grant, Exit Value

Calculating NPV, IRR and Payback period

Creating flexible model to calculate cost of equity (CAPM) using Form controls

Calculating XNPV, XIRR

Finding out Payback period using COUNTIF

Creating Yearly statements

Converting monthly statements to yearly statements using SUMIF

Calculating NPV and IRR, Discuss problems with NPV and IRR functions

Linking output with Project chart using hyperlinks

Performing Sensitivity analysis of 9 variables on 3 outputs

a. Using Scenario manager to perform sensitivity analysis

b. Facing and correcting issues with Scenario Manager

Creating bar diagram of annual traffic chart

Drawing Project financial Dashboard

Creating chart using primary and secondary axis

Creating flexible chart for toll revenues, operating expenses, profits and profit margin – Macros

Creating waterfall chart for build-up of traffic

Creating cash flow chart – combination of bar and line chart

Creating custom scenario to mix-match scenarios during construction, after construction and/or revenues – Macros

Inserting a user form to get the input for simulation

Inserting the combo box, text box, spin button, Ok/ cancel button, message box

Generating random numbers to tackle growth

Creating Monte Carlo Simulation for traffic/growth

Creating status bar with REPT function

Calculating frequency using FREQUENCY

Data Collection from Public Sources

Understanding the Business Profile of the Company

Modeling Revenue and Expenses Drivers

Modeling Income Statement

Modeling Implied EBITDA

Balance Sheet Projections

Cash Flow Statement Projection

Performing Valuation and Sensitivity Analysis

Calculating Enterprise Value and Implied Market capitalization