SemesterSpring Semester, 2018
DepartmentJunior Class A, Department of International Business Junior Class B, Department of International Business Senior Class A, Department of International Business Senior Class B, Department of International Business
Course NameSimulation Modeling with Business Applications
InstructorFU YEE-TIEN
Credit3.0
Course TypeElective
Prerequisite
Course Objective
Course Description
Course Schedule

Please see the attached.



Course Schedule



Session 1 Introduction to Financial Modelling and Spreadsheet Essentials



Frameworks and concepts covered



=



• Course mechanics

• Introduction to financial modelling



o Strengths and weaknesses of spreadsheets o Six golden rules of spreadsheet design

o Do we make the most of modelling?



• Spreadsheet essentials

o Lookup and reference functions o NPV and IRR functions

o Data tables

o Database manipulation



Session 2 Measuring Risk Frameworks and concepts covered



=



Preparation



Read “Modern Risk Measurement”.



• Estimating betas with regression analysis • Using daily, weekly, and monthly data

• Testing market efficiency



o With regression analysis



o With pivot tables

• Recording and editing macros



Sessions 3 and 4 Portfolio Optimization



Frameworks and concepts covered



• Mean-variance portfolio selection

o Computing mean-variance portfolios

o Back-testing portfolio performance

o Research on portfolio selection

• Bond portfolio selection

• Term structure estimation

• Capital budgeting

• Spreadsheet features

o Matrix operations in Excel

o Using Solver for optimization

o Using macros to generate the efficient frontier



Preparation



Students who are not familiar with Solver need to read the following sections of

Albright and Winston before class: 3.1, 3.2, 3.3, 3.4, 3.7, 3.8, 4.1, 4.7, 6.1, 6.2, 6.3, 7.1, and 7.2.



Session 5 Advanced risk analysis



Frameworks and concepts covered



• Monte-Carlo simulation

• Risk analysis of discounted cash flow models • Case: London European Airways

• Spreadsheet features

o Using @Risk for Monte-Carlo simulation

o Combining macros with @Risk



Preparation



Students who are not familiar with @Risk should read "Allowing for Risk in Spreadsheet Models--A Tutorial on Risk Analysis with @Risk", and Chapter 9 of Winston and Albright before class.



Session 6 Workshop Assignment on Portfolio Optimization For this session, we will meet directly at the computer laboratory. The



workshop assignment will be distributed, and the students will work in pairs on the assignment with the help of the professor and tutors.



Frameworks and concepts covered



The assignment will build on the concepts covered in Sessions 3 and 4. Specifically, the students will have to build a Solver model to deal with a portfolio management situation.



Preparation



Students who are not familiar with Solver need to read the following sections of Albright and Winston before class: 3.1, 3.2, 3.3, 3.4, 3.7, 3.8, 4.1, 4.7, 6.1, 6.2, 6.3, 7.1, and 7.2.



Session 7 American Option Valuation



Frameworks and concepts covered



• Review of options

• Binomial trees

• Risk neutral valuation

• American option valuation

• Estimating stock return volatility

• Matching volatility with a binomial tree



Session 8 Workshop Assignment on Real Option Valuation with Binomial Trees



For this session, we will first have a short lecture and then we will move to the computer laboratory. The workshop assignment will be distributed, and the students will work in pairs on the assignment with the help of the professor and tutors.



Frameworks and concepts covered



• Real options

• Valuing an internet start-up with real options



Session 9 Black-Scholes Formula and Exotic Option Valuation



Frameworks and concepts covered



• Black-Scholes formula

o Pricing European options

o Estimating implied stock return volatilities

• Simulating stock price evolution with Monte- Carlo simulation

• Exotic option valuation with Monte-Carlo simulation

o Knockout options

o Lookback options

o Asian options

o As-you-like-it options



Session 10 Real Option Valuation with Monte-Carlo Simulation



For this session, we will first have a guest speaker and then we will move to the computer laboratory to work on real-option valuation with Monte-Carlos Simulation.



Frameworks and concepts covered



Real Option Valuation with Monte-Carlo Simulation 


Teaching Methods
Teaching Assistant

TBA


Requirement/Grading

Please see the attached.



COURSE TEAM PROJECTS



Course projects will be completed in teams, usually of 4 or 5 members. You will be assigned to one team for the first half of the term, up to midterm week. And then you will be assigned to a different team for the second half of the course.



Projects deliverables will be accepted for instructor’s improvement suggestions. Part of the grading process is a peer review of the project.



