Modeling Output

Dependent Variable: Durability of Pipe in Years, $($DURBLTY$)$

$\backslash$table$[[\backslash$table$[[$Symbol$],[($Independent$],[$Variables$)]],\backslash$table$[[$Coefficient$],[$Estimate$]],$t$-$statistic,$\backslash$table$[[$ss the Variable$],[$significant$?],[($Yes$/$No$)]],\backslash$table$[[$Intuitiveness of$],[$the result$]],\backslash$table$[[$Reasons for Intuitiveness or Non$-],[$Intuitiveness$]]],[$Constant Term,,,,,$],[\backslash$table$[[$MATRL$],[=0$ if concrete $\_],[-=1$ if steel$]],,,,,],[\backslash$table$[[$ACIDTY$],[=0$ if not acidia$],[=1$ if acidic$]],,,,,],[\backslash$table$[[$AVFLOW$],[(100\text{'}$s gallons$],[$per hr$.)]],,,,,],[$GRADE $(\%),,,,,],[\backslash$table$[[$MNTCE$],[($$$100\text{'}$s per$],[$linear mile$)]],,,,,]]$

$($e$)$ Using stepwise regression analysis methods, determine the "best" predictive regression function to model the durability of pipe. Choose the "best" model based on adjusted R$-$square value and such that each explanatory variable has a statistical significance $(p-$value$)$ of $0\mathrm{.}15$ or better and the overall model utility $(F$ value$)$ of $\mathrm{.}05$ or better. You may choose to use either Excel functions or SPSS statistical software available in vlabs on the UML website to perform the stepwise analysis. Show all results. $(15)$

$($f$)$ Answer the same questions from part $($d$)$ with the "improved" predictive linear regression model. Comment on your results. $(10)$

$($g$)$ Using the Interquartile $($IQR$)$ Method and Chauvenet's Criterion, determine if any of the AVFLOW data should be considered as outliers. $(10)$ Data for Problem $3$:

