Company name is microchip technology inc. Get data from Bloomberg if possible. Please answer all questions thoroughly and correct. Use excel and Word document for explanation.
Isaiah Hubbard, a recent finance graduate, has just begun working for Thompson and Ezell, an investment firm. Jameya Thompson, one of the firm’s founders, has been talking to Isaiah about the firm’s investment portfolio.
As with any investment, Jameya is concerned about the risk of the investment as well as the potential return. More specifically, because the company holds a diversified portfolio, Jameya is concerned about the systematic risk of current and potential investments. One position the company currently holds is stock in (Microchip Technology ), Inc. (Microchip Technology ) designs, manufactures, and markets thermal imaging and infrared camera systems. Although better known for its military applications, the company has divisions that design products for other applications such as automotive night vision, commercial products that require minute temperature difference measurements, recreational marine usage, and firefighting.
Thompson and Ezell currently use a commercial data vendor (Bloomberg Historical Prices [HP]) for information about its positions. Because of this, Jameya is unsure exactly how the numbers provided are calculated. The data provider considers its methods proprietary and will not disclose how stock betas and other information are calculated. Jameya is uncomfortable with not knowing exactly how these numbers are being computed and also believes that it could be less expensive to calculate the necessary statistics in-house.
To explore this question, Jameya has asked Isaiah to do the following assignments:
Download the ending monthly stock prices for (Microchip technology inc) for the last 61 months using the Bloomberg Terminal. Use the adjusted closing price to account for stock splits and dividend payments. Next, download the ending value of the S&P 500 index over the same period. Find the three-month Treasury bill constant maturity rate for the historical risk-free rate.
What are the monthly returns, average monthly returns, and standard deviations for (Your Company) stock, the three-month Treasury bill, and the S&P 500 for this period?
Beta is a risk management tool widely used in financial modeling. It demonstrates the volatility (riskiness) of an asset or a portfolio in correlation to the market. In reality, most professionals use some benchmark index, such as the S&P 500.
Use the following link for MS Excel calculations details
Interpretation
It is assumed that the market has a beta of 1. If the beta of a security is >1, the security is more volatile (more risky) than the market; however, in case it is <1, the stock is less volatile (less risky). Betas help calculate yields and returns for securities.
Beta in Excel
Here are the steps to calculate Beta in Excel:
Retrieve the historical price of a security and the benchmark index in 2 separate columns.
Calculate the price change for the security in percentage with the use of this formula:
Calculate Beta using the SLOPE function. It works the following way: SLOPE (known_ys; known_xs). Known_ys stands for the % of equity change range, and known_xs means the % range of index change. The returned value is the beta.
Compare your calculated beta for (Your Company) to the beta on Bloomberg Terminal. How similar are they? Why might they be different?
