Financial Modeling Ravi Shukla Whitman School of Management - - PowerPoint PPT Presentation

Financial Modeling Ravi Shukla

Whitman School of Management Syracuse University rkshukla@syr.edu | +1 315-443-3576 Financial Management Association 2019 Annual Meetings New Orleans October 24, 2019

Synopsis

• Students learn to build fool-proof, interactive, user-friendly, well-

documented financial models for a “user” using Microsoft Excel.

• Fool-proof: The models should be built on correct logic, taking all
• r as many scenarios as feasible. Data validation and consistency

checks should ensures that the values that the user is allowed to enter do not break the model.

• Interactive: Model results should change in response to data entered
• r buttons pressed by the user.
• User-friendly: Uncluttered, consistent layout. Helpful features such

as navigation aids, prompts and feedback.

• Well-documented: User Guide to explain to the user what the model

does, how it works, how to use it and how to interpret the results. Text-boxes and cell comments for context-sensitive help.

Catalog Description and Learning Objectives

• Catalog Description: Build models for financial statement analysis, val-

uation, capital budgeting, capital structure, portfolio selection, interest rate risk, option valuation, and other areas of finance using a computer tool such as Microsoft Excel.

• Learning Objectives:
• 1. break down a financial problem into manageable pieces
• 2. build interactive, fool-proof and well-documented financial models
• 3. perform what-if analysis
• 4. incorporate subtler aspects of finance in your analysis
• 5. use advanced techniques in Excel and VBA
• Prerequisites: Principles of finance, corporate finance, investments

History

• Offered for the first time in spring 1996 as “Computer Based Models.”

Revived as “Financial Modeling” in spring 2004. Has been offered at least once a year since spring 2006 to undergraduate or graduate stu- dents.

• Stable since 2016: No significant change to the course design (individual

project details and instructions get updated continually). Same seven projects used during the semester. Projects are completed in groups. An in-class final exam is used to test students’ individual knowledge and skill.

• Debt of gratitude to the following books which helped in the develop-

ment of the course:

• Simon Benninga, Financial Modeling.
• Chandan Sengupta, Financial Modeling Using Excel and VBA.
• Mary Jackson and Mike Staunton, Advanced Modelling in Finance

using Excel and VBA.

Current Course Design

• Seven two-week projects: Two-stage dividend discount model (0%),

capital budgeting under constraints (5%), optimal capital structure (5%), financial forecasting and equity valuation (15%), bond valuation and interest rate risk (15%), financial planning for retirement (20%),

• ptimal portfolio selection (20%).
• Projects are completed in randomly created groups of two or three

students at the start of each project. A student works with another student only once in the semester.

• Anonymous peer reviews are conducted after each project and they

do impact course grades.

• In class individual final exam (20%).

Projects

• Similar to cases: Analyze the given information to arrive at results and
• conclusions. The analysis is in the form of an interactive model.
• Given information about the market conditions, the risk of the stock

and dividends in the short run and growth rate in the long run calcu- late the value of the stock. Perform sensitivity analysis with respect to the beta and growth rate. The length of the short run is set by the user (3 to 5 years).

• Given information (life, beta, cash flows) about a set of capital in-

vestment projects and budget and risk constraints, determine the

• ptimal set of projects to accept. How would you modify the pro-

cess if the user doesn’t believe in CAPM but uses risk classifications and hurdle rates.

• Given a client’s age, salary, anticipated retirement age and life span,

anticipated social security benefits, asset allocation, determine the minimum required saving rate to maintain a standard of living.

Project Instructions

• 25–40 pages in length.
• Start with background information and theory related to the project.
• Provides step-by-step instructions to build the model. Supplemented by

my videos. Links to the videos are embedded in the project instructions.

• For each project, students can view a user experience video.

Other resources are also available on the course Blackboard site.

• Students are required to extend the basic idea of the project by im-

plementing innovations to earn the last 10 points. A list of suggested innovations is provided. Students receive minimal help for these inno-

