Spreadsheets for Tracking Stocks
Spreadsheets for Tracking Stocks document sample
Shared by: aia37607
Guidelines for Company Reports The company reports are designed to get you ready for the new stock presentations. They provide us an opportunity to monitor the existing stocks in our portfolio. The company reports have some specific information that the groups must collect, analyze, and report on for one of your stocks that you are to follow. (I will let you know which stock you are to follow in detail). You must prepare a written summary of your report that will be collected and graded. You must include any calculations (spreadsheets) that you use for your analysis. Your written report should focus on the descriptions and interpretation of your analysis. Do not assume that your reader knows the methods that you use. Also, you should prepare a 5 minute presentation of your findings to be presented in class. Additionally, I may ask each group if there is any relevant information about their companies. Do not waste class time discussing trivial information. However, I will be tracking the stocks and I will deduct points if you do not mention all relevant information. Company Report #1 (Due Friday, January 26): Basic Information (This should be done for all stocks that you are to follow. All other detailed reports will focus only on one stock.) -- What does the company do? -- Where does it stand in its industry? -- How has it performed? -- Basic valuation ratios (P/E, P/B, P/S) -- What are the growth expectations for the company? Company Report #2 (Due Friday, February 9): Financial Statement Analysis --Industry comparison of financial ratios -- Where is the company strong and where is it weak? -- Is the company a good company? Company Report #3 (Due Friday, February 23): DCF Valuation -- Value company using FCFF valuation technique -- Discuss DCF Valuation Input Assumptions, make sure all assumptions are justified Company Report #4 (Due Wednesday, April 11): Forecasting Financials -- Value company using financial statement forecasting Guidelines for Market Update Update the class on any major movements in the equity markets and on any important events in the financial world during the past week. Paying attention to recent “Abreast of the Market” and “Heard on the Street” columns from the Wall Street Journal will help you enormously in this. Note, that just saying “the Dow was up 30 points last week” will get you an F for this part of the course. Also, don’t just copy the headlines for the week. Be creative, what we need is your analysis of the market. Try to emphasize any news from or that will affect our industries. We need to know how market events will affect our investments. You will be required to provide a brief write up of your update (see brief sample below, I expect you to have more news) as well as a 5 to 10 minute summary to the class on your update. Very Brief Sample Market Update Dow: Down 94.60 to 8824.41 (-1.06%) for the week at the close on Friday. Nasdaq: Down 43.96 to 1347.78 (-3.16%) for the week at the close on Friday. Major news: Consumer confidence fell to lowest levels in 9 months. -- Helped explain drop in market -- Offset positive economic news regarding durable goods orders -- Has big impact for retailing, but positive news regarding durable goods is a possible sign the economy is stabilizing Technology stocks hit hardest -- Nortel Networks announced another round of layoffs. Impacts all of technology sector which, given the dot com bust, is particularly sensitive to bad news. Merrill Lynch fined $7.7 million to couple who claimed firm mismanaged their account -- Importance of fiduciary responsibility to clients -- Could lead to further lawsuits, dragging down financial services sector? Industry News: Retail sector experienced drop in sales for the month. -- Hits our retail stocks hard, may lead to cuts in profits Natural gas prices rising -- Benefits energy concerns like DGAS -- May affect consumers, could reduce discretionary spending (YCC could be affected) New Stock Assignments These assignments are designed to help you in the process of picking a new stock to present to the class. Essentially, these assignments get you to look at your industry and begin to make some judgments about the stocks in that industry well before you have to select a stock to present. No presentations of the new stock assignments will be made but I may ask you some questions. Assignment #1 (Due Monday, January 22): Provide me a spreadsheet listing of all stock within your industry based on the Yahoo industry categories that you have been assigned. Email me the spreadsheet by the due date. I do not want a hard copy! Assignment #2 (Due Monday, February 12) Based on Peter Lynch’s criteria for selecting a stock, identify the 10 companies within your industries that you believe Peter Lynch would deem as potentially good investments. For each company categorize the stock based on Peter Lynch’s types of stocks and list the good (and bad) “Peter Lynch” characteristics that you have observed. Provide me with a written summary of the above information. Assignment #3 (Due Wednesday, February 21) List your top ten companies within your industries. These do not have to be the same as the 10 best Peter Lynch companies. Basically I want you to give me what you think would be the 10 best stock picks within your industries. For each of your picks, explain why you think the company is a good company and why you think that the company may be undervalued. Guidelines for New Stock Evaluation: Your report should be structured as follows: 1. Cover page and executive summary: -- Following the cover page, you should have a one-page executive summary of parts 2-5 below as well as a discussion as to why the company would fit the fund’s “value” strategy 2. Company and industry analysis -- What does the company do? (not too much history, just the main points, don’t copy stuff from websites, etc. without citation) -- What industry is the company in and who are the main competitors? -- What challenges do the company and the industry face? -- What major events have taken place in the company over the last couple of years and how do they impact the value of the company? -- Who owns the company and how does that impact value? -- Any other things we need to know about this company or industry? This is the section where you demonstrate your knowledge of the company and its industry. 3. Ratio analysis -- A thorough ratio analysis should be done covering all categories of ratios -- You should spend a significant amount of time on this discussion. Make sure to interpret the ratios. What do they mean? Where is the company strong and where is it weak? -- Ultimately, we need to know if this is a good company? 4. Expectations -- Based on your industry and company analysis and your ratio analysis, what do you expect from your company? -- Is it fast growing, slow growing, etc.? -- What does the future hold? -- Where will increases in value come from? -- Why is the company undervalued? -- These expectations will for the basis for the assumptions you make in your valuations -- Expectations should come from what the company has done and will do in the future 5. Valuation -- List all assumptions that you will be using and justify the assumptions (if you have done a good job in parts 3 and 4, this should be easy) -- Discuss the valuation methods used. Don’t assume that the reader knows the details of each method. -- Use all the valuation approaches discussed in class. Hopefully, you will converge at an answer. -- Sensitivity analysis should be done. How do your valuations change when your assumptions change? -- Come up with your target price and recommendation (strong buy, moderate buy, weak buy, don’t buy) -- All computations (spreadsheets) should be included and make sense -- You will be required to provide your computations to the class one class period before your presentation. -- You are also required to provide me with a final version of your spreadsheets. 6. In-Class Presentation -- You should prepare a 15 minute presentation that summarizes your findings and delivers your recommendation. The rest of the groups should be familiar with the stock and the methods used, so just get to the results. We will leave 10 minutes for discussion, so be prepared to answer questions. Guidelines for supplemental stock analysis: For each stock presented, your group must have a supplemental analysis (10 in total for the semester). The supplemental stock analyses are designed to make sure that your group is familiar with the stocks that are being presented. You can’t make an informed decision if you are not well informed. The supplemental analysis should consist of two parts: 1. A one page executive summary that covers your general perceptions of what the company does, if this company is a good company, and if it is undervalued. 2. A summary of your impression regarding the valuation of the company, including (but not limited to): -- An idea of a reasonable growth rate for the company -- An understand of any issues that may affect valuation (negative earnings, etc.) -- An understanding of their basic valuation ratios (P/E, P/B, P/S) and if these seem high/low -- You should value the company using one of the techniques described in class -- Give your impression about the value of the stock (over or under valued) I will ask for a quick summary of your groups’ impression of the stock during the new stock presentation. Also, I will ask for questions from the audience, so be prepared! We need to pick good investments.