These projects are expected to be professional work. Presentation is important. At a minimum:





  • Where required, all text should be typed and presented in a case-like format.




  • Spreadsheets and graphs should fit on one page or separated in a logical manner. They should be visually appealing, easy to read and incorporated into the text.




  • You should include a cover page/tab with each person’s name and project title.




  • You should have subsections labeled with brief descriptions immediately preceding the spreadsheet and qualitative analysis.




  • All spreadsheets should be labeled with a title and description.



    In general, I should be able to read through the project and follow the analysis easily without referencing the assignment.



    The project details may change during the course of the term. But, the plan right now is for the following projects:







  1. Valuation model for an MBS (300 points total)



    You will be given the layout for a MBS issued by Freddie Mac. Your job is to develop a valuation of the tranches. The primary deliverable is the Excel spreadsheet. A 1-page (or so) write-up will be required to explain tranches that are complicated and deserve some discussion.




  2. Performance reporting and risk evaluation (150 points total)



    For this project, your team will be given a Harvard case about a small asset management firm. Your job is to develop a 1-page (single-sided or double-sided, up to you) handout that the firm would use to summarize its investment philosophy and performance for the two funds it operates. These are known as “tear sheets”. You can (kind of) see examples of such 1-page documents by running a search for a “sample tear sheet” on any of the internet search engines.



    I realize this is likely the first time many of you will tackle such an assignment. Your primary job is to get the numbers right – calculate the various portfolio fund performance measures as outlined in the case





(correctly) and think about how to present them in a meaningful way. This will be an intermediate deliverable for this project.



More importantly, I want to see the performance report directly tied to the spreadsheet. If I update numbers on the spreadsheet, the performance report is updated as well. From there, develop bells and whistles to make the spreadsheet and report more usable and user-friendly.



The deliverable is the spreadsheet and the report that the spreadsheet creates.





  1. Additional Funds Needed model (150 points total)



    You will be given the financials and other relevant data for a corporation and/or a scenario. The project involves developing a model to determine the funding needs of the company. Valuation impact based on using debt versus equity will be examined. The deliverable is a 2-3 page executive summary of the results and scenarios. Results from your Excel spreadsheet should be incorporated as Exhibits, Tables, and/or Charts. The full spreadsheet will be also handed-in.




  2. DCF / Pitchbook competition (150 points total)



    Your team will be responsible for researching a firm and developing a DCF valuation of the firm. Then, your team will update a Powerpoint “pitch” presentation. You will then record a 10-minute presentation of the pitch. The deliverables will be the video and the PPT presentation.




  3. Derivatives project (150 points total) Details and deliverables TBA.




  4. Creative case development/presentation and model (100 points total)



    For the final project, your team will develop a spreadsheet model of your choice. Your team will develop the project scope and deliverables, and your team will present your project to the class via a prerecorded 10minute presentation. The deliverables will be the video and spreadsheet.





For all cases, peer evaluations (of part of the project) are required. A 5-point penalty will be assigned for a late (by less than 24 hours) peer evaluation. A 10-point penalty will be assigned after 24 hours. Needless to say, this is a critical component of this class. 


Textbook & Reference

Thefollowingtextbookwillbeutilizedthroughouttheterm:JohnCharnes, Modeling Financial Derivatives with Excel and Crystal Ball. The second edition of this book is available in pdf format for free from the electronic Wiley Library through NCCU library.



Severaltextbooks,however,canbeusedtocomplementthematerialsinthe course pack:



• “Spreadsheet Modeling and Applications”, by Albright and Winston, Thomson/Duxbury Press 2005. This is the textbook covering the basics of modelling as well as the basics of the prerequisite tools Solver and @Risk. Students should read Chapters 3 and 9 of this book before Sessions 3 and 5, respectively.



• “Principles of Corporate Finance”, by Brealey, Myers, and Allen, 10th ed., 2010. This is the textbook for the prerequisite core course "Corporate Finance" and covers the basic financial frameworks that are required in the course.



• “Options, Futures and Other Derivatives”, by John C. Hull, Sixth Edition, Prentice Hall 2006. This textbook is recommended for other complementary elective courses such as “Derivatives” and “Financial Engineering and Risk Management”. The book is particularly useful to understand the theoretical concepts related to option valuation.



• “VBA for modelers”, by Christian Albright, Second Edition, Palisade 2006. This textbook provides a step-by-step guide to using VBA to build decision- support systems with Excel.



In addition, the following entertaining, easy-to-read, and informative New York Times bestseller gives a good overview of some of the characters and strategies in the quantitative finance industry:



• “The Quants: How a small band of maths wizards took over Wall Street and nearly destroyed it”, by Scott Patterson, Random House Business Books 2010.



• “Operations Research, Tenth Edition”, by Taha is highly recommended for audience to study algorithms in optimization and simulation. 


Urls about Course
ADDITIONAL COURSE INFORMATION Reference 1: U. of Florida Syllabus of Prof. John C. Banko. Reference 2: London School of Business Syllabus of Prof. Victor DeMiguel.
Attachment

Syllabus of Simulation Modeling with Business Applications ??.pdf