Regression Analysis using Excel

The assignment content discusses using Excel's data analysis add-in to run regression programs, particularly in solving a problem involving selling price as the dependent variable and age or square footage or bedrooms as the independent variables. Additionally, it explains how to calculate simple moving averages, weighted moving averages, and exponential moving averages, and compares their effectiveness. The content also highlights the importance of checking p-values to determine significance and using labels for input ranges.

4.22
As has been mentioned in the document to use excel to run the programs, we can use the
data analysis add-in to run the same. Because this is regression, we need to use the
regression option in data analysis tool pak. In excel, go to data, click on data analysis, click
on regression, input y range (this would be dependent variable) which is selling price in this
case, input x range (this would be independent variable) which can be SQ Footage,
Bedrooms or Age, keep Labels option checked if you have labels in the table, and give
output range or have the output in new worksheet.
The model with age being the independent variable out of the three individual models is the
best because the p-value for both intercept and age is below 0.05. On top of that, the r^2 is
0.70 which is the highest amongst the three individual models. This explains that more
percentage of variation in the dependent variable is accounted for by the independent
variable
In this case, the requirement is to solve the problem via excel. However, if it needs to be
done manually, we can do it in the following way (example for age being the independent
variable): -
S.P (y) Age (x) xy x^2 y^2
84000 30 2520000 900 7056000000
79000 25 1975000 625 6241000000
91500 30 2745000 900 8372250000
120000 40 4800000 1600 14400000000
127500 18 2295000 324 16256250000
132500 30 3975000 900 17556250000
145000 19 2755000 361 21025000000
164000 7 1148000 49 26896000000
155000 10 1550000 100 24025000000
168000 1 168000 1 28224000000
172500 3 517500 9 29756250000
174000 3 522000 9 30276000000
175000 1 175000 1 30625000000
177500 0 0 0 31506250000
184000 2 368000 4 33856000000
195500 10 1955000 100 38220250000
195000 3 585000 9 38025000000
2540000 232 28053500 5892 402316500000 Total
b1= nE(xy)-ExEy/nE(x^2)-(Ex^2)
=17*28053500-(232*2540000)/17*5892-(232*232)
=476909500- 589280000/100164-53824
= -112370500/ 46340

=-2424.9137
b0=Ey-b1Ex/n
=2540000-(-2424.9137*232)/17
= 3102579.97/17
= 182504.704
The equation, y=b0+b1x, y=182504.704-2424.9137x
4.23
In this case, we would have selling price as the input to input y range and SQ Footage and
Bedrooms as the input to input x range. From that, we get the equation as
SP=24716.0531+48.6440854*SQ FT+2515.09316*Bedrooms. As has been asked for to check
the price based on the sq ft and number of bedrooms, we can input the values 2000 for
square footage and 3 for number of bedrooms, we get the value 129549.5034 for selling
price.
4.31
As has been mentioned in the document to use excel to run the programs, we can use the
data analysis add-in to run the same. Because this is regression, we need to use the
regression option in data analysis tool pak. In excel, go to data, click on data analysis, click
on regression, input y range (this would be dependent variable) which is W in this case,
input x range (this would be independent variable) which can be ERA, R, Avg or OBP, keep
Labels option checked if you have labels in the table, and give output range or have the
output in new worksheet.
The best fit variables for the model would be those who have a p-value of less than 0.05
which in this case are ERA and R. The best model amongst the individual models is where
the independent variable is ERA because we can see that the p-value is lower than 0.05
which means that the variable is significant. Also, we can see that r^2 amongst all the
models is highest when the independent variable is ERA.
5.17 & 5.18
Simple average is calculated by taking the sum of three values and dividing it by 3 and so on
till the end. Through this, we get the forecast for 12th period as 13.67. MAD is the mean
average deviation. It is the average of all the values which are the difference of actual-
forecast (3-yr MA).
Weighted moving average is calculated by assigning weights to the actual values, adding up
those weighted values and dividing the sum by the weights. Through this, we get the
forecast for 12th period as 14. MAD is the mean average deviation. It is the average of all
the values which are the difference of actual-forecast (WMA).

Exponential moving average is calculated by multiplying the smoothing constant, in this case
0.3. Through this, we get the forecast for 12th period as 14.42. MAD is the mean average
deviation. It is the average of all the values which are the difference of actual-forecast (3-yr
MA).
Amongst simple moving average and weighted moving average, weighted moving average is
better because it has lower mean average deviation. Amongst exponential moving average
and weighted moving average, exponential moving average is better because it has lower
mean average deviation.
Through excel, we can use the option Moving Average in data analysis tool-pak to calculate
simple moving average. Give Input range, click on labels in first row (only if you have labels
in the data-table), give interval (3 in this case). For exponential smoothing, use the option
Exponential Smoothing, give input range and damping factor as 0.3 and check Labels option
if you have labels in the first row.
5.21
Simple average is calculated by taking the sum of three values and dividing it by 3 and so on
till the end. Through this, we get the forecast for Fall for Senior Year as 3.2. MAD is the
mean average deviation. It is the average of all the values which are the difference of
actual-forecast (3-yr MA).
Exponential moving average is calculated by multiplying the smoothing constant, in this case
0.2. Through this, we get the forecast for 12th period as 3.249. MAD is the mean average
deviation. It is the average of all the values which are the difference of actual-forecast (3-yr
MA).
EMA is a better method because the MAD is coming to 0.0535 which is lower than 0.2833
for simple moving average