My company is Denso Corportation
Risk and Return Analysis Project (Group Portion)
• Submitting a file upload (Turnitin enabled)
Risk and Return Analysis Project (Please submit your group project here: 1 word document and other other supporting files for your group)
This project applies the concepts and theories that comprise the corporate finance class to real companies. If corporate finance is best learned through application and in real time, there is no better way to learn the subject than to try out things you learned in your chapters on a real company in real time. The purpose of this assignment is to provide a -hands on- experience on risk and return concepts you learned through the course by applying them to a “real life” situation. You'll also have an opportunity to work with fellow class members and as you research your company and present the results of your analysis in both a short paper and short group PowerPoint presentation.
• You should find a group to work with of 3 people. Each of you will select your own company and create a group be the end of first week or I’ll create a group for you. Post your group and companies information on “Risk and Return Analysis Project” section of your discussion board.
• You will use past 3 to 5 years of monthly historical stock price, market index price and risk free rate (Treasury Constant Maturities – 3 month) information in this analysis
• There should be a common theme in each group. For instance, an entertainment group can include movie companies, television broadcasters and syndication companies. An automobile group can include auto companies, suppliers to auto companies and even an auto dealership. In putting together the group of companies, try to pick as diverse a mix of companies as possible (small and large, domestic and foreign, closely held and widely held etc.).
• Avoid the following companies:
o Financial service firms (banks, insurance companies & investment banks)
o Money losing companies
o Companies with large capital arms (GE and the auto companies)
o Real estate investment trusts
• It is a group project. However, each group member must submit the excel file used in the analysis. You will also combine all three companies’ information and prepared a combined group analysis as a final product of your team work. 80% grade on your team project will depend on your final product which is the “Risk and Return Analysis Project” paper and 20% grade will come from your individual data analysis.
• Prepare a 15-minute team group presentation slides about the case and the team's recommendations.
• Please choose a publically held companies that pays a dividend and has some long-term debt on the balance sheet (it should be called Long-Term Debt).
You will use the same companies you have selected for your first project. Retrieve the data for the period of February 1, 2008 to January 31, 2013. Open all of the data files in Excel and copy and paste the data into one file - you will only need the adjusted closing price data for your analysis. Make sure you save your file in Excel format. In addition to the stocks listed above, also download weekly values for the S&P 500 Index for the same time period (from the main Yahoo Finance page go down to Markets Summary and select S&P 500 Index – the Yahoo Finance ticker is ^GSPC). Include the S&P 500 index in your Excel files with the stock prices. Calculate monthly returns for each stock and the S&P 500 Index. It may be helpful to watch the video, entitled “Using Excel with Stock Price Data,” that is posted on your course Website. Here is instruction to get information needed for this project:
• Go to Yahoo!Finance at http://finance.yahoo.com/ (Links to an external site.) . Click on the “S&P” link which will take you to “More on ^GSPC” page. ^GSPC is the ticker symbol for S&P 500 index. It is a good proxy for market.
• Go get historical price on S&P 500, click on historical price. Here you can change the parameters to get pricing data for daily, weekly or monthly. By typing ticker symbol for your company here in the “Get Historical Prices for” box, you can get historical price and financial information.
• To get the risk free rate information got to the Federal Reserve Website: http://www.federalreserve.gov/releases/h15/data.htm (Links to an external site.) and download data for Treasury Constant Maturities – 3 month.
• Create price file with date, and adjusted close price for both market and stocks.
• Watch the instruction video posted in your course Website for more information on data collection and completing this project.
B. Project Format
I could tell you that I am looking for content and that I am flexible on format, but I am sure that you still want to get a sense of typical formatting. There is no one format that will work for everyone. You might find it worthwhile keeping these general suggestions in mind while writing the report:
• An integrated report works better than separate reports for each firm. By an integrated report, I mean having all the companies discussed, by section, rather than having three company reports bound together. For instance, analyze the risk profiles and parameters for all of the companies in your group under a risk and return section.
• Do not rehash what should be common knowledge. Thus, describing the regression procedure for estimating betas is not necessary, but explaining why your firms have the betas that you do is necessary.
• Use tables to summarize data and findings. Since the analysis is paralleled across the companies, you might find it useful (and I definitely will) if you start each section with a table that summarizes your findings for that section across the companies in your group.
• The questions, both in the main section and in the help manual, are designed to guide you through the project. You can alter the sequence and choose the presentation format that works best for you.
• Do not include the data sources that you used for your report (such as the Bloomberg pages or annual reports. If you are including your spreadsheet output, attach only the output pages and not the input pages.)
• Be brief - an integrated report of 4 to 6 pages (single-spaced, double between paragraphs) about the risk and return team analysis.
• Include at least the following in your analysis:
Risk and Return
• What return would you have earned investing in this company’s stock during your analysis period? Would you have under or outperformed the market? How much of the performance can be attributed to management?
• Calculate the average monthly return and standard deviation of returns for all stocks in your group and the S&P 500 index.
• Find the coefficient of variation for each stock and the index.
• Calculate the covariance and correlation between the returns for all pairs of stocks and between each stock and the S&P 500.
• Calculate the average monthly return and standard deviation of returns for an equally-weighted portfolio of the 3 stocks (i.e., the weight of each stock is 1/3 or 33.33 percent and if you have 4 stocks the weight of each stock is ¼ or 25 percent). Note: the best way to do this is to find the return to the portfolio for each month and then use the Excel AVERAGE and STDEV functions to find the average and standard deviation of the portfolio return series. The return to the portfolio in each month is just the weighted average of the returns for each stock for the month: Rpi = w1R1 + w2R2 + w3R3 + … (where Rpi is the return to the portfolio in month i).
• Use regression to find the beta for each of the stocks. Use the S&P 500 index as the market index.
• What is the risk profile of your company? How much overall risk is there in this firm? Where is this risk coming from (market, firm, industry or currency)? How is the risk profile changing?
• How risky is this company’s equity? What is its cost of equity? Hint: use the CAPM equation to estimate cost of equity for each stock.
• What is the weighted average beta of for an equally-weighted portfolio of your the 3 stocks (4 stocks if 4 people in your group).
• What is portfolio return using the CAPM?
• Note that you need to nicely tabulate these calculations in your integrated report and explain.
C. How to Submit Assignment?
• You will create an integrated report including the stocks you have chosen. Thus, you will submit only one group report for your group. However, each person in the group will submit the date tables used to create the final report. 80% of your project grade will come from the integrated analysis and 20% from your individual contribution in the project. Thus it is important to that each of you will submit your company analysis excel file. Please submit your assignment via your course website. I’ll not accept the assignments attached to email. Warning: all assignments submitted via your course Website will be analyzed by turnitin software for originality. The software will report any copied portion in your assignment with the source information.