• vations. Innovations are usually in the form of a switch. For example,

to generate random variables from a normal distribution (the base case)

• r user specified probability distribution (extension).
• Students are given a set of targets they should try to meet before the

start of each class to stay on course.

Typical Classes

• Four 90-minute class meetings per project in a 30-seat, computer class-

room.

• Class 1: Presentation of the previous project by a randomly selected
• group. Brief explanation of the project on which the students will work
• next. New groups are announced, with music (“Change Partners” by

Crosby, Stills & Nash). Students sit with new groups (groups are as- signed specific seats). Groups work on the projects, following project

• instructions. The TA and I walk around helping students.
• Classes 2 through 4: The class starts with a 5-10 minute description of

some aspect of the project (most students don’t pay attention; they are busy working on the project). After that, the students work and the TA and I walk around the class and help.

Typical Classes (Contd.)

• Students have to put in another 20 hours outside of the classes over

the period of two weeks to finish the project. Most students put in this time the weekend before the project is due.

• In every class, I take attendance using finance or Excel based question

administered using Kahoot!. The question is related to the current project; specifically to what the students would be working on if they are keeping up with the targets. Examples:

• =EDATE(TODAY(),-6) would give a date (a) six months before

today (b) six days before today.

• Unleveraged beta refers to (a) beta of assets (b) beta of stock.

Project Grading Sample Student Model

• Submission (2 points)
• Layout (8 points)
• Documentation (10 points)
• Correctness (70 points). 10 of these points are used for data validation

and consistency check.

• Innovations (10 points)

Instructions for each project include a grading rubric that helps the students understand how the project will be graded and helps me in grading. I provide feedback and score using the grading rubric. (Technical note: Feedback is created using an Excel spreadsheet and MS Word MailMerge.)

Final Exam

• Students are asked to study a chapter from a finance book they have

used in one of the prerequisite courses. This becomes the basis for the model.

• At the exam time, they are given a small project that, in my opinion,

they should be able to complete in two hours. I do adjust the scores if no student score sufficiently high.

• Students are told to build the model along the lines of what they have

done during the semester. They are also told to provide three cell comments or text boxes as documentation.

• Exams are open book, open notes, open web. The students can consult

anything they need to except another human being.

Other Features

• Each project ends with a group selected randomly making a pitch for

the project to their peers.

• For each project, I select one group randomly and ask them to visit my
• ffice for a general non-technical discussion of the model.

Why Do Students Like The Course? (They Really Do!)

• Minimal lecturing by the professor. They see the class as a lab.
• They get Excel to do things they didn’t think it could do. Examples:
• dropdown boxes
• conditional formatting to make cells visible/invisible
• links to external websites
• VBA to navigate within the model, to backup and restore data, to

download data from Yahoo! Finance, US Treasury, etc. to run the solver to find optimal solutions, to run Monte Carlo with “dynamic graphs.”

• They see applications of concepts and formulas from their other courses.
• They apply math concepts using Excel: Linear interpolation, curve fit-

ting, bootstrapping, ad-hoc probability distribution, Cholesky decompo- sition, Newton Raphson, cubic splines, etc.

• Getting to work with and learn from smart classmates.

What They Don’t Like About It

• Too much work. Too fast paced. No time to take a breather.
• Having to work with new group every two weeks.
• Having to work with students who are not serious, do not know their

stuff, do not do their share of work.

Final Thoughts

• Surprises to me: Students need to consult the web for CAPM (even after

using it in four projects; even some of the better students), perpetuity formula, WACC, Modigliani-Miller, etc.

• It takes some cajoling to get students to work cooperatively as teams.
• There is some freeloading. But grades of freeloaders suffer at the end

because (a) They don’t learn the skills and perform poorly on the indi- vidual, in-class exam, and (b) their grade gets reduced because of poor peer evaluation (I send out average peer scores to all students after Project # 4 so they know where they stand).

I welcome your thoughts. Thank You! Ravi Shukla (rkshukla@syr.edu)