FIN513 Financial Modeling Module 4,

Page 1 of 7

FIN513

Financial Modeling

Module 4, 2015-2016

Course Information

Office: PHBS Building, Room 752

Phone: It is best to contact me via email. Email: [email protected] Office Hour: Mondays 11:00-12:00 Teaching Assistant: Hikmat Alihonov Phone: 13662262848

Email: [email protected] Classes:

Lectures: Monday & Thursday 13:30 - 15:20 Venue: PHBS Building, Room 401

1.

Course Description

1.1 Context

Course overview:

This course is intended for the student who wishes to learn how to utilize financial theory in real world applications. The course is practical in nature. Upon completion of the course, the student will be fluent in both Excel as well as financial modeling. Such fluency will position him/her very well for essentially any financial job. The student will also have a nice tool kit of many real world financial models across a very broad range of topics. This combination of fluency of financial modeling and portfolio of models will prove invaluable during both interviews with potential employers as well as execution of finance-related employment tasks. Prerequisites:

Asset Valuation Theory (AVT). Also, the course will be simpler if the student has previous exposure to Excel and to financial statement analysis. Nonetheless, the first lecture will be used to introduce the basics of Excel, including Excel’s basic financial functions. Additionally, the professor will spend a few minutes at the beginning of many lectures reviewing the relevant financial concepts to be modeled that day. However, a student who has NOT completed AVT will not find these brief reviews sufficient. Thus, AVT is a definite pre-requisite.

1.2 Textbooks and Reading Materials

Textbook

Page 2 of 8 ISBN-13: 860-1401358411; ISBN-10: 0262027283

Chandan Sengupta, “Financial Modeling Using Excel and VBA”, 2nd Ed., 2010, Wiley Finance, ISBN-13: 78-0471267683; ISBN-10: 0471267686

Recommended Readings

Michael Rees, “Financial Modelling in Practice”, 2008, Wiley Finance, ISBN: 978-0-470-99744-4. Mary Jackson and Mike Staunton, “Advanced Modelling in Finance using Excel and VBA”, 2001, Wiley Finance, ISBN-13: 978-0-471-49922-0.

John Charnes, “Financial Modeling with Crystal Ball and Excel”, 2012, Wiley Finance, ISBN 978-1-118-17544-6.

Simon Benninga, “Principles of Finance with Excel”, 2006, Oxford University Press, ISBN-13: 978-0-19-530150-2.

Isaac Gottlieb, “Next Generation Excel, Modeling in Excel for Analysts and MBAs”, 2010, John Wiley and Sons, ISBN: 978-0-470-82473-3.

2.

Learning Outcomes

2.1 Intended Learning Outcomes

Learning Goals Objectives Assessment

1. Our graduates will be effective

communicators.

1.1. Our students will produce quality business and research-oriented documents. 1.2. Students are able to professionally present their ideas and also logically explain and defend their argument.

2. Our graduates will be skilled in team work and leadership.

2.1. Students will be able to lead and participate in group for projects, discussion, and presentation.

2.2. Students will be able to apply leadership theories and related skills. 3. Our graduates will be

trained in ethics. 3.1. In a case setting, students will use appropriate techniques to analyze business problems and identify the ethical aspects, provide a solution and defend it.

3.2. Our students will practice ethics in the duration of the program.

4. Our graduates will have a global

perspective.

4.1. Students will have an international exposure.

5. Our graduates will be skilled in problem-solving and critical thinking.

5.1. Our students will have a good

understanding of fundamental theories in their fields.

5.2. Our students will be prepared to face problems in various business settings and find solutions.

5.3. Our students will demonstrate competency in critical thinking.

2.2 Course specific objectives

2.3 Assessment/Grading Details

Assessment task Weighting

Professor’s Subject Evaluation 10%

Page 3 of 8

Individual Final Exam Score 50%

Total 100%

*Group Peers’ Subjective Evaluation: Each student will self-select into groups of five or six students. (The professor will assign any student to a group who chooses not to self-select.) Evaluations from each student’s group peers will be done during the final week of the module. NO HUMAN BEING OTHER THAN THE PROFESSOR WILL SEE ANY STUDENT’S EVALUATIONS; NOT EVEN THE TAs. These evaluations will factor into the “Average of student’s Group Project Scores”. So a student who receives his/her proportional weight from his peers’ evaluations will have a factor of 100%. A student who receives more than (less than) his/her proportional weight will have a factor greater than (less than) 100%.

FINAL EXAM: If the student has actively participated in all project work, if the student has attended all lectures, if the student has kept up with textbook lecture readings, and if the student has studied carefully the lecture notes provided by the professor, then the final exam will be straightforward. Otherwise, the student will struggle with it.

FINAL EXAM GUIDELINES: Please review carefully the guidelines. If the student is uncomfortable with these, then the student should not take this course. The professor will grade that which is saved onto his/her USB (thumb) drive. If the student can NOT confidently work quickly and efficiently and save your work afterward, then he/she should NOT take the course. The student who chooses to take the final exam with inferior equipment does so at his own peril.

