Dcf Model And Company Forecast

Fin 4041 First Project Due date: 10/3/2021 What: submit a three-page buy/sell recommend regarding Nike. This report is to be supported by a valuation of the firm (DCF model and company forecasts) which you have completed in the forecast modeling spreadsheet. Specifics of report: Submit a three-page report that focuses on your recommendations and assumptions regarding Amazon. Your report must state a buy/sell recommendation-you recommend to buy/sell or hold the stock. Base your recommendation upon the closing price of Nike on 9/27/2021. Your report should support your recommendation. Please keep in mind that the report is only three pages so please keep your supporting arguments brief and succinct. Your valuation should be a key component of your support for your recommendation. A recommended breakdown between assumptions and recommendations is two pages devoted to your assumptions and one page devoted to your recommendations. Specifics of spreadsheet: You are to use the pre project 1 spreadsheet that we have done in class as a template. You should download four years of historic financial data for Nike. You must include the income statement, balance sheet and statement of cash flows. Once you have downloaded the Nike data, you should create your own forecast models, using the pre-project 1 spreadsheet as a template. DONOT try to fit the Nike data into the actual pre-project spreadsheet. You must forecast Nike’s Income statement and balance sheet for a minimum of four years (you don’t need to forecast the statement of cash flows, but it does provide a lot of useful information). Utilizing your forecasted income statements and balance sheets, you should create your discounted cash flow model. Your DCF model will provide you with the value of Nike-enterprise value. To determine your stock price, subtract net debt (debt-cash) from your DCF valuation. This gives you the market value of equity. Then divide the market value of equity by the number of shares outstanding to determine your forecasted stock price. DCF Model: Keep in mind that your model should generate a specific value. Use most recent 10Q and 10K for information such as shares outstanding, capital structure, etc. DCF Data: Beta: .87 Risk free rate: 1.32% Market premium: market return-risk free rate: 5.5% Enterprise value: EV=Equity + Net Debt (Debt-cash). The DCF model is also the enterprise value. This basic relationship enables you to determine the equity value of the firm that you have projected based upon your forecasts. Compare your equity value (the intrinsic value) to the market value to help determine what your recommendation should be. Process: Your process should follow the discussions from class: 1) know your company. A great place to start is Porter’s five forces. These should guide you on how you forecast your company; 2) Create your spreadsheet. You might want to consider putting dummy numbers in the sheets to make sure everything works correctly. 3) import historical data into your spreadsheet from the company’s 10K and 10Q. 4) Forecast the company for at least 4 years and maybe more; 5) find the free cash flow for the firm; 6) create and calculate the DCF model and find the firm value; 7) use the EV formula to find the value of equity and then the value of the stock; 8) write up your assumptions and recommendations; 9) take the rest of the day off!!! Grading: Spreadsheet 75% Report 25% 1. Spreadsheet a. Spreadsheet is 75% of the grade b. Spreadsheet will be graded on the following criteria i. Does it work? Does the spreadsheet calculate correctly ii. Formatting-how easy is it to use and follow iii. Hard coding vs. data entry-very few items should be hard coded iv. Do the financial statements work correctly and are they articulated-this is critical v. Spreadsheet must be original-not a copy of someone else’s or the spreadsheet I posted on Canvas vi. The degree to which your data is generated from your forecasting sheet to your financial statements 2. Executive summary a. Summary is 25% of the grade b. Summary will be graded on the following criteria i. Show me your assumptions ii. Show me your thought process (with your assumptions in mind) iii. Talk about the factors that influenced your forecasts-include the how and why (see example below) iv. Remember that the process will be of greater importance than the outcome v. No more than 3 pages-be concise vi. Walk me through your thought process as much as you can, help me see what you did and why you did what you did. The more you can do that the better your grade-and the outcome of your forecasts vii. Format-bullet points are fine (actually preferred) 3. Example a. The how and why i. Item-AG Lafley rejoining P&G ii. Effect on forecast-potential revenue increase and decrease of SGA iii. How-increase revenue growth by 1.5% and decrease SGA as a percent of sales by .5% iv. Why-Lafley was brought in by the board to cut costs and more aggressively pursue topline growth. Has a history of being able to achieve his goals. v. Please keep in mind that the above is an example!