Learn Financial Modelling - Step by Step - Session 4 | 3 Statement Financial Modelling

Welcome to the Financial Modeling Series

Introduction to the Session

• The speaker, Parth, welcomes viewers to the Financial Modeling series and emphasizes the importance of learning how to plot financial numbers in Excel.

• He encourages viewers to subscribe and like the video for better visibility on YouTube, highlighting that engagement helps improve content reach.

• Parth recommends watching previous sessions for foundational knowledge that will be applied in this session.

Overview of Today's Objectives

• The session aims to plot historical financial statements using Tata Motors as a case study, starting from scratch without pre-existing templates.

• He plans to provide a complete solution by the end of the session and will share resources for further practice.

Data Collection Process

• Parth discusses two options for gathering data: manually entering figures from annual reports or using online resources efficiently.

• He mentions a specific website where users can find comprehensive financial statements for Tata Motors quickly.

Downloading Financial Statements

• Viewers are guided through downloading quarterly results in PDF format from an online portal.

• After downloading, he explains how to locate relevant pages (193 onwards) containing balance sheets and cash flow statements within the annual report.

Working with Excel

• Once data is downloaded into Excel, it becomes easier to manage and format according to personal preferences.

• Parth critiques traditional methods of copying data manually from various sources, advocating instead for efficient resource use during investment banking tasks.

Data Management and Financial Modeling Techniques

Introduction to Data Handling

• The speaker discusses the importance of managing raw data effectively, indicating that all raw data will be compiled into a specific sheet for analysis.

• Emphasizes the need for holistic learning among students, suggesting they should not start with this video without prior knowledge from earlier content.

Initial Steps in Data Plotting

• The speaker begins by plotting initial data points and highlights the significance of maintaining consistency throughout the modeling process.

• Advises against altering certain sheets when integrating client data into financial models, stressing that foundational elements must remain untouched.

Creating Dynamic Links in Sheets

• Demonstrates how to create dynamic links between different sheets, ensuring that changes in one sheet automatically reflect in another.

• Discusses the importance of keeping historical financial statements intact while making necessary updates to other parts of the model.

Formatting and Presentation

• Explains formatting techniques such as bolding headers and using color conventions to enhance readability within financial statements.

• Describes methods for adjusting date formats and ensuring proper alignment within cells for clarity.

Error Handling and Data Integrity

• Introduces error handling strategies, particularly focusing on avoiding incorrect formulas that could disrupt overall data integrity.

• Highlights the necessity of setting default values (like zero) when no valid data is available to maintain model functionality.

Finalizing Financial Figures

• Concludes with steps on finalizing figures by applying currency symbols correctly and ensuring negative values are displayed appropriately.

Understanding Cost Management in Financial Statements

Key Components of Cost Analysis

• Discussion on the importance of raw material costs, manufacturing expenses, and employee costs in financial statements.

• Explanation of how inventory adjustments affect the cost of goods sold (COGS), emphasizing the need for accurate tracking.

• Comparison between annual financial statements to identify discrepancies in reported numbers over different periods.

Gross Margin Calculation

• Introduction to calculating gross margin as a percentage of sales, highlighting its significance in assessing profitability.

• Encouragement to focus on available data without unnecessary stress regarding minor discrepancies.

Income Considerations

• Acknowledgment that fluctuating income figures can complicate analysis; emphasis on focusing on consistent metrics instead.

• Clarification that all expenditures have been incorporated into calculations leading up to earnings before interest and depreciation.

Expense Tracking and Reporting

• Steps taken to consolidate selling and general administrative expenses into a coherent format for analysis.

• Mention of challenges faced when detailed breakdowns of expenditures are not provided, but reassurance that it is manageable for now.

Finalizing Financial Models

• Assurance that constructing a comprehensive financial model will yield insights applicable across various companies quickly.

• Formatting adjustments made to ensure clarity and consistency throughout the financial data presentation.

Interest and Depreciation Insights

• Importance placed on accurately linking interest data from previous quarters for precise calculations moving forward.

• Emphasis on verifying interest figures directly from reliable sources within the data sheets.

Analyzing Earnings Efficiency

• Discussion about formatting issues while extracting relevant interest information from past quarters for thorough analysis.

• Highlighting the necessity of evaluating business efficiency by isolating earnings from external factors like high-interest loans.

Conclusion: Profit Before Tax Assessment

• Final remarks on determining profit before tax, with an understanding that terminology may vary (e.g., "earnings" vs. "profit").

Understanding Effective Tax Rate and Net Profit

Effective Tax Rate Discussion

• The effective tax rate is introduced as a significant concept, with the speaker indicating that it will be explained further in the context of net profit calculations.

Analysis of Net Margin

• The company’s net margin has turned negative, but this is not seen as a major issue. The speaker emphasizes that the profit discussed pertains to equity shareholders, also referred to as earnings available for equity shareholders.

Management Decisions on Profit Distribution

• It is noted that management will decide whether to distribute profits entirely as dividends or reinvest them back into the business. There are no objections to distributing losses from 2018 onwards, highlighting a strategy to reuse losses.

Dividend Calculation Insights

• The speaker discusses how dividends are calculated and mentions the importance of having dividend information readily available for analysis.

Finalizing Financial Statements

Treatment of Cash Flow Analysis

Overview of Operating Activities

• The treatment begins with a focus on operating activities, emphasizing the importance of profit from operations and its direct access.

• A structured format is suggested for presenting financial data, starting with profit from operations and including changes in working capital and direct taxes.

Investment Activities

• The speaker discusses setting up investment activities, indicating that prior instructions should be followed to ensure proper execution.

• Emphasis is placed on cash flow from investing activities, highlighting the need to start with purchases and sales related to plant investments.

Financial Statement Automation

• The process involves pasting financial data into a professional-looking model that includes balance sheets and income statements.

• Instructions are provided for using screener.in to upload financial statements manually, ensuring all necessary data is captured accurately.

Cash Flow Adjustments

• A cross-checking method is recommended to verify cashless treatments within the financial analysis.

• The speaker notes potential discrepancies in cash flow figures due to missing items like receivables and inventory adjustments.

Finalizing Financial Data

• Attention is drawn to specific items that may not align correctly in the cash flow statement; suggestions are made for correcting these issues.

• The speaker reassures that most elements will align properly after adjustments are made, focusing on achieving accurate net cash flow statements.

Financing Activities Review

• Discussion shifts towards financing activities, noting differences between total items listed versus actual entries observed in the analysis.

• Specific attention is given to proceeds from share borrowings and repayments, clarifying how these should be recorded accurately.

Conclusion of Analysis Process

• A final check on various financing items reveals some discrepancies; corrections are advised for dividends paid and other liabilities.