How to Use Excel To Estimate Stock Prices Via Regression  Statistics Help
This first question has to do with the data previously collected on WalMart stocks.
1. Can you predict what the price of your company's stock will do from the DJIA?
Solution: We should perform a Regression Analysis. We have the following table with data:
WMART 
DJIA 
45.74 
11069.06 
46.1 
11115.32 
46.63 
11120.68 
46.89 
11058.97 
46.45 
11028.39 
45.51 
10892.32 
45.75 
10919.05 
45.7 
10883.35 
45.4 
10858.62 
44.74 
10749.76 
45.08 
10798.27 
45.49 
10793.62 
46.28 
10851.98 
46.14 
10953.95 
46.11 
10864.86 
46.41 
10899.92 
45.84 
10907.21 
46.32 
10809.47 
45.97 
10709.74 
45.72 
10712.22 
45.25 
10688.77 
45 
10667.39 
45.8 
10880.71 
45.18 
10854.86 
44.95 
10896.32 
45.4 
10959.87 
45.74 
10962.36 
46.57 
11043.44 
45.86 
11011.58 
45.71 
11011.9 
45.88 
10959.31 
45.69 
10882.15 
46.32 
10880.15 
46.23 
10847.41 
46.8 
10717.5 
47.48 
10784.82 
47.84 
10796.26 
47.73 
10777.77 
48.34 
10883.27 
48.6 
10889.44 
48.65 
10805.63 
48.6 
10805.55 
48.96 
10836.53 
49.27 
10875.59 
49.26 
10881.67 
49.51 
10883.51 
49.32 
10823.72 
48.53 
10767.77 
47.93 
10778.58 
47.56 
10755.12 
47.61 
10810.91 
47.48 
10856.86 
47 
10835.01 
47.82 
10877.51 
47.88 
10912.57 
48.41 
10805.87 
48.86 
10888.16 
49.85 
10890.72 
50.34 
10931.62 
50.42 
10916.09 
50.05 
10871.43 
49.47 
10820.28 
49.35 
10766.33 
49.09 
10720.22 
48.73 
10674.76 
48.63 
10686.44 
49.15 
10697.17 
48.85 
10686.04 
48.89 
10640.1 
48.05 
10546.21 
47.47 
10539.72 
47.9 
10586.23 
47.55 
10530.76 
47.31 
10522.59 
47.42 
10472.73 
46.85 
10406.77 
47.17 
10440.07 
45.36 
10402.77 
44.6 
10229.95 
45.44 
10344.98 
45.25 
10377.87 
46.07 
10385 
45.58 
10215.22 
45.46 
10281.1 
45.85 
10414.13 
44.99 
10285.26 
45.1 
10348.1 
44.9 
10287.34 
44.62 
10216.59 
44.8 
10216.9 
44.88 
10253.17 
44.4 
10238.76 
43.9 
10292.31 
43.8 
10287.1 
43.37 
10317.36 
43.72 
10441.11 
43.63 
10535.48 
43.69 
10568.7 
43.41 
10552.78 
43 
10473.08 

We use Excel’s regression tool to get:
SUMMARY OUTPUT 

Regression Statistics 

Multiple R 
0.426505 

R Square 
0.181906 

Adjusted R Square 
0.173559 

Standard Error 
1.629229 

Observations 
100 

ANOVA 

df 
SS 
MS 
F 
Significance F 

Regression 
1 
57.84099 
57.84099 
21.7907 
9.66E06 

Residual 
98 
260.1301 
2.654389 

Total 
99 
317.9711 
Coefficients 
Standard Error 
t Stat 
Pvalue 
Lower 95% 
Upper 95%  
Intercept 
13.09174 
7.19216 
1.820279 
0.071769 
1.18087 
27.36435 
DJIA 
0.003134 
0.000671 
4.668051 
9.66E06 
0.001802 
0.004467 
The regression equation is:
\[y=13.09174+0.003134x\]where \(y\) represents the predicted WalMart stock value and \(x\) is the DJIA index. We should interpret this equation with caution because the correlation coefficient is 0.426565, which indicates only a moderate degree of linear association (even though is significantly different from zero).
2. For this case assignment we are going to look at housing starts again but this time we are going to add another variable to the equation. Read the short article below concerning lumber prices. Then I would like you to compute the multiple regression formula using the interest rates, lumber prices, and number of housing starts provided below. Once you have computed your multiple regression formula answer the following questions.
What is the regression formula that you computed?
Solution: We get the following output
Therefore, our regression equation is:
\[\hat{y}=155138.149531203318.3801{{x}_{1}}17836.760125{{x}_{2}}\]whererepresents the Housing start,represents the Interest Rate, andrepresents the Price per board foot.
What would the approximate number of housing starts be at the following interest rates and lumber prices:
9.0% at $1.00 per board foot
8.5% at $1.50 per board foot
5.5% at $1.25 per board foot
4.5% at $0.90 per board foot
3.7% at $1.00 per board foot
2.3% at $0.75 per board foot.
NOTE: Use Excel to assist you with the calculations. Input the above data in an Excel spreadsheet and input your multiple regression formula using the data cells as the variables. Excel can then automatically compute the results.
Solution: We have the following table made with Excel and using the regression equation found in the previous part:
Interest Rate 
Price per board foot 
Estimated Housing Start 
0.09 
1 
$29,002.7352 
0.085 
1.5 
$26,100.94703 
0.055 
1.25 
$66,659.68847 
0.045 
0.9 
$84,935.73831 
0.037 
1 
$92,778.60934 
0.023 
0.75 
$114,084.2567 
If you were the owner of a business in the housing construction sector how would this information affect your decisions?
Solution: The way we interpret the regression line is that for each %1 increase in the interest rate, the starting price of housing for that interest rate will decrease $12033.18 in average, and if the Price per Board foot increases in $0.01, the starting price of housing will decrease $178.37 in average. That information could be very valuable to make decisions based on the estimated future starting price of housing.
Lumber Prices Continue To Tumble
ENR. New York: Jun 16, 2003. Vol. 250, Iss. 23; pg. 28
There appears to be no bottom in sight for falling lumber prices. In June, prices for the most commonly used species of 2 X 4s fell 0.7%, following May's 1.9% decline. With the exception of a brief rally during the first quarter, prices have been in a free fall for almost a year (see chart below). Prices for dimensional softwood lumber are now down between 7 to 12% from last year's level. Plywood prices have climbed 1.9% during the last two months and are now within 1% of June 2002's price level.
ENR. New York: Jun 16, 2003. Vol. 250, Iss. 23; pg. 28
Housing Starts in Relation to Interest Rates and Lumber Prices
Interest Rate Price Per Board Foot Housing Starts
11% $1.25 8,500
11% $1.00 9,000
11% $0.90 9,200
11% $0.75 9,500
10% $1.25 9,700
10% $1.00 10,000
10% $0.90 10,300
9% $1.25 22,000
9% $1.00 24,000
8% $1.25 39,000
8% $0.90 45,000
8% $0.75 52,000
Click HERE to compute your multiple regression formula. (Use the multiple regression equation to compute your formula. NOTE: Enter percentages as .08, .09, .10, etc.)
You can send you Excel Stats homework problems for a Free Quote. We will be back shortly (sometimes within minutes) with our very competitive quote. So, it costs you NOTHING to find out how much would it be to get stepbystep solutions to your Excel homework problems.
Our experts can help YOU with your Statistics Assignments. Get your FREE Quote.