Excel Homework Help

From the given data set, we have developed a predictive model to estimate the prices of the apartments. For this purpose different linear regression models are considered which are estimated using the set of observations and these models are further examined against the holdout data set in excel assignment help. This report includes the analysis, important data set, tables and results.  We have also derived the regression results to put it in the excel format along with all the data which can be further used for writing a report in excel homework help. The statistical parameters like p value and alpha were objectively interpreted and suitable conclusions were drawn accordingly.

Introduction

The aim of the paper is to develop a predictive model for apartment prices in the New York City market. In particular, the aim is to predict the Excel homework help fair market selling price for the apartment unit O, floor 6, building 270, Grand Central Parkway, New York, an apartment that has 1 bedroom, 1,5 bathrooms, a corner location, a claimed area of 1,268 square feet and a calculated area of 1,000 square feet.

To predict the given apartment’s price, four different linear regression models are considered below for the Excel project help apartments in the three buildings at 269, 270 and 271 Grand Central Parkway. The models were estimated on a training set of 880 observations with each model’s validity assessed against a “holdout” data set of 155 observations. Armed with estimated regression coefficients for each of the four models, the holdout data was used to generate predicted prices, and Excel assignment helpthose predicted prices were then used to develop the following metrics for model comparison: average prediction error, root mean square error, correlation between predicted price and actual price and from the correlation between predicted and actual prices, an R2, the simple square of the correlation coefficient.

The paper is organized as follows: The next section describes the variables used and provides descriptive statistics. The following section provides model specification and estimates for the models which are then assessed against the help with Excel assignment holdout sample along with the predicted price given by each model. Finally, the last section uses the preferred model to generate a prediction.

Data

The data consist of 1,185 observations on the variables described in Table 1 below.[1] Prior to estimating a statistics using Excel homework help regression model, the data was split into a training set of 880 observations and a holdout data set of 155 observations that was used to evaluate the Excel homework help regression models. To create the two data sets, a set of 1,185 pseudo-random draws from the uniform probability distribution were taken, and do my Excel homework the observations with the smallest 155 values of the uniform random variable were allocated to the holdout set.

Table 1Available variables and their level of measure

[1] An additional 163 observations had either a missing price (1) or a price of 0 because they changed owners through inheritance rather than sale. These were omitted from the regressions described below.

training set of 880 observations and a holdout data set of 155 observations that was used to evaluate the Excel homework help regression models. To create the two data sets, a set of 1,185 pseudo-random draws from the uniform probability distribution were taken, and do my Excel homework the observations with the smallest 155 values of the uniform random variable were allocated to the holdout set.

Table 1Available variables and their level of measure

Variable Description Level of Measure
Price Selling price in dollars Ratio
Building Address, one of 269,270 or 271 Grand

Central Parkway

Categorical
Unit One of A-Z, description of apartment details Categorical
Configuration Number of bedrooms and bathrooms Categorical
Floor Floor of building, from 1-33 Ratio
Corner Indicator equal to 1 if apartment is in the corner of make my Excel homework  the building Categorical
Square feet, claimed The square feet listed by the seller Ratio
Square Feet, Calculated The square feet calculated by room size Ratio
Bedrooms Number of bedrooms Ratio
Bathrooms Number of bathrooms Ratio
Price index Index of housing prices in New York State from the St. Louis Federal Reserve Ratio
Price index, squared Square of the price index Ratio

The random allocation of the data to training data set andExcel assignment for money holdout data set should ensure similar characteristics of the two data sets. Descriptive statistics for the variables measured at the ratio level are provided in Table 2 for the two data sets are presented in Table 2.

Table 2Descriptive Statistics

  1. Training data, N=880
Mean Std. Dev. Median Min Max Range
Price 465,609.73 219,434.39 430,000 50,000 1,300,000 1,250,000
Floor 16.51 9.83 16 1 33 32
Bathrooms 1.83 0.75 2 1 4 3
Bedrooms 1.37 0.74 1 0 3 3
Square Feet, claimed 1,236.74 403.01 1,115 655 2,000 1,345
Square Feet, calculated 967.65 321.49 967 508 1,600 1,092
Price Index 589.68 27.92 583 555 643 87
Price Index squared 348,500.85 33,340.19 340,297 308,525 413,140 104,616

(Continued)
B. Holdout data, N=155

