ch nu mba

Financial Modelling and Valuation Techniques

COURSE HANDOUT

Course Code: FIN 601FINANCIAL MODELING AND VALUATION TECHNIQUES

Course HandoutNumber of Credit-3

Academic Year: 2014 -15

Term V

of 9


Course-in-charge Dr. Ruzbeh J BodhanwalaContact Information: [email protected] Prerequisites Basic knowledge of Management Accounting, Advanced Financial

Management, Basic Excel, Basic QTEvaluation Scheme Mid Term Exam- Online 20

Project – Modelling a company 20Presentation and report on revenue drivers 10Surprise Quiz -1 paper and pen 10End Term Exam- Online 40

Pedagogic tools Lecture method Excel Lab Cases

of 9


Course DescriptionThe course aims to develop the capability of financial modelling using excel and using it to find solutions to business problems and for decision making. Modelling techniques for accurate financial forecasting are used in many areas of finance, such as derivatives, valuation, project evaluation, deal structuring, portfolio management and the like. In the course, the participants will learn the model building skills required to build powerful models in finance with the help of excel. There are many features of model building that are common irrespective of the final model that one intends to build. In the course the emphasis is on the different model building skills that one should have irrespective of the final use that one is going to make of it. By the end of the course the participants should be better able to understand the basic and advanced features of excel; understand how to build models in excel to suit one’s purpose; build models in different areas of finance including investments, corporate finance and derivatives; identify and control the key sensitivities with advanced spread-sheet simulation; understand how risk can be built into the model to enhance decision making process.

Learning Outcomes:

Knowledge To develop the understanding of financial modelling using excel and using it to find solu-

tions to business problems and for decision making.(K1) To understand modelling techniques for accurate financial forecasting for valuation. (K2) To understand the interlink- age between financial statements and other schedules used in

financial statement modelling (K3) To develop a working business model for equity valuation for a minimum of 2 companies

(K4)

Skills Ability to forecast financial statements and apply various valuation techniques. (S1) Ability to apply Monte Carlo simulation techniques in order to analyse all dimensions of

forecasting and valuation (S2) Ability to apply tools for sensitivity & scenario analysis (S3) Capability to use dynamic charting tools in Excel.(S4)

of 9

Financial Modelling and Valuation Techniques S

.No.

Dat

e Topics Learning Outcome

Pedagogy Pre read-ings/ Case Study

1 Financial Functions on excel

Handling dates S1 S2 Lec+Excel Ch-1 &33Scenario Analysis

of 9


What-If

Sensitivity

Vlookup, Offset, Match

Dynamic Charting With Automated Inputs :

2 Do Goal Seek S1 S2 Do Ch-1 &33Auditing Toolbars

Watch Windows

Basic Macro

Handling Circular Reference

Audit Functions

3 Do Grouping Functions Do F1 excel resourcesChoose Function

Range Naming Techniques

Calculate Enable/Disable Option

4 Protecting Wk.

Filters (To Display Selected Queries) S1,S2 ,S3

Do DO

Data Input Validation

Data Tables For Sensitivity Analysis

5 Do Basics Of Model Building On Excel Spread sheets

S1 S2 Do Ch-3

Designing Model

Building For Safety

Testing A Model

6 Choosing Appropriate Deals K1 K2 Do Ch-3

Discuss Strategic Rationale

7 Do Regulatory Roadblocks/Challenges K3 & K2

Do

Control Premiums, Synergies Ch-3

8 Revenue drivers for various industries K3 & K2

Do Ch-4

9 Do Revenue drivers for various industries K3 & K2

Ch-4

10 Cost drivers for various industries K3 & K2

Do Ch-4

11 Do Cost drivers for various industries K3 & K2

Do Ch-4

12 Do Basis of Debt payment schedule K3 & K2

Ch-30

13 Do Building a debt payment schedule; interaction with minimum cash balance ;

K3 & K2

Do Ch-30

Debt revolver

14 Bldg Tax Sch.

Deferred assets and liabilities; K3 & K2

Do Notes

Effective and Marginal tax rate

15 DO Forecasting DTA and DTL K3 & Do Notes

of 9


K216 Bldg WC

Sch.Determination of WCR; K3 &

K2Do Notes

