Historical Data Portfolio Formation
Comment: Project overviews are meant to layout the minimum requirements for a given project and to explain the motivation for a given project. Meeting the minimum requirements does not guarantee an A or even a passing grade. These projects are very rich, with many possible extensions to the analysis, various interpretations and recommendations from the results, and alternative methods of analysis that can be applied or discussed relative to the primary method introduced in the project. The finance faculty wishes to give students a chance to differentiate themselves to potential employers in the interview process and have left “meat on the bone” for the students to explore.
In industry, project overviews are rarely, if ever, created. Your boss will simply say “do this” and expect you to formulate the required aspects of the project. In addition, detailed written reports, as are required for this class, are also rarely done in industry. However, employers want to know that potential employees are not only technically capable, but also can communicate their findings effectively in written form. Proof read your papers. DO NOT write sentences that you do not understand but think that the professor will. Use proper grammar, spelling, and effective organization of the material presented. Do not think “what does the professor want”, rather think “what would impress my boss” or “what would get the customer to choose my company”. Bosses and customers are busy. They hate too much information more than to little information. Be clear and to the point. Look for ways to table or plot critical information so that readers can understand the results at a glance. Good Luck.[unique_solution]
Motivation: Asset allocation or the amount of money invested in each asset of a portfolio will make a significant difference in the risk and return profile of a portfolio. This project shows how Excel can be used to optimize asset allocation while achieving specific metrics such as maximizing the Sharpe Ratio of the portfolio. Although the equations for portfolio optimization appear complex, when applied in the Excel software, the process is relatively simple and direct.
Along with general formatting, equations, and general spread sheet design, the student will be introduced to the excel solver. Solver solutions will also require constraint development.
Basic requirements of the report:
1) A short description of each company assigned to the student is required. The description should be no longer than one or two sentences, double spaced, 11 point type, Garamond font. Give the reader key details of what the company does. Know your companies!
2) A brief review of the data is required. The time span of the data, what the data is, where the data comes from, and what the data is used for. Historical data and YTD evaluation data are different because they come from different sources. Although the videos are based on 2014, the same videos are used in future years. Your analysis is based with the most recent year of data as the starting point. Know your data and what is driving the changes in the data!
3) A Sharpe Optimal portfolio is required. Weights, returns, risk, and the portfolio beta must be tabled and discussed. The constraints used for the solver must be tabled and discussed.
4) A Minimum Variance portfolio is required. Weights, returns, risk, and the portfolio beta must be tabled and discussed. The constraints used for the solver must be tabled and discussed.
5) A Targeted Beta portfolio is required. Weights, returns, risk, and the portfolio beta must be tabled and discussed. The constraints used for the solver must be tabled and discussed.
6) Year to date (YTD) results must be tabled and discussed for each portfolio. Focus on comparing the results between each of the evaluated portfolio. Select the best performing portfolio and justify the selection.
7) You must identify and discuss major market events in you analysis year. Specifically discuss how these events impact the specific firms in your portfolio. For example, if there is a major oil shock in your analysis year and you have no oil stocks in your portfolio they you have limited oil exposure and limited impact.
8) Select 3 or 4 stocks that have either very high or very low returns relative to expectation. Analyze what was the driver of these returns. Please note that stating the stock price increased or decreased is not an explanation. Explain the economic impact for the firms.
9) We assume a market return of 10%. What is the actual return on the S&P for the analysis year? How does this change you expected return for your portfolio and firms?
10) Discuss the economic and financial properties of you portfolio.
11) Conduct a probability assessment of the portfolio’s performance based on the assumed return of 10% and the actual return of the S&P for your analysis year.
12) All tables and plots, except the working tables, must be integrated in the text and labeled as Table 1, Table 2, or Figure 1, Figure 2 ect.
13) Working tables should be labeled and included in the appendix as indicated in the videos.