[go: up one dir, main page]

0% found this document useful (0 votes)
24 views3 pages

Excel Formulas

The document provides a comprehensive overview of financial statement analysis, including key metrics for income statements, balance sheets, and cash flow analysis. It also covers valuation metrics such as discounted cash flow and multiples-based valuation, along with capital budgeting and investment analysis techniques. Additionally, it discusses financial forecasting, mergers and acquisitions analysis, and risk analysis methods.

Uploaded by

asimzahoor5
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
24 views3 pages

Excel Formulas

The document provides a comprehensive overview of financial statement analysis, including key metrics for income statements, balance sheets, and cash flow analysis. It also covers valuation metrics such as discounted cash flow and multiples-based valuation, along with capital budgeting and investment analysis techniques. Additionally, it discusses financial forecasting, mergers and acquisitions analysis, and risk analysis methods.

Uploaded by

asimzahoor5
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

1.

Financial Statement Analysis


Income Statement

 Gross Profit = Revenue - Cost of Goods Sold (COGS)


 Gross Profit Margin = (Gross Profit / Revenue) × 100
 Operating Profit (EBIT) = Revenue - Operating Expenses
 Operating Profit Margin = (Operating Profit / Revenue) × 100
 Net Profit (Net Income) = Revenue - Total Expenses
 Net Profit Margin = (Net Profit / Revenue) × 100
 Earnings Per Share (EPS) = Net Income / Weighted Average Shares Outstanding
 EBITDA = EBIT + Depreciation + Amortization
 EBITDA Margin = (EBITDA / Revenue) × 100

Balance Sheet

 Current Ratio = Current Assets / Current Liabilities


 Quick Ratio = (Current Assets - Inventory) / Current Liabilities
 Debt-to-Equity Ratio = Total Debt / Total Equity
 Equity Multiplier = Total Assets / Total Equity
 Working Capital = Current Assets - Current Liabilities
 Net Working Capital (NWC) Ratio = (Current Assets - Current Liabilities) / Total
Assets
 Return on Assets (ROA) = (Net Income / Total Assets) × 100
 Return on Equity (ROE) = (Net Income / Shareholder’s Equity) × 100
 Return on Invested Capital (ROIC) = (EBIT × (1 - Tax Rate)) / (Debt + Equity)

Cash Flow Analysis

 Free Cash Flow (FCF) = Operating Cash Flow - Capital Expenditures (CapEx)
 Free Cash Flow to Equity (FCFE) = FCF + Net Borrowings - Debt Repayments
 Cash Conversion Cycle (CCC) = Days Inventory Outstanding + Days Sales
Outstanding - Days Payable Outstanding
 Operating Cash Flow Ratio = Operating Cash Flow / Current Liabilities

2. Valuation Metrics
Discounted Cash Flow (DCF) Valuation

 Present Value (PV) = Future Cash Flow / (1 + Discount Rate)ⁿ


 Net Present Value (NPV) = Σ [CFt / (1 + r)ᵗ] - Initial Investment
 Internal Rate of Return (IRR) = Discount rate where NPV = 0
 Payback Period = Initial Investment / Annual Cash Inflows
 Profitability Index (PI) = PV of Future Cash Flows / Initial Investment

Multiples-Based Valuation

 Enterprise Value (EV) = Market Cap + Total Debt - Cash & Equivalents
 EV/EBITDA = Enterprise Value / EBITDA
 EV/Sales = Enterprise Value / Revenue
 P/E Ratio = Market Price per Share / Earnings per Share (EPS)
 Price-to-Book Ratio (P/B) = Market Price per Share / Book Value per Share

3. Capital Budgeting & Investment Analysis


 Weighted Average Cost of Capital (WACC) = (E/V × Re) + (D/V × Rd × (1 - Tax
Rate))
o Where:
 E = Market Value of Equity
 D = Market Value of Debt
 V = Total Value (E + D)
 Re = Cost of Equity
 Rd = Cost of Debt
 Capital Asset Pricing Model (CAPM) = Risk-Free Rate + Beta × (Market Return -
Risk-Free Rate)
 Cost of Equity (Re) = (D1 / P0) + g
o Where:
 D1 = Expected Dividend
 P0 = Current Stock Price
 g = Growth Rate
 Cost of Debt (Rd) = Interest Expense / Total Debt
 Leverage Ratio = Total Debt / Total Capital

4. Financial Forecasting & Sensitivity Analysis


 Revenue Growth Rate = (Current Year Revenue - Previous Year Revenue) / Previous
Year Revenue
 Compounded Annual Growth Rate (CAGR) = [(Final Value / Initial Value)^(1/n)] - 1
 Break-Even Point (Units) = Fixed Costs / (Selling Price per Unit - Variable Cost per
Unit)
 Break-Even Revenue = Fixed Costs / Contribution Margin Ratio
 Operating Leverage = % Change in EBIT / % Change in Sales
 Financial Leverage = % Change in EPS / % Change in EBIT
5. Mergers & Acquisitions (M&A) Analysis
 Accretion / Dilution = Target EPS - Acquirer EPS (if positive = accretive, negative =
dilutive)
 Synergy Value = Expected Combined Value - (Acquirer Value + Target Value)
 Enterprise Value to Revenue = EV / Revenue

6. Risk Analysis & Scenario Planning


 Value at Risk (VaR) = Portfolio Value × Z-Score × Standard Deviation
 Expected Return (Portfolio) = Σ (Weight of Asset × Expected Return of Asset)
 Standard Deviation (Portfolio Risk) = √Σ [Wi² × σi² + ΣΣ (Wi × Wj × Covij)]

You might also like