This event is now over

Event Details

About

MS Excel ® is today unarguably the most commonly used spreadsheet utility globally to do finance. In spite of this, according to various surveys on Excel usage, a rather miniscule percentage of Excel Users use it to its full potential. The focus of the course is to help the participants learn the tools and capabilities of this spreadsheet application to perform from the simplest to the most complicated and elaborate financial analysis.

 The important aspects this workshop focuses on is to apply the tools effectively while constructing financial models, caring for scalability, making them flexibile, structuring in such a way that auditing the model results is not cumbersome. These essential attributes make financial models accurate, flexible and user-friendly. The workshop would use a ‘learning by doing’ approach, because that’s how the science and art of financial modeling is learnt.

Results

We expect that the participants attending the course will be able to learn significant financial modeling capabilities using Excel that would be pertinent for corporate finance, financial analysis, risk management, transaction structuring like modeling for M&A, etc. The level of the course is Intermediate to advanced.

Key Benefits

Master the use of Excel’s financial modelling tools and capabilities

How to design a model to suit your purpose

Understand the different types of financial models and when each should be applied

Construct financial models making use of a broad range of Excel methods and techniques

Accurate forecasting corporate cash flows for project finance deals and structures

Incorporate elements such as risk, sensitivity, optimisation and forecasting into financial models

Produce meaningful management reports and charts for communication

How to identify and control key sensitivities through advanced spreadsheet simulation

How to design a model to maximise flexibility and reliability

Practical tips for checking and debugging the model

 

Who should attend ?

 •Corporate Finance Professionals

 •Quantitative analysts

 •Investment Bankers

 •Risk professionals

 •Treasury managers

 •Controllers

 •Data analysts and economists

Faculty

An Investment Banking professional with over 10 years of experience and a strong focus on medium sized organizations. He has hands on experience of setting up systems in start up and growth companies and is well networked in the investment banking and private equity space. He has strong sector exposure in Cement, Automobiles, Textiles and Infrastructure. With him, UNICOM has developed training modules to simplify financial modeling and investment analysis for corporate executives in order to cope with financial and strategic responsibilities.

Agenda

DAY ONE : 

 Session One

Creating the first financial statement model in Excel to begin with (with an exercise and hands on practical session; focus on how to build a model right from the scratch, linkages with excel spread sheets, assumptions, use of past financial statements for the projections and building forecasted financial statements)

Important issues for preparation and building of a financial model

Excel Functions and commands to supercharge worksheets (most of the participants may be aware about the functions, yet just a quick revision and how these functions are used in financial modelling)

· Different ways of summing and counting: SUMIF; SUMIFS; SUMPRODUCT; DSUM; DCOUNT; DCOUNTA; COUNTBLANK; COUNTIF; DMAX; DAVERAGE

· IF (This Is True, Do This, Else Do This)

· Lookup & reference: CHOOSE; OFFSET; INDEX; MATCH; HLOOKUP; VLOOKUP

· Data Analysis Toolpak

· Important Excel Functions and commands for modeling

·         Conditional Formating

·         Online collaboration

·         Auditing

·         Protecting the workbook

·         Sharing the workbook

·         Data Validation

·         Handling external data

·         Sorting

·         Filters

·         Subtotals

·         Pivot Tables

 Session Two

· Statistical Data Analysis: trend analysis, regression, moving average

· Optimisation using

·         Goal Seek

·         Scenario Manager

·         Data Table: Row and Column input cell

·         Solver

         · Scenario Building

·         Switches

·         Forms

·         Scenario building optimistic, base case and pessimistic assumptions

Day Two : 

Session Three :
Topics in Finance 

· Principles of financial modelling—Accuracy, Flexibility & User-friendliness

· Defining Model objective

· Outlining model plan

· Spread sheet maps

· Flowchart and information flow

· Layout and architecture of financial model

· Setting up modules

· Identifying inputs and variables

· Defining deliverables and functionality

· Cataloguing outputs

· Stress testing Models

· Model Documentation

· Financial Statement modelling

· Projection of Revenues, COGS, SG&A and other Income Statement and Balance Sheet items

· Select model drivers and assumptions

· How to create an interlinked model for Income Statement and Balance Sheet

· How circularity improves accuracy but also destabilizes the model

· Building a fully integrated Cash Flow Statement

· Modelling need for financing in future time

· Analysing the output and cross-checking with surplus funds and necessary to finance

· Models for Debt repayment with prepayment option

· Modelling Amortizing & Accreting Loans

· Modelling Pay In Kind (PIK) securities

· Model for computing Beta

· Modelling un-levering and re-levering of betas

· Modelling term structure of Beta

· Model for WaCC with various debt-equity choices

· Modelling term structure of WaCC

· DCF valuation

· Relative valuation (PE, EBITDA multiple)

· Combining DCF and relative valuation models

· Modelling for Leveraged Buy Out & Management Buy Out

· Sources of funds for acquisition

· Modelling uses of funds

· Modelling ESOPs and Earn-Outs

· Partial and full dilution due to ESOPs

· IRR calculation for financial sponsor on fully diluted basis

· Purchase Accounting Model

· Model for Stock-for-Stock Deal

· Model for Cash-for-Stock Deal

· Modelling when M&A financed by issue of debt

· Model illustrating Accounting for a partial Acquisition

· Accretion Dilution Model

· Deal Structure: Cash, Fixed-Value Stock Offer, Fixed-Shares Stock Offer

Session Four :

Modelling for Corporate Finance Transaction 

· Case Outline and the process participants will go through in solving the case and structuring an LBO/MBO transaction

 

Contact

For more detail please contact Mr. Ritesh Aukhary: 7829330321/ 9771493353/ contact@unicomlearning.com

www.unicomlearning.com/learningsolutions/course/22/financial-modelling-using-excel

Schedule

November 25, 2011 — 9:00 am to
November 26, 2011 — 6:00 pm

Add to your calendar

Venue

Hotel VITS
Andheri Kurla Road, International Airport Zone,
Andheri East,
Mumbai, Maharashtra
India

Map