NYU Stern School of Business

Undergraduate College

ACCT-UB.0023.001 (C10.0023): Financial Modeling and Analysis

Spring 2012

Instructor Details

Perkal, David

dperkal@stern.nyu.edu

646-672-9433

F, 12:30pm to 2:00pm

KMC 10-98

 

Chang, Ashley

ac2695@stern.nyu.edu

TBD

TBD

 

Course Meetings

F, 9:30am to 12:15pm

Tisch T-UC25


Final Exam:

Schedule exceptions
    Class will not meet on:
    Class will meet on:

 

Course Description and Learning Goals

Making educated decisions today by forecasting operating and financial performance is a critical exercise for owners, managers, consultants, investment bankers, creditors, equity and credit analysts, and investors such as private equity groups, hedge funds, institutional investors and individuals.  As we are ineluctably constrained by our inability to predict the future, financial projections are rarely perfect.  Nonetheless, we endeavor to model several scenarios predicated on historical and anticipated results to derive various conclusions.  There are myriad variables which can certainly be incorporated into projections.  However, the best financial model is always the one that is stable and robust, yet simple and easy to build, navigate and audit. 

The goal of this course is to instruct step-by-step how to build a comprehensive, multi-purpose projection model in Excel and subsequently interpret selective operating, credit and equity valuation data.  Based on this information, you will learn how to evaluate a company’s operating and financial performance; how to develop an appropriate capital structure by structuring debt and equity transactions which not only protect both the creditors and shareholders, but also create an appropriate risk and reward equilibrium; and how to formulate an educated investment opinion. 

 

Course Pre-Requisites

The prerequisites are Principles of Financial Accounting and Foundations of Financial Markets.  The knowledge and skills attained in these courses are essential in understanding how to construct a financial model and interpret selective operating, credit and equity valuation data.  You should also be very proficient in Excel before matriculating as this course is not designed to teach it.    

 

Course Outline

The class lectures are based on a projection model of a fictitious publicly-traded company called Victory Sportswear, Inc.  Each lecture we will deconstruct the financial model in detail and learn how to replicate it from scratch.  As the process of building a projection model is a methodical, sequential process, the topics in the course will be covered in the order listed in the chart below.  There is no specified time frame for each step in the process as some will be covered more rapidly than others.  Therefore, it is incumbent upon you to attend each lecture to ensure that you extract the most benefit from the course and thus are able to build a financial model on your own.  

 

Topic

Description

Introduction to the financial model

 

  1. Briefly introduce the financial model.
  2. Input the prior three years and year-to-date financial statements, and prepare the columns for a five-year projection model with the balance sheet audit formula (A = L + SE).
  3. Prepare the cover page and introduce the “CHOOSE” function as it pertains to inputting the downside, flat and upside case scenarios.

 

Project the income statement

 

  1. Develop the assumptions for each scenario based on historical and anticipated results.
  2. Input those assumptions and link them to the appropriate cells in the income statement with the exception of D&A expense, interest expense,rent expense (if significant enough to warrant capitalizing operating leases), other income (expenses) and other non-cash expenses such as reserves.
  3. Discuss quality of earnings and non-recurring charges such as asset impairments, restructuring and gains and/or losses on asset sales. 

 

Project the balance sheet

 

  1. Develop the assumptions for each scenario based on historical and anticipated results.
  2. Input those assumptions and link them to the appropriate cells in the balance sheet and income statement with the exception of the cash, PP&E and intangibles and debt accounts.
  3. Construct a PP&E and intangibles schedule with capex and D&A expense.
  4. Capitalize off-balance sheet financings such as sales of accounts receivable and operating leases (if significant enough to warrant capitalization). 
  5. Link the cells in the PP&E and intangibles and capitalized accounts receivable and operating lease schedules to the appropriate cells in the balance sheet and income statement. 

 

Construct the statement of cash flows

 

  1. Construct the statement of cash flows from operating activities, investing activities and financing activities with the exception of borrowings and repayments of debt.
  2. Construct the debt worksheet with each tranche of debt and learn how to construct “IF” and “MIN” statements, revolver cash needs and repayments and cash waterfall payments.
  3. Link the cells in the debt worksheet to the appropriate cells in the balance sheet, income statement, and statement of cash flows from financing activities.
  4. Link the ending cash balance on the statement of cash flows with the ending cash balance on the balance sheet.   
  5. Calculate estimated cash taxes and cash interest paid and link to the appropriate cells in the statement of cash flows.    

   

Construct the summary pages

 

  1. Test the financial model to ensure stability and that the balance sheet balances.
  2. Construct the summary operating, credit and equity valuation pages and link to the appropriate cells in the projection model. 
  3. Analyze the data and learn to how to evaluate the operating and financial performance, how to develop a viable capital structure including appropriately structuring debt and equity transactions and how to formulate an educated investment opinion.
  4. Discuss “Sources of Funds and Uses of Funds” in a transactional-based model for a merger, acquisition, divestiture, LBO and debt or equity recapitalization. 

 

 

Required Course Materials

There is no textbook for the course.  During the lectures, we will be utilizing a projection model in Excel to which you will have access via the course Blackboard.  Each class you will need to bring your company 10-K, most recent 10-Q and a laptop with any version of Excel with sufficient battery power to last three hours as you will be required to replicate the techniques learned to build your own financial model.

 

