Lecture 5: Financial Modeling – Ratio Analysis

Introduction

• Speaker: Parth from Valuation School
• Focus: Ratio analysis in financial modeling
• Objective: Teach professional ratio calculations, trend analysis, and statistical calculations (mean, median, etc.)

Pre-Requisites

• Ensure you have watched previous sessions on income statement, balance sheet, and cash flow statement preparation.
• Example used from previous sessions: Tata Motors
• Cash flow statement details have been updated.

Objectives of the Session

• Remove fear of financial modeling.
• Simplify financial modeling processes.
• Prepare for DCF analysis in upcoming sessions.

Ratio Analysis Process

1. Sheet Preparation: (Ratio Analysis)

   • Insert a new sheet for ratio analysis.
   • Copy historical financial data.
   • Rename sheet appropriately.

2. Types of Ratios Used: (Growth, Margins, Returns, etc.)

   • Growth Ratios: Sales, EBITDA, EBIT, Net profit, Dividend growth
   • Margin Ratios: Gross, Operating, EBITDA, EBIT, EBT, Net profit margins
   • Expense Ratios: Sales expense, depreciation, operating income as a percentage of sales
   • Return Ratios: Return on Capital Employed (ROCE), Return on Equity (ROE), Sustainable growth rate
   • Working Capital Ratios: Debtor turnover, Creditor turnover, Inventory turnover, Fixed asset turnover, Capital turnover
   • Cash Flow Ratios

Detailed Instructions and Calculations

• Growth Ratios: Use IFERROR to handle errors when data is missing.
• Margin Ratios: Ensure margins decrease logically from gross to net profit.
• Return on Capital Employed (ROCE): Pre-tax return calculation using EBIT
• Return on Equity (ROE): Post-tax calculation excluding preference share dividends
• Sustainable Growth Rate: Calculated using retained earnings and ROE
• Interest Coverage Ratio: Measures ability to pay interest, calculated using EBIT
• Working Capital Ratios: Determine how efficiently capital is used in daily operations
• Days Calculations: Calculate Debtor, Payable, Inventory days, and Cash Conversion Cycle

Visualization

• Use sparklines in Excel to visualize trends for each ratio
• Add color and formatting for better readability

Summary

• Ratio analysis provides insight into company’s financial health
• Essential for valuation and investment decision-making
• Future sessions will cover common size statements and peer analysis

Closing Remarks

• Encouragement to provide feedback and engage with content
• Invitation to join Telegram group for additional resources and live sessions

Additional Resources

• Problem and solution sheets available in the description
• Recommended video on finance career paths linked in the lecture

These notes summarize the key points from Parth's lecture on ratio analysis, providing a concise overview and reference for future study sessions.