Financial Training Programs Icon

Financial Modeling in Excel


2 Days
Classroom Session   |  
3 Days
Live Online

IIBA (CDU)

ASPE is an IIBA Endorsed Education Provider of business analysis training. Select Project Delivery courses offer IIBA continuing development units (CDU) in accordance with IIBA standards.

NASBA (CPE)

NASBA continuing professional education credits (CPE) assist Certified Public Accountants in reaching their continuing education requirements.

PMI (PDU)

Select courses offer Leadership (PDU-L), Strategic (PDU-S) and Technical PMI professional development units that vary according to certification. Technical PDUs are available in the following types: ACP, PBA, PfMP, PMP/PgMP, RMP, and SP.

NASBA
NASBA
17.00 CPEs

Expertise Level: Expert
Certification
Classroom Registration Fees
Individual:
$2295.00
Group Rate:
$2095.00
(per registrant, 2 or more)
GSA Individual:
$1652.40
Live Online Registration
Live Online:
$2295.00
Private Onsite Package

This course can be tailored to your needs for private, onsite delivery at your location.

Learn More About Enterprise Team Training

Overview

This two-day program will expand your modeling techniques and Excel skills. You'll systematically plan, construct, test and implement a full three statement financial model, that can be used to predict financial performance, value a company, determine external funding needs, or gage a firms credit worthiness. You'll learn a step-by-step process for planning and building your financial model, using all of the powerful tools, macros and advanced features that Excel can offer. You'll understand how each piece of construction and data input affects your reports and how your data interacts. You'll have everything in place to construct a solid, well-built and well-tested Financial Model.

You already have Excel skill, basic modeling experience, and you know the components of a financial statement – now learn the complex formulas and topics that enhance your financial modeling and your decision-making.

Learn to model in standard Microsoft Excel - understand the toolset top to bottom
Learn what differentiates a good financial model from an average one
Understand the difference between Absolute and Relative references
Learn the most efficient way to set up an assumptions worksheet
Plan your comprehensive design specs - knowing the order in which to build each component
Know the toolbar inside and out, what you can customize, what you shouldn't
Learn to use named ranges and other tools to make navigation easier throughout the model
Build models to perform Valuation, IRR, NPV and other management information
Auditing and testing your model without reexamining every single step
Understand how to effectively use mission critical tools: PivotTables, Scenarios, DataTables and Goal Seek
Upcoming Dates and Locations
Guaranteed To Run

There aren’t any public sessions currently scheduled for this course, but if you fill out the form below, we can tell you about how we can bring this course to you!

Course Outline

1. Excel – Advanced Skills, Techniques, Tips and Tricks

  • Review of Excel functions commonly used in Financial Modeling
  • Getting "off the mouse"
  • Keystroke shortcuts and spreadsheet navigation (precedents, dependents, GOTO)
  • Formulas: absolute vs. relative references
  • Named ranges
  • Advanced Formatting
  • Functions including MIN, MAX, OFFSET, VLOOKUP, and TEXT
  • Advanced techniques
  • Understanding and troubleshooting errors
    • Avoiding circular references
    • Dixing #REF's models
    • Checking corporate finance models
    • Formula auditing and more
  • Formatting Tricks:
    • Avoiding spacer columns
    • Custom number formats
    • Triggers
    • Modular modeling and more
  • Data tables
  • Using triggers
  • Pivot tables
  • Conditional formatting
  • Making your model idiot proof - Data validation
  • Filtering
  • Using the FORECAST function

2. Advanced Valuation Techniques

  • The Time Value Of Money in EXCEL
    • PV, FV, and the Internal Rate Of Return in Excel
  • The Weighted Average Cost Of Capital
    • Capital Asset Pricing Model
    • Levered/Unlevered Beta
    • Calculating The Cost Of Debt
    • Getting The Weights
    • Market Value Versus Book Value
    • Calculate WACC Using Live Data
  • Unlevered Free Cash Flows (in Excel)
  • Terminal Value Calculations (in Excel)
    • Constant growth
    • Using a terminal EV/EBITDA multiple
  • The Indirect Method And Enterprise Value
    • What Is Net Debt?
  • Valuing cyclical firms
  • Valuing firms in financial distress

3. Modeling, Building the Integrated Model
Build a fully integrated transaction and valuation model with scenario analysis in Excel.

  • The flow of a model, based on your needs
  • Annual versus monthly models
  • Historical operating assumptions
  • Projected operating assumptions
    • Explore the strategic drivers of each of the operating assumptions
    • Sales – price and volume
    • COGS – economies of scale, operating leverage
  • Income statement above the line
  • Working capital assumptions
    • What is working capital and why does it matter?
    • Explore the strategic drivers of each of the working capital assumptions
    • Sales – price and volume
    • COGS – economies of scale, operating leverage
    • Etc.
  • The balance sheet
    • Required versus excess cash
  • The income statement below the line
  • The statement of cash flows
  • The debt schedule
  • Getting the balance sheet to balance
  • Managing the average interest circular reference
  • Building in scenarios / the OFFSET function / linking to operating assumptions and working capital assumptions
  • Building in a depreciation schedule
  • Unlevered free cash flow
  • DCF/sensitivity analysis

4. Class Wrap-Up and Discussion 
As a group, discuss how we might create a favorable impact on financial best practices within our organizations.

Who should attend

Anyone using Excel to create financial models for critical decision making will benefit from this advanced course, including:

  • Financial Analysts/ Sr. Financial Analysts
  • Valuation Professionals
  • Investment Professionals
  • Mergers & Acquisitions Specialists
  • Controllers
  • CFOs
  • Data Analysts
  • Data Scientists
Pre-Requisites

Please bring a laptop computer running Microsoft Excel 2003 or higher — the most popular and accepted software for developing and building financial models today. If possible, have the Data Analysis Toolpak available to use with your version of Excel (however, the Toolpak is not mandatory). This course focuses primarily on features of Excel available in Excel 2003 or more recent versions. The tools and techniques in this class will work well for any operating system or computing platform running Excel.

Yes, this course looks perfect for my needs!