FINANCIAL MODELLING
PROFESSIONAL GUIDE
Pehle Business Samjho. Phir Data Lo. Phir Model Banao.
Document 1 Document 2
Financial Modelling Banane Se Pehle Assumptions Kin Cheezon Ka Lete Hain
Kya Karta Hoon? Aur Kyun?
Step-by-step practical process for building Deep dive into 6 core assumption categories
accurate financial models — from business — Revenue, Costs, Working Capital, Assets,
understanding to sanity checks. Financing, and Macro.
"Model banana 20% kaam hai — Research aur Assumptions 80% kaam
hai"
Target Audience Finance Students, Analysts, Business Professionals
Language Hinglish (Hindi + English) — Practical & Conversational
Topics Covered 9-Step Modelling Process + 6 Assumption Categories
Financial Modelling Guide Page 1 Professional Reference
DOCUMENT 1 — FINANCIAL MODELLING BANANE SE PEHLE KYA
KARTA HOON?
Ek Professional Ka Practical Step-by-Step Process
Mera Practical Process
Research Understand Structure Build Check
STEP 1 BUSINESS SAMJHO — 'Pehle Insaan Bano, Phir Analyst'
Model banane se pehle yeh 5 sawaal khud se poocho:
1 Yeh business kaise paise kamata hai?
2 Sabse bada cost kya hai?
3 Kya grow kar raha hai ya shrink?
4 Competition kaisa hai?
5 Koi bada risk hai kya?
Agar business samjha nahi toh assumptions galat honge Galat assumptions = Galat model
= Galat decision
STEP 2 DATA COLLECT KARO
Yeh sources use karta hoon:
Data Type Source
Annual Reports BSE / NSE website
Industry data IBEF, Statista
Financial Modelling Guide Page 2 Professional Reference
Competitor data Same BSE/NSE
Macro data RBI website
Analyst views Motilal, HDFC Securities
Golden Rule: Minimum 3 saal ka data chahiye hamesha
STEP 3 EXCEL STRUCTURE PEHLE BANAO
Seedha numbers mat daalo — pehle structure banao:
Sheet 1 Assumptions Sabse important — poore model ki neenv
Sheet 2 Income Statement Revenue se Net Profit tak
Sheet 3 Cash Flow Statement Operating, Investing, Financing
Sheet 4 Balance Sheet Assets = Liabilities + Equity
Sheet 5 Ratios & Analysis Performance metrics
Sheet 6 Charts & Dashboard Visual summary
Ghar banane se pehle blueprint banate hain — Model mein bhi blueprint pehle!
STEP 4 ASSUMPTIONS SHEET SABSE PEHLE BHARO
Yeh poore model ki neenv hai. Assumptions sheet mein yeh sab likhta hoon:
REVENUE ASSUMPTIONS COST ASSUMPTIONS
→ Growth rate = 20% → COGS % = 58%
→ Price per unit = Rs.500 → Salary hike = 10% per year
→ Volume growth = 15% → Rent escalation = 5% per year
Financial Modelling Guide Page 3 Professional Reference
WORKING CAPITAL FINANCING
→ Debtor days = 45 → Interest rate = 10%
→ Creditor days = 30 → Tax rate = 25%
→ Inventory days = 30 → Capex = Rs.10 Cr per year
GOLDEN RULE: Har assumption ek hi jagah hona chahiye. Baaki sheets usi cell ko refer
karengi — ek jagah change karo, poora model update ho jaaye!
Financial Modelling Guide Page 4 Professional Reference
STEP 5 INCOME STATEMENT BANAO
Assumptions sheet se links lagao — directly type mat karo:
Revenue = Last year x (1 + Growth Rate assumption)
COGS = Revenue x COGS % assumption
Gross Profit = Revenue - COGS
... aur neeche jaate raho
Har cell mein formula hona chahiye — hardcoded
Key Rule:
numbers nahi
STEP 6 CASH FLOW BANAO
Net Profit Income Statement se uthao
+ Depreciation Non-cash expense — wapas add karo
- Working Capital Changes Debtors/Creditors/Inventory changes
- Capex New asset purchases ghataao
= Free Cash Flow Company ka actual cash generation
STEP 7 BALANCE SHEET COMPLETE KARO
Fixed Assets = Last year - Depreciation + Capex
Cash = Cash Flow se aaya closing balance
Equity = Last year + Net Profit - Dividend
Assets = Liabilities + Equity? Haan ✓ Model sahi hai
Nahi ✗ Koi galti hai, dhundho
STEP 8 SANITY CHECK KARO
Financial Modelling Guide Page 5 Professional Reference
Model complete hone ke baad yeh checklist:
Sanity Check Checklist
■ Balance sheet balance ho rahi hai?
■ Cash kabhi negative toh nahi?
■ Margins industry ke hisaab se realistic hain?
■ Koi #REF ya #VALUE error toh nahi Excel mein?
■ Assumptions documented hain?
■ Teen scenarios hain — Bull / Base / Bear?
STEP 9 SCENARIO ANALYSIS KARO
Scenario Description Key Question
Base Case Jo hona chahiye realistically Most likely outcome kya hai?
Bull Case Sab accha hua toh Best case mein kitna upside?
Bear Case Sab bura hua toh Kya company survive karegi?
Bear case mein bhi company survive kar rahi hai? Haan = Safe | Nahi = Risk bahut zyada
hai
MASTER CHECKLIST — HAR MODEL MEIN
Before Building While Building After Building
■ Business samjha? ■ Assumptions ek sheet mein hain?
■ Balance sheet balance hui?
■ 3 saal data collect kiya? ■ Hardcoded numbers toh nahi daale?
■ Cash negative toh nahi?
■ Industry benchmarks dekhe? ■ Har formula linked hai? ■ Scenarios banaye?
■ Structure ready hai? ■ Sanity check kiya?
Financial Modelling Guide Page 6 Professional Reference
"Seedha Excel mat kholna — yahi sabse badi galti hai"
Financial Modelling Guide Page 7 Professional Reference
DOCUMENT 2 — ASSUMPTIONS KIN CHEEZON KA LETE HAIN AUR
KYUN?
Future Ki Uncertainty Ko Educated Guess Se Replace Karo
Assumption = Future ke baare mein educated guess Educated isliye — kyunki Random
nahi hota Research + Logic + Data se aata hai
Basically 6 Cheezein Hain Jinka Assumption Lete
Hain
1 Revenue 2 Costs 3 Working Capital
Assets &
4 Depreciation 5 Financing 6 Macro
CATEGORY 1 REVENUE ASSUMPTIONS
Kya Assume Karte Hain Kyun Lete Hain Kahan Se Aata Hai
→ Revenue Growth Rate Revenue poore model ki Past 3 saal ki growth average
foundation hai
→ Price per unit Industry growth rate compare
Revenue sahi nahi toh baaki karo
→ Volume (units) sab galat
Management guidance dekho
→ New products/markets
Conservative rakho — thoda
kam assume karo
Example Calculation:
FY22 growth = 18% | FY23 = 22% | FY24 = 20%
Average = 20%
Forecast Assumption = 18% (thoda conservative)
Financial Modelling Guide Page 8 Professional Reference
CATEGORY 2 COST ASSUMPTIONS
Kya Assume Karte Hain Kyun Lete Hain Kahan Se Aata Hai
COGS: Raw material % Costs control karna business Historical margins analyze
ka sabse bada kaam hai karo
COGS: Labour cost %
Revenue badha ke nahi — Competitor margins
OpEx: Salary hike % costs control se profit hota hai benchmark karo
OpEx: Rent + escalation Inflation rate + cost escalation
Marketing % of revenue Fixed vs Variable cost samjho
Example:
COGS historically = 55-60% of revenue
Industry average = 57%
Assumption = 58% (middle ground)
Salary hike historically = 8-10%
Assumption = 10% per year
Financial Modelling Guide Page 9 Professional Reference
CATEGORY 3 WORKING CAPITAL ASSUMPTIONS
Metric Matlab Typical Value
Debtor Days Customer kitne din mein pay karta hai 45 days (B2B typical)
Creditor Days Hum supplier ko kitne din mein pay karte hain 30 days
Inventory Days Stock kitne din ka rakhte hain 30 days
Yahi decide karta hai — Profit hua par cash aaya ya nahi! Company A ne Rs.100 Cr ka
maal becha, customers ne 90 din baad pay kiya = 3 mahine tak cash nahi — business ke
liye loan lena pada!
Formula: Debtors / Revenue x 365 = Debtor Days
B2B Business = 60-90 days (normal)
Retail Business = 0-15 days (normal)
CATEGORY 4 ASSETS & DEPRECIATION ASSUMPTIONS
Kya Assume Karte Hain Kyun Lete Hain Kahan Se Aata Hai
→ Capex (new machinery) Capex seedha cash flow pe Management guidance (annual
asar karta hai report)
→ Depreciation Rate %
Bada investment = Cash kam Historical capex pattern
→ Asset Life (years) hoga temporarily
Industry norms: Manufacturing
= zyada IT = kam
Capex ka asar teeno statements pe padta hai: P&L; (Depreciation) | Balance Sheet (Asset
value) | Cash Flow (Capital expenditure)
CATEGORY 5 FINANCING ASSUMPTIONS
Item Matlab Typical Value
Interest Rate Loan par kitna % lagega 10-12% (India typical)
Financial Modelling Guide Page 10 Professional Reference
New Debt Aur loan lenge kya? Based on capex needs
Repayment Purana loan kab bhuktenge? Loan schedule se
Dividend Profit ka kitna % denge? 0-30% (varies)
New Equity Naya share issue karenge? If needed for growth
Example Impact:
Loan = Rs.100 Cr | Interest Rate = 10%
Interest per year = Rs.10 Cr
-> Directly P&L; mein expense
-> Net Profit Rs.10 Cr kam ho jaayega
CATEGORY 6 MACRO ASSUMPTIONS
Factor Matlab Source India Typical
Tax Rate Kitna % tax lagega Income Tax Act India = 25-30%
Inflation Costs kitne % badhenge RBI projections 4-6% typical
Exchange Rate Import/export businesses ke liye RBI, Bloomberg Varies
GDP Growth Overall economy kaisi rahegi IMF, World Bank 6-7% India
Financial Modelling Guide Page 11 Professional Reference
COMPLETE SUMMARY — SAB ASSUMPTIONS EK TABLE MEIN
Assumption Kya Assume Karte Hain Kyun Zaroori Hai
Revenue Growth rate, price, volume Model ki foundation
Costs COGS %, salary, rent Profit decide karta hai
Working Capital Debtor, creditor, inventory days Cash flow decide karta hai
Assets Capex, depreciation Long term investment
Financing Interest rate, debt, dividend Capital structure
Macro Tax, inflation, forex Baahri factors
"Har assumption ke peeche ek solid reason hona chahiye"
Q: "Yeh 20% growth kyun assume kiya?"
A: "Kyunki past 3 saal average 22% tha, industry 18% grow kar rahi hai, isliye 20%
conservative aur logical hai"
Assumption defend kar sako — tabhi strong model hai. Random number daala — toh
model ki koi value nahi.
Jitna Utna Better Jitna Better Utna Better Jitna Better Utna Better
Research Assumption Assumption Model Model Decision
Financial Modelling Guide Page 12 Professional Reference