ЁЯУК

Financial Modeling - Plotting Financial Statements

Jul 8, 2024

Financial Modeling - Plotting Financial Statements

Introduction

  • Presenter: Parth from the Valuation School
  • Purpose: Start plotting numbers in financial models
  • Importance of subscribing and liking video for youtube algorithm
  • Recommendation to watch previous three sessions for foundational knowledge

Session Overview

  • Focus: Plotting financial statements in Excel and basic analysis
  • Future sessions: In-depth financial analysis of specific companies

Starting from Scratch

  • Importance of building models from scratch
  • No pre-filled sheets; learning by doing
  • Example: Using Tata Motors' historical financial statements

Data Collection Methods

  1. Manual Input: Downloading balance sheets, P&L from annual reports
  2. Automated Methods: Using websites like Screener

Downloading Data

  • Demonstration of downloading and exporting Tata MotorsтАЩ financial data from Screener
  • Importance of exporting to Excel for manipulation

Setting Up Excel Sheet

  • Coding data points: Inputting historical data from Tata Motors
  • Formatting: Ensuring all Excel cells are cleaned and prepped
  • Column Setup: Setting up years, income statements, etc.

Plotting Financial Data

Income Statement

  1. Sales: Using formulas to plot sales figures from downloaded data
  2. Cost of Goods Sold (COGS): Deriving from raw material, employee costs, etc.
  3. Gross Margin Calculation: Sales less COGS
  4. Operating Expenses: Adding selling and admin expenses, depreciation, and interest
  5. EBITDA Calculation: Econometric calculations presented
  6. Taxes: Calculating effective tax rates
  7. Net Profit: Income left after all expenses and taxes
  8. Equity Shareholder Earnings: Final net profit calculations for equity holders

Balance Sheet

  1. Setup: Starting with Current and Non-current assets
  2. Breakdown: Including inventories, receivables, and more
  3. Capital Services: Working capital and changes

Cash Flow Statements

  1. Operations: Information gathered directly from annual reports
  2. Investing: Investments, asset purchases and disposals
  3. Financing: Loans, equity financing activities

Tips and Tricks

  • Focus on automated data pulling for efficiency
  • Importance of formatting for clarity and professionalism
  • Using dynamic Excel formulas for scalability and updates
  • Remember: Always verify against original data sources for accuracy

Final Thoughts

  • Next Session: Deep dive into financial analysis of other companies using the same model structure

Recommendations:

  • Use dual screens for seamless data toggling
  • Keep backups of initial data files
  • Organize sheets logically for easier reference

Full transcript