Question

Day	Price	# of Shares (hundreds)	Volume of Exchange (millions)
1	87.5	950	11
2	86	945	11.25
3	84	940	11.75
4	83	930	11.75
5	84.5	935	12
6	84	935	13
7	82	932	13.25
8	80	938	14.5
9	78.5	925	15
10	79	900	16.5
11	77	875	17
12	77.5	870	17.5

a. use excel or XLSTAT to determine an equation that can be used to predict price

b. at 95% confidence, which variables are significant and which arent

c. one day, the number sold is: 94,500 and the volume of exchange is 16 million, what would the price be? Use XLSTAT and construct a prediction interval

Answer 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.949172
R Square	0.900928
Adjusted R Square	0.878912
Standard Error	1.205046
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	2	118.8474	59.42372	40.92158	3.03E-05
Residual	9	13.06923	1.452137
Total	11	131.9167
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	118.5059	33.57526	3.52956	0.006418	42.55336	194.4584
Shares	-0.01629	0.031499	-0.51705	0.617584	-0.08754	0.054969
Volume	-1.57263	0.358994	-4.38065	0.001769	-2.38473	-0.76053

a) The equation that is used to predict the price is

Price = 118.5059 - 0.01629*Shares - 1.57263*Volume

b) At 95% confidence,

P value of # of Shares = 0.617584 > 0.05

So, # of Share is insignificant.

P value of Volume = 0.001769 < 0.05

So, Volume of Exchange is significant.

c) If # of shares sold = 94500

Volume of exchange = 16 million

then price = 118.5059 - 0.01629*945 - 1.57263*16= 77.95