2.4 Academic Honesty and Plagiarism

It is important for a student’s effort and credit to be recognized through class assessment. Credits earned for a student work due to efforts done by others are clearly unfair. Deliberate dishonesty is considered academic misconducts, which include plagiarism; cheating on assignments or examinations; engaging in unauthorized collaboration on academic work; taking, acquiring, or using test materials without faculty permission; submitting false or incomplete records of academic achievement; acting alone or in cooperation with another to falsify records or to obtain dishonestly grades, honors, awards, or professional endorsement; or altering, forging, or misusing a University academic record; or fabricating or falsifying of data, research procedures, or data analysis.

All assessments are subject to academic misconduct check. Misconduct check may include reproducing the assessment, providing a copy to another member of faculty, and/or communicate a copy of this assignment to the PHBS Discipline Committee. A suspected plagiarized document/assignment submitted to a plagiarism checking service may be kept in its database for future reference purpose.

Where violation is suspected, penalties will be implemented. The penalties for academic misconduct may include: deduction of honour points, a mark of zero on the assessment, a fail grade for the whole course, and reference of the matter to the Peking University Registrar. For more information of plagiarism, please refer to PHBS Student Handbook.

Page 4 of 8

3.

Topics & Teaching Schedule

Lecture

Dates

Primary Text Book Chapters

(See next page for Ch. titles)

Projects

1

Thursday, April 28, 2016 Introduction to Excel;

Part 5 (Ch. 29, excluding VBA)

2

Monday, May 2, 2016

Part 5 (Ch. 30, 31, 32, part of 33),

Introduce

Ch. 18

, and

Ch. 1

3

Thursday, May 5, 2016

Part 5 (Continue Ch. 33)

4

Monday, May 9, 2016

Part 5 (Ch. 34, 35)

5

Thursday, May 12, 2016

Part 1 (Ch. 1, 2?)

6

Monday, May 16, 2016

Part 1 (Ch. 2 and Ch. 27)

#1, Due Wednesday,

May 18, 20:00

7

Thursday, May 19, 2016

Part 1 (Ch. 3, 4)

8

Monday, May 23, 2016

Part 1 (Ch. 4)

#2, Due Wednesday,

May 25, 20:00

9

Thursday, May 26, 2016

Part 2 (Ch. 8, 9, 10)

10

Monday, May 30, 2016

Part 2 (Ch. 12,15)

#3, Due Wednesday,

June 1, 20:00

11

Thursday, Jun 2, 2016

Part 2 (Ch. 14)

12

Monday, Jun 6, 2016

Ch. 16 self-study before class

Part 3 (Ch. 17)

#4, Due Wednesday,

June 8, 03:00

13

Wednesday, Jun 8, 2016

Part 3 (Ch. 19)

14

Monday, Jun 13, 2016

Part 3 (Ch. 20 and 21)

#5, Due Wednesday,

June 15, 20:00

15

Thursday, Jun 16, 2016

Part 3 (Ch. 22 – 23)

16

Monday, Jun 20, 2016

Part 3 (Ch. 24)

#6, Due Wednesday,

Jun 22, 20:00

17

Thursday, June 23, 2016

Part 4 (Ch. 25, Start Ch. 26)

18

Monday, June 27, 2016

Part 4 (Continue Ch. 26, Ch.

28)

Final

EXAM

Wednesday,

June 29, 2016

Page 5 of 8 Chapters in Simon Benninga, “Financial Modeling”, 3rd Ed., 2008, Massachusetts Institute of Technology, ISBN: 978-0-262-02628-4.

Part

Chapter

Title

Part 1

CORPORATE FINANCE MODELS

1

Basic Financial Calculations

2

Calculating the Cost of Capital

3

Financial Statement Modeling

4

Building a Financial Model: PPG Corporation

Part 2

PORTFOLIO MODELS

8

Portfolio Models – Introduction

9

Calculating Efficient Portfolios with No Short Sale Restrictions

10

Calculating the Variance-Covariance Matrix

12

Efficient Portfolios without Short Sales

14

Event Studies

15

Value at Risk

Part 3

OPTION-PRICING MODELS

16

An Introduction to Options

17

The Binomial Option-Pricing Model

18

The Lognormal Distribution

19

The Black-Scholes Model

20

Option Greeks

21

Portfolio Insurance

22

An Introduction to Monte Carlo Methods

23

Using Monte CarloMethods for Option pricing

24

Real Options

Part 4

BONDS

25

Duration

26

Immunization Strategies

27

Modeling the Term Structure

28

Calculating Default-Adjusted Expected Bond Returns

Part 5

TECHNICAL CONSIDERATIONS

29

Generating Random Numbers

30

Data Tables

31

Matrices

32

The Gauss-Seidel Method

33

Excel Functions

34

Using Array Functions and Formulas

Page 6 of 8

4.

Miscellaneous

