Financial modelling
Understanding the basics of building a
financial model
Getting Data
©2018 Grant Thornton India LLP. All rights reserved.
Getting Relevant Data – Sources
• If company is listed:
• One can get annual reports and Quarterly results directly from edgar or
• Company’s websites or
• Refer www.google.com/finance or http://in.finance.yahoo.com/ for relevant company data
• If Company is making initial Public Offer:
• One can get relevant data through Edgar
©2018 Grant Thornton India LLP. All rights reserved.
Getting Relevant Data – edgar
©2018 Grant Thornton India LLP. All rights reserved.
Getting Relevant Data – edgar
©2018 Grant Thornton India LLP. All rights reserved.
Getting Relevant Data – edgar
For initial Public Offer
For listed company
©2018 Grant Thornton India LLP. All rights reserved.
Valuation
©2018 Grant Thornton India LLP. All rights reserved.
Case: Valuation of Manufacturing Company
Candidate Notes
• This case study is about “Camera Manufacturing Company”. This is a hypothetical situation and used
for instructional purpose only.
• Candidates are advised to create the models from scratch. Though keeping the time in mind,
students will be given pre-filled templates for the training.
• Please read the case carefully.
©2018 Grant Thornton India LLP. All rights reserved.
Financial Modeling Case Study
• There is a new sports camera company (CMC) which entered into the market 3 years ago. After 3
years, CMC have decided to file for IPO. They have done the ‘S1 filing’ for the same, below is the
extract of S1.
• Historical P&L
©2018 Grant Thornton India LLP. All rights reserved.
Financial Modeling Case Study
• Historical BS
©2018 Grant Thornton India LLP. All rights reserved.
Financial Modeling Case Study
• Historical BS
©2018 Grant Thornton India LLP. All rights reserved.
Financial Modeling Case Study
• CMC has two revenue streams and four kind of operating costs, as depicted by historical P&L.
• CMC has invested in machinery and equipment (PPE) in first and third year, which is depreciated
with SLM.
• Below is the crux of ‘Management Discussion and Analysis’ (MD&A):
• CMC is not planning to invest in CAPEX next 5 years.
• Big competitor is entering into the same category, because of which CMC will not be able to
maintain their sales volume. It might go slightly down in next two – three years before start
going up slowly. It will affect the ‘Accessories’ category heavily.
• CMC has planned to follow historical inflation for raising the prices and expect the same for
their expenses.
• CMC has invested heavily in R&D in the initial years and they would like to gradually decrease
the contribution to the same.
• There is one part where CMC is going to increase back the contribution to historical numbers
that is ‘Sales and Marketing’ because of increased competition.
• CMC has planned the target of Debt to Capital ratio to 0.5
©2018 Grant Thornton India LLP. All rights reserved.
Financial Modeling Case Study
Assignment:
• Identify key drivers of the revenue and cost.
• Prepare projected financial statements based on S1 filing.
• Prepare historical and projected Cash Flow statement.
• Based on the projected financial statements, build a DCF model to arrive at equity share value.
©2018 Grant Thornton India LLP. All rights reserved.
Agenda
• Understanding an integrated financial Model
• Summarizing financial statements
• Building Integrated Model – Financial Statements & Projections
• Building Integrated Model – Valuation
©2018 Grant Thornton India LLP. All rights reserved.
Integrated Model
All line items are linked to each other, making the model integrated and flexible
©2018 Grant Thornton India LLP. All rights reserved.
Integrated Model
Different parts of an integrated model:
1. Logically breaking model into different components (sheets)
2. Formatting sheet and numbers for easy intepretation
3. Linking cells for formulas and numbers across sheets to make it flexible
4. Using conditional formatting, iterative calculations (circular reference), sensitivity
analysis etc. to make model useful
©2018 Grant Thornton India LLP. All rights reserved.
Integrated Model
Steps to create an integrated model:
1. Getting historical numbers from S1/ 10k reports
• Applying the formulas to summarize
2. Reading research reports, MD&A etc. to understand the various drivers of P&L and BS
3. Identifying and calculating drivers for P&L and BS line items for historical years
4. Forecasting drivers for the line items of P&L and BS
5. Creating Revenue and cost build-up
6. Forecasting line items of P&L and BS
7. Creating Asset Schedule for CAPEX and Depreciation
8. Working on Debt Schedule for debt and interest
9. Calculating Equity, Retained earnings to complete PAT calculation
10. Creating Cash Flow statement – Indirect method
11. Understanding the concept of circular reference and its use in Financial Modeling
12. Valuation: Absolute v/s Relative
• Absolute Valuation: WACC, Terminal Value etc.
• Relative Valuation: Trading comps, Transaction comps
13. Sensitivity Analysis
©2018 Grant Thornton India LLP. All rights reserved.
Agenda
• Understanding an integrated financial Model
• Summarizing financial statements
• Building Integrated Model – Financial Statements & Projections
• Building Integrated Model – Valuation
©2018 Grant Thornton India LLP. All rights reserved.
Financial Statements
• Getting historical numbers from public data
Par_01_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Financial Statements
• Linking formulas to check the precision of numbers and our understanding
Par_01_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Agenda
• Understanding an integrated financial Model
• Summarizing financial statements
• Building Integrated Model – Financial Statements & Projections
• Building Integrated Model – Valuation
©2018 Grant Thornton India LLP. All rights reserved.
Drivers for P&L
• Identifying and calculating drivers of the historical P&L
Par_02_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Drivers for BS
• Work on current assets and current liabilities
Par_02_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Forecasting Drivers – P&L
• Calculating line items from forecasted drivers
Par_03_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Forecasting Drivers – BS
• Forecasting drivers of current assets and liabilities
Par_03_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Revenue Build-up
• Calculating P&L line items from forecasted drivers of P&L
Par_04_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Cost Build-up
• Calculating P&L line items from forecasted drivers of P&L
Par_04_CMC
©2018 Grant Thornton India LLP. All rights reserved.
BS – Current Assets and Liabilities
• Calculating BS: CA/ CL
Par_04_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Asset Schedule
• Working on Depreciation schedule
• Based on the depreciation policy of the company project the depreciation for each of the investments for each
of the years
• Calculating Gross and Net block
Par_05_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Debt Schedule
• Calculate debt repayment schedule
• Finding out the interest rate and interest payment
Par_06_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Equity
• Working on paid-up capital and Retained earnings
Par_06_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Cashflow Statement
• Cashflow statement: Direct and Indirect method
• CFO, CFI and CFF
Par_07_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Circular Reference
• Cash → PAT → Interest → Cash
• Iteration is a double edged sword
• Update Interest, cash
Par_08_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Valuation
• Valuation: Absolute v/s Relative valuation
• Absolute Valuation: DCF v/s DDM
• DCF:
• Equity Valuation
• Free Cash Flow to Firm (FCFF)
• Enterprise Valuation
• Free Cash Flow to Equity (FCFE)
Par_09_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Valuation
• Relative Valuation: Trading comps v/s Transaction comps
• Equity Valuation
• P/ E Multiples
• Enterprise Valuation
• EV / EBITDA Multiple
• EV / Sales Multiple
Par_10_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Sensitivity Analysis
• Using Data Tables for sensitivity analysis
Par_11_CMC
©2018 Grant Thornton India LLP. All rights reserved.
Questions
