P-A4

Professors Guideline:

Post 1

This posting below explains how to setup the initial template and populate the basic data. I will be posting another discussion shortly on how to run Regression Analysis (that will come later...)    Firstly and most importantly, you will use EXCEL to solve this assignment. There are 2 ways to calculate correlation, simple and multiple regression equations. One is by formulas and the other is by using EXCEL. Once again, you will USE EXCEL. Please see attached template you will be using for running Regression Analysis in Excel. Excel Analysis Tool Pak- This excel add in needs to be installed with your Excel software. Please click the following link on instructions on how to install this free add-in -  http://office.microsoft.com/en-us/excel-help/use-the-analysis-toolpak-to-perform-complex-data-analysis-HP010342762.aspx

      The assignment is slightly confusing in it that it asks for you to analyze Microsoft Annual Sales (Revenue) and 3 other variables which might be related to sales. In the assignment, it states that Microsoft Sales can be attributed to quality, customer service, and location. Now for Microsoft these 3 variables (quality, customer service and location) don’t really make sense with relation to sales and even if some of them do, they are not readily available on the internet in an annualized format like Sales is. So you are free to pick 3 independent variables (such as Operating Income, Inventories, No. of sales staff, costs, Annual taxes paid, anything you can use from the Financial Statements or Microsoft website, etc.) data for which are readily available on the internet for the last 10 years. So when you pick the 3 variables ensure that the annual data for the past 10 years is freely available on the internet and it can be somewhat be correlated to sales. 

First gather data from the Internet on Microsoft’s sales from the past 10 years (2003-2012) and populate the excel file attached. You can get this data from a site like Microsoft’s official website ( http://www.microsoft.com/investor/reports/ar11/financial_review/balance_sheets.html ) or Wikiinvest ( http://www.wikinvest.com/wiki/MSFT ) or Yahoo Finance.  You then also need to pick 3 other variables related to Microsoft and gather the data for the same 10 years and populate the excel template.

Post 2

This posting will explain to run "simple" and "multiple" regression via excel as well as interpret the results. This posting will also explain the objectives of the assignment and ensure you also understand the grading rubrics for this assignment. Once again, this assignment is planned to be solved via EXCEL- So, please plan to use Excel ONLY for this assignment (also make sure you have installed the add in as advised in the previous posting). Below is the grading rubrics- I have attempted to tie in the rubrics with the assignment objective and what needs to be specifically done for more clarity.

Analyzed and predicted what sign each variable will be before running the regression, and explained why they made that prediction.- Before you conduct regressions, analyze and include commentary on how the Microsoft sales will correlate with each of the 3 variables you pick. In the commentary, mention if the correlation for sales and say Operating Income is positive and explain why.	10
Interpreted the simple linear and multiple regression after running the regression.- You will conduct a total of 4 Regressions- 3 simple and 1 multiple. Conduct Simple Regression between Sales (y) and first predictor variable, Sales and second predictor variable and then sales and third variable separately.  Then conduct Multiple regression once with Sales as y (criterion) variable and all 3 variables together.  For each of the above Regressions, look at the excel regression output and determine the correlation and look for significance from the output as well	10
Analyzed and explained if the regression fits the data well. Look at the R squared value for each of the Excel regression outputs and determine the fit.	5
Analyzed and explained if each predictor plays a significant role in explaining the significance of the multiple regression. Only look at the Excel output for multiple regression and look at the p values and determine significance for each of the 3 variables you picked	5
Analyzed and explained if some predictors are not useful.	5
Considered removing the predictors that are not useful and rerunning the regression.	5
Analyzed and explained if the predictors are related too significantly to one another (correlation).	5

Finally, I have attached a sheet which talks about conducting simple and multiple regressions.