Assignment Requirements
So basically I have the Excel chart and the example assignment from my friend. What I need you to do is to use the Excel chart data and apply it into my friend’s assignment. Not to copy just use your own word and make it like a new work but use my friend’s assignment as reference. and Probably add more definitions for it.
So to sum up. All the numbers/data you will need to use from the excel chart and writing style I want you to follow my friend’s assignment as reference.
I have only 12hours. so please let me know if anything you dont understand now cuz I’ll be sleeping in 2hours(3am) cuz it’s 1am now.
I will upload all the document for you.
Introduction
The purpose of this essay is going to illustrate that relationship and interactions between variable factors and regression model, and then predicting future changes of company – Apple’s share price in share market. This report is constructed by data collecting, distinguishing and built up a multiple regression model to analysing information and graphs, such as P value, VIF and F test etc.
Original Data:
Date  Gold_x_AAPL_acc1  Silver_vel10_x_Baltic_dry_acc2  Oil_vel5_x_AAPL_vel7  Baltic_dry_vel5_x_AAPL_vel11  5year_vel4_x_SP500_vel12  Future_change 
12/11/2013  0.021007  0.039063  0.182188  0.103333  0.291806  0.2625 
11/11/2013  0.027378  0.064445  0.235156  0.253993  0.301476  0.316667 
8/11/2013  0.08724  0.015625  0.146979  0.234896  0.372431  0.395833 
7/11/2013  0.034236  0.012708  0.110677  0.360972  0.045833  0.658333 
6/11/2013  0.076563  0.197777  0.095  0.475694  0.331459  0.320833 
5/11/2013  0.054861  0.235989  0.011458  0.375  0.335313  0.325 
4/11/2013  0.125417  0.393021  0.014236  0.155694  0.473611  0.254167 
1/11/2013  0.079132  0.5875  0.038889  0.055087  0.516076  0.541667 
31/10/2013  0.076823  0.60783  0.201562  0.040399  0.539688  0.554167 
30/10/2013  0.209792  0.092083  0.196875  0.014844  0.437708  0.445833 
29/10/2013  0.068177  0.395416  0.382673  0.022083  0.455381  0.745833 
…  …  …  …  …  …  … 
…  …  …  …  …  …  … 
11/02/2013  0.416666  0.129167  0.444618  0.455381  0.119401  0.408333 
8/02/2013  0.628334  0.551597  0.189496  0.367362  0.189844  0.6 
7/02/2013  0.847084  0.477969  0.148541  0.015625  0.053437  0.916667 
6/02/2013  0.320833  0.364653  0.183768  0.012413  0.13125  0.929167 
5/02/2013  0.824913  0.322917  0.193958  0.004375  0.305278  0.3 
4/02/2013  0.075347  0.362465  0.232916  0.005208  0.195972  0.641667 
1/02/2013  0.496875  0.44625  0.008594  0.006094  0.385486  0.475 
31/01/2013  0.419966  0.30586  0.019201  0.022656  0.407057  0.820833 
Those data which applied in this essay are collected from Yahoo finance (Yahoo finance, 2013). Yahoo finance contains a range of statistics, and those data is an open resource to use. Therefore, this report picked up 5 different independent variables from Yahoo finance database to analysing the fluctuation of share price and predicting future changes of share price.
Identification of Variables
Y variable
Variable: future change
X variable
Variable 1: Gold_x_AAPL_acc1
The figure shows that relationship between gold and Apple.
Variable 2: Silver_vel10_x_Baltic_dry_acc2
The number asserts that raw material and shipping goods has impacted on Apple’s business operation.
Variable 3: Oil_vel5_x_AAPL_vel7
The information points out that the relevance between oil prices and the cost of shipping goods worldwide.
Variable 4: Baltic_dry_vel5_x_AAPL_vel11
According to the figure to understanding the cost of shipping goods worldwide, such as deliver finish goods from manufactory to markets.
Variable 5: 5year_vel4_x_SP500_vel12
By comparing the short term share price with SP500 figure to demonstrates Apple’s performing in the market.
Check inputs for collinearity
Collinearity is the undesirable situation where the correlations among the independent variables are strong. Collinearity will misleadingly inflatethe standard errors (Evans, 2013). Thus, it makes some variables statistically may be found not to be significantly different from 0 while they should be otherwise significant.
First of all, we want to see if any of those inputs have collinearity by using PHStat Multiple Regression function to analysis VIF.VIF, variance inflation faction is a figure to understand issue in linear model. When independent variable has got linear issue, then it means there are some similar explanations between independent variables. Moreover, the issue drives researcher could not demonstrate how many degree of impact on Y. Researcher would recognise there is no linear problem when the number of VIF smaller. If VIF for one of the variables is around or greater than 5, there is collinearity associated with that variable, one of these variables must be removed from the regression model (Evans 2013). As can be seen from the case, all 5 independent variable’s VIF are less than 5, therefore, there is no linear issue in this report.
From the result’s outputs show that as below:
Regression Analysis 

