Create a trip generation model for "Trips" variable using EXCEL to perform linear, least squares multiple regressions. The equation will be of the form y = bo + brxl + brx2 where y is the number of trips (dependent variable) and x are the independent variables.
Follow the steps in class slide "Steps in Multivariate Model Development"
1. Review data
- Plots of all dependent vs independent variables
• Analyze apparent and likely relationships (signs + / -)
• Reasonableness of using data (continuous? indicator?)
- Check relationships and correlations, plot independent variables
• Any correlations > 0.5 ?
2. Estimate model (s)
- Do I need to add or remove a variable based on step 1?
- Check if signs are intuitive
- Inspect goodness of fit measures and coefficient estimates, p-values
- Remove, 1 at the time, variables with p-values > 0.05 (start with the highest)
3. Create and analyze diagnostic plots (y vs x)
- Observed vs predicted
- Residuals vs. predicted
- Standardized residuals vs. predicted
- Based on what you learned repeat steps 2 and 3 or stop and recommend a model.
4. Recommend a final model. Interpret the meaning of each coefficient. Create a summary table showing the model equation, the R-squared value, the intercept, the coefficient, and the p-value for each variable in the final model. Analyze the final model: does the sign of the coefficients make sense? What is the overall goodness-of-fit? Comment if you have done further exploration of the data (e.g. outliers)
5. What is the probability that a household with 3 members, 2 adults, no access to transit, in an urban area, with $40,000 income, 2 workers, and 2 vehicles will generate more than 8 trips?
6. A developer is proposing to build 20 units for households as described in point 5. What is the forecasted number of average trips per day? Give an estimate of the maximum number of trips per day with a 95% confidence level. If this number is too high and the developer wants to reduce the forecast, what can he/she do?
Attachment:- Data.xlsx