Financial Analysis with Modeling in Excel

Teacher

Debi Prasad Satapathy

Category

Course Attendees

Still no participant

Course Reviews

Still no reviews

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 9

Financial Amortization

PV, PMT formulas

Session 10

Financial Analytics

Pivot Tables

Session 11

Scanario Analysis

Goal Seek

Session 12

Sensitivity Analysis

Goal Seek

Session 13

Business Valuation

NPV, IRR formulas

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

Our Main Teachers

Dr. Satapathy is Assistant Professor (Finance and Accounting) at CUTM. He holds a master’s degree in finance and control from Utkal university. He also has completed doctoral level fellow programme in management from NIFM Faridabad. He has more than 11 Years of teaching, training and research experience. He has worked at VIT university Bhopal and […]