Close
FinMod-P07
(PGDM 2007-09 : Term-VI)

Financial Modeling Using Excel
(Faculty: Prof. Pitabas Mohanty)
Email: pitabasm@xlri.ac.in


Introduction

Modeling 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 we will also emphasize 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
· Building models in different areas of finance including investments, corporate finance and derivatives
· Identifying and controlling the key sensitivities with advanced spreadsheet simulation
· Understand how risk can be built into the model to enhance decision making process

Pedagogy

The course will be covered with a mixture of demonstration of practical examples and assignments.

Expected Participants

The participants are required to be fairly conversant with finance. It is expected that they have opted for SAPM, FIS, O&F, and Project Financing among others as their electives. Those participants who are reasonably comfortable with excel will get the maximum benefits out of this course.

Evaluation


In the course, we will be building about 4-5 models. The students are supposed to submit the completed models as group assignments a day after the class is over. If a student is absent from the class then he will get no credit for the assignment. Thus for example, if we take 3 sessions to build a model and a student is absent on 2 of these 3 sessions, then he will get 1/3rd of what his/her group gets (irrespective of the reason why he/she is absent from the class).
Assignments: 60 Marks
End Term: 40 Marks

References


· Practical Financial Modeling, by Jonathan Swan, Elsevier Publication
· Mastering Financial Modeling by Alstar Day, Prentice Hall Publication
· Financial Modeling by Simon Beninga, MIT Press
· Advanced Modeling in Finance using Excel and VBA by Mary Jackson and Mike Staunton, Wiley Finance
· Spreadsheet Modeling in Corporate Finance by Craig W Holden, Prentice Hall
· Spreadsheet Modeling in Investments by Craig W Holden, Prentice Hall
· Microsoft Excel 2003 Bible by John Walkenbach, Wiley Dreamtech
· Excel 2002 Power programming with VBA by John Walkenbach, Wiley Dreamtech
· Excel 2002 VBA Programming with XML and ASP by Julitta Karol, BPB Publications


Content



The course has been divided into the following four modules.

Module 1: Introduction to Modeling Using Excel (5)

· Understanding the basic features of excel (1)
o Introduction to the basic features of Excel · Understanding advanced features of excel (1)
o Database functions in excel
o Creating basic and advanced charts
o Using forms and control toolbox
o Understanding finance functions present in excel · Building Models Using Excel (2)
o Do’s and Don’ts of Modeling
o Building a simple project appraisal model for Smokey Valley Limited · Sensitivity analysis using Excel (1)
o Scenario Manager
o Other sensitivity analysis features
Module 2: Spreadsheet Modeling in Corporate Finance (6)

· Forecasting Financial Statements (1)
o Discussion of the Case on OLP Limited
o Forecasting financial statements by putting capital structure and dividend constraints

· Excel in Project Appraisal (2)
o Discussion of Case on Dubai Sands Limited

· Excel in Valuation (2)
o Valuation of Emaar

Module 3: Spreadsheet Modeling in Investments (2)

· Excel in Derivatives (2)
o Black and Scholes Model in Excel
o Greeks in Excel
· Excel in Portfolio Management (2)
o Creating Efficient Frontier

Module 4: Introduction to VBA (4)

· Understanding Subroutines and Functions and building simple financial models using subroutines and functions (2)
o Recording and editing macros
o Subroutines and functions
o Decision rules
o Message box and input box
o Debugging · Designing advanced financial models using VBA (2)
o User forms
o Other advanced Features
o Actual Model Building (The figure in the parenthesis represents the number of sessions)
Created By: Bijoy Kar on 11/10/2008 at 12:42 PM
Category: PGP-II Doctype: Document

...........................