Professor’s Subjective Evaluation: This is based in part, on his/her punctuality, attendance, classroom behavior, attitude, preparedness, etc… Per PHBS policy, if he/she is absent 6 (or more) lectures, then he/she automatically fails the course. The professor appreciates the student letting him know in advance if he/she will be tardy or absent. However, this does not excuse an absence. Please note that the number of absences is independent of whether or not they are approved by the University or HSBC Business School. (The professor does not distinguish between approved or unapproved absences.) Also, the professor does not sign PHBS forms related to the student’s planned absence(s).

To minimize classroom disruptions, the professor strongly urges the student to be punctual. All announcements are made at the beginning of class, making punctuality all the more important. If you miss a lecture, you are responsible for material covered. Secure information missed from a fellow student, not from the professor.

Disturbing class lectures will negatively impact the student’s subjective evaluation. Talking during class, having a cell phone ring, etc… are disturbances that are unacceptable. These rules are designed to optimize the learning environment for all students.

Educational Norms and Expectations: The student is responsible for material covered in any class. If a student misses a class, he/she should retrieve lecture notes from a classmate. It is in the student’s best interest to read the relevant chapters in the book BEFORE the lecture. That way, the student will find the lecture period to be much more productive.

Suggestions for improving the course: The professor is committed to making this course as good as possible. If the student has suggestions to improve the course, he/she should inform the professor, IN PRIVATE. (During a lecture is not the appropriate time for such feedback, as there is no time during the lecture for such discussions.) The course is obviously for the student’s benefit, not the professor’s. So any feedback is greatly appreciated and is seriously considered.

Add/Drop the Course: Per PHBS policy, the student is not allowed to add or drop this course after the first week.

Miscellaneous: Any issue not specifically addressed here will be handled at the discretion of the professor.

Page 7 of 8

FIRST DAY “HANDOUTS”, and Confirmation of Course Expectation

(1) Ch. 11 (Brown Reilly) Growth Analysis.ppt; (2) Ch 1 Lecture Supplement.docx

(3) FM Lecture 10 in class from Ch. 15 Supplement (VAR).pdf; (4) Regression Primer.docx

(5) Regression Primer Supplement.pdf; (6) ROR Gross Returns Lognormal Risk Neutral Valuation, etc.docx

(7) Financial Modeling Final Exam Guidelines.docx; (8) ;

(9) FM Project Guidelines; (10) Chinese GaoKao incedents; (11) Peer Evaluation; (12) Group Selection;

(13) Syllabus (including this sheet to be signed and returned at beginning of lecture #3.

PLUS

: 28 Excel Files: Ch. 35, Lectures 1, 2, 3, 4, 5, 6, 7, 9, 9, 9, 10, 10, 11, 12, 12, 13, 14, 14, 15, 15, 16, 16,

17, 18, and 18.

PLUS

: Six (6) group project files.

This is the final page of the syllabus for Financial Modeling.

I received a copy of the

entire syllabus

(total number of pages is shown below).

I acknowledge receipt of all of the above

forty-seven (47) files

., some of which are

Excel files

, and some of

which are

handouts

. I acknowledge that several

additional references

are listed in this syllabus, including

supplemental textbooks

.

I acknowledge that the first page of this syllabus contains a detailed

course description

. I understand that the

course is

difficult

, and this description allows me to fully perform a

proper course assessment

, and

accordingly, I choose to take this course as an elective.

Also on the first page, the professor’s

office hours, office location, and email address

are clearly stated.

This syllabus also contains a

detailed calendar of lectures

,

including content

(via Textbook chapter titles)

for each lecture. Per the course description, this course examines a

wide range of knowledge

.

I have completed the

course pre-requisites

.

I understand that the final exam will be timed, in class, on Excel. (Thus, I realize that it is my responsibility to

come with

adequate tools

to complete the task, i.e., a reliable computer capable of Excel and of saving my

work onto a thumb drive.)

I understand that the professor will grade the solution to the final exam that I deliver to him via the

thumb

drive

(Professor will

not

grade work located elsewhere, e.g., on a hard drive or an opened Excel file, etc…)

I understand that there are

6 projects

to be completed with my selected group members.

I understand that the course is

FAST-paced

and will require a lot of work.

I understand that I will be

treated fairly

, i.e., exactly the same as all other students per the guidelines

described in this syllabus. I do not require special treatment, and

I will not request special treatment

, i.e., as

part of selecting this elective course, I accept this syllabus’ guidelines.

I understand exactly

how my course grade will be determined

per the guidelines in the syllabus.

I understand that

disturbing lectures

(e.g., talking)

negatively impacts

my subjective evaluation and may

result in my

expulsion from lecture

. I understand that

arriving late

(and of course,

missing lectures

) (1)

negatively impacts my course grade, and (2) is unacceptable, as professor

locks doors

when lecture begins.

I understand that if I miss a lecture, the

professor is not responsible for my securing information

covered

during said lecture. (I will secure such information from a fellow student; not professor.)

I understand that

requesting professor to make any exceptions

in this syllabus is not only inherently

unfair

to all the rest of my classmates, but will

not be honored

and will send professor a negative signal,

negatively

impacting my final score

.

I have read the syllabus

carefully

, and agree to abide by all guidelines.

Date, Student’s Signature and Student ID Number

: __________ ____________