Mean Std. Dev. Median Min Max Range
Price 469,163.14 222,192.12 435,000 147,500 1,200,000 1,052,500
Floor 15.52 9.64 14 1 33 32
Bathrooms 1.85 0.79 2 1 4 3
Bedrooms 1.42 0.75 1 0 3 3
Square Feet, claimed 1,254.94 410.69 1,115 655 2,000 1,345
Square Feet, calculated 984.99 325.94 984 508 1,600 1,092
Price Index 585.22 26.13 575 555 643 87
Price Index squared 343,163.47 31,095.00 330,476 308,525 413,140 104,616

Among the means in Table 2, the largest relative differences are for the statistics using Excel assignment help floor the apartment is on, with the Training set about 6% higher (16.51 versus 15.52), and number of bedrooms, with the holdout set about 3.5% larger (1.42 versus 1.37). All of the Excel homework help remaining relative differences in the means are less than 3%.Note that we should not be surprised that the minimums, maximums and ranges for the two square feet measures, bedrooms, bathrooms, the Excel homework for money Price index and the Price index squared are identical, as the housing characteristics are identical for any two apartments with the same value for the categorical variable Unit given in Table 1 and of course the price indices are the same by date for each data set.

The distribution of apartment prices in the two data sets displayed in two histograms in Figure 1 below. The relative heights of the bars indicate two fairly well matched samples, with theExcel problems with answers major difference being slightly heavier tail above $1 million for the holdout set, although from Table 2, the training set has the larger maximum.

To conserve space in Excel assignment helpthe body of the text, frequency counts and percentages for the categorical variables in Table 1 are presented as Table A.1 in the Appendix. For Building, Configuration, and Corner, percentages are quite similar in the two data sets. There are some differences in Unit, which is to be pay for Excel assignment expected when 26 different levels of the variable are distributed over 880 observations in one data set and 155 observations in the other data set.

Models and Estimates

Four different specifications were estimated as linear regression models. The estimated specifications were influenced by two types of Excel homework help considerations. First, there are several perfect multicollinearities among the full set of variables in Table 1. For example, number of bedrooms and bathrooms defines Configuration, so these three variables cannot be entered together. Unit and Corner are

Figure 1 Frequency Histogram of Apartment Prices

  1. Training set

Excel homework help

B. Holdout set

Excel assignment help

Also perfectly multicollinear, as are square feet claimed or calculated and Configuration. Further, the correlation between number of Excel project help bedrooms and number of bathrooms is 0.94, the correlation between number of bedrooms and either of the square feet variables is above 0.92, and the correlation between number of pay for Excel homework bathrooms and square feet is above 0.93 for both measures of square feet. Generally, when correlations between variables are above 0.9, we should expect multicollinearity problem if both variables are used as independent variables.

A second problem is that Excel’s Data Analysis Tool Pack is limited to at most 16 independent variables. Although the most detailed description of an apartment is given by Unit, by itself Unit exceeds the constraint on the permissible number of independent variables.

In view of these considerations, the four specifications in Table 3 were chosen for estimation. All of the models contain indicators for building 270 and 271 (with building 269 omitted to avoid perfect multicollinearity with the intercept) and all models contain the floor of the apartment, the price index, and the square of the price index, which was found to be significant in Excel homework help all regressions. For the additional variables in the models, Models 1 and 2 trade a few Units that have mean prices significantly different than the help with Excel assignment grand mean against a few additional configurations that have mean prices significantly different than Alcove Configuration.

Table 3 Regression specifications

Model 1 Model 2 Model 3 Model 4
Building 270 Building 270 Building 270 Building 270
Building 271 Building 271 Building 271 Building 271
Unit G Unit D Corner
Unit H Unit G Square feet, claimed
Unit K Unit H Square feet, calculated Square feet, calculated
Unit N Unit K
Unit O Unit L
Unit U Unit N 1 BR, 2 BA
Unit V Unit O 1 BR, 1BA
1 BR, 1,5BA Unit V 1 BR, 1.5BA, C
1 BR, 1.5BA, C Unit W 2 BR, 2.5BA
2 BR, 2.5BA 1 BR, 1.5BA, C 2BR, 2.5BA, C
2BR, 2.5BA, C 2 BR, 2.5BA, C 3 BR, 3.5BA, C
Price Index Price Index Price Index Price Index
Price index, squared Price index, squared Price index, squared Price index, squared
Floor Floor Floor Floor

