NYU Stern School of Business

Undergraduate College

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

Fall 2011

Instructor Details

Perkal, David

Email:dperkal@stern.nyu.edu

Phone:646-672-9433

Office Hours:MW, 11:00am to 12:00pm

Office Location:KMC 10-177

Course Meetings

Meeting time(s):MW, 9:30am to 10:45am

Location:Tisch T-LC21

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	Briefly introduce the financial model. 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). 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	Develop the assumptions for each scenario based on historical and anticipated results. 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. 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	Develop the assumptions for each scenario based on historical and anticipated results. 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. Construct a PP&E and intangibles schedule with capex and D&A expense. Capitalize off-balance sheet financings such as sales of accounts receivable and operating leases (if significant enough to warrant capitalization). 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	Construct the statement of cash flows from operating activities, investing activities and financing activities with the exception of borrowings and repayments of debt. 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. 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. Link the ending cash balance on the statement of cash flows with the ending cash balance on the balance sheet. Calculate estimated cash taxes and cash interest paid and link to the appropriate cells in the statement of cash flows.
Construct the summary pages	Test the financial model to ensure stability and that the balance sheet balances. Construct the summary operating, credit and equity valuation pages and link to the appropriate cells in the projection model. 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. 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. 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:

First Deliverable (30%) – Income Statement Projections (Steps 1 – 41): Upon completion of the income statement projections with the exception of depreciation and amortization and interest expense, you will submit a copy of your projection model which includes the income statement and assumptions and append it to a written report not to exceed fifteen pages single spaced. The report should briefly introduce the company, its products and/or services, locations, competitive advantages, major customers, major suppliers, senior management, board of directors, auditing firm, and any major changes including acquisitions, divestitures or lawsuits. The introductory portion should be no more than one page. The balance of the report will include a detailed discussion of and rationale for each assumption in the three scenarios embedded in your forecasts corroborated by historical performance, recent developments and anticipated results.
Second Deliverable (30%) – Balance Sheet Projections and Statement of Cash Flows (Steps 42 – 79): Upon completion of the balance sheet projections and construction of the statement of cash flows, you will submit a copy of your projection model which includes the balance sheet and assumptions and statement of cash flows and append it to a written report not to exceed fifteen pages single spaced. The report will include a detailed discussion of and rationale for each assumption in the three scenarios embedded in your forecasts corroborated by historical performance, recent developments and anticipated results.
Third Deliverable (40%) – Summary Statistics and Valuation Analyses: You will submit a copy of the summary statistics, discount cash flow valuation and relative valuation and append it to a written report not to exceed fifteen pages single spaced. The report will include a detailed discussion of the operating data with an analysis of the profitability and liquidity of the company and the credit data with an analysis of the capital structure and solvency of the company. This section of the report should only contain an analysis under the downside scenario as the goal is to assess the company’s ability to generate cash flow and manage leverage in a difficult operating environment. The report will also contain a detailed discussion of the equity valuation data with an analysis of the assumptions embedded in the DCF, the equity valuation attained with the DCF versus the multiples and your opinion on the appropriate value of the stock predicated on your qualitative and quantitative analysis. The equity valuation section of the report should only contain an analysis under the scenario that you feel is most likely for the company. Finally, you will e-mail the professor your projection model so that he may confirm its stability and that the balance sheet balances.

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.

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:

Exercise integrity in all aspects of one's academic work including, but not limited to, the preparation and completion of exams, papers and all other course requirements by not engaging in any method or means that provides an unfair advantage.
Clearly acknowledge the work and efforts of others when submitting written work as one’s own. Ideas, data, direct quotations (which should be designated with quotation marks), paraphrasing, creative expression, or any other incorporation of the work of others should be fully referenced.
Refrain from behaving in ways that knowingly support, assist, or in any way attempt to enable another person to engage in any violation of the Code of Conduct. Our support also includes reporting any observed violations of this Code of Conduct or other School and University policies that are deemed to adversely affect the NYU Stern community.

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.