FINANCIAL MODELING IN EXCEL

749kB Size 11 Downloads 47 Views

Modeling skills and become the best Financial Modeler in town. FINANCIAL MODELING IN EXCEL. Online Training. 2016 Sessions. • Exclusive Live Virtual.
ADVANCED EXCEL Online Training

& DASHBOARDS

FINANCIAL MODELING IN EXCEL

MASTERCLASS 2016 Sessions

• Exclusive Live Virtual

Strengthen your Decision Making and Business Modeling skills and become the best Financial Modeler in town.

Class Training by Experts

• Complimentary Online Package • Authorized Training provider by

CFA Institute

and GARP

• Approved by GARP for 10 CPE credit points.

In this Online Training… Learn how to build stunning Financial Models and assess viability of projects/firms

The Financial Modeling - Full Integrated Model course teaches you the art of constructing an integrated financial model – from scratch. You will soon be building models that are robust and provide you with flexible projections that can be used to

Register now

thoroughly analyze a company from multiple standpoints: Planning, Investment, Financing and Valuation. Benefits of attending: 1. Design & Structure various financial problems and

Book Online

translate them to Excel 2. Ability to create models quickly 1. Online: FMCG Case Study

Join now4

2. Classroom: Accenture Inc. Case Study 3. Understand valuation techniques through DCF 1. Cost of Capital (WACC)

2. Terminal Value 3. Free Cash Flow to the firm and Equity Holders

Email [email protected]

4. Understanding techniques to quickly audit robust models

Who should attend? • Financial Research Analysts • Project Finance Analysts • Corporate Finance Managers

• Business planning & strategy • Investment Banking Associates and Analysts • Credit Analysts • Entrepreneurs

Phone +1 347 647 9001

Financial Modeling Live Virtual Class Course Agenda

Financial Modeling Live Virtual Class Training Course Agenda Financial Modeling - Full Integrated Model - Day 1/2 Overview of Fully Integrated Model Overview Keyboard shortcuts for efficient model building Formulas and cross referencing Formatting numbers in Excel Creating a financial model template Conditional formatting Creating a consistent template for modeling Data Gathering from Annual Reports (10K filing) Historical financial statement modeling (P&L and Creating the historical statements and linking derived B/S) numbers Gathering Data for growth drivers from research reports and other sources Calculating P&L growth numbers/ CAGR Calculating growth drivers Calculating P&L ratios Calculating Balance Sheet ratios Building assumptions for P&L and B/S Linking assumptions for P&L (Except depreciation and Building assumptions and projections Interest) Linking assumptions for Balance Sheet (Except Fixed Assets and Debt) Building the Asset and depreciation schedule

Financial Modeling - Full Integrated Model - Day 3/4 Building Debt & Interest schedule (Without circular referencing) Building Cash flow statement

Building interest on excess cash and cash revolver

Understanding cash from operations, investing and financing activities Using Excel for Iteration - Goal Seek & circular referencing Linking cash revolver and interest to Cash flow, B/S and P&L Formula navigation and debugging errors in excel Handling errors in Excel - Iserror function

Building ratios for analysis

Understanding liquidity ratios Understanding profitability ratios Understanding and building trading comps Understanding and building transaction comps Understanding and building DCF

Updating integrated model for valuation analysis

Building free cash flow to firm & equity Understanding and estimating levered & unlevered beta Understanding & estimating WACC Understanding terminal value Using data-validation to improve sheet structure

Financial Modeling Live Virtual Class Training Course Agenda Financial Modeling - Full Integrated Model - Day 5/6 Named ranges as methodology for modeling Case: Selecting the relevant revenue Use Index to get the relevant value Introduction and context in Advanced functions Case: Portfolio analysis for modeling Case: Creating flexible models for scenarios.

Case: Flexible model generation. Deferred Tax and Loss carried forward. Excel Audit shortcuts Formula Auditing

Formula Auditing: Road Model Evaluate Formula Building Primary & Secondary Axis in a Chart

Advanced Charting technique

Tracking the progress of the construction of the commercial property: Radar Charts Summary of Valuation: Creating Football Charts Stacked bar charts

Financial Modeling - Full Integrated Model - Day 7/8 Introduction and context Introduction to Macros & Advanced Application Key concepts in Macros Building in Excel

Macros as recorded Robots! Implementing user forms in Excel Macros Designing application to gather user data

Linking forms to excel cells Building flexible charts in excel Understanding the relevant language constructs Example: Write your First Macro Errors in VBA

Understanding VBA and debugging

