Question

Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict the DJIA by the Nasdaq, the S&P 500, the Nikkei, the Hang Seng, the FTSE 100, and the IPC. Performing a stepwise regression analysis at a 5% level of significance, add the independent variable from Step 2 and continue to perform the stepwise regression analysis until you have reached the best linear model. Which independent variables are in the best linear model? Check all that apply.

Excel Data Here

https://drive.google.com/file/d/1TQG5r2wzLGk--75whZXyb0SDTHZTWS0S/view?usp=sharing

A: Nasdaq

B: S&P 500

C: Nikkei

D: Hang Seng

E: FTSE 100

F: IPC

Choose All Answers That Apply

Answer 1

1. In Excel go to Data Analysis under data tab and click Regression Analysis.
2. Then select Y variable range and X variable range.
3. Click on label and select output range.
4. Click OK.
5. You will get an output as below.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.989
R Square	0.978
Adjusted R Square	0.976
Standard Error	218.055
Observations	63
ANOVA
	df	SS	MS	F	Significance F
Regression	6	119,575,403.52	19,929,233.92	419.14	0.000
Residual	56	2,662,682.29	47,547.90
Total	62	122,238,085.81
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1,412.330	381.634	3.701	0.000	647.824	2,176.835
NASDAQ	(1.259)	0.633	(1.987)	0.052	(2.528)	0.010
S&P 500	10.799	1.633	6.611	0.000	7.527	14.071
NIKKEI 225	(8.838)	4.189	(2.110)	0.039	(17.229)	(0.446)
HANG SENG	0.489	0.166	2.944	0.005	0.156	0.822
FTSE 100	(0.128)	0.107	(1.195)	0.237	(0.343)	0.087
IPC	0.171	0.110	1.550	0.127	(0.050)	0.392

• Here the model is significant as the P value is 0.00 which is less than 0.05 at 5% significant level.
• When you refer the Coefficients Table, P value of  NASDAQ,FTSE 100 and IPC are greater than 0.05. Hence these three independent variables are not significant at 5% level.
• So the Intercept,S&P 500,NIKKEI 225,HANG SENG independent variables are in the best linear model as they show significant at 5% significant level.