Syllabus
What is Financial Analysis?
What is Financial Modeling?
Financial Planning
Why use Excel?
Financial Modeling
Purpose of a Financial Model
Input
Constraints
Output
Analysis of the Output
Action Plan based on Output
Excel Commands and Functions
Logical
Mathematical
Financial
Statistical
Financial Statement Analysis
Evaluating External Financing Requirement
Sales Policy
Purchase Policy
Solving Problems on Time-Value-of-Money and
Security Valuation:
Finding PV and FV
Finding NPV of Cash-flow Streams
Bond Valuation
Regression Analysis in Finance
Regression Using Excel
Understanding the Regression Output
Using Regression for Forecasting
Using Regression for Financial Analysis
Solving Capital-Budgeting Problem using Excel
Evaluating a Capital Investment Decision
Scenario Analysis
Sensitivity Analysis
Session 1
Intoduction to financial Modelling
Session 2
Introduction to basic excel function
Create, edit, and delete defined names for cells and ranges
• Paste a list of defined names as documentation
• Use defined names in formulas
• Add defined names to existing formulas
• Create valid rules for data entry
• Protect the contents of worksheets and workbooks
• Add, edit, and delete comments
Session 3
Introduction to Advance excel function
Create a worksheet group
• Format and edit multiple worksheets at once
• Create cell references to other worksheets
• Consolidate information from multiple worksheets using 3-D references
• Create and print a worksheet group
• Create a link to data in another workbook
• Create and print a worksheet group
• Create a link to data in another workbook
• How to edit links
• Create and use an Excel workspace
• Insert a hyperlink in a cell
Session 4
Working with Logical functions (IF, AND, and OR)
• Working with comparison operators such as <, <¼, ¼ <>, >, or >¼ to
compare two values
• Inserting calculated columns in an Excel Table
• Using structured references in formulas (Fully qualified and Unqualified)
• Nest the IF function
• Using the VLOOKUP and HLOOKUP functions (to find approximate and exact
match)
• Use the IFERROR function
• Use conditional formatting to highlight values
• COUNTIF, SUMIF, AVERAGEI
Session 5
Work with financial functions such as FV, PV, RATE, NPER, and PMT
Session 6
Determine a payback period
• Calculate a net present value (NPV) and an internal rate of return (IRR)
Session 7
Calculate a net present value (XNPV) and an internal rate of return (XIRR) AND MIRR
Session 8
Budget management
Pivot tables, Consolidate
Session 14
Financial Statement analysis
Session 15
Case Study
Developing a Regression Model Excel Function(s)
1 Explore potential relationships Pivot tables and charts
2 Create two-dimensional scatterplots Charts: scatterplots
3 Prepare a correlation table Analysis tool pack: correlation
4 Determine dependent and independent
variables
(Based on the analyst’s experience)
5 Create a single-variable regression Analysis tool pack: regression
6 Create a multivariable regression Analysis tool pack: regression
7 Optimize the predictive model Based on p-values