Step into VBA Writing VBA codes: Random Number Generator: Use of loop, Excel functions Message box, Input box

Advanced Financial Applications

Breaking circular loops Monte Carlo simulation

Financial Modeling Online Modules

Financial Modeling - Online Modules Developing Basic Modeling Skills Pre-requisites to Financial Modeling

Understanding Financial Statements (FSS) Module I: Basic Financial Statements and its analysis techniques

- Profit & Loss (P&L) Statement - Balance Sheet (B/S) - Cash Flow Statement Financial Statement Analysis Line items and linkages in the FSS Essential Excel Usage Referencing framework & shortcuts

Module II: Essentials of Excel for Financial Modeling

Case: Funding eligibility for startup company Case: Equated Monthly Installments (EMI) Exercise: Analyzing financial case study & back of the envelop calculations Integrated Model (Step I) - historical financial statements Calculating Growth drivers - P&L (YoY and CAGR)

Module III: Analyzing growth drivers and projecting future numbers

- B/S (Days (of inventory, A/R, etc.)) Understand Management Discussion Analysis (MDA) Build Assumptions about the future Project revenues/ costs Part projecting P&L and B/S

Projecting Asset and Depreciation Module IV: Creating schedules that cut across Projecting Debt and Interest P&L and B/S Projecting PAT and Retained Earnings Modeling Cash Flow Case Study: Break Even Analysis

Note: Each online Module should take ~2 to 3 Hours These modules should be completed prior to Live Virtual Class Training *– Online Course is Complementary in Live Virtual Class Training

Financial Modeling - Online Modules Developing Advanced Modeling Skills Advanced aspects of excel - Circular references - Macros Module V: Advanced excel functions – Macros Understanding Circular reference and Circular references in Finance Case: Circular references in interest expense Case: Macros to generate charts Case: Macros to break circular loops Analyzing the statements Using ratios - Profitability Module VI: Financial Statement Analysis in Excel - Liquidity and Array functions for Credit Risk Modeling - Others Basics of corporate finance Case: Credit risk Model - Arrays and lookup functions

Developing Advanced Modeling Skills on Valuation

Module VII: Valuation Techniques

Module VIII: Industry Model

Understanding Valuation methodology - Absolute valuation - DCF - Comps (comparatives) Implementing DCF - Cash - Timing of cash - Expectation of investors (Cost of capital) Practice and Implement a Completely Integrated Valuation Model

Developing Advanced Modeling Skills on Presentation Valuation by Comps (Comparatives) Transaction comps & Trading comps Important multiples like P/E, EV/ EBITDA, EV/ Sales Module IX: Valuation Techniques Case Study: Modeling advanced Accounting Concept Deferred Taxes Creating forms in Excel Charting Theme - Football field Module X: Charting Techniques relevant to - Waterfall Mechanism finance in Excel - Creating Valuation Dashboard Case Study: Monte Carlo Simulation Using Historical P&L and B/S to create the Cash Flow statement by indirect method Building Historical B/S Module XI: Creating Cash Flow using Indirect Categorizing items in operating, Investing and financing Method Building Cash as difference in Assets and Liabilities Updating Cash Flow Statement 10 Case Studies and Assignments on creating Cash flow statement

What do you get? By attending our Financial Modeling Live Virtual Class 2015 you get, • • • • • •

Exposure to best-in-class modeling techniques, design ideas & practical tips Tips & ideas on using Excel better for Financial Modeling Downloadable workbooks, PDF guides & videos Unlimited download Access to all videos & material on financial modeling Networking opportunities with fellow professionals Email support for your Financial Modeling questions for 1 Year

What should you have? Suggested Skill Level •



To benefit the most from this workshop, you need to have basic level working knowledge of MS Excel. Basic idea of terms like income statement, balance sheet and cash flow

 This Live Virtual class is conducted virtually and you will have to use your own laptop/desktop with you. The course is demonstrated using Microsoft Excel 2010 & 2013, but students can use older versions if they prefer.

Got several participants? Run this course in your company.

Register now Book Online Join now4

Email [email protected]

Phone

+1 347 647 9001

ADVANCED EXCEL Online Training

& DASHBOARDS

FINANCIAL MODELING IN EXCEL

MASTERCLASS 2016 Sessions

• Exclusive Live Virtual

Strengthen your Decision Making and Business Modeling skills and become the best Financial Modeler in town.

Class Training by Experts

• Complimentary Online Package • Authorized Training provider by

CFA Institute

and GARP

• Approved by GARP for 10 CPE credit points.

Comments