Video summary

Part 1/9 - Learn Financial Modeling in Excel - Step by Step (By Ex-JP Morgan Analyst)

Main summary

Key takeaways

Finance

Part 1/9 - Learn Financial Modeling in Excel - Step by Step

By Ex-JP Morgan Analyst

Finance-Specific Content Summary

Overview

This video is the first installment in a nine-part series designed to teach financial modeling in Excel, specifically tailored for beginners. The instructor, a former JP Morgan analyst, guides viewers through building a fully integrated three-statement financial model, which includes:

  • Income Statement
  • Balance Sheet
  • Cash Flow Statement

The model is based on a case study of a fictional company named Apex Solutions, a global technology firm with three distinct business segments:

  • Software: Enterprise software, cloud solutions, customized software
  • Hardware: Servers, networking equipment, computing devices
  • IT Services: IT consulting, cloud services, cybersecurity, managed IT services

Key Assets / Sectors

  • Company: Apex Solutions (fictional)
  • Sectors: Technology, Software, Hardware, IT Services

Methodology / Step-by-Step Framework for Financial Modeling

  1. Create the Model Structure

    • Organize historical financial data (Income Statement, Balance Sheet, Cash Flow) in a clean, analyst-friendly Excel format.
    • Use separate Excel tabs for each financial statement and supporting schedules such as depreciation, working capital, and debt.
    • Avoid building everything on one sheet to maintain clarity and ease of debugging.
    • Collect historical data from annual reports or SEC filings (provided in the tutorial).

  2. Analyze Historical Financial Statements

  3. Project Revenues and Costs

  4. Project Depreciation

  5. Project Working Capital

  6. Complete the Cash Flow Statement

  7. Project Debt and Interest Expense

  8. Integrate All Statements

Note: Financial modeling is not linear. The process involves moving back and forth between statements due to interdependencies—for example, depreciation affects both the income statement and balance sheet, while debt impacts the balance sheet and interest expense in the income statement.

Important Model Construction Details

  • Color Coding in Excel:
    • Blue: Constants / historical factual data
    • Black: Formulas
    • Green: Links to other sheets (cross-sheet references)

This color-coding scheme is an industry standard that improves model readability and maintenance.

Explicit Recommendations and Cautions

  • Beginners should start with simple assumptions.
  • Building the model structure is the most challenging step because it requires gathering and formatting data from multiple sources.
  • Avoid using a single Excel sheet for the entire model to prevent complexity and difficulty in debugging.
  • The provided case study and Excel template include historical income statement and balance sheet data; cash flow details are simplified.

Key Numbers / Timelines

  • Historical data typically covers 3-5 years.
  • The final model will forecast future financials for Apex Solutions (no specific forecast period mentioned in this video).

Disclaimers

  • The model and tutorial are beginner-level with basic assumptions.
  • The presenter emphasizes that this is a learning tool and not financial advice.

Presenters / Sources

  • Presenter: DH Vadya, former JP Morgan analyst
  • Platform: Wall Street Mojo

Note: No specific tickers, real companies, or market instruments are mentioned, as the focus is on teaching financial modeling using a fictional company case study.

Original video