Beta analysis by period for MSFT & GOOG

Jun 06

A review of the monthly rates of return for Google and Microsoft for two successive five year periods allow me to calculate the beta, ß, for each period. Recall that ß is calculated under the Capital Asset Pricing Model (CAPM) as follows:   Getting the data It was easy to download the data on yahoo finance. Once there, I was able to set the time period of interest and frequency as shown here (click to enlarge).   Once you have the set of data that interests you, scroll down to the bottom and download the data, as shown here.   Note that in order to calculate the ß in Excel, you need the market comparison data as well. In order to get that I change my search to ^DJI with the same period and frequency as shown above. However, I noticed that they don’t provide a download option for that data. Since the data is shown in tabular format, you can copy and paste it directly in to Excel. Don’t forget to click Next until you have grabbed all the necessary pages. Google data only went back to 2004, so the analysis for the second five year period for Google has fewer data points than the same analysis for Microsoft. Setup in Excel I setup a workbook with three sheets, one for MSFT, one for GOOG and the last for DJI, or market data. Since I wanted to get two success five year periods, I added an empty line at the five year mark. I hid all but the date, closing price and adjusted closing price on each sheet. Adjusted closing price In order to get the most accurate historical view of return rate, I used the adjusted closing price to calculate my monthly return. Formatting I then display the market data side by side with the stock specific return rate on each page. Excel conditional formatting made it easy to show movement graphically. That graphical view is a good sanity check while reviewing the formulas. Scatter plot Finally I inserted a scatter plot and used Excel’s built in linear mapping to show the ß line and calculate R-squared. Note that I also calculated ß using the SLOPE formula...

Read More

Microsoft vs. Google – executive compensation

May 18

The course project for Corporate Finance this session involves an analysis of two competing companies, at least one of which is on the Dow. Sources suggested for information include, and Additional insights may be available in the Wall Street Journal. Choice of Microsoft and Google The first question involves a comparison of executive compensation between the two companies chosen. Since I own a software business, I chose Microsoft (MSFT) and Google (GOOG) as competing companies for my evaluation. In the software space, these two companies also represent very different approaches to software design, development and consumer consumption of the end result. Business Models Before I jump in to my analysis, it might be worth mentioning that, while both are software companies, they follow different business models. Microsoft has been more of a traditional software company, selling software as a product. That includes packaging, distribution, wholesale, OEM and other supply chain concerns. Google has been a software as a service (SaaS) company from the beginning. Delivery of their products has been almost exclusively online. Both companies have deviated from their roots in recent years. For example, Microsoft is attempting to move into the cloud  and services space with offerings like Bing, Azure and Office Online. Meanwhile, Google has been moving into a product space with their Android devices and Chromebooks. Google’s purchase of Motorola is further evidence of their interest in the product space.  Compensation Analysis The professor suggested that the 10-k filing would be a good place to get information for this project. Unfortunately, the 10-k filing section for Executive Compensation only made a reference to a Proxy document. I did a little more digging and discovered that I could find the Proxy statement using these steps: Go to In the main search type the company name (ticker symbol doesn’t work). Also make sure the option next to search has “company filings” selected. Click the link under the “CIK” column for the company you want. Multiple results will come up. In my experience, you want the result with the SIC value in the description, as shown below. In the “filter results” panel near the top, type “DEF” into the “Filter Type” box Results are sorted by filing date by default. Choose...

Read More