Question

help solve (please show excel formula for part b)

An engineering firm is interested in investigating whether the variability in the cost of small projects (defined as projects under $10 million)can be accounted for, in part, by differences in the number of direct engineering consulting hours billed. A random sample of 20 small projects was selected from small projects performed over the past two years. The number of engineering consulting hours billed for each project, along with the project's cost was recorded. The results are available below. Complete parts a through d use a 95% confidence level and xp-3000 where needed. 囲Click the icon to view the data. a. Develop a simple linear regression model to explain the variation in total project cost based on the number of billed consulting hours The regression equation is y 742989+1030)x. Round to the nearest integer as needed.) b. Construct and interpret a 95% confidence interval estimate for the regression slope coefficent. The 95% confidence interval is D Round to two decimal places as needed.) Project Billed Consulting Hours 3,940 3,121 2,988 3,996 4,907 5,140 4,012 4,274 3,144 4,123 2,525 3,266 3,879 3,168 3,938 3,685 4,206 4,325 2,859 3,735 Total Project Cost $4,324,021 $3,750,468 $3,578,636 $5,056,026 $5,527,686 $5,639,852 $5,257,83:3 $5,682,301 $4,135,094 $4,599,515 $3,343,056 $3,866,62:2 $4,867,556 $4,046,856 $5,068,739 $5,162,261 $5,148,926 $5,195,802 $3,352,44:3 $4,747,717 10 12 13 15 16 17 18 19 20

Answer 1

Sol:

in excel install analysis tool pak and then go to

Data>Data analysis>regression

You will get

select Y as

Book1 Excel File Home Insert Page Layout Formulas Data Review View Tell me what you want to do Adabala Naveenkumarvenkat 2 Share A1 x Billed consulting hours Sensitivity: Not set mal Confidential Billed consulting hours Total project cost 3940 4324021 3121 3750468 2988 3578636 3996 5056026 4907 5527686 5140 5639852 4012 5257833 4274 5682301 3144 4135094 4123 4599515 2525 3343056 3266 3866622 3879 4867556 3168 4046856 3938 5068739 3685 5162261 4206 5148926 4325 5195802 2859 3352443 3735 4747717 Dala Analysis Analysis Tools Covariance Descriptive Statistics Cancel 10 F Test Two Sample for Variances Fourier Analysis 12 13 stogram Moving Average Random Number Generation Rank and Percentile 15 16 17 18 19 20 21 7 23 Sheet2 Sheet1 Average: 2310666025 Count 42 Min 2525 Max: 5682301 Sum 92426641 +100% Type here to search 806 AM 4/9/2019

Total project cost

x as billed consulting hours

Book1 Excel Home Insert Page Layout Formulas Data Review View Tell me what you want to do Adabala Naveenkumarvenkat 2 Share B1 X Billed consulting hours Sensitivity Not set Billed consulting hours Total project cost Regression 39401 4324021 za s 2988 3578636 3996 5056026 4907 5527686 5140 5639852 4012 5257833 4274 5682301 3144 4135094 4123 4599515 2525 3343056 3266 3866622 3879: 4867556 3168 4046856 3938 5068739 3685 5162261 4206; 5148 43251 5195802 2859 3352443 3735 4747717 Input OK 3750468 Input & Range. 5BS 15B$21 Input & Range. SA$1:SAS21 Help Labels -| Confidence Level: Constant is Zero 10 O gutput Range: New Workshoet Py ONew Workbook Residuals 13 15 16 17 18 19 20 21 7 23 П Residuals Residual Plots Line Fit Plots Standardized Residuals Probabilit Normal Prohability Plots 1 Sheet2 Sheet1 Average: 2310666025 Count 42 Min 252S Max5682301 Sum 92426641 E ® 8:07 AM 4/9/2019 Type here to search

You will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.910659
R Square	0.829299
Adjusted R Square	0.819816
Standard Error	324017.6
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	9.18E+12	9.18E+12	87.44761	2.48E-08
Residual	18	1.89E+12	1.05E+11
Total	19	1.11E+13
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	742989.2	420621.2	1.766409	0.094281	-140703	1626682
Billed consulting hours	1030.049	110.1499	9.351343	2.48E-08	798.6328	1261.465

From above ouptut

Regression eq is

y=742989+1030*x

which is the answer marked

The 95% confidence interval is

Lower 95%	Upper 95%
798.6328	1261.465

ANSWER:

the 95% confidence interval is 798.63 ,1261.47