Assessment Components

N/A

 

Grading

Your grade is based on a class project which includes three deliverables described below and an in-class presentation.  The class project will provide you with the opportunity to apply the skills and analytical techniques learned in the classroom to an actual company.  Upon completion of the project, you should have the confidence and ability to not only build a stable, robust projection model, but also to interpret selective operating, credit and equity valuation data.

By the second lecture, you will select a publicly-traded company listed on either the NYSE or NASDAQ.  A list of all of the companies in the S&P 500 Index is posted on the course Blackboard to assist in your selection.  However, you should not select a financial services company to analyze as it can be difficult to navigate. 

You will print the most recent 10-K, 10-Q and any other relevant SEC filings such as an 8-K from www.sec.gov.  These filings will serve as primary source documents for the project and provide the requisite foundation for building your assumptions embedded in your financial model.  You should also avail yourself of recent press releases, news stories and any other pertinent information which will ultimately deepen your knowledge of the company. 

You will start by inputting into Excel three years of historical and year-to-date financial information, i.e. income statements, balance sheets and statements of cash flows.  You will then begin to build a five-year projection model similar to the one we will be utilizing in class which entails developing three case scenarios, i.e., downside, flat and upside, and generating summary operating, credit and equity valuation data.  The summary operating data will include profitability and liquidity statistics.  The summary credit data will include capital structure and solvency statistics.  The summary equity valuation data will include a discounted cash flow (DCF) valuation and several relative valuation multiples such as Price/Earnings, Price/Sales, Enterprise Value/EBITDA and Enterprise Value/Sales.

Your grade comprises the following three deliverables: 

     

 

Academic Integrity

Integrity is critical to the learning process and to all that we do here at NYU Stern. As members of our community, all students agree to abide by the NYU Stern Student Code of Conduct, which includes a commitment to:

The entire Stern Student Code of Conduct applies to all students enrolled in Stern courses and can be found here:

Undergraduate College: http://www.stern.nyu.edu/uc/codeofconduct
Graduate Programs: http://w4.stern.nyu.edu/studentactivities/involved.cfm?doc_id=102505

To help ensure the integrity of our learning community, prose assignments you submit to Blackboard will be submitted to Turnitin.  Turnitin will compare your submission to a database of prior submissions to Turnitin, current and archived Web pages, periodicals, journals, and publications.  Additionally, your document will become part of the Turnitin database.

 

Recording of Classes

Your class may be recorded for educational purposes

 

Students with Disabilities

If you have a qualified disability and will require academic accommodation of any kind during this course, you must notify me at the beginning of the course and provide a letter from the Moses Center for Students with Disabilities (CSD, 998-4980, www.nyu.edu/csd) verifying your registration and outlining the accommodations they recommend.  If you will need to take an exam at the CSD, you must submit a completed Exam Accommodations Form to them at least one week prior to the scheduled exam time to be guaranteed accommodation.

 

Group Projects

Guidelines for Group Projects

Business activities involve group effort. Consequently, learning how to work effectively in a group is a critical part of your business education.

Every member is expected to carry an equal share of the group’s workload. As such, it is in your interest to be involved in all aspects of the project. Even if you divide the work rather than work on each piece together, you are still responsible for each part. The group project will be graded as a whole:   its different components will not be graded separately. Your exams may contain questions that are based on aspects of your group projects.

It is recommended that each group establish ground rules early in the process to facilitate your joint work including a problem-solving process for handling conflicts. In the infrequent case where you believe that a group member is not carrying out his or her fair share of work, you are urged not to permit problems to develop to a point where they become serious. If you cannot resolve conflicts internally after your best efforts, they should be brought to my attention and I will work with you to find a resolution.

You will be asked to complete a peer evaluation form to evaluate the contribution of each of your group members (including your own contribution) at the conclusion of each project. If there is consensus that a group member did not contribute a fair share of work to the project, I will consider this feedback during grading.

 

Re-Grading

The process of assigning grades is intended to be one of unbiased evaluation. Students are encouraged to respect the integrity and authority of the professor’s grading system and are discouraged from pursuing arbitrary challenges to it.

If you believe an inadvertent error has been made in the grading of an individual assignment or in assessing an overall course grade, a request to have the grade re-evaluated may be submitted. You must submit such requests in writing to me within 7 days of receiving the grade, including a brief written statement of why you believe that an error in grading has been made.

 

Professional Responsibilities For This Course

Attendance

 

Participation

In-class contribution is a significant part of your grade and an important part of our shared learning experience. Your active participation helps me to evaluate your overall performance.
You can excel in this area if you come to class on time and contribute to the course by:

 

Assignments

 

Classroom Norms

 

Stern Policies

General Behavior
The School expects that students will conduct themselves with respect and professionalism toward faculty, students, and others present in class and will follow the rules laid down by the instructor for classroom behavior.  Students who fail to do so may be asked to leave the classroom. 

 

Collaboration on Graded Assignments
Students may not work together on graded assignment unless the instructor gives express permission. 

 

Course Evaluations
Course evaluations are important to us and to students who come after you.  Please complete them thoughtfully.

 

Printer Friendly Version