See attached data file.
Statistical analysis for a financial investment, please view attached Excel spreadsheet and answer the following questions.
1. Consider that you might have a very limited amount of money to invest, and thus would prefer 'cheaper' to more 'expensive' stock. What was the average price of each stock during the year?
(Don't forget to include the S&P 500 in this question or in other questions below).
2. You are more interested in the kind of returns stocks have been getting. What was the average return for each stock during 2009?
3. The average return just tells you what the average is of the daily returns obtained from the stocks, and it is thus a measure of whether, on average, the stock tended to go up more than down or vice versa. A better measure of return would be to consider, had you purchased the stock at the beginning of the year, how much would you have earned by the end of the year? Calculate this return, which is referred to as the 'buy-and-hold' return. Hint: Look at the formulas for the daily returns. Any return is basically calculated the same way: price of the day when you sell minus price on the day that you bought, divided by price on the day that you bought). Based on these numbers, which stock did the best in 2009? Which one had the worst return?
4. Liquidity is also an important factor when investing. There are various liquidity effects to consider, but in this case we are concerned the following: supposed you buy a certain stock and plan to keep it for a while, then run into an emergency (medical treatment? trip to the Bahamas?) and need to sell the stock quickly to get your money back (which may be more or less than you originally invested, depending on how well your stock has done: that is the nature of the game!) An indication of liquidity is the traded volume: that tells you how much interest there is in the market in buying and selling the stock, and therefore tells you, should you need to sell quickly, how likely it is that you will find a buyer. Calculate the mean traded volume for 2009 for all stocks. As you have seen in your studies, averages (arithmetic means) can be deceiving if the distribution of data is not close to being 'normal'. Calculate also the median volume, as well as the 1st and 3rd quartiles (Q1 and Q3), and the inter quartile range. Looking at the mean (and mode) of the traded volume data, and not considering the S&P 500 (since you can't invest in it here), which stock has the highest liquidity? Which one the lowest?
5. In investment theory, 'risk' is neither a bad thing nor a good thing, it can simply be defined as 'the probability that the real returns will be far away from the expected returns'. In other words, if two stocks have the same expected return, say 10%, but one can have real returns between 9% and 11%, while the other can have real returns between 0% and 20%, the latter one is riskier: the real return can move in a much larger space, so it's harder to know what to expect when investing in it. If both have the same expected return, then it is far better to invest in the first stock, at a lower risk. Let's say the expected return of a stock (or index) is the mean of its past returns (as calculated in question 1). Then the 'riskiness' of that stock can be measured by how dispersed are the numbers that make up that mean, in other words, by measures of variability. Calculate the variance and standard deviation of the stock (and index) prices. According to the definition given here, which is the riskiest stock? Which one has the lowest risk? Normally you'd want to invest in a stock that has the highest expected return and at the lowest risk. This is normally a trade-off, and real 'bargains' (stocks that have higher returns and lower risks than others) are hard to find. However, in this example, can you find such a stock?
6. Sometimes it can be a good idea to invest in stocks that 'follow' an index closely. This means that their returns follow a very similar distribution: when the index goes up, the stock goes up and vice versa. Other times you want stocks that don't follow the index that closely. Calculate the correlations between the S&P 500 index and the rest of the stocks, and between each stock and each other stock (hint: it's easier and tidier to arrange this into a â??correlation matrix:: just put the names of all stocks in one line, and in a column, then fill in the matrix cells with the corresponding correlations). If you wanted to follow the S&P 500 closely, which stock would you buy? If you did not want to follow it that closely, which one would you choose?
This solution provides detailed step-by-step calculations of various aspects of stock analysis including Mean Stock Price, Average Return, Buy and Hold Return, Liquidity, Risk, Standard Deviation, Correlation Matrix etc in EXCEL and provides students with a clear perspective of the underlying concepts.