$\backslash$table$[[$PIPE ID$,$AVFLOW,MNTCES,GRADE,MATRL,ACIDITY,DURBLTY$],[$P $15916,11\mathrm{.}031,2\mathrm{.}25,0\mathrm{.}07,0,0,14\mathrm{.}55],[$P $16091,3\mathrm{.}48,3,0\mathrm{.}06,0,1,52\mathrm{.}58],[$P $16099,3\mathrm{.}14,4,0\mathrm{.}06,0,1,4\mathrm{.}68],[$P $16563,15\mathrm{.}17,2\mathrm{.}63,0\mathrm{.}05,0,1,11\mathrm{.}51],[$P $10001,10\mathrm{.}943,2\mathrm{.}5,0\mathrm{.}07,0,1,9\mathrm{.}35],[$P $10018,8\mathrm{.}3,2\mathrm{.}5,0\mathrm{.}02,0,1,15\mathrm{.}65],[$P $10019,7\mathrm{.}882,3,0\mathrm{.}06,0,0,13\mathrm{.}7],[$P $10044,5\mathrm{.}9,4\mathrm{.}25,0\mathrm{.}08,0,1,11\mathrm{.}94],[$P $10045,10\mathrm{.}45,2\mathrm{.}86,0\mathrm{.}05,0,1,8\mathrm{.}36],[$P $10046,11\mathrm{.}5,2\mathrm{.}6,0\mathrm{.}06,0,0,10\mathrm{.}94],[$P $10049,3\mathrm{.}86,3\mathrm{.}5,0\mathrm{.}06,0,1,38\mathrm{.}24],[$P $10117,13\mathrm{.}07,2,0,1,0,14\mathrm{.}74],[$P $10119,4\mathrm{.}9,2\mathrm{.}5,0\mathrm{.}07,0,1,10\mathrm{.}38],[$P $10147,3\mathrm{.}734,5\mathrm{.}75,0\mathrm{.}08,0,1,26\mathrm{.}05],[$P $10199,6\mathrm{.}68,3,0\mathrm{.}06,0,1,14\mathrm{.}63],[$P $10451,7\mathrm{.}003,3,0\mathrm{.}06,0,0,9\mathrm{.}43],[$P $10455,3\mathrm{.}279,4,0\mathrm{.}08,0,0,17\mathrm{.}57],[$P $10459,7\mathrm{.}578,3,0\mathrm{.}06,0,0,13\mathrm{.}22],[$P $10507,4\mathrm{.}78,3,0\mathrm{.}08,0,1,11\mathrm{.}09],[$P $10512,12\mathrm{.}21,1\mathrm{.}75,0\mathrm{.}06,0,0,12\mathrm{.}78],[$P $10513,0\mathrm{.}18,1,0\mathrm{.}06,0,0,51\mathrm{.}67],[$P $10514,10\mathrm{.}31,3\mathrm{.}5,0\mathrm{.}06,0,0,19\mathrm{.}51],[$P $10519,10\mathrm{.}58,2\mathrm{.}19,0\mathrm{.}06,0,0,11\mathrm{.}44],[$P $10610,3\mathrm{.}4,2\mathrm{.}75,0\mathrm{.}07,0,0,27\mathrm{.}21],[$P $10660,9\mathrm{.}147,5\mathrm{.}2,0\mathrm{.}06,0,0,116\mathrm{.}52],[$P $10956,12\mathrm{.}396,4\mathrm{.}5,0\mathrm{.}07,0,1,26],[$P $15099,3\mathrm{.}74,3,0\mathrm{.}06,0,0,38\mathrm{.}73],[$P $15065,3\mathrm{.}74,3,0\mathrm{.}06,1,0,38\mathrm{.}73],[$P $15160,8\mathrm{.}05,2\mathrm{.}6,0\mathrm{.}05,0,1,11\mathrm{.}17],[$P $15171,9\mathrm{.}39,1\mathrm{.}75,0\mathrm{.}02,0,0,20\mathrm{.}95$Problem $3$: Multiple Regression Model Development $(60)$

Given: The durability of any civil engineering system is typically determined by factors such as

the system environment, constituent material$($s$),$ rate of system use, level of maintenance, etc.

A water supply system is a typical example. It is hypothesized that the duration of any water pipe

system depends on pipe attributes such as the pipe material $($MATRL$),$ the acidity of the soil

$($ACIDTY$)$ in which the pipe is buried, the average daily flow in $100$s of gallons per $hr,($AVFLOW$),$

the average gradient of the pipes $($GRADE$),$ and the average annual amount of pipe maintenance

done in $$100$s per mile $($MNTCE$).$ From a random sample of water supply pipes in a certain state,

data on the above characteristics were collected $($see data below$).$

Find: Develop a multiple linear regression model and answer the following questions. Provide

answers to questions $($a$),($b$)$ and $($c$)$ using the table below.

$($a$)$ Using the supplied dataset, run the model using the regression function in the data analysis

tab of MS Excel. Fill in Columns $2$ and $3$ in the table below. Attach a copy of the output. $(10)$

$($b$)$ From your output, determine which variables are significant at $90\%$ confidence? $($Hint: This

implicitly involves hypothesis testing where we are simply comparing the absolute value of the

calculated $t$ statistic with the critical $t$ statistic$).$ To answer, fill in Column $4$ of the table below.

$(10)$

$($c$)$ Assess the intuitiveness of the result for each variable. Does each sign make sense to you?

Explain. To answer, fill in Columns $5$ and $6$ of the table below. $(5)$

$($d$)$ A water distribution system is planned for a new suburb on the outskirts of Lafayette. It is

intended to use concrete pipes sloping along a gentle terrain with an average gradient of $1\mathrm{.}25\%,$

and soil tests indicate that the soil in that area is acidic. From population projections, the

expected average hourly flow rate is $200$ gallons, and the pipes are expected to receive an annual

maintenance of $$150$ per mile. $($i$)$ Using the model you have developed, predict how long the

proposed pipe system is expected to last. $(5)($ii$)$ Assuming everything else remains the same,

what level of maintenance would be needed if the system were to last for $50$ years? $(5)$