Key ratios used

17 DO Building a WC schedule Do Ch-4

K(3) & K(2)

18 Bldg As-set Sch.

Basics of building asset schedule; K3 & K2

Do Ch-4

Calculation of Depreciation & its interaction with Capex

19 Bldg As-set Sch.

Building an Asset schedule K3 & K2

Do Ch-4

20 Bldg In-come st.

Integrating with BS and Cash flow statement K3 & K2, K4

Do Ch-4

21 Bldg IS Shareholder’s Equity Schedule K3 & K2& K4

Do

Shares Outstanding Schedule Ch-4

22 Bldg BS Balance Sheet, Income Statement And Cash Flow Adjustments

K3 & K2 & K4

Do Ch-4

23 FCFF Mod.

DCF Model For Valuation K2 & S2 Do Notes

PE Based Modelling

24 FCFF Mod.

PEER Modelling K2 & S2 Do Notes

Weighted Pricing

SOTP & Waterfall method

25 Do Case study K1-4& S1-4

Do Nestle AR


Lecture +Excel Lab + Case study

Nestle AR

27 EVA Mod.

Case study K1-4& S1-4

Do Nestle AR


Do Nestle AR

29 Op. Pri. for firm valuation

Case study K1-4& S1-4

Do Ch-16

30 Do Case Study K1-4& S1-4

Do Ch-16

of 9


Text Book(s):1. Financial modeling-Simon Beninga, MIT Press2. Investment Valuation: Tools and Techniques for determining the Value of Any Asset-

Aswath Damodaran3. Financial Valuation: Application and Models, 3rd Edition- James Hitchner

Reference Books:1. Valuation measuring and managing the value, 3rd Ed., Copeland, Wiley publication.2. Business Analysis and Valuation Using Financial Statements: Text and Cases, Paul M.

Healy and Krishna G. Palepu3. Financial management using excel spreadsheets, R J Bodhanwala, Taxmann Publication

Newspapers/ Journals/ Magazines* Economic Times, Business Line, Business Standard* Journal of Finance * Business India, Business Today, Capital Market

Web resources:www.damodaranonlinewww.edupristine.comwww.wallstreetprep.comwww.wallstreetmojo.com

Project

of 9

http://www.wallstreetmojo.com/

http://www.wallstreetprep.com/

http://www.edupristine.com/

http://www.damodaranonline/


Project work will be announced in the classRubrics

Level of Achievement

Project Report Viva

Exemplary •Addresses the issues.•Presents arguments in a logical order.•Uses acceptable style and grammar (no errors).•Backs conclusions with data and warrants

Excellent answers to viva questions

Absolute clarity of concepts

Very Good •Does not address the issue explicitly, although does so tangentially.•States a relevant and justifiable presentation. •Presents arguments in a logical order.•Uses acceptable style and grammar {one or two error(s)}.

Very good answers to viva questions

Very good clarity of concepts

Good •States a relevant and justifiable presentation. •Presents arguments in a logical order.

Satisfactory answers to viva questions

Satisfactory level of conceptual clarity

Needs Improvement

•Does not address the question.•States no relevant answers.•Indicates misconceptions.•Is not clearly or logically organized.•Fails to use acceptable style and grammar (two or more errors).

Lacks accuracy of answers Lacks conceptual clarity

Exposed Lacks all attributes of a satisfactory report

Lacks all the attributes of satisfactory knowledge level

Plagiarism

of 9


We are committed to upholding standards of academic integrity and honesty. Plagiarism in any form is unacceptable and will be treated seriously

Make up Policy There won’t be any make-up for any assessment component missed by the student unless it has been missed due to any medical emergency or any tragic event connected with immediate family member(s).

Consultation HoursThe students can meet the Course-in-charge on matters related to the course being taught on all days he is available on campus after taking prior appointment through email. Instead of personal meetings, they can also request responses only through emails. Phone calls should be avoided except in cases of emergency. Course-in-charge will try to respond to the best of his ability.

Grading Policy A composite score will be prepared after consolidating the marks obtained in all assessment components and the composite score will be converted to letter grades, viz. A, B, C, D and E as per university policy. The grading system followed is Relative Grading and normally it is centred around the mean.

of 9

ch nu mba

Documents