DCF Valuation Model Overview and Components
Uploaded by
Aparna BirleDCF Valuation Model Overview and Components
Uploaded by
Aparna BirleTABLE OF CONTENTS
1. OVERVIEW
2. COMPANY PROFILE & ASSUMPTIONS
3. PROFIT & LOSS A/C INPUT
4. BALANCE SHEET INPUT
5. COMMON SIZING
6. REVENUE BUILD-UP
7. COST BUILDUP
8. FIXED ASSETS SCHEDULE (FAS)
9. BALANCE SHEET ITEMS
10. WORKING CAPITAL
11. INCOME STATEMENT
12. BALANCE SHEET
13. CASH FLOW STATEMENT
14. RATIO ANALYSIS
15. STANDARD INCOME STATEMENT
16. CHARTS
17. EQUITY BETA
18. MARKET DATA
19. DCF VALUATION
20. DISCLAIMER
OVERVIEW
Discounted Cash Flow (DCF) valuation model is a powerful tool used to estimate
A
the intrinsic value of a company based on its future cash flows.
he model is structured into various sections, each playing a critical role in building
T
an accurate forecast and valuation. Each sheet serves a specific purpose, from
inputting financial data to conducting detailed analysis and generating key financial
ratios.
he model is designed to assess financial health, provide insights into revenue
T
generation, and cost structure and estimate market valuation through a Discounted
Cash Flow (DCF) approach.
ere’s an overview of the key components and tabs often included in a DCF
H
model:
COMPANY PROFILE & ASSUMPTIONS
● Purpose:
he assumptions sheet is foundational to the model as it sets the key inputs that drive
T
calculations across other sheets. These inputs can include growth rates, discount
rates, tax rates, and cost assumptions.
● Basis:
Contains company-specific details, such as its name, industry, sector, website, and
market price.
● Calculations:
- Contains the key financial and macroeconomic assumptions such as growth rates,
inflation rates, and discount rates, which are crucial for forecasting HPCL’s future
financial performance.
- This sheet is primarily reference-based; it doesn’t contain formulas but lists values
and descriptions that may be referenced across other sheets for model consistency.
PROFIT & LOSS A/C INPUT
● P
urpose:
This sheet contains raw data for creating the Profit & Loss (P&L) statement,
including revenues, expenses, and profits over different periods and the Key
Performance Indicators (KPIs) that are essential to understanding the
company's financial performance and health.
● B
asis:
It is organized by years (e.g., 2018-19 to 2022-23) and includes core revenue
and expense items.
● C
alculations:
Data includes line items like revenue from operations, total income, and costs.
This input data will likely feed into further calculations on income statements
and possibly projections in later sheets.
- K PIs (Key Performance Indicators):These KPIs are calculated using
simple formulas within the model-
❖ Revenue % (y-o-y):
Formula:
= 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝑌
𝑒𝑎𝑟 ÷ 𝑃𝑟𝑒𝑣𝑖𝑜𝑢𝑠𝑌
𝑒𝑎𝑟𝑅
𝑒𝑣𝑒𝑛𝑢𝑒 − 1
❖ G
P% (Gross Profit Margin):
Formula:
= 𝐺𝑟𝑜𝑠𝑠𝑃
𝑟𝑜𝑓𝑖𝑡 ÷ 𝑅𝑒𝑣𝑒𝑛𝑢𝑒 × 1
00
❖ E
BITDA% (Earnings Before Interest, Taxes, Depreciation, and
Amortization Margin):
Formula:
= 𝐸𝐵𝐼𝑇𝐷𝐴 ÷ 𝑅𝑒𝑣𝑒𝑛𝑢𝑒 × 100
❖ EBIT% (Earnings Before Interest and Taxes Margin):
Formula:
= 𝐸𝐵𝐼𝑇 ÷ 𝑅
𝑒𝑣𝑒𝑛𝑢𝑒 × 100
❖ N
P% (Net Profit Margin):
Formula:
= 𝑁𝑒𝑡𝑃𝑟𝑜𝑓𝑖𝑡 ÷ 𝑅𝑒𝑣𝑒𝑛𝑢𝑒 × 1
00
❖ R
OA (Return on Assets):
Formula:
= 𝑁𝑒𝑡𝐼𝑛𝑐𝑜𝑚𝑒 ÷ 𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠 × 100
❖ A
sset Turnover:
Formula:
= 𝑅𝑒𝑣𝑒𝑛𝑢𝑒 ÷ 𝑇𝑜𝑡𝑎𝑙𝐴
𝑠𝑠𝑒𝑡𝑠
❖ A
ssets / Equity:
Formula:
= 𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠 ÷ 𝑇𝑜𝑡𝑎𝑙𝐸
𝑞𝑢𝑖𝑡𝑦 × 100
❖ R
OE (Return on Equity):
Formula:
= 𝑁𝑒𝑡𝐼𝑛𝑐𝑜𝑚𝑒 ÷ 𝑆ℎ𝑎𝑟𝑒ℎ𝑜𝑙𝑑𝑒𝑟'𝑠𝐸𝑞𝑢𝑖𝑡𝑦 × 100
BALANCE SHEET INPUT
● P
urpose:
Records historical balance sheet information, vital for understanding the
company's financial position.
● B
asis:
Segregated by assets (e.g., Non-Current Assets, PPE, Capital
Work-in-Progress) and liabilities, it provides a structured view of the balance
sheet.
● C
alculations:
Similar to the P&L Input, this sheet likely serves as a static input with no
internal calculations. The values, however, may be used in the calculation of
ratios, working capital, and financial health indicators in other sheets.
- KPIs (Key Performance Indicators):These KPIs arecalculated using
simple formulas within the model-
➔ LIQUIDITY RATIOS:
❖ C
urrent Ratio:
Formula:
= 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐴
𝑠𝑠𝑒𝑡𝑠 ÷ 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠
❖ Q
uick Ratio::
Formula:
= (𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐴𝑠𝑠𝑒𝑡𝑠 − 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑖𝑒𝑠) ÷ 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠
➔ SOLVENCY RATIOS:
❖ D
ebt Ratio:
Formula:
= 𝑇𝑜𝑡𝑎𝑙𝐷𝑒𝑏𝑡 ÷ 𝑇𝑜𝑡𝑎𝑙𝐴
𝑠𝑠𝑒𝑡𝑠
❖ Debt-Equity Ratio:
Formula:
= 𝑇𝑜𝑡𝑎𝑙𝐷𝑒𝑏𝑡 ÷ 𝑆ℎ𝑎𝑟𝑒ℎ𝑜𝑙𝑑𝑒𝑟'𝑠𝐸𝑞𝑢𝑖𝑡𝑦
❖ I nterest Coverage Ratio:
Formula:
= 𝐸𝐵𝐼𝑇 ÷ 𝐼𝑛𝑡𝑒𝑟𝑒𝑠𝑡𝐸𝑥𝑝𝑒𝑛𝑠𝑒
❖ D
ebt-Service Coverage Ratio (DSCR):
Formula:
= 𝑁𝑒𝑡𝑂𝑝𝑒𝑟𝑎𝑡𝑖𝑛𝑔𝐼𝑛𝑐𝑜𝑚𝑒 ÷ 𝐷𝑒𝑏𝑡𝑆𝑒𝑟𝑣𝑖𝑐𝑒
COMMON SIZING
● Purpose:
ommon sizing is used to express each
C
line item in the income statement as a
percentage of revenue. This allows for
easier comparison of margins and cost
structures.
● Basis:
Historical financials, usually from the income statement and balance sheet. This
approach makes it easier to analyze cost and profit changes over time as a function of
total revenue.
● Calculations:
Common Size Formula:
➔ For P&L items- ➔ F or Balance Sheet Items-
= 𝐿𝑖𝑛𝑒𝐼 𝑡𝑒𝑚𝑉𝑎𝑙𝑢𝑒 ÷ 𝑇𝑜𝑡𝑎𝑙𝑅𝑒𝑣𝑒𝑛𝑢𝑒 × 100 ❖ Assets -
= 𝐿𝑖𝑛𝑒𝐼 𝑡𝑒𝑚𝑉𝑎𝑙𝑢𝑒 ÷ 𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠 × 100
❖ Liabilities -
= 𝐿𝑖𝑛𝑒𝐼𝑡𝑒𝑚𝑉𝑎𝑙𝑢𝑒 ÷ 𝑇𝑜𝑡𝑎𝑙𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠 × 100
REVENUE BUILD-UP
● P
urpose:
This section is designed to forecast future revenues based on historical data and
business assumptions.
● B
asis:
The sheet typically includes:
➔ Volumes(e.g., production or sales volume)
➔ Pricing(e.g., price per unit)
➔ Revenue Growth Rates
➔ Other Income(e.g., profits from investments, JV/associates)
● Calculations:
➔ Revenue Calculation Formula:
Formula:
= 𝑆𝑎𝑙𝑒𝑠𝑉 𝑜𝑙𝑢𝑚𝑒×𝑃𝑟𝑖𝑐𝑒𝑝𝑒𝑟𝑈
𝑛𝑖𝑡
➔ Other Income:
Formula:
= 𝐽𝑉/𝐴
𝑠𝑠𝑜𝑐𝑖𝑎𝑡𝑒𝑠𝐶𝑜𝑛𝑡𝑟𝑖𝑏𝑢𝑡𝑖𝑜𝑛×𝑃 𝑟𝑜𝑓𝑖𝑡𝑆ℎ𝑎𝑟𝑒%
inancial models often incorporatemultiple scenariosto reflect varying assumptions
F
about future performance. The common scenarios in the Revenue Build-Up sheet
include:
● B
ase Case Scenario:This is the most realistic orexpected scenario, where the
assumptions are based on the company’s historical performance and moderate
growth expectations. The base case assumes the most likely revenue trends
based on steady production and market conditions.
● B
est Case Scenario:This scenario assumes favorable conditions, such as
higher-than-expected production volumes, price increases, or market
expansion. It shows the potential upside of the company’s revenue generation.
For instance, if post-pandemic recovery is strong, the revenue might be
expected to grow at a higher rate.
○ E
xample: If the company estimates a4% annual growthratefor revenue
per unit due to strong demand or price hikes, it is incorporated in this
scenario.
● W
orst Case Scenario:This assumes unfavorable market conditions, such as a
downturn in demand, price drops, or operational challenges. It's useful for
understanding the downside risks.
○ E
xample: The company assumes anegative growth rateor a flat growth
due to market contractions or poor demand, which will result in lower
revenue projections.
COST BUILDUP
● Purpose:
he primary goal of the Cost Build-up sheet is to estimate future costs related to
T
production, operations, and financing, providing a clear outlook on how these costs
will impact the company's profitability over time.
● B asis:
➔ Historical Cost Data: Many of the costs are projectedas a percentage of the previous
year's actuals or revenue. This approach ensures that the model remains consistent
with past performance while incorporating future changes.
➔ Percentage of Revenue Method: Costs are often estimatedas a proportion of revenue
or production. For instance, production costs or selling expenses might be forecasted
as a percentage of sales revenue.
● Calculations:
In theCost Build-up sheet, costs such as
➔ Cost of Materials Consumed ➔ Employee Benefit Expenses
➔ Purchases ➔ Other Expenses,
➔ Excise Duty ➔ Finance Costs
are projected using anAverage of previous years’data.
This method smoothens out any fluctuations in costs and provides a stable estimate
for future projections.
➔ Tax Rate: The tax rate is assumed to increase by1% every two years.
FIXED ASSETS SCHEDULE (FAS)
● Purpose:
he FAS sheet outlines the movement of fixed assets, tracking their acquisition,
T
depreciation, and disposal, which helps assess the company's long-term investment
and resource allocation.
● Basis:
Depreciation is calculated using the historical average method, with asset lives and
CAPEX projections based on past trends, while CWIP is forecasted to increase based on
expected asset growth and completion timelines.
● Calculations:
For each block of assets, Calculations are done as
➔ Opening Balance: Carries over the prior year's closingbalance.
➔ Additions: Represents new assets added, based on CAPEXforecasts.
➔ Disposals: Deduct assets removed from service or sold.
➔ Depreciation: Calculated annually, typically on astraight-line basis, by dividing the
asset's cost by its useful life.
➔ Accumulated Depreciation: Adds up yearly depreciationto show total expenses to
date.
➔ Net Book Value (NBV): Calculated as the opening balanceplus additions minus
disposals and accumulated depreciation.
➔ Closing Balance: Final value of assets after adjustments for the year, used as the
opening balance for the next year.
BALANCE SHEET ITEMS
● Purpose:
ocuses on line items for Balance Sheet forecasting, mainly working capital
F
components.
● Basis:
Uses turnover ratios and days outstanding values from Assumptions.
● Calculations:
Forecasted values by calculating averages based on historical data from previous
years’ wherever trends or seasonality made this approach appropriate. For items
where past data patterns did not apply or additional insights were needed, use an
assumption sheet that contained specific projections, industry benchmarks, and
expert input to provide a more accurate forecast. This approach ensured that the
forecasting model was both data-driven and adaptable to unique scenarios or
external factors.
WORKING CAPITAL
● Purpose:
his sheet calculates the company’s working capital, which is a measure of how much
T
cash it has to cover its day-to-day expenses.
● Basis:
Information from the Balance Sheet (current assets and current liabilities).
● Calculations:
Key metrics derived from this analysis include Days Sales Outstanding (DSO), Days
Inventory Outstanding (DIO), and Days Payable Outstanding (DPO), which help
measure the company’s efficiency in managing its cash conversion cycle.
- Key Metrics:
❖ D
ays Sales Outstanding (DSO):
Formula:
= (𝑇𝑟𝑎𝑑𝑒𝑅𝑒𝑐𝑒𝑖𝑣𝑎𝑏𝑙𝑒𝑠 ÷ 𝑅𝑒𝑣𝑒𝑛𝑢𝑒) × 360
❖ D
ays Inventory Outstanding (DIO):
Formula:
= (𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑖𝑒𝑠 ÷ 𝐶𝑂𝐺𝑆) × 360
❖ D
ays Payable Outstanding (DPO):
Formula:
= (𝑇𝑟𝑎𝑑𝑒𝑃𝑎𝑦𝑎𝑏𝑙𝑒 ÷ 𝐶𝑂𝐺𝑆) × 360
❖ N
et Trade Cycle:
Formula:
= 𝐷𝑆𝑂 + 𝐷
𝐼𝑂 − 𝐷𝑃𝑂
orforecasted years, these metrics (DSO, DIO, DPO)are often based on historical
F
averages or trends and used to project the cash flow impact of working capital. Here’s
how each term influences forecasts:
❖ T
rade Receivables (using DSO):
Instead of directly calculating forecasted receivables based only on DSO,
each year’s Trade Receivable is increased by a growth rate (e.g., 1.06 or
6%) to reflect the anticipated rise in sales and customer credit. This
multiplier reflects expected changes in credit policy or sales volume
growth.
❖ I nventories (using DIO):
Inventory levels for future years are adjusted with a 1.05 or 5 % growth
rate, indicating an expected increase in inventory to match higher
projected demand or production needs. This growth helps maintain
sufficient stock as sales grow, ensuring no supply shortages.
❖ T
rade Payables (using DPO):
Future payables are also multiplied by 1.05 or a 5% growth factor,
showing anticipated increases in purchases or extended credit terms
with suppliers, supporting operational scaling. This multiplier indicates
expected expenses in line with COGS growth or adjustments in payment
policies.
INCOME STATEMENT
● Purpose:
he income statement pulls together the revenue and cost buildup sheets to calculate
T
profits.
● Basis:
Uses forecasted revenue and cost data.
● Calculations:
1. Interlinking Key Financial Sheets: Revenue Buildup,Cost Buildup, and FAS
sheets are interlinked to ensure cohesive financial forecasting. Each sheet
provides foundational data that supports the overall forecast model,
integrating revenues and costs with key metrics for more accurate predictions.
2. Forecasting Comprehensive Income, Dividends, and Scenario-Based
Projections: Projecting other comprehensive incomehistorical averages from
prior years provides a stable, data-driven foundation, reducing reliance on
single-year outliers and minimizing volatility.
Additionally, the dividend is forecasted using scenario-specific assumptions
from the assumptions sheet, allowing the model to adapt dynamically to
different cases and produce realistic, context-sensitive projections.
BALANCE SHEET
● Purpose:
he projected balance sheet shows the company’s financial position at a specific point
T
in time. It includes assets, liabilities, and equity, and helps in understanding how the
company’s capital structure evolves over the forecast period.
● Basis:
Data from the BS items, FAS and Working Capital.
● Calculations:
BS items, FAS and Working Capital sheets are interlinked to ensure cohesive financial
forecasting.
CASH FLOW STATEMENT
● Purpose:
racks cash inflows and outflows, categorizing them into operational, investing, and
T
financing activities. This helps analyze the cash generation capability.
● Basis:
Uses data from both the Income Statement and Balance Sheet.
● Calculations:
The sheet calculates cash from three main activities: Operating, Investing and
Financing.
1. O perating Activities: Start with Net Income, add Depreciation, and adjust for
changes in Working Capital like Trade Receivables, Inventory and Trade
Payables.
2. Investing Activities: Subtract CAPEX (difference inPPE plus Depreciation) and
add any asset sale proceeds.
3. Financing Activities: Include changes in Debt andequity transactions
(issuance/repurchase) and subtract Dividends Paid.
4. Net Cash Flow: Sum Operating, Investing, and FinancingCash Flows.
5. Ending Cash: Calculate as= 𝐵𝑒𝑔𝑖𝑛𝑛𝑖𝑛𝑔𝐶𝑎𝑠ℎ + 𝑁𝑒𝑡𝐶𝑎𝑠ℎ𝐹
𝑙𝑜𝑤.
This gives the closing cash balance.
RATIO ANALYSIS
● Purpose:
ontains financial ratios that help assess the company’s performance, such as
C
profitability, liquidity, and efficiency.
● Basis:
Data from financial statements.
● Calculations:
➔ Profitability Ratios:
❖ Gross Margin
Formula= 𝐺𝑟𝑜𝑠𝑠𝑃𝑟𝑜𝑓𝑖𝑡/𝑅𝑒𝑣𝑒𝑛𝑢𝑒 * 1
00
❖ EBITDA Margin
Formula= 𝐸𝐵𝐼𝑇𝐷𝐴/𝑅𝑒𝑣𝑒𝑛𝑢𝑒 * 100
❖ Operating Margin
Formula= 𝑂𝑝𝑒𝑟𝑎𝑡𝑖𝑛𝑔/𝑅𝑒𝑣𝑒𝑛𝑢𝑒 * 100
❖ Pretax Margin
Formula= 𝐸𝐵𝑇/𝑅𝑒𝑣𝑒𝑛𝑢𝑒 * 100
❖ Effective Tax Rate
Formula= 𝑇𝑎𝑥𝐸𝑥𝑝𝑒𝑛𝑠𝑒/𝐸𝐵𝑇 * 100
❖ Net Margin
Formula= 𝑁𝑒𝑡𝐼𝑛𝑐𝑜𝑚𝑒/𝑅𝑒𝑣𝑒𝑛𝑢𝑒 * 1
00
➔ DuPont/ Earning Power Ratios:
❖ Asset Turnover
Formula= 𝑅𝑒𝑣𝑒𝑛𝑢𝑒/𝐴𝑣𝑒𝑟𝑎𝑔𝑒𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠 * 100
❖ Pretax ROA
Formula= 𝐸𝐵𝐼𝑇/𝐴𝑣𝑒𝑟𝑎𝑔𝑒𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠 * 100
❖ Pretax ROE
Formula= 𝐸𝐵𝑇/𝐴
𝑣𝑒𝑟𝑎𝑔𝑒𝑆
ℎ𝑎𝑟𝑒ℎ𝑜𝑙𝑑𝑒𝑟𝑠'𝐸𝑞𝑢𝑖𝑡𝑦 * 1
00
❖ Tax Complement
Formula= 1 − 𝐸𝑓𝑓𝑒𝑐𝑡𝑖𝑣𝑒𝑇𝑎𝑥𝑅𝑎𝑡𝑒
❖ ROE
Formula= 𝑁𝑒𝑡𝐼𝑛𝑐𝑜𝑚𝑒/𝐴𝑣𝑒𝑟𝑎𝑔𝑒𝑆ℎ𝑎𝑟𝑒ℎ𝑜𝑙𝑑𝑒𝑟'𝑠𝐸𝑞𝑢𝑖𝑡𝑦 * 1
00
❖ Equity Retention
Formula= 1 − 𝐷𝑖𝑣𝑖𝑑𝑒𝑛𝑑𝑃𝑎𝑦𝑜𝑢𝑡𝑅𝑎𝑡𝑖𝑜
❖ Reinvestment Rate
Formula= 𝑅𝑂𝐸/𝐸𝑞𝑢𝑖𝑡𝑦𝑅𝑒𝑡𝑒𝑛𝑡𝑖𝑜𝑛
➔ Liquidity Ratios:
❖ Q uick Ratio
Formula =(𝑇𝑜𝑡𝑎𝑙𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐴 𝑠𝑠𝑒𝑡𝑠 − 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑖𝑒𝑠)/𝑇
𝑜𝑡𝑎𝑙𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠
❖ Current Ratio=
Formula =𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝐴𝑠𝑠𝑒𝑡𝑠/𝐶 𝑢𝑟𝑟𝑒𝑛𝑡𝐿 𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠
❖ Cash Cycle (Days)
Formula =𝐴𝑣𝑔. 𝐴/ 𝑅𝐷𝑎𝑦𝑠 + 𝐴𝑣𝑔. 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦𝐷
𝑎𝑦𝑠 − 𝐴
𝑣𝑔. 𝐴
/𝑃
𝐷𝑎𝑦𝑠
➔ Leverage Ratios
❖ Asset/Equity
Formula =𝑇𝑜𝑡𝑎𝑙𝐴𝑠𝑠𝑒𝑡𝑠/ 𝑇𝑜𝑡𝑎𝑙𝐸
𝑞𝑢𝑖𝑡𝑦
❖ Debt/Equity
Formula =𝑇𝑜𝑡𝑎𝑙𝐷𝑒𝑏𝑡/𝑇𝑜𝑡𝑎𝑙𝐸𝑞𝑢𝑖𝑡𝑦
❖ % LT Debt to Total Capital
Formula =𝐿𝑜𝑛𝑔 − 𝑇𝑒𝑟𝑚𝐷𝑒𝑏𝑡/𝑇𝑜𝑡𝑎𝑙𝐶𝑎𝑝𝑖𝑡𝑎𝑙 * 100
❖ Interest Coverage Ratio
Formula =𝐸𝐵𝐼𝑇/𝐼𝑛𝑡𝑒𝑟𝑒𝑠𝑡𝐸𝑥𝑝𝑒𝑛𝑠𝑒
➔ Operating Ratios
❖ A/R Turnover
Formula =𝑅𝑒𝑣𝑒𝑛𝑢𝑒/𝐴 𝑣𝑒𝑟𝑎𝑔𝑒𝐴𝑐𝑐𝑜𝑢𝑛𝑡𝑠𝑅𝑒𝑐𝑒𝑖𝑣𝑎𝑏𝑙𝑒
❖ Avg. A/R Days
Formula =365/𝐴/ 𝑅𝑇𝑢𝑟𝑛𝑜𝑣𝑒𝑟
❖ Inventory Turnover
Formula =𝐶𝑜𝑠𝑡𝑜𝑓𝐺𝑜𝑜𝑑𝑠𝑆𝑜𝑙𝑑/ 𝐴𝑣𝑒𝑟𝑎𝑔𝑒𝐼 𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦
❖ Avg. Inventory Days
Formula =365/𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦𝑇𝑢𝑟𝑛𝑜𝑣𝑒𝑟
❖ A/P Turnover
Formula =𝐶𝑜𝑠𝑡𝑜𝑓𝐺𝑜𝑜𝑑𝑠𝑆𝑜𝑙𝑑/ 𝐴𝑣𝑒𝑟𝑎𝑔𝑒𝐴 𝑐𝑐𝑜𝑢𝑛𝑡𝑠𝑃
𝑎𝑦𝑎𝑏𝑙𝑒
❖ Avg. A/P Days
Formula =365/𝐴/ 𝑃𝑇 𝑢𝑟𝑛𝑜𝑣𝑒𝑟
❖ Fixed Asset Turnover
Formula =𝑅𝑒𝑣𝑒𝑛𝑢𝑒/𝐴 𝑣𝑒𝑟𝑎𝑔𝑒𝑁𝑒𝑡𝐹𝑖𝑥𝑒𝑑𝐴𝑠𝑠𝑒𝑡𝑠
❖ WC / Sales Growth
Formula =𝐶ℎ𝑎𝑛𝑔𝑒𝑖𝑛𝑊𝑜𝑟𝑘𝑖𝑛𝑔𝐶𝑎𝑝𝑖𝑡𝑎𝑙/ 𝑆𝑎𝑙𝑒𝑠𝐺𝑟𝑜𝑤𝑡ℎ
❖ ROCE
Formula =𝐸𝐵𝐼𝑇/𝑇 𝑜𝑡𝑎𝑙𝐶𝑎𝑝𝑖𝑡𝑎𝑙𝐸 𝑚𝑝𝑙𝑜𝑦𝑒𝑑 * 100
STANDARD INCOME STATEMENT
● Purpose:
his is a simplified version of the Income Statement for easy comparison across
T
different periods.
● Basis:
Data from the Income Statement sheet.
● Calculations:
Interlinking is conducted through the income statement sheet. Year-over-year
growth is calculated as previously described. This ensures consistency and clarity in
financial analysis.
CHARTS
● Purpose:
Provides visual representations of the company’s financial data.
● Basis:
Data from the Income Statement, Balance Sheet, and other key sheets.
● Charts:
❖ Revenue & YoY Growth:
Chart Type -Combo (Revenue From Operations - Clustered
Column and YoY Growth - Line with Markers)
❖ EBITDA vs Net Profit:
Chart Type -3-D Clustered Column
❖ Industry vs Company Sales Growth:
Chart Type -Line with Markers
❖ ROE vs ROCE:
Chart Type -Combo (ROE - Clustered Column and ROCE- Stacked
Line with Markers)
EQUITY BETA
● Purpose:
eta measures the stock’s volatility relative to the market. It’s a crucial input in
B
calculating the cost of equity, which is part of the discount rate used in the DCF
calculation.
● Basis:
Stock market data.
● Calculations:
❖ Covariance-The function is used to calculate the samplecovariance between
two sets of values. The syntax is= 𝐶𝑜𝑣𝑎𝑟𝑖𝑎𝑛𝑐𝑒. 𝑆(𝑎𝑟𝑟𝑎𝑦1, 𝑎𝑟𝑟𝑎𝑦2)
❖ V
ariance-The function is used to calculate the samplevariance. The syntax is
= 𝑉𝑎𝑟. 𝑆(𝐶𝑒𝑙𝑙: 𝐶𝑒𝑙𝑙)
❖ Beta -Formula= 𝐶𝑜𝑣𝑎𝑟𝑖𝑎𝑛𝑐𝑒 ÷ 𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒
MARKET DATA
● Purpose:
ontains data on the broader market, such as stock prices, interest rates, and
C
economic indicators.
● Basis:
Data from the market, such as the stock exchange.
● Calculations:
Data is taken from NSE site from 2019 to 2024 and at the end calculated Market return
by using the formula in Excel “RATE(nper, pmt, pv, [fv])”
DCF VALUATION
● Purpose:
his is the heart of the model. The DCF valuation sheet calculates the company’s
T
intrinsic value by discounting future cash flows back to their present value using the
discount rate.
It answers the key question:What is the company worth today based on its future
cash-generating potential?
● Basis:
Projections of unlevered free cash flow, assumptions like the cost of equity, discount
rate (WACC), terminal value, intrinsic value and sensitivity analysis.
● Calculations:
❖ Unlevered FCFF:
Formula
= 𝐸𝐵𝐼𝑇 − 𝑇
𝑎𝑥𝑒𝑠 + 𝐷𝑒𝑝𝑟𝑒𝑐𝑖𝑎𝑡𝑖𝑜𝑛&𝐴𝑚𝑜𝑟𝑡𝑖𝑧𝑎𝑡𝑖𝑜𝑛 − 𝐶𝑎𝑝𝑒𝑥 − 𝐶
ℎ𝑎𝑛𝑔𝑒𝑖 𝑛𝑊𝑜𝑟𝑘𝑖𝑛𝑔𝐶
𝑎𝑝𝑖𝑡𝑎𝑙
❖ C
ost of Equity (CAPM):
Formula= 𝑅𝑓 + [β × (𝑅𝑚 − 𝑅𝑓)]
Here,
𝑓i.e.Risk-Free Returnis taken from the
𝑅
Assumptions Sheet (10 year Bond yield rate)
β i.eBetais taken from the Equity Beta Sheet
𝑚i.eMarket Returnis taken from the Market
𝑅
Data Sheet
❖ D
iscount rate (WACC):
Formula
= ((𝐶𝑜𝑠𝑡𝑜𝑓𝐸𝑞𝑢𝑖𝑡𝑦 × %𝑃𝑟𝑜𝑝𝑜𝑟𝑡𝑖𝑜𝑛𝑜𝑓𝐸
𝑞𝑢𝑖𝑡𝑦) +
(𝐶𝑜𝑠𝑡𝑜𝑓𝐷
𝑒𝑏𝑡(𝑎𝑓𝑡𝑒𝑟𝑡𝑎𝑥) × %
𝑃𝑟𝑜𝑝𝑜𝑟𝑡𝑖𝑜𝑛𝑜𝑓𝐷
𝑒𝑏𝑡)
➔ E quityis taken from the Cost of Equity
calculated above
➔ D
ebt (after tax)is taken from the
Assumptions Sheet (Interest Rate)
➔ Weightageis calculated as
● Equity= 𝐸𝑞𝑢𝑖𝑡𝑦𝑉 𝑎𝑙𝑢𝑒 ÷ 𝑀𝑎𝑟𝑘𝑒𝑡𝑉𝑎𝑙𝑢𝑒
● Debt= 𝐷𝑒𝑏𝑡𝑉
𝑎𝑙𝑢𝑒 ÷ 𝑀𝑎𝑟𝑘𝑒𝑡𝑉𝑎𝑙𝑢𝑒
➔ Equity Valueis taken from the Assumptions Sheet (MarketCapitalization)
➔ D
ebt Valueis taken from the Balance Sheet (Non-CurrentFinancial Liabilities
Borrowings of 2022-23)
➔ Market Value= 𝐸𝑞𝑢𝑖𝑡𝑦𝑉
𝑎𝑙𝑢𝑒 + 𝐷𝑒𝑏𝑡𝑉
𝑎𝑙𝑢𝑒
❖ Terminal Value:
➔ P
erpetual Growth rateis taken from the
Assumptions Sheet (Perpetual Growth rate)
➔ Terminal Value
= (𝐹𝑖𝑛𝑎𝑙𝑌𝑒𝑎𝑟𝐹𝐶𝐹𝐹 × (1 + 𝑃𝑒𝑟𝑝𝑒𝑡𝑢𝑎𝑙𝐺𝑟𝑜𝑤𝑡ℎ𝑅𝑎𝑡𝑒))
÷ (𝐷𝑖𝑠𝑐𝑜𝑢𝑛𝑡𝑟𝑎𝑡𝑒 − 𝑃𝑒𝑟𝑝𝑒𝑡𝑢𝑎𝑙𝐺𝑟𝑜𝑤𝑡ℎ𝑅𝑎𝑡𝑒)
❖ Intrinsic Value per share:
Formula= 𝐸𝑞𝑢𝑖𝑡𝑦𝑉
𝑎𝑙𝑢𝑒 ÷ 𝑁𝑜. 𝑜𝑓𝑠ℎ𝑎𝑟𝑒𝑠𝑜𝑢𝑡𝑠𝑡𝑎𝑛𝑑𝑖𝑛𝑔
➔ Equity Value
= 𝐸𝑛𝑡𝑒𝑟𝑝𝑟𝑖𝑠𝑒𝑉𝑎𝑙𝑢𝑒 + 𝐶𝑎𝑠ℎ&𝐶𝑎𝑠ℎ𝐸𝑞𝑢𝑖𝑣𝑎𝑙𝑒𝑛𝑡𝑠
− 𝑇𝑜𝑡𝑎𝑙𝐷𝑒𝑏𝑡 − 𝑁𝐶𝐼 − 𝑃𝑟𝑒𝑓𝑒𝑟𝑟𝑒𝑑𝑆𝑡𝑜𝑐𝑘
➔ Enterprise Value
= 𝑆𝑢𝑚𝑜𝑓𝑃𝑉𝑜𝑓𝐶𝑎𝑠ℎ𝐹𝑙𝑜𝑤𝑠 + 𝑃𝑉𝑜𝑓𝑇𝑒𝑟𝑚𝑖𝑛𝑎𝑙𝑉𝑎𝑙𝑢𝑒
➔ Sum of PV of Cash Flows
= 𝑁𝑃𝑉(𝐷𝑖𝑠𝑐𝑜𝑢𝑛𝑡𝑅𝑎𝑡𝑒, Σ𝑈𝑛𝑙𝑒𝑣𝑒𝑟𝑒𝑑𝐹𝐶𝐹𝐹
➔ PV of Terminal Value
= 𝑃𝑉(𝐷𝑖𝑠𝑐𝑜𝑢𝑛𝑡𝑅𝑎𝑡𝑒, 𝑛𝑜. 𝑜𝑓𝑦𝑒𝑎𝑟𝑠𝑡𝑒𝑟𝑚𝑖𝑛𝑎𝑙𝑣𝑎𝑙𝑢𝑒𝑖𝑠𝑟𝑒𝑎𝑙𝑖𝑧𝑒𝑑, 0, 𝑇𝑒𝑟𝑚𝑖𝑛𝑎𝑙𝑉𝑎𝑙𝑢𝑒)
❖ C
urrent Market Price:
Taken from the Assumptions Sheet
❖ U
pside potential:
Formula= (𝐼𝑛𝑡𝑟𝑖𝑛𝑠𝑖𝑐𝑣
𝑎𝑙𝑢𝑒𝑝
𝑒𝑟𝑠 ℎ𝑎𝑟𝑒 − 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝑀
𝑎𝑟𝑘𝑒𝑡𝑃
𝑟𝑖𝑐𝑒) ÷ 𝐶𝑢𝑟𝑟𝑒𝑛𝑡𝑀
𝑎𝑟𝑘𝑒𝑡𝑃
𝑟𝑖𝑐𝑒
❖ Sensitivity Analysis:
ensitivity analysis, also known as what-if analysis, is a tool in Excel that helps users
S
determine how changes to input variables affect the outputs of a model.
Steps for Sensitivity Analysis:
❖ I dentify Key Variables: Determine the key variablesto analyze, such as the
discount rate and terminal growth rate in this sheet.
❖ S
et Variable Ranges: Define a range of values for each variable (e.g., discount
rates from 11% to 15% and terminal growth rates from 2% to 6%).
❖ C
reate a Sensitivity Table: Create a table with discount rates as rows and
terminal growth rates as columns.
❖ I nput Base Case Model: Ensure a base case intrinsic value model is ready,
referencing the identified variables.
❖ C
alculate Values: In the intersection cells of the table, input formulas to
calculate intrinsic value based on the corresponding discount rate and terminal
growth rate.
❖ F
ill the Table: Use Excel’s fill handle to copy theformulas across all cells to
compute values for all combinations.
❖ F
ormat the Table: Apply formatting for clarity, suchas borders and conditional
formatting to highlight significant results.
❖ A
nalyze Results: Review the table to understand howchanges in the variables
affect intrinsic value, identifying trends and sensitivities.
Summary
ollowing these steps allows for an effective sensitivity analysis, providing insights
F
into how different assumptions impact the valuation model.
DISCLAIMER
his assignment, prepared under the mentorship of Nikhil Sir during the internship at
T
ProCapitas, presents a summary of the DCF (Discounted Cash Flow) valuation model
for HPCL (Hindustan Petroleum Corporation Limited). The content includes
screenshots of the Excel workbook; however, these images are only glimpses of the
individual sheets and do not represent the complete data or analysis contained within
the model.
lease note that the steps outlined in this summary may not be universally applicable
P
to all types of financial models, as methodologies can vary based on specific
circumstances, data availability, and analytical frameworks. Users of this document
should consider the unique requirements and context of their own models when
applying any concepts or methodologies discussed herein.
his summary is intended for educational purposes and reflects the work completed
T
during the internship. All rights to the original data and models remain with the
respective authors and stakeholders.
You might also like
- Financial Template: Break-Even AnalysisNo ratings yetFinancial Template: Break-Even Analysis47 pages
- Cash Flow and Financial Forecasting GuideNo ratings yetCash Flow and Financial Forecasting Guide13 pages
- Century Pacific Food Financial AnalysisNo ratings yetCentury Pacific Food Financial Analysis55 pages
- Vertical Analysis & Common Size StatementsNo ratings yetVertical Analysis & Common Size Statements4 pages
- Financial Analysis of Century Pacific FoodNo ratings yetFinancial Analysis of Century Pacific Food56 pages
- AnythingLLM Export - 2026-07-16 16_12_46No ratings yetAnythingLLM Export - 2026-07-16 16_12_4619 pages
- Importance of Prospective Analysis in Finance100% (1)Importance of Prospective Analysis in Finance3 pages
- FY 2020-21 Common Size Financial StatementsNo ratings yetFY 2020-21 Common Size Financial Statements9 pages
- Notes Unit 3 Financial Accounting & AnalysisNo ratings yetNotes Unit 3 Financial Accounting & Analysis14 pages
- Financial Analysis: P&L and Balance SheetNo ratings yetFinancial Analysis: P&L and Balance Sheet15 pages
- Essential Income Statement Analysis GuideNo ratings yetEssential Income Statement Analysis Guide9 pages
- Cash Conversion Cycle Analysis in ExcelNo ratings yetCash Conversion Cycle Analysis in Excel14 pages
- Unit 5 Financial Statements and Ratio AnalysisNo ratings yetUnit 5 Financial Statements and Ratio Analysis19 pages
- Financial Analysis of Century Pacific FoodNo ratings yetFinancial Analysis of Century Pacific Food56 pages
- Financial Analysis and Cash Flow StatementNo ratings yetFinancial Analysis and Cash Flow Statement3 pages
- Financial Statements and Accounting MethodsNo ratings yetFinancial Statements and Accounting Methods2 pages
- Understanding Financial Statements OverviewNo ratings yetUnderstanding Financial Statements Overview8 pages
- Relaxo Footwear Financial Overview Mar '24No ratings yetRelaxo Footwear Financial Overview Mar '2411 pages
- Awal Mia: Finance & Audit Expert ProfileNo ratings yetAwal Mia: Finance & Audit Expert Profile2 pages
- Carriers Legal Liability Insurance PolicyNo ratings yetCarriers Legal Liability Insurance Policy3 pages
- Central Accounting Manager Job DescriptionNo ratings yetCentral Accounting Manager Job Description2 pages
- Major Purchase Guidelines: Cars & HomesNo ratings yetMajor Purchase Guidelines: Cars & Homes41 pages
- Financial Analysis Project for TYBCOM 2025-26No ratings yetFinancial Analysis Project for TYBCOM 2025-265 pages
- Islamic Finance Development Report 2021No ratings yetIslamic Finance Development Report 202178 pages
- Overview of Islamic Housing Finance in PakistanNo ratings yetOverview of Islamic Housing Finance in Pakistan20 pages
- CIBIL Score Report for B'KALPANA B'KUMARINo ratings yetCIBIL Score Report for B'KALPANA B'KUMARI22 pages