5year_vel4_x_SP500_vel12 and all other X  
Regression Statistics  
Multiple R  0.2601 
R Square  0.0677 
Adjusted R Square  0.0484 
Standard Error  0.2053 
Observations  199 
VIF  1.0726 
Regression Analysis  
Baltic_dry_vel5_x_AAPL_vel11 and all other X  
Regression Statistics  
Multiple R  0.4759 
R Square  0.2265 
Adjusted R Square  0.2105 
Standard Error  0.1770 
Observations  199 
VIF  1.2928 
Regression Analysis  
Silver_vel10_x_Baltic_dry_acc2 and all other X  
Regression Statistics  
Multiple R  0.3629 
R Square  0.1317 
Adjusted R Square  0.1138 
Standard Error  0.2020 
Observations  199 
VIF  1.1517 
Regression Analysis  
Oil_vel5_x_AAPL_vel7 and all other X  
Regression Statistics  
Multiple R  0.5462 
R Square  0.2984 
Adjusted R Square  0.2839 
Standard Error  0.1985 
Observations  199 
VIF  1.4253 
Regression Analysis  
Gold_x_AAPL_acc1 and all other X  
Regression Statistics  
Multiple R  0.1711 
R Square  0.0293 
Adjusted R Square  0.0093 
Standard Error  0.1758 
Observations  199 
VIF  1.0301 
From the output table shows that all the VIF are less than 5.
Check residuals are normally distributed
The other way to check whether the inputs have collinearity, we will check whether the residuals are normally distributed. By using the excel function data analysis we can compute the following graphs.
From the last Graph, it is a straight line (or almost straight), the residuals are normally distributed, and all our hypothesis tests will be accurate.
Check which inputs are helping.
In this step, we are going to check for two important figures – adjust R square and Pvalue.
R square
In the regression model, R square is presenting the proportion of variation which clarifies by independent variables. As the result, R square could be a standard of the degree of accuracy on the prediction between X and Y. The ratio of the regression sum of squares (SSR) to the total sum of squares (SST) is resolved R square’s value. Furthermore, adjusted R square is the number which excludesor reduces impacts from other independent variables.
As can be seen from the regression analysis, the number of adjusted R square is 0.158405, meanwhile, it pointed out that there is a weak relationship between X and Y by 15% only. Therefore, even there may some other elements to effect on variable Y but the outcome would not be different due to the feeble relationship amid Y and Xs. However, the relationship is still statistically significant.
Regression Statistics  
Multiple R  0.42386 
R Square  0.179658 
Adjusted R Square  0.158405 
Standard Error  0.249028 
Observations  199 
Coefficients  Standard Error  t Stat  Pvalue  Lower 95%  Upper 95%  Lower 95.0%  Upper 95.0%  
Intercept  0.497323086  0.041719076  11.92076  6.581E25  0.415039232  0.579606941  0.415039232  0.579606941 
Gold_x_AAPL_acc1  0.18629918  0.101725034  1.8314  0.06858258  0.014336327  0.386934688  0.014336327  0.386934688 
Silver_vel10_x_Baltic_dry_acc2  0.377818335  0.088513082  4.268503  3.083E05  0.203241179  0.552395491  0.203241179  0.552395491 
Oil_vel5_x_AAPL_vel7  0.108244958  0.090080908  1.201641  0.23097488  0.069424471  0.285914387  0.069424471  0.285914387 
Baltic_dry_vel5_x_AAPL_vel11  0.256465655  0.100999  2.539289  0.01189634  0.455669182  0.05726213  0.455669182  0.057262129 
5year_vel4_x_SP500_vel12  0.361791706  0.087098442  4.153825  4.9072E05  0.533578722  0.19000469  0.533578722  0.19000469 
In the output sheet, the Adjusted R square is 0.158405. And we look further down where we have to check each Pvalue for each input. If input has a pvalue that is more than 0.05, then we should consider deleting that whole column of inputs from the original data. As the above table shows that there are two inputs data P value greater than 0.05, so that we should go back to original data to delete those two columns to have look how Adjust R square changed.
Date  Silver_vel10_x_Baltic_dry_acc2  Baltic_dry_vel5_x_AAPL_vel11  5year_vel4_x_SP500_vel12  Future_change 
12/11/2013  0.039063  0.103333  0.291806  0.2625 
11/11/2013  0.064445  0.253993  0.301476  0.316667 
8/11/2013  0.015625  0.234896  0.372431  0.395833 
7/11/2013  0.012708  0.360972  0.045833  0.658333 
6/11/2013  0.197777  0.475694  0.331459  0.320833 
5/11/2013  0.235989  0.375  0.335313  0.325 
4/11/2013  0.393021  0.155694  0.473611  0.254167 
1/11/2013  0.5875  0.055087  0.516076  0.541667 
31/10/2013  0.60783  0.040399  0.539688  0.554167 
30/10/2013  0.092083  0.014844  0.437708  0.445833 
29/10/2013  0.395416  0.022083  0.455381  0.745833 
…  …  …  …  … 
…  …  …  …  … 
11/02/2013  0.129167  0.455381  0.119401  0.408333 
8/02/2013  0.551597  0.367362  0.189844  0.6 
7/02/2013  0.477969  0.015625  0.053437  0.916667 
6/02/2013  0.364653  0.012413  0.13125  0.929167 
5/02/2013  0.322917  0.004375  0.305278  0.3 
4/02/2013  0.362465  0.005208  0.195972  0.641667 
1/02/2013  0.44625  0.006094  0.385486  0.475 
31/01/2013  0.30586  0.022656  0.407057  0.820833 
After delete those two columns, output tables will show as below:
Regression Statistics  
Multiple R  0.399072  
R Square  0.159258  
Adjusted R Square  0.146324  
Standard Error  0.250809  
Observations  199  
Coefficients  Standard Error  t Stat  Pvalue  Lower 95%  Upper 95%  Lower 95.0%  Upper 95.0%  
Intercept  0.53142673  0.038261011  13.8895108  6.05374E31  0.455968204  0.606885246  0.455968204  0.606885246  
Silver_vel10_x_Baltic_dry_acc2  0.42743504  0.085345172  5.008309548  1.22576E06  0.259116945  0.595753133  0.259116945  0.595753133  
Baltic_dry_vel5_x_AAPL_vel11  0.206687  0.09149364  2.2590309  0.024988049  0.38713109  0.02624283  0.38713109  0.02624283  
5year_vel4_x_SP500_vel12  0.3276067  0.085414441  3.83549547  0.000169117  0.49606141  0.159152  0.49606141  0.159152  
After delete two columns of data Adjusted R square becomes smaller from 0.158405131316566 to 0.146324.Because after delete 2 columns data, adjust R square become smaller. Hence we should undelete that last column we just deleted. Consider about the change of the Adjust R square, instead of delete two columns in the original data, we are going to delete the column with higher Pvalue. Table will show as below.
Date  Gold_x_AAPL_acc1  Silver_vel10_x_Baltic_dry_acc2  Baltic_dry_vel5_x_AAPL_vel11  5year_vel4_x_SP500_vel12  Future_change 
12/11/2013  0.021007  0.039063  0.103333  0.291806  0.2625 
11/11/2013  0.027378  0.064445  0.253993  0.301476  0.316667 
8/11/2013  0.08724  0.015625  0.234896  0.372431  0.395833 
7/11/2013  0.034236  0.012708  0.360972  0.045833  0.658333 
6/11/2013  0.076563  0.197777  0.475694  0.331459  0.320833 
5/11/2013  0.054861  0.235989  0.375  0.335313  0.325 
4/11/2013  0.125417  0.393021  0.155694  0.473611  0.254167 
1/11/2013  0.079132  0.5875  0.055087  0.516076  0.541667 
31/10/2013  0.076823  0.60783  0.040399  0.539688  0.554167 
30/10/2013  0.209792  0.092083  0.014844  0.437708  0.445833 
29/10/2013  0.068177  0.395416  0.022083  0.455381  0.745833 
…  …  …  …  …  … 
…  …  …  …  …  … 
11/02/2013  0.416666  0.129167  0.455381  0.119401  0.408333 
8/02/2013  0.628334  0.551597  0.367362  0.189844  0.6 
7/02/2013  0.847084  0.477969  0.015625  0.053437  0.916667 
6/02/2013  0.320833  0.364653  0.012413  0.13125  0.929167 
5/02/2013  0.824913  0.322917  0.004375  0.305278  0.3 
4/02/2013  0.075347  0.362465  0.005208  0.195972  0.641667 
1/02/2013  0.496875  0.44625  0.006094  0.385486  0.475 
31/01/2013  0.419966  0.30586  0.022656  0.407057  0.820833 
Regression Statistics  
Multiple R  0.416557415 
R Square  0.17352008 
Adjusted R Square  0.156479257 
Standard Error  0.249313166 
Observations  199 
Compare with these three Adjust R square, after deleting either two columns or one column, both of the adjust R square results are smaller than the original one. Thus, we have to make a decision that we should not take that action to delete those columns.
Make a prediction with a confidence interval
To predict the future change, we will use PHStat’s Confidence interval estimate & Prediction function with 95% confidence intervals. And we will use the original date show below to fill in the table to predict future changes.
Date  Gold_x_AAPL_acc1  Silver_vel10_x_Baltic_dry_acc2  Oil_vel5_x_AAPL_vel7  Baltic_dry_vel5_x_AAPL_vel11  5year_vel4_x_SP500_vel12  Future_change 
11/11/2013  0.027378  0.064445  0.235156  0.253993  0.301476  0.316667 
Data  
Confidence Level  95% 
1  
Gold_x_AAPL_acc1 given value  0.027378 
Silver_vel10_x_Baltic_dry_acc2 given value  0.064445 
Oil_vel5_x_AAPL_vel7 given value  0.235156 
Baltic_dry_vel5_x_AAPL_vel11 given value  0.253993 
5year_vel4_x_SP500_vel12 given value  0.301476 
For Average Predicted Y (YHat)  
Interval Half Width  0.05934 
Confidence Interval Lower Limit  0.318675 
Confidence Interval Upper Limit  0.437354 
For Individual Response Y  
Interval Half Width  0.494738 
Prediction Interval Lower Limit  0.11672 
Prediction Interval Upper Limit  0.872753 
As can be seen from the graph as above, the analysing presented that number of interval half width is standing on the 0.49, almost 0.5 for individual response Y. Hence, there is an optimistic sign for predictors and investors as a reference. However, there is not really encourage investors to doing investment due to the figure of prediction is not reached 1 yet. So, there is still have some risks to impact on the share price. Also, the prediction may not accurate enough because there is a lack of related samples to analysis. On the other hand, share market has got plenty of uncertainties, therefore, the report user can not just rely on data analysing, also still need to think twice after apply this report and before doing investment.
The DurbinWatson test
DurbinWatson Calculations  
Sum of Squared Difference of Residuals  24.72287316 
Sum of Squared Residuals  11.96892238 
DurbinWatson Statistic  2.065588896 
The DurbinWatson test is applied for examine the presence of serial correlation residual. The value of DurbinWatson statistic ranges from 0 to 4, and the acceptable range is from 1.50 to 2.50 (Evans, 2013). According to the graph, it shows our DurbinWatson statistic is 2.065588896. As the result, the outcome is acceptable.If the DurbinWatson statistic was not in the acceptable range, we would add a caution to the findings for a violation of regression assumptions.
Conclusion
To sum up, according to the regression model analysing, there is no so much influences on future change by 5 independent variables. Originally, we indicate that gold and silver are both important raw materials for products and also paly as a financial solver to helps company to balance revenue and make up a deficit from international exchange rates. However, those two materials are not having as high relevance with share price as expected. It might cause both raw materials are not key element of products and also Apple’s financial apartment would focus on other investments rather than gold and silver markets to reduce risks. On the other hand, oil and Baltic index are another point of view to forecasting Apple’s future change. Unfortunately, those two factors have no strong relationship with Apple’s share price. The reason to drives those factors have no such impacts on Apple’s share price might due to different shipping and investment strategy to avoid risks. What is more, we also brought SP500 to compare with Apple’s business performances and predicted future change of share price. However, the figure illustrated that Apple’s share price has no strong relevance with SP500 as well. As the result, Apple’s business performing and SP500 is unhooked. In other words, Apple’s business is operating more independently.
References
Evans, J. R. (2013). Statistics, Data Analysis and Decision Modeling (5^{th} ed.). Harlow, England: Pearson Education Limited
Yahoo Finance. (2014). Apple Profil. Retrived January 04, From http://finance.yahoo.com/q/pr?s=aapl
Order Now