Financial Modeling – 7221 Spring Semester, Term 1 - 2017 Mon/Wed 4:30-6:00 PM, Gerlach Hall 355 Instructor Information Brian Izzo Office Hours by Appointment Phone: 248-910-2174 Email:
[email protected]
Overview You’re in your first job after graduation as a financial analyst for large multi-national company. You completed the training, made new friends, and even cashed your first paycheck. The next 30 years will be a breeze. Your boss stops in, makes small talk, and then asks you to meet with Sales and Engineering on a proposal that requires substantial capital investment. Key requirements: determine capital investment, working capital, forecast income statement and cash flow, calculate net present value (NPV), internal rate of return (IRR), payback period and sales price. And make sure the model can accommodate sensitivity analysis. Your sales and engineering teams are counting on you to provide key guidance to win the program. Your boss is expecting you to manage your colleagues in sales and engineering to ensure the project adds value to the company. In today’s lean and fast moving business environment, management teams and owners need well thought-out financial models and analyses to make decisions and drive business performance. Spreadsheets are a powerful tool used by almost all companies. You, as the modeler, wear three main hats: 1) the finance expert 2) spreadsheet whiz and 3) visual designer / architect. This course will challenge students to improve their finance and modeling skills by constructing a wide variety of spreadsheet models and summarize the output in a relevant, consumable way. The course objectives:
Develop a variety of financial models in Excel Communicate financial model results clearly and concisely Enhance practical understanding of financial theory underlying financial models Enhance excel skills
This course only requires the basic use of Excel.
Course Description To develop modeling skills this course will be “hands on”. It will be learning by doing. The course will be run more as a seminar and independent study rather than a pure lecture / recitation course. The textbook is an excellent reference tool presenting how to construct various types of models in Excel. Lectures will be given throughout the term to expand on the readings, promote Excel skill development, and share real world application and use. Students will complete a mix of assignments. 1
Replicate models from the textbook (to be completed individually) Construct original models within the group that you will form Participate in a final group project and presentation
Course Materials
Excel Modeling in Corporate Finance 5th Edition, Craig Holden, Prentice Hall 2015 ISBN 0205987257
Other Sources There will be some occasional handouts in the class. One of the best sites and authors of Excel books can be found at http://www.spreadsheetpage.com. Other excellent books include:
Principles of Financial Modeling, by Simon Benninga, Third Edition Mastering Financial Modeling in Microsoft Excel, Alastair L. Day, Third Edition
Class Attendance Students are expected to attend class and arrive on time. YOU ARE RESPONSIBLE for any announcements that may be made during class. Consult one of your classmates if you happen to be absent. The three P’s of class attendance: Participation – students are encouraged to actively participate in class. An essential part of learning is the exchange and sharing of ideas Preparation – come to class prepared and ready to learn Promptness - arrive before the beginning of each class. If you happen to arrive late, please enter quietly and do not disturb your fellow students. Email communication If it is necessary to communicate with class between sessions, I will send an email to the class or update Canvas with an announcement. I expect that you will check your email or the class website on Canvas daily. I will check my e-mail at least daily. Grading Procedures and Policies Plus and minus grades will be used. There are no exams in the course. Grades will be determined as follows: Spreadsheet model replication, text Case models Final team spreadsheet model Class presentation of final group model Class attendance / other assignments Peer review Total
25% 25% 25% 10% 10% 5% 100%
2
Students are required to submit an electronic copy of all spreadsheet models. The electronic copy of your models should be submitted into the appropriate CANVAS dropbox. Each electronic file should be appropriately name. I would suggest using a naming convention similar to this example: CH 1 Single cash flow - your name Excel tabs should be renamed to reflect the appropriate assignment from the text. Specifically, each tab should reference the assigned model number and text chapter (for example, “Ch. 1 model 1.1”). Given the number of models assigned in the course, it is important that you complete all assignments on time. Assignments must first match their screen shots as they appear in the text and then be adjusted for proper formatting that we will discuss in class to adequately prove that you have mastered the nuances associated with the various techniques of displaying data in Excel. You can only learn if you do the assignments yourself. Do not get behind. Groups and Final Group Presentation Teams of five (5) should be formed at the end of the first night of class and communicated as part of the assignment to the instructor that is due in the second class. Students will select their own teammates. I would encourage each team to have at least one native English speaker to help ensure that memos, group cases and the final project are written clearly. I would also suggest that each team begin start working on the final assignment sooner rather than later. The final group presentation should be no longer than 15 minutes (teams will be allotted an additional 3 minutes for set up time) and presented in a professional manner using PowerPoint. At this stage of your professional and educational career, a polished work product (which includes the presentation, excel model, and written report memo) is expected. At a minimum, each presentation should include a demonstration of the model, dialogue regarding the assigned case, assumptions, modeling techniques and recommendation/findings. Remember, similar to many jobs, groups are competing against one another. Peer review Peer review will be used to allow students to provide useful information about each teammate’s contributions. This is done to help avoid the free-rider problem and to also reward those students that contribute more than expected. Each student will have a total of 200 points to award to the other members of his or her group. Points should be allocated proportionately based on each individual’s contribution to the team during the course, as you perceived it. For example, if you think that teammate A contributed twice as much as teammate B, then teammate A should get twice the allocated points as teammate B. Since each student awards 200 points to team members, the average expected score peer review is also 200 points. The peer review forms will be completed at the end of the course. The form must be signed, but all forms will retain anonymity and confidentiality. Students may be told the total number of points earned from their teammates, but they will not be told the points awarded by any teammate. Peer review is an important component of the course grade, please take this responsibility seriously.
3
Instructor Judgment Grading is more subjective in this course that in many finance courses, and I want to make sure that everyone understands this clearly at the outset. I strive to be fair and impartial when making judgments regarding quality of work, professionalism, and creativity compared to others. Academic Integrity The following is from the Fisher College statement on your obligation to maintain academic integrity. Students are responsible for knowing and abiding by these standards. “As a member of the Fisher College of Business community, I am personally committed to the highest standards of ethical behavior. Honesty and integrity are the foundation from which I will measure my actions. I will hold myself accountable to adhere to these standards. As a leader in the community and business environment, I will pledge to live by these principles and celebrate those who share these beliefs.” Cheating is grounds for failing this course and additional sanctions. In accordance with faculty rule 3335-5-487, all instances of alleged academic misconduct will be reported to the university Committee on Academic Misconduct. If the committee concludes that a violation has occurred, it recommends appropriate sanctions to the Office of Academic Affairs. As stated in the university’s Code of Student Conduct (http://studentaffairs.osu.edu/resources/), students have an obligation to report academic misconduct. Such misconduct can be reported to the appropriate faculty member or program dean; confidentiality is protected. Permitted collaboration for this course is as follows: Group models and presentation - collaboration permitted with teammates only Disability Accommodation To ensure that disability-related concerns are properly addressed from the beginning, students with disabilities who require reasonable accommodations to participate in this class are asked to contact the Office of Disability Services (150 Pomerene Hall, 292-3307). Please also inform me of any arrangements you are making with ODS.
4
COURSE SCHEDULE Note: Models that are replicated from the Excel Modeling in Corporate Finance (EMCF) text are shown below in italics. Group models are shown in the class scheduled as bold.
Date Week1 January 9
January 11
Class Content Introduction Excel skills Formatting Form Groups
Week2 January 16
No Class
January 18
Finance review
January 25
Week 4 January 30 February 1
Week 5 February 6
February 8
EMCF CH1 – Single Cash Flow 1.1, 1.2
Modeling
EMCF CH2 – Annuity 2.1, 2.2 EMCF CH3 – Net Present Value 3.1, 3.2 EMCF CH4 – Real & Nominal Inflation 4.1, 4.2 Memo – group member identification Case – Class Characteristics
Guest Speaker (TBD)
Case – Retirement
Capital budgeting Presentation output
EMCF CH14 – Project NPV 14.1, 14.2,14.3, 14.4 EMCF CH15 – Cost Reduction Project 15.1 EMCF CH16 – Break-Even Analysis 16.1
Guest speaker (CI)
Case – Capital Budgeting
Basics of valuation Financial statement modeling
EMCF CH13 – Capital Structure 13.1 EMCF CH17 – Corp. Financial Planning 17.1-17.5, 17.6
Guest Speaker (TBD) Financial statement modeling
Case – Phoenix
Financial ratios Finance review
EMCF CH5 – Loan Amortization 5.1, 5.2 EMCF CH6 – Lease vs. Buy 6.1, 6.2
- PV, FV, - Annuities - NPV
Week 3 January 23
Assignments
- Bonds - Lease vs. buy
Week 6 February 13
Excel charts
Case – to be determined
February 15
Course recap
EMCF CH7 – Bond Valuation 7.1, 7.2 EMCF CH9 – Stock Valuation 9.1
Week 7 February 21
Final Group Team Presentations
5
