FINANCIAL MODELLING is a calculator

Why financial modelling?

Imagine a company (say Tata Steel), it’s current stock price is rs 110. Your friend thinks it should be
100 while you think it to be rs 120.

Now require an objective way to conclude this since I cannot be speculating all the time. You
recommend investing, your friend recommend shorting the stock. So we create a financial model to
quantify our views on the basis of scientific application/results to come to a single value.

Implement 1234 in step 5

-PE Firm

A leveraged buyout (LBO) is a type of acquisition in which the buyer uses a significant amount of
debt financing to purchase a company, while contributing a smaller portion of their own equity capit
al. The assets of the acquired company often serve as collateral for the borrowed funds, and the com
pany’s future cash flows are used to repay the debt. LBOs are commonly executed by private equity
firms or management teams aiming to gain control of a company with minimal upfront investment.

Say tata bev is going to buy bisleri, it isn’t just investing but buying the entire company. So we’ll be
more interested in Enterprise Value.

Fairness Opinion document- A

very bulky exercise of
company our target company
with each peer company
individually and see how it’s
performing. Used in IB
To check if my ratios ain’t offtrack with those of industry’s. Say, Tesla has a plus of tech loaded cars so
it has a premium dd there.

Application by Users (not by developers) should be the motive, a user should understand it and his
purpose should be served.

Minimum
static (hard
quoted).

No AI

Can’t be used if the company has just started etc (D1= Future cashflow)

Difficult to come up with reasonable numbers for

everything in the formula. Negative no. in (). No. in 1 decimal, Eg: A calculates Ke by
CAPM, B calculates it by % till 2 decimal, , at 3 distance arbitrage pricing model.
LTGR (associated with Rf, GDP…subjective)

Have you considered

those assets that the
company has in all or
those that affect their
cash flows

The PEG ratio measures a stock's price relative to its earnings growth, helping investors assess whet
her it is overvalued, undervalued, or fairly priced.
 Session 3: Leftmost coln width= 1.15, # helps in navigating
Ctrl+down arrow, ctrl+[ gives you where the formula is picked from
LTM= Last 12 months
 Importance of Name Manager function: (ALT>M>C) To add a
value by not linking it with a different sheet cell.
Hard quoted values to be blue
 Vlookup: To ease finding data from large data in vertical rows
 To avoid red triangle mark at the top right corner of the cell,
use data validation fn
[Link], primeinvestors  Protect your sheet
Session 4 screener or capital iq or tradingeconomics
ROCE, Retained earnings %, Return on eq %, self sustained growth
1. [Link] > search for a company
rate, ICR are main 5 ratios to judge the financial health of the co.
2. download latest annual report by scrolling down, export to excel from the top

3. Download the relevant pages from the annual report (B/S, Cashflow, P/L), Convert pdf to excel

4. Expand the cash flow and copy from screener

5. After preparing the 3 statements> [Link]/excel> upload your excel file> search any company
and export to get its model (You have to copy paste the values of only the CASHFLOW & some
minor changes) (DO MAKE RANDOM CHECKS)

Imp of EBITDA- It’s a pure business earning that shows how efficiently you run business and it
doesn’t vary as per say different methods of dep used by companies, rate of interest on loan accrued
etc (means it doesn’t deviate as per accounting measures/practices used)

ROCE= EBIT/EQ+DEBT
Return on eq %= EAES/SHF (PAT-Pref div or Net Profit/Eq funds+reserves)
Key Definitions
Return on Equity (ROE) indicates how efficiently a company generates profit fro
m shareholders’ equity. It is calculated as Net Profit ÷ Shareholders’ Equity × 10
0. A higher ROE suggests that the company is effectively using investors’ funds t
Are working capital ratios o generate profits, but extremely high ROE may indicate excessive leverage rath
er than operational efficiency.
 Don’t distribute dividend Return on Capital Employed (ROCE) evaluates how efficiently a company uses a
If roce>wacc ll its capital, including both equity and debt, to generate profits. The formula is
 Self- sustained gr rate EBIT ÷ Capital Employed × 100, where capital employed is typically total assets
Earn and reinvest for your minus current liabilities or equity plus long-term debt. ROCE is particularly usef
Own growth (some startups ul for capital-intensive industries, as it reflects the company’s ability to reinvest
that’s why are bootstrapped) profits efficiently
=g=b*r (RE*Re)
 ICR= EBIT/Interest (High is better

If =1, co. is definitely loss making since ebit=int, nothing left for below sections

 Debtor turnover ratio- how much sale is rs1 debtor generating for me (sales/receivables)

AM=mean of all %sale growth

Bcz growth opp
in emerging ecy Stdev (fn)
is more
Similar issue with GM (no outliers are considered)

=rr(nper,pv,fv) or (last yr sales/Y1 sales)^(1/final

 -DCF Valuation

A project where there’s no growth,

no DCF but capital budgeting applies

Rf is the safe interest rate that

investor could have gotten
elsewhere on a safe bond

ERP is the eq risk premium i.e. risk

undertaken by investing in equity
and beta is the co. specific volatility Regression: y=a+bx where y is HUL returns and x is
as per overall mkt Nifty returns, b=slope=beta

Then we run a statistical regression, we get this

graph. No other slope except this slope line can have
minimum squared distance
Risk in eq (pov of co.)
Beta of mkt is 1. Mean reversion is a concept of beta
-No. of shares to influence drifting to adjust the co. beta to adjust it closer to
-Willingness to trade mkt beta=1

Any weights can be assigned (varies from co.)

ETFs Calc of beta 1. =slope 2. =covariance.s/variance.s

3. data analysis>toolpak>regression
Session 9: [Link]>historical data>choose frequency. We require only adjusted closing price
from the doc downloaded

Ctrl+Shift+L adds/removes filter Alt+D+F+S expands/shrinks filters

Shift+6 gives ^

Session 14: Relative Pricing:- Used for assets which are not cash generating. Eg: Monalisa painting
rs 10 L (holds value for ambani to show it off but not for me)

1. Comaparable companies 2. Precedent Transaction

Trading comps and precedent transactions are two main relative valuation methodologies used in fin
ance. Trading comps value a company by comparing it to similar public companies, while precedent t
ransactions value a company based on what acquirers have actually paid for similar companies in pas
t M&A deals.

DCF is 100% number crunching (the act of processing large data into more useful data). Drawback- It
is not based on market mood. It is highly complex.

Session 19: Value at Risk shows risk of losing money (time period, confidence level, loss amount). It
answers ‘what is the amount I will lose with 95% confidence over the next month.’
Session 20: Dupont analysis is used to measure profitability
of the co. by showing the reason of increase on return on
equity (pp,oe, lev). Inc in lev is a bad sign

-MOAT: competitive adv like pricing power (sin stocks),

operating efficiency=OE
You are 99% confident to lose 485rs on this stock -Economics profit= normal p/l profit + opp cost

-Max shareholder wealth

-PP=pricing power

-Inc of lev increases roe since cost of debt<cost of eq