Financial Forcasting for Chipotle Assignment
madnah
project2015s.docGeneral Requirement
Each person will calculate the intrinsic (fundamental) value of an actual company. While technical analysis is interesting, we take the perspective of the financial manager (CFO) of a corporation when calculating the value of a firm.
Project Guidelines
1. Download recent years of monthly stock prices and historical Financial Statements for your company from Finance Yahoo website. BE SURE that this data is available before you pick your company! The steps to use Finance Yahoo data is attached below.
2. Calculate a beta, cost of equity, cost of debt, weights of equity and debt, and WACC for your company; along with the historical growth rate in sales.
3. You may use and/or review the sample spreadsheet “proforma” provided on the course Titanium website.
4. Forecast the sales in 2015.
5. Project the 2015 balance sheet and the 2015 income statement by the percent of sales method. (You need to calculate and analyze net new financing and may need to do multiple passes to make the final balance sheet balanced, but two passes every year will be good enough for this project).
6. Use the projected data in the 2015 balance sheet and the 2015 income statement to calculate the FCF in 2015.
7. Repeat steps 5, 6, 7 to generate the FCFs for 2016, and 2017.
8. Assume that FCF will grow at a constant rate “g” after 2017. Start with any rate, namely “g0”, which is between 0 and the WACC of the company.
9. Use the Horizon Value Formula to calculate the expected total enterprise value at 2017. (The discount rate is the WACC that you calculated earlier.)
10. Use the forecasted FCFs to calculate the expected total enterprise value at 2014. (Again, the discount rate is the WACC that you calculated earlier.)
11. The firm’s intrinsic equity value at 2014 = the expected total enterprise value at 2014 + cash in 2014 – debt value in 2014.
12. Then calculate the intrinsic value of stock per share, as compared to the actual stock price per share at the end of 2014, to see whether the market overvalued or undervalued the stock at that time.
13. Now conduct a scenario analysis, to see how the results in steps 10-13 will change when you use alternative FCF growth rates, such as when g=g0-1%, g=g0+1%, g=g0+2%, etc. Create a table to summarize the results of this scenario analysis.
14. Use industry multiples (such as P/E, value to EBIT, etc.) to find a rough estimation of intrinsic value of equity, and stock price per share. Examine what FCF growth rate can reach the industry multiple results.
15. Optional: from results in steps 13, 14 and 15, make a conclusion on the reasonable FCF growth rate, intrinsic value of equity and stock price per share, and whether there is an overvaluation/undervaluation at the end of 2014.
16. Optional: provide executive suggestions on how the firm can increase its equity value in the future, based on your analysis of its capital structure, dividend policy, governance, etc.
17. In summary, for this project, you are expected to use 2012, 2013 and 2014 historical data to predict statements in 2015, 2016 and 2017, in order to estimate the intrinsic equity value of the company at the end of 2014. If in any case, the 2015 financial statements of your company have already been available from the beginning of the semester, you could roll one year forward to do the project instead: using 2013, 2014 and 2015 data to predict statements in 2016, 2017 and 2018, in order to estimate the intrinsic equity value of the company at the end of 2015.
18. Also note that you should build links among sheets (for instance, links the 2015 inputs in sheets “2016”, “2017” and “firm value” to the last forecasted column in sheet “2015”, instead of copying and pasting the 2015 data to other sheets). This is necessary to facilitate your data adjustment and reduce the likelihood of mistakes.
19. Sample steps in your presentation:
- Introduce the firm;
- Analyze beta, cost of equity, cost of debt, capital structure, and WACC;
- Interpret sales growth rate, and major ratios of operating variables to sales;
- Discuss its net new financing each year and explain how you plan to finance it in your forecasting;
- Justify this firm’s FCFs;
- Show how to evaluate intrinsic equity value and stock price per share based on a certain FCF growth rate;
- Scenario analysis based on different FCF growth rates;
- Equity value estimations based on multiples.
- Provide a short executive suggestion on how the firm can increase its equity value in the future, from improving its capital structure, dividend policy, governance, etc.
20. Send me the electronic file of your spreadsheet. Check the deadline of this submission.
21. Download and complete the peer evaluation form, and hand in the form by the final exam date.
22. Note that the work you do and/or submit should be your own. Over-using data/tables/charts/PPT Slides from a company’s homepage is plagiarism and also defeats the purpose of education.
Steps to Use Finance Yahoo Data
2. Enter the symbol of the company that you choose, e.g., IBM, then click “GO”
3. Click on “max” under the stock price trend figure.
4. Click on “Historical Prices” near the bottom of the page.
5. Set date range to “Monthly” from “Jan.1, 2009” to “Dec. 31, 2014”, then click “Get Prices”, and download the data to spreadsheet.
6. Go back to IBM’s page, on the left side of the page, click on “Financials”, and find recent three-year annual “income statements” and “balance sheets”. Copy them to the EXCEL, but you may need to retype the data if the copied tables do not allow you to do calculations on the spreadsheets.
Steps to Find the Fiscal Year of a Firm
Wall Street financial analysts do use fiscal year stock return to match the information from financial statements. The fiscal year of a firm (for instance, IBM) can be found as follows:
(1) Go to http://finance.yahoo.com
(2) Enter symbol of the stock, IBM, then click “GO”
(3) Click “Income Statement” in the “Financials” on the left of the IBM page
(4) Switch to “Annual Data” (since the default is “Quarterly Data”)
(5) The first date in “PERIOD ENDING”, 31-Dec-2014, is the end of the fiscal year for IBM, which is just the end of the calendar year. For other firms, these two days might be different.
I don’t mind if you still use calendar year stock return to calculate beta, since usually the beta calculated is not significantly different from the beta if fiscal year stock return is used.
