《Excel金融建模课程教学大纲模版英文版.docx》由会员分享，可在线阅读，更多相关《Excel金融建模课程教学大纲模版英文版.docx（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Syllabus of Financial Modeling Using ExcelCourse Name： Financial Modeling Using Excel Course Code：Credits： 2.0Total Credit Hours：32Lecture Hours： 32Experiment Hours：Programming Hours：Practice Hours：Total Number of Experimental (Programming) Projects 0Where, Compulsory ( 0 ), Optional ( 0 ).School： 0
2、8 School of BusinessTarget Major： FinanceI、Course Nature & AimsThe objective for this course is to develop the financial modeling skills used in the application of financial theory to practical problems in investment analysis, portfolio management, and valuation. In particular, the course will cover
3、 the application of Excel spreadsheet functions and visual basic programming to the statistical analysis of financial market data, the use of optimization models to determine mean-variance efficient allocations of financial assets, and the valuation of fixed income and derivative securities. Additio
4、nal topics to be covered include, active portfolio management, simulation and retirement planning, and the valuation of exotic options and high yield bonds.II、Course Objectives1 . Moral Education and Character Cultivation.This course help students gain a comprehensive understanding of EXCEL financia
5、l modeling related knowledge through the study of theory and techniques of EXCEL financial modeling. Through the history of financial modelling and the process of establishing relevant theories and techniques, students will be able to understand how their predecessors thought during the development
6、of financial modelling and how they overcame the obstacles encountered, helping them to develop a scientific mindset and a spirit of facing challenges in their work. From the perspective of the role of the discipline of financial modelling in Chinas innovation-driven development, and using the resea
7、rch work of outstanding contributors as the carrier, the education of socialist core values is integrated into the content and teaching process of the curriculum, highlighting value leadership, knowledge transfer and capacity development, helping students to correctly understand the laws of history,
8、 accurately grasp the basic conditions of the country, master scientific worldview and methodology, and promote the establishment of correct worldview and values.2 .Course ObjectivesThrough the study of this course, students qualities, skills, knowledge and abilities obtained are as follows：Chapter
9、8Introductionto Options onEquities4000400Chapter 9 Application of BinomialTrees4000400Chapter 1() Introduction to Valuing Options onBonds2000400Chapter 11Interest RateModels4000400Chapter 12FinancialStatementAnalysisTools2000200Total320003500Sum32V、Summary of Experimental (Programming) ProjectsNo ex
10、periment (programming) sessionVI、Teaching MethodClass lectures: This course is mainly taught by the instructor, supplemented by self-study and after-school assignments submitted through online study platform. The teaching process is flexible in the use of books and multimedia teaching. Students are
11、encouraged to communicate actively with the instructor in order to ask questions or review lecture material.Heuristic teaching: Important learning points are taught with the idea of asking questions, analyzing problemsand solving problems, so as to develop students* corresponding abilities. In-class
12、 exercises and real life examples that engage students and enhance their employability are frequently adopted.VD Course Assessment and Achievement EvaluationAssessment Methods： Non-ExaminationExamination Formats： Open-bookGrading Methods： Five-level SystemCourse Assessment Content, Assessment Format
13、 and Supporting Course ObjectivesCourseObjectives (Indices)AssessmentContentAssessment Formats and Proportion ( % )GradingClassroomQuestioningAssignmentEvaluationRoutine TestExperimentReportTermReportTermPaperMidtermExamFinalExamProportion(%)Objective 1 (Index 10-2)Lookupandindex functions, Database
14、 manipulation, user-defined VBA functions105Objective 2 (Index2-3)mean andvariance, one factor model, multifactor model, principal component analysis, beta, value at risk1()5Objective 3 (Index 9-1)Efficient frontier, capital asset pricing models, style analysis, financial distress prediction1015Obje
15、ctive 4 (Index 4-2)NPV, IRR, Black-Scholes option pricing formula, yield to1015maturity, duration, convexity, liquidity ratio, efficiency ratio, leverage ratio, coverage ratios, profitabilityratiosObjective 5 (Index5-2)Binomial tree model, American option pricing, Janow-Rudd model, Cox -Ross-Rubinst
16、ein model, Leisen -Reimer model, Blake bond option pricing formula, Vasicek model, CIR model1010Total100VIII、Course ResourcesTextbooks： Simon Benninga. Financial Modeling (Fourth Edition) MJ. Boston: Massachusetts Institute of Technology, 2014.Bibliography：1. Simon Benninga, Tai Mofkadi. Principles
17、of Finance with Excel (Third Edition) M. Oxford: Oxford University Press, 2017.2. Timothy R. Mayes, Todd M. Shank. Financial Analysis with Microsoft Excel (Seventh Edition) Ml. South-Western Cengage Learning, 2012.3. Jackson, Staunton. Advanced modelling in finance using Excel and VBAM. Willey, 2001
18、.Reading Materials： No.IX、NotesPrerequisites: Statistics, Investment, Corporate FinanceFollow-up Courses:Contents and Requirements of Students* Self-study: NoBilingual Teaching or Not: NotRequirements and Proportion of Bilingual Teaching: NoDiscipline and Considerations of Practice Session: no pract
19、ice sessionNotes:Objective 1. Become proficient with spreadsheet functions and other advanced tools in Excel.(Corresponding to Chapter 1,2, supporting for graduation requirements index 10.1 10.2 10.3)Objective 2. Demonstrating proficiency in using regression analysis to estimate exposure to systemat
20、ic and residual risk for individual securities and portfolios.(Corresponding to Chapter 4,5,6,7, supporting for graduation requirements index 12.1、12.2 12.3)Objective 3. Constructing and solving optimization models to determine the portfolio weights and total risk for mean variance efficient portfol
21、ios.(Corresponding to Chapter 4,6,12, supporting for graduation requirements index 9.1 9.2 9.3)Objective 4. Distinguish among the most common valuation methodologies, apply cash flow valuation and perform sensitivity analysis.(Corresponding to Chapter 3,5,7,10,11,12, supporting for graduation requir
22、ements index 4.1、4.2 4.3)Objective 5. Making use of Monte Carlo simulation, interpret results and learn how to combine optimization and simulation.(Corresponding to Chapter 8,9,10,11, supporting for graduation requirements index 5.1、5.2、5.3)3. Supporting for Graduation RequirementsThe graduation req
23、uirements supported by course objectives are mainly reflected in the graduation requirements indices 2-3, 4-2, 5-2,9-1,10-2, as follows:Supporting for Graduation RequirementsCourseObjectivesGraduationRequirementsIndices and Contents Supporting for GraduationRequirementsTeachingTopicsLevel ofSupportI
24、ndicesContentsObjective 110. ProfessionalcompetenceIndex10.1.Construct proprietary architectonic system of finance through a thorough understanding of financial knowledge.Chapter 1,2HIndex10.2.Develop, design, plan and execute new ideas in the field of financial instruments facilitated by the combin
25、ed knowledge of management and economics.Chapter 1,2HIndex10.3.Build wide-ranging professional expertise and enhance professional ability in analyzing and solving problems through their own body of knowledge.Chapter 1,2HObjective 212. CareerDevelopment AbilityIndex12.3.Master the professional theori
26、es and knowledge of finance, management,Chapter 4,5,6,7Heconomics, and trade related to finance, which pave the way for future career development.Index12.3.Demonstrate self-learning andself-motivation ability in related disciplines.Chapter 4,5,6,7HIndex12.3.Have interdisciplinary and professional de
27、velopment capabilities.Chapter 4,5,6,7HObjective 39. Investment and financial decision-making abilityIndex9.1.Build a solid foundation in financial theories and methods.Chapter 4,6,12HIndex9.2.Make investment decisions and judgement, capitalizing on the knowledge from a number of courses including s
28、ecurities investment, risk management, and venture capital.Chapter 4,6,12HIndex9.3.Make financial decisions in an international perspective, derived from a comprehensive understanding of a variety of courses such as macroeconomics, international finance and financial engineering.Chapter 4,6,12HObjec
29、tive 44. Practicalapplication abilityIndex4.1.Use flexibly the acquired professional knowledge in financial practice.Chapter3,5,7,10,11,12HIndex4.2.Digest, interpret, highlight, and present information from domestic and international sources, so as to provide consultation for the government, enterpr
30、ises, financial institutions on practical issues.Chapter3,5,7,10,11,12HIndex4.3.Apply principles of modern finance and economics as well as analytical tools to cope with practical challenges while conducting scientific research.Chapter3,5,7,10,11,12HObjective 55. Manipulation of financial analytical
31、 tools abilityIndex5.1.Skillfully use computer for business operations.Chapter8,9,10,11HIndex5.2.Skillfully process data, build models and make evaluations with the assistance ofChapter8,9,10,11Hmethods from modem financialeconometrics and data science.Index5.3.Write professional papers and research
32、 reports.Chapter8,9,10,11HHI、Basic Course ContentChapter 1 Introduction to Financial Modelling and Spreadsheet Essentials (supporting course objectives 1)1.1 Introduction to financial modelling1.2 Lookup and reference functions1.3 Database manipulationTeaching Requirements: After study of this chapt
33、er, students will gain familiarity with certain Excel functions and procedures used in the text. These include mathematical, statistical and lookup functions from ExcePs extensive range of functions, as well as much-used procedures such as setting up Data Tables and displaying results in XY charts.
34、Also included are methods of summarizing data sets, conducting regression analyses, and accessing ExccPs Goal Seek and Solver.Key Points： Lookup and reference functionsDifficult Points： Database manipulationChapter 2 Introduction to VBA (supporting course objectives 1)2.1 Introduction to write VBA m
35、acro2.2 Elements of programming2.3 Writing user-defined VBA functionsTeaching Requirements: After study of this chapter, students should master the use of VBA and macros within Excel. Students can argue the case fbr mastering some VBA, touches on the object-oriented9 fbcus of the language, and adopt
36、 an incremental approach to mastering the code. Students will learn how functions with several scalar inputs are written in much the same way. They will be capable to handle arrays as inputs and outputs of functions.Key Points： Introduction to write VBA macroDifficult Points： Writing user-defined VB
37、A functionsChapter 3 Introduction to capital budgeting (supporting course objectives 4)3.1 The NPV rule forjudging investments and projects3.2 The IRR rule forjudging investments3.3 Capital budgeting principleTeaching Requirements: After study of this chapter, students should understand the process
38、of Capital budgeting. They will understand the application of two standard concepts used in capital budgeting: net present value (NPV) and internal rate of return (IRR). They can choose and rank projects among alternatives based on NPV and IRR, and make comparison of the different answers provided b
39、y the two criteria. They will know how to calculate sunk costs and incorporate taxes into the valuation decision.Key Points： The NPV rule and IRR ruleDifficult Points： Capital budgeting principleChapter 4 Portfolio Optimisation (supporting course objectives 2,3)4.1 Portfolio mean and variance4.2 Usi
40、ng solver to find efficient points4.3 Generating efficient frontierTeaching Requirements: After study of this chapter, students should solve the problem of portfolio optimisation. Students will become familiar with spreadsheet implementation of calculation procedures, and know how to explore generic
41、 portfolio problem types. Students should be able to establish the weights for risky portfolios using Excels Solver, whereas for the allocation between risky and risk-free assets in the generic portfolio problems, user-defined functions for spreadsheet formulas are preferable.Key Points： Portfolio m
42、ean and variance, Generating efficient frontierDifficult Points： Using solver to find efficient pointsChapter 5 Asset Pricing (supporting course objectives 2,4)5.1 Single factor model5.2 Estimating beta coefficients5.3 Capital asset pricing model5.4 Value at riskTeaching Requirements: After study of
43、 this chapter, students should learn how to build the single-index model and how to calculate associated risk measures, in particular the estimation of betas and variance-covariance matrices from the returns of individual assets. Students will master the central numerical technique of regression, wh
44、ich is applied to estimate the beta of an asset, i.e. the return on the asset relative to the return on the market. Students should understand the importance of beta in CAPM model as they can describe the relative responsiveness of assets to the market. However, they also facilitate the calculation
45、of covariances via the single-index model. If log returns can be assumed to be normally distributed, students should derive analytic answers for Value-at-Risk.Key Points： Single factor model, Estimating beta coefficientsDifficult Points： Capital asset pricing modelChapter 6 Performance Measurement a
46、nd Attribution (supporting course objectives 2,3)6.1 Conventional performance measurement6.2 Active and passive management6.3 Introduction to style analysisTeaching Requirements: After study of this chapter, students should know how to calculate four conventional performance measurements including S
47、harpe ratio, Treynor ratio, Jensens ratio and Appraisal ratio. They understand how to evaluate an active portfolio differently from a passive index portfolio. They learn how to conduct style analysis using a multi-index model as the return benchmark. Style analysis uses Excels Solver to carry out quadratic programming. They learn about the two extensions: obtaining confidence intervals for the style weights and exposur