Estimates for the models are presented along with standard errors for the coefficients and goodness of fit statistics in Table 4. All of the models help with Excel homework have highly statistically significant F statistics, indicating that we can reject the null hypothesis that the model predicts no better than the sample mean of apartment price. Note that in each model, the contents ofExcel homework help the intercept include the values of categorical variables in the specification that were not explicitly included inExcel questions with answers the regression and these vary considerable across the different models. For example, in the first two models, there 26 Units labeled from A to Z, and any Unit that does not have a listed coefficient is part of the intercept. The values ofdata analysis using Excel homework help Configuration that are included in the intercept also vary across the models. For each of these categorical variables, the estimated coefficients on the included values are estimates of the difference between the average apartment prices for
Table 4 Regression estimates, standard errors and goodness of fit statistics

[1]The full set of configurations includes Studio Alcove, 1 bedroom – 2 bathrooms (i.e., 1 BR, 2 BA), 1 bedroom – 1 bathroom, 1 bedroom – 1.5 bathrooms, 1 bedroom – 1.5 bathrooms – corner, 2 bedrooms – 2.5 bathrooms, 2 bedrooms -2 bathrooms – corner and 3 bedrooms – 3.5 bathrooms – corner.

Model 1 Model 2 Model 3 Model 4
Intercept -7,099,415.07* -7,490,364.87* -7,445,960.63** -7,421,862.49
2,972,720.00 3,295,830.09 2,082,195.53 2,080,201.19
Building270 18,188.80 23,122.07 20,877.29** 19,967.03
12,151.48 13,457.16 8,498.02 8,487.23
Building271 25,373.38* 41,813.74** 31,441.97** 30,796.27
12,513.14 13,771.05 8,728.01 8,705.52
Price Index 22,144.71* 26,226.81* 22,172.72** 22,244.21**
9,977.14 11,062.23 6,985.77 6,985.45
Price Index squared -16.37* -22.01* -16.39** -16.45
8.36 9.26 5.85 5.85
Floor 4,643.29** 298.75 1,555.09** 1,507.48
607.37 623.68 446.65 480.45
Unit D 265,159.93**
26,872.90
Unit G 78,181.44** 235,835.78**
26,292.28 24,790.50
Unit H 76,118.66** 216,073.51**
26,175.21 25,238.05
Unit K 78,549.95** 229,754.93**
27,545.70 25,701.00
Unit L 220,036.16**
26,195.80
Unit N 38,679.89 270,978.15**
26,378.45 25,630.19
Unit O 53,832.65* 305,692.97**
26,054.39 25,207.14
Unit U -82,958.13*
37,475.49
Unit V 319,563.33** 378,559.41**
22,417.11 24,113.25
Unit W 373,950.31**
24,512.04
1BR, 1BA 26,346.25
17,799.28
1BR, 1.5BA 39,082.38** 53,488.92
14,107.71 32,871.90
1BR, 1.5BA, Corner 114,055.37** 10,711.38 88,380.98
20,199.12 19,826.53 43,461.91

(Continued)

Model 1 Model 2 Model 3 Model 4
1BR, 2BA -259.86
25,561.59
2BR, 2.5BA 170,554.11** -8,516.91 87,704.75
23,115.96 16,254.78 65,704.44
2BR, 2.5BA, Corner 281,739.76** 157,618.30
19,925.73 67,236.02
3BR, 3.5BA, Corner 136,875.51
94,811.44
Square feet, claimed 152.42*
65.23
Square feet, calculated 317.49** 401.02
76.18 84.19
Corner 34,667.17**
10,869.89
Prob > F <0.0001 <0.0001 <0.0001 <0.0001
Standard Err. Est. 147,995.28 163,854.71 103,856.86 103,428.65
R2 0.55 0.45 0.78 0.78

* Significant at 0.05 level of significance **Significant at 0.01 level of significance

The included value and the average apartment price for the values of the categorical variable that is included in the intercept. Broadly speaking, Model 1 and Model 2 find statistically significant coefficients on the dummy variables for Units, but the Excel assignment helpExcel-imposed constraint omits large numbers of units that are included in the estimate intercept, which holds the R2for these models to 0.55 and 0.45, respectively. In contrast, Model 3, which includes both square feet measures and Corner, the dummy variable indicating a corner apartment, and Model 4, which includes complete specification of Configuration, both have R2 equal to 0.78. Over all of the Excel assignment helpmodels the price index is statistically significant with the positive sign that we would anticipate, and in three of the four models, the square of the price index is also statistically significant.

