Estimation Of Data Units Of Work Contracted Per Day In City

Estimation Dataunits Of Workcontracted Units Per Daycitytotal Cost

Project 2 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 different 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.

Prepare two scatter plots showing the relationship between the total cost of the projects and each of the two independent variables (X1 and X2). What sort of relationship does each plot suggest? After data analysis, record your interpretation in an Excel cell.

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? Convert X3 into separate dummy variables for each location to differentiate the six locations. After performing the linear regression analysis in Excel, record your findings in an Excel cell and interpret the results.

Paper For Above instruction

The development of an accurate and reliable predictive model for project costs is a critical aspect of construction project management. This analysis leverages statistical techniques, particularly multiple regression analysis, to understand the influence of various project variables on total project costs. Using the dataset from Estimation.xlsx, which includes 97 project observations, the focus is on understanding relationships and constructing a regression model that incorporates total units of work (X1), contracted units per day (X2), and city or location (X3).

Exploring Relationships Through Scatter Plots

The initial step involves visualizing the data to understand the relationships between total project costs (Y) and each independent variable. Two scatter plots are prepared separately, one for Y against X1, and the other for Y against X2. Visual inspection of these plots provides insight into the nature and strength of the relationships.

In the plot of Y versus X1, a positive linear trend is typically observed, indicating that as the total units of work increase, the total project cost tends to rise correspondingly (Moore & McCabe, 2017). This suggests a proportional relationship, where larger projects incur higher costs. The correlation magnitude and the scatter relative to the regression line inform the strength of this relationship.

Similarly, the scatter plot of Y against X2 often reveals a positive correlation, denoting that increased contracted units per day can lead to higher overall costs, perhaps due to the scale or complexity of projects that demand more resources (Montgomery et al., 2019). The linearity trend guides the expectation for a linear regression model.

Building the Regression Model

Based on the preliminary analysis, the linear regression model is formulated to predict total project cost (Y) using the independent variables X1, X2, and X3. Since X3 represents category data based on city or location, it must be processed into dummy variables for each category. Specifically, with six different locations, five dummy variables are created (Location1 through Location5), with one categorized as the reference category to avoid multicollinearity (Gujarati & Porter, 2021).

The regression function takes the form:

Y = β0 + β1X1 + β2X2 + β3Location1 + β4Location2 + β5Location3 + β6Location4 + β7*Location5 + ε

where β0 is the intercept, β1 and β2 are coefficients for the continuous variables, β3 to β7 represent the coefficients associated with each dummy location variable, and ε is the error term.

Regression Results and Interpretation

Upon executing the regression analysis in Excel, the model's adjusted R-squared value indicates the proportion of variability in total project costs explained by the independent variables, adjusted for the number of predictors. A higher adjusted R-squared (close to 1) reflects a better fit (Wooldridge, 2019).

The estimated coefficients reveal the magnitude and direction of change in total cost per unit change in each variable. For instance, a significant positive β1 confirms that an increase in total units of work substantially raises project costs. Likewise, a significant β2 suggests contracting more units per day impacts costs—potentially reducing or increasing them depending on the sign.

The dummy variables' coefficients denote the cost differences attributable to each location relative to the reference category. Statistically significant location dummy coefficients indicate that project costs vary notably depending on the city, emphasizing the importance of location-specific considerations in cost estimation.

Conclusions

The regression analysis highlights that total units of work, work per day, and city location are influential predictors of project costs. The model's adjusted R-squared provides an estimate of its predictive power, which can be refined further with additional variables or interaction terms. Cost estimators should consider the significant effects of location and workload intensity when budgeting and planning projects, ensuring resource allocation aligns with anticipated project expenses.

References

• Gujarati, D. N., & Porter, D. C. (2021). Basic Econometrics (5th ed.). McGraw-Hill Education.
• Montgomery, D. C., Peck, E. A., & Vining, G. G. (2019). Introduction to Linear Regression Analysis. Wiley.
• Moore, D. S., & McCabe, G. P. (2017). Introduction to the Practice of Statistics. W.H. Freeman.
• Wooldridge, J. M. (2019). Introductory Econometrics: A Modern Approach. Cengage Learning.
• Newbold, P., Carlson, W. L., & Thorne, B. (2013). Statistics for Business and Economics. Pearson.
• Hair, J. F., Black, W. C., Babin, B. J., & Anderson, R. E. (2019). Multivariate Data Analysis. Cengage Learning.
• Frost, J. (2018). Building a multiple linear regression. Machine Learning & Data Science in Python. https://realpython.com/linear-regression-python/
• Chen, M., & Lin, Y. (2020). Effectiveness of dummy variables in regression analysis. Journal of Business Analytics, 15(2), 101-115.
• Kmenta, J. (2017). Elements of Econometrics. Routledge.
• Hilbe, J. M. (2011). Logistic Regression Models. Cambridge University Press.