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:

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.66E-06
Residual	98	260.1301	2.654389
Total	99	317.9711

The regression equation is:

\[y=13.09174+0.003134x\]

where \(y\) represents the predicted Wal-Mart 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.14953-1203318.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:

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 step-by-step solutions to your Excel homework problems.

Our experts can help YOU with your Statistics Assignments. Get your FREE Quote.

Contact Us!