Close

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. We will start the course by building a simple project finance model. The focus of this model will be to understand the different features present in Excel. We will also learn about some of the dos and don’ts of modeling. In the second model, we will be forecasting the financial statements of a company for which the assumptions will be given. In this model, we will learn how to balance a model, how to manage circularity in a model and how to value the entity after forecasting the financial statements. In the third model, we will build a valuation model from scratch. We will see how to build a completely new model. Our fourth model will be an advanced merger model. We will see how investment bankers build financial models here. Again, we shall be building this model from scratch. We will also spend one session in understanding how macros are used in Financial Models.

Expected Participants

You are required to be fairly conversant with finance. I expect that you know the basic features of Excel. I am not going to teach Excel in this course. 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. I will help you building almost 90% of the model in the class. The remaining 10% of the model will be left to you either as a quiz (to be submitted immediately after the class as an email) or as an assignment (to be submitted within 24 hours of the end of the class). In addition, there will also be an end term examination, where you will be required to build a model from scratch.

Your marks in the quiz or the assignment will be proportionately reduced for every class in which you are absent. Thus for example, if we take 3 sessions to build a model and you are absent on 2 of these 3 sessions, then you will get 1/3rd of the mark that you deserve in that component of evaluation (irrespective of the reason why you are absent from the class). No make-up assignment will be given. In case your assignment is similar to somebody else’, then both of you will get 0 in the assignment. These are individual assignments. You MUST attempt these assignments yourself.

Assignments/Quizzes: 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



Model 1: Building a Simple Project Finance Model

Sessions 1 to 5:

· Building Project Finance Model for Smokey Valley
o Do’s and Don’ts of Modeling
o Doing Date Calculations
o Managing Currency and Currency Units
o Estimating NPV, IRR, Payback, etc.
o Sensitivity analysis using Excel
o Scenario Manager
o Other sensitivity analysis features
o Using Macros in a Financial Model
o Building an Executive Summary for the Model
Model 2: Building a Simple Financial Statements Model

Sessions 6 to 9:

· Building a Financial Statements Model
o Balancing the Model
o Managing Circularity
o Valuing the Entity
o Building a Dashboard to summarize the valuation results

Model 3: Building a Valuation Model

Sessions 10 to 13:

· Building a Valuation Model
o Managing Consistency in Dates
o Building the different blocks for completing the financial statements
o Valuing the company using DCF and relative valuation model
o Preparing a Football-field chart to summarize the valuation results

Model 4: Building a Merger Model

Sessions 14 to 17:

· Building a Merger Model
o Building the Financial Model for the Acquirer and Target
o Building Sensitivity into the Model (as an input rather than as an output)
o Modeling the Synergy
o Preparing the Executive Summary
o Making the Model Print-Ready
Model 5: Building an Advanced Project Finance Model

Sessions 18 to 20:

· Building a Real Estate Model
o Building the Structure of the Model
o Building the different components of the Model
o Determining the DSCR of the project