Financial Modeling
Instructor: Fritz Koger, CFA, PhD email: fritzkoger@phbs.pku.edu.cn
Office: 752
Office Hours: 13:30 – 14:30, Tuesday
Course: Financial Modeling Class Location: ???
Class Meeting Time:
Section 1 (Elective): Tuesday and Friday, 10:30 – 12:20.
TA Review Sessions: None
Teaching Assistants (TA): TBD (Name, email, phone number)
Course Goals and Description: 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 (and Crystal Ball, CB, and Visual Basic for Applications,VBA) 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.
The professor has two primary goals: arm the student with
(1) many practical financial Excel (and CB and VBA) models and
(2) programming knowledge of Excel (and CB and VBA) to develop his/her own models as the need arises in his/her professional and personal life.
An extremely important aspect of these goals is the ability to perform
(1) sensitivity analyses (i.e., single variable comparative statics) and
(2) scenario analyses (i.e., simultaneous multi-variable comparative statics).
The student will learn proficiency in these valuable methods, e.g., via extensive use of Excel data tables.
Lectures will be conducted primarily via live Excel (and CB and VBA) exhibitions, where the student will build models simultaneously with the professor. As such, each student is required to bring to each lecture a laptop equipped with Excel. (The student is not required to have CB and VBA on his/her personal laptop. Access to CB and VBA will be discussed as the course proceeds.)
Financial Modeling is very "hands-on", with most of the grade determined via demonstration of building models via homeworks/projects. All homeworks/projects will be completed in groups. Group membership will be determined by the students themselves by the second lecture. (The professor is happy to assign groups to those students who prefer not to choose their own group members.) The purpose of homeworks/projects is to reinforce and to extend the student’s knowledge regarding models built during lectures.
Course Prerequisites: None, though previous exposure to Excel and to basic finance courses (e.g., Corporate Finance, Investments, Asset Valuation Theory) will make the course easier. Nonetheless, the first couple of lectures will be used to introduce the basics of Excel, including Excel’s basic financial functions. Additionally, as the professor will spend a few minutes at the beginning of each lecture reviewing the relevant financial concepts to be modeled that day, prior knowledge is unnecessary, though helpful.
Course Material: Primary Textbook:
Simon Benninga, “Financial Modeling”, 3rd Ed., 2008, Massachusetts Institute of Technology, ISBN: 978-0-262-02628-4
Supplemental Textbooks:
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.
Course Topics: See list of chapter titles on ultimate page of this syllabus.
Course Guidelines: There are two overarching themes: the professor’s aims are to (1) be as fair as possible to everyone, and (2) create the optimal learning environment for everyone! The professor firmly believes that treating individuals differently is inherently unfair. Thus, everyone will be treated the same.
Grading:.
The student’s final grade will be
10% (Subjective Evaluation) + 30% (Individual Final Project Score)
+ 60% (Average of student’s Group Project Scores).
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.)
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.
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.
Statement about Academic Integrity: This class will be conducted in full accordance with PKU’s policies regarding academic integrity. Anyone caught cheating will be punished as severely as the school permits.
On group projects, each group is to work independently of other groups. Whereas it is OK for students between different groups to consult each other, each group’s deliverable should be independently developed. Simply copying one group’s project by another group will result in penalties for both groups. For the final (individual) project, no consultation between students is allowed. The final (individual) project is to be solely developed by each individual, with no assistance of any kind from any other person. Again, policies are designed with fairness in mind.
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.
Course Schedule:
Chapters in Simon Benninga, “Financial Modeling”, 3rd Ed., 2008, Massachusetts Institute of Technology, ISBN: 978-0-262-02628-4
Office: 752
Office Hours: 13:30 – 14:30, Tuesday
Course: Financial Modeling Class Location: ???
Class Meeting Time:
Section 1 (Elective): Tuesday and Friday, 10:30 – 12:20.
TA Review Sessions: None
Teaching Assistants (TA): TBD (Name, email, phone number)
Course Goals and Description: 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 (and Crystal Ball, CB, and Visual Basic for Applications,VBA) 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.
The professor has two primary goals: arm the student with
(1) many practical financial Excel (and CB and VBA) models and
(2) programming knowledge of Excel (and CB and VBA) to develop his/her own models as the need arises in his/her professional and personal life.
An extremely important aspect of these goals is the ability to perform
(1) sensitivity analyses (i.e., single variable comparative statics) and
(2) scenario analyses (i.e., simultaneous multi-variable comparative statics).
The student will learn proficiency in these valuable methods, e.g., via extensive use of Excel data tables.
Lectures will be conducted primarily via live Excel (and CB and VBA) exhibitions, where the student will build models simultaneously with the professor. As such, each student is required to bring to each lecture a laptop equipped with Excel. (The student is not required to have CB and VBA on his/her personal laptop. Access to CB and VBA will be discussed as the course proceeds.)
Financial Modeling is very "hands-on", with most of the grade determined via demonstration of building models via homeworks/projects. All homeworks/projects will be completed in groups. Group membership will be determined by the students themselves by the second lecture. (The professor is happy to assign groups to those students who prefer not to choose their own group members.) The purpose of homeworks/projects is to reinforce and to extend the student’s knowledge regarding models built during lectures.
Course Prerequisites: None, though previous exposure to Excel and to basic finance courses (e.g., Corporate Finance, Investments, Asset Valuation Theory) will make the course easier. Nonetheless, the first couple of lectures will be used to introduce the basics of Excel, including Excel’s basic financial functions. Additionally, as the professor will spend a few minutes at the beginning of each lecture reviewing the relevant financial concepts to be modeled that day, prior knowledge is unnecessary, though helpful.
Course Material: Primary Textbook:
Simon Benninga, “Financial Modeling”, 3rd Ed., 2008, Massachusetts Institute of Technology, ISBN: 978-0-262-02628-4
Supplemental Textbooks:
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.
Course Topics: See list of chapter titles on ultimate page of this syllabus.
Course Guidelines: There are two overarching themes: the professor’s aims are to (1) be as fair as possible to everyone, and (2) create the optimal learning environment for everyone! The professor firmly believes that treating individuals differently is inherently unfair. Thus, everyone will be treated the same.
Grading:.
The student’s final grade will be
10% (Subjective Evaluation) + 30% (Individual Final Project Score)
+ 60% (Average of student’s Group Project Scores).
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.)
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.
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.
Statement about Academic Integrity: This class will be conducted in full accordance with PKU’s policies regarding academic integrity. Anyone caught cheating will be punished as severely as the school permits.
On group projects, each group is to work independently of other groups. Whereas it is OK for students between different groups to consult each other, each group’s deliverable should be independently developed. Simply copying one group’s project by another group will result in penalties for both groups. For the final (individual) project, no consultation between students is allowed. The final (individual) project is to be solely developed by each individual, with no assistance of any kind from any other person. Again, policies are designed with fairness in mind.
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.
Course Schedule:
| “奥别别办” | Lecture | Dates | Primary Text Book Chapters (See next page for Chapter titles) |
| 1 | 1 | Friday, April 25, 2014 | Introduction to Excel; Part 5 (Ch. 29 – 35) |
| 2 | Tuesday, April 29, 2014 | Part 5 (Ch. 29 – 35) | |
| 2 | 3 | WEDNESDAY, April 30, 2014, 10:30 am |
Part 5 (Ch. 29 – 35) |
| NO CLASS: FRIDAY, MAY 02, 2014 |
|||
| 4 | Tuesday, May 06, 2014 | Part 5 (Ch. 29 – 35) | |
| 3 | 5 | Friday, May 09, 2014 | Part 1 (Ch. 1 - 4) |
| 6 | Tuesday, May 013, 2014 | Part 1 (Ch. 1 - 4) | |
| 4 | 7 | Friday, May 16, 2014 | Part 1 (Ch. 1 - 4) |
| 8 | Tuesday, May 20, 2014 | Part 1 (Ch. 1 - 4) | |
| 5 | 9 | WEDNESDAY, May 21, 2014 |
Part 2 (Ch. 8 – 11) |
| 10 | Friday, May 23, 2014 | Part 2 (Ch. 8 – 11) | |
| NO CLASS: TUESDAY, MAY 27, 2014 |
|||
| 6 | 11 | Friday, May 30, 2014 | Part 2 (Ch. 8 – 11) |
| 12 | Tuesday, June 03, 2014 | Part 3 (Ch. 16 – 22) | |
| 7 | 13 | Friday, June 06, 2014 | Part 3 (Ch. 16 – 22) |
| 14 | Tuesday, June 10, 2014 | Part 3 (Ch. 16 – 22) | |
| 8 | 15 | Friday, June 13, 2014 | Part 3 (Ch. 16 – 22) |
| 16 | Tuesday, June 17, 2014 | Part 4 (Ch. 25 – 27) | |
| 9 | 17 | Friday, June 20, 2014 | Part 4 (Ch. 25 – 27) |
| 18 | Tuesday, June 24, 2014 | Part 4 (Ch. 25 – 27) | |
| Final Project |
Monday, June 30, 2014, 12:00 pm (noon, or mid-day) |
||
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 | |
| 11 | Estimating Betas and the Security Market Line | |
| 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 | |
| Part 4 | BONDS | |
| 25 | Duration | |
| 26 | Immunization Strategies | |
| 27 | Modeling the Term Structure | |
| 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 | |
| 35 | Some Excel Hints | |