Financial Modeling Expert by Using Excel Inspire Training Center

Financial Modeling Expert by Using Excel

Inspire Training Center

Price: USD 816
USD 1,020
Instructor led live virtual classroom online. Classes may be individual or in group.
• Timings: Weekday evening classes

Course details

Course Outline
Day 1:
Excel Best Practice (Tools & Shortcuts).
• Using the F-Keys and combinations of F-Keys and ALT.
• Keyboard shortcuts for efficient model building.
• Cross-referencing.
• Named ranges as a methodology for modeling.
• Using Data-Validation to improve sheet structure.
• Tables - Pivot tables - Pivot Chart.
• Insert shapes and smart graphics.
Day 2:
Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): -
• Case (Dealing with Data Base): Tables - Pivot table - Sum ifs & Countifs.
• Case: Replacing email domain using text function.
• Case study: Using Logical functions (IF, And, ETC.).
• Using Lookup & Reference functions (VLOOKUP, Offset, Match, and Index).
• Date and Time Functions.
Day 3:
• Building the Primary & Secondary Axis in a Chart.
• Double Line Chart.
Financial Modeling by using MS-Excel
• Grouped Charting.
• Flexible charts - dynamic chart based for changing ranges.
• Tracking the progress of the construction of the commercial property, Radar Charts.
• Pyramid chart for comparison (Stacked bar charts).
• Water Fall Charts to identify the cost breakdown Structure of a project.
• What-If analysis: (Worked Example included): -
• i. Scenario manager
• ii. Goal Seek
• iii. Data table.
• Create a solid excel template to include dropdown menus and specific selection by Protecting the worksheet and workbook.
• Case study: Work between multiple sheets.
• Text to the column to split text or merge text.
• Conditional formatting.
• Importing and exporting data from external sources.
Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): -
• Case (Array Formulas): Portfolio analysis Sum Product.
• Case (Array Formulas): Calculating geometric return.
• Case (Lookup array more than 2 conditions): Using Dropdown menus - combining Index & Match.
•  Combining Logical Functions with another Logical - Lookup - Reference - IS Functions.
• Case: IRR & NPV Flexible model generation.
Formula Auditing:
• Excel Audit complicated functions.
• Evaluate Formulas.
• Breaking circular reference error (Loop) from excel options.
• Dealing with errors.
Day 6:
Macros:
Introduction to Macros & Advanced Application Building in Excel.

Designing the application to gather user data:
• Record macro & Understanding the created code.
• Errors in VBA
• Step into VBA
• Writing VBA codes
• Random Number Generator: Use of loop, Excel functions
• Message box.
• Input box.
• CAPM Model: using Form Controls (Scroll bar) to calculate the required rate of return.
• Flexible Chart using Form controls (checkbox).
• Case Study: Interest during construction (IDC) & Breaking circular loops using macros.
• Advanced Financial Applications using macro (Monte Carlo simulation).
Day 7:
FMCG Case study: Understanding & gathering information
• Understanding accounting basics double entry and creating (P&L and B/S).
• Creating a consistent financial model template.
• Historical financial statement modeling (P&L and B/S): -
a) Data Gathering from Annual Reports & Management Discussion.
b) Creating the historical statements and linking derived numbers
• Calculating growth drivers: -
a) Gathering Data for growth drivers from research reports and other sources (Revenue - Costing - expenses)
b) Calculating P&L growth numbers/ CAGR.
c) Calculating P&L ratios.
d) Calculating B/S ratios.
Day 8:
FMCG Case study:
• Building assumptions and projections: -
a) Building assumptions for P&L and B/S.
• Building schedules:
a) Fixed Assets and depreciation schedule.
b) Debt & Interest schedule.
• Understand & Build forecasted Profit and loss statement.
• Understand & Build forecasted balance sheet.
• Understand & Build Cash flow statement.
• Building interest on excess cash and cash revolver.
a) Using Excel for Iteration - Goal Seek & circular referencing.
Day 9:
Financial analysis ratios & Valuation: -
• Understand & Build liquidity and profitability ratios for analysis.
• Updating integrated model for valuation analysis: -
a) Understanding and building trading comparatives.
b) Understanding and building transaction comparatives.
c) Understanding and building DCF
d) Building free cash flow to firm & equity
e) Understanding and estimating beta
f) Understanding & estimating WACC
g) Using data-validation to improve sheet structure
h) Building Football field chart to decide which share price (company's value) to consider.
Day 10:
• Scenario & Sensitivity Analysis in Excel
• Case study: Mergers & Acquisitions (M&A) Modeling
• Case study: Leveraged Buyout LBO Modeling
• Theoretical: Corporate & Business Strategy
• Dashboards & Data Visualization
Updated on 03 October, 2020

Eligibility / Requirements

PREREQUISITES
✓ Some knowledge of basic word processing, computing, and spreadsheets.
✓ Familiarity with the Microsoft Office work environment.
✓ An understanding of the Internet and web-page functionality.

Job roles this course is suitable for:

Accountant , Finanical Anaylst , Financial Modeling