Finally, Table 5 below metrics for the fit of the four models of Table 4 to the holdout data set, where the metrics include the average prediction error, the root mean square error, the correlation between the predicted and actual price inExcel homework solution the holdout data set, and the R2, where R2 is simply the square of the correlation. Each of the four models provides biased predictions of price for the holdout data, as none of the average prediction errors are zero. A plausible explanation of this is that the independent variable that provides the most detailed description of apartments, Unit, has 26 values, and it is difficult to data analysis using Excel assignment help achieve a perfectly balanced training and holdout data sets given that the holdout data set has only 155 observations. Root mean squared errors of for the holdout and training data sets are very close. Correlations of the actual price in Excel homework help the holdout data set and the price predicted by each regression specification range from a low of 0.71 for Model 2 to a high of 0.89 for Model 4, and the values of R2 for the holdout data set are also quite similar to the corresponding values help with Excel homework for the training data set. Overall, the results of Table 5 clearly favor Models 3 and 4 over Models 1 and 2. I would choose Model 4 as its lower bias more than offsets its slightly higher variance.

Table 5 Fit of models to holdout data

Model 1 Model 2 Model 3 Model 4
Average prediction error 23,564.01 -6,371.45 7,247.87 6,744.66
Root mean squared error 143,244.39 166,118.22 105,065.25 105,424.08
Correlation, predicted and actual price 0.80 0.71 0.89 0.89
R2 (square of correlation) 0.64 0.50 0.79 0.80

Conclusion and Answer to Initial Problem

Of the four models considered above, the model that fits the holdout data with the least bias is Model 4, which includes a complete specification of Excel assignment helpConfiguration. To predict the fair market selling price for the apartment unit O, floor 6, building 270, Grand Central Parkway, New York, an apartment that has 1 bedroom, 1.5 bathrooms, a corner location, a claimed area of 1,268 square feet and a calculated area of 1,000 square feet, we simply substitute the relevant valuesinto the Excel assignment solutionregression function for Model 4, using the third quarter 2015 value for the price index, obtaining a predicted price of $532,853.

Prediction

Coefficients Values for apartment Product of Col H* Col I
Intercept -7,421,862.49 1 -7,421,862.49
Building 270 19,967.03 1 19,967.03
1BR, 1.5BA, Corner 88,380.98 1 88,380.98
Price Index 22,244.21 604.25 13,441,062.80
Price Index squared -16.45 365,118.06 -6,004,758.37
Square Feet, calculated 401.02 1,000.00 401,018.22
Floor 1,507.48 6 9,044.89
Predicted price 532,853.05

Appendix

Table A.1 Frequency counts and percentages for categorical variables

Training Data Holdout data
Frequency Percentage Frequency Percentage
Building 269 295 33.5 269 48 31.0
270 305 34.6 270 48 31.0
271 280 31.8 271 59 38.1
Unit A 54 6.1 A 9 5.8
B 51 5.8 B 3 1.9
C 16 1.8 C 5 3.2
D 41 4.7 D 7 4.5
E 45 5.1 E 4 2.6
F 17 1.9 F 2 1.3
G 49 5.6 G 7 4.5
H 47 5.3 H 12 7.7
I 0 0.0 I 0 0.0
J 11 1.2 J 5 3.2
K 45 5.1 K 8 5.2
L 43 4.9 L 8 5.2
M 17 1.9 M 5 3.2
N 45 5.1 N 12 7.7
O 47 5.3 O 6 3.9
P 13 1.5 P 2 1.3
Q 8 0.9 Q 0 0.0
R 52 5.9 R 12 7.7
S 43 4.9 S 11 7.1
T 41 4.7 T 9 5.8
U 17 1.9 U 2 1.3
V 52 5.9 V 8 5.2
W 50 5.7 W 11 7.1
X 13 1.5 X 2 1.3
Y 53 6.0 Y 4 2.6
Z 11 1.2 Z 1 0.6

Continued

Training

data

Holdout

data

Frequency Percentage Frequency Percentage
Configuration Alcove 67 7.6 Alcove 9 5.8
1BR,

2BA

28 3.2 1BR,

2BA

2 1.3
1BR,

1BA

159 18.0 1BR,

1BA

31 20.0
1BR,

1.5BA

218 24.7 1BR,

1.5BA

43 27.7
1BR,

1.5BA, Corner

89 10.1 1BR,

1.5BA, Corner

12 7.7
2BR,

2.5BA

129 14.6 2BR,

2.5BA

22 14.2
2BR,

2.5BA, Corner

120 13.6 2BR,

2.5BA,

Corner

20 12.9
3BR,

3.5BA, Corner

71 8.1 3BR,

3.5BA, Corner

16 10.3
Corner 280 31.8 Corner 48 31.0
Not corner 601 68.2 Not corner 107 69.0