Question

A cost estimator for a construction company has collected the data found in the source file Estimation.xlsx describing the total cost (Y) of 97 difference projects and the following 3 independent variables thought to exert relevant influence on the total cost: total units of work required (X1), contracted units of work per day (X2), and city/location of work (X3). The cost estimator would like to develop a regression model to predict the total cost of a project as a function of these 3 independent variables.

b. Suppose the estimator wants to use the total units of work required (X1), contracted units of work per day (X2), and city/location of work (X3) as the independent variables to predict total cost. What should be the regression function between Y and X1, X2, and X3? What is the adjusted R-squared value of this model? What conclusions can you make? (Note that X3 is a dummy variable. You should process it into different categories as I showed you in the class lecture. You should expand X3 into Location1, Location 2, …. Location 5 to differentiate the six locations.)

Answer 1

I have answered the question below

Please up vote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

To select the model that shows the highest adjusted, perform the following steps:

1. Select the entire data. Go to XL MinerPredictMultiple Linear Regression as shown below:

The following dialogue box will appear. The screenshot is shown below:

2. Update the dialogue box. Move the dependent variable(Y) to the output variable and move all the independent variables to the selected variable column to find the best fitted model as shown below:

3. Click on Next and the following dialog box appears:

4. Click on Variable Selection on the upper right side and the dialog box shown below appears:

Click on the finish and the final result is shown below:

From the above table, it is clear that the selected model would be the one with variables Units of work and City. It is because this model has the highest adjustedwhich is greater than the other two. The combination of (units of work) and (city) has.

6. To calculate the equation for this model, click on Choose Subset as shown below:

After the dialog box appears, click on Next and then Finish and the following results is shown below:

Therefore, the equation is.