Question

Eclipse Engineering provides services of structural engineering. They just opened a new branch in Portland, OR. One of their products is called Structural Insulated Panels, or SIPs. It is a type of foam insulation that replaces typical wall and roof framing for residential or commercial buildings. Clients are billed a fixed fee for each project based on square footage of the building, square footage of the panel area, and number of panels. Depending on how long a project takes to complete, there usually ends up being additional cost not billed to the client (fee burned). Eclipse engineering is using simple linear regression to model their costs. We will investigate the data to find the best model that can be used to predict what the fee burned will be for a particular project!

1. Calculate the correlation between each explanatory variable and the response variable (fee burned).
2. a. Perform a test for significant correlation at 5% between Panel Area and Fee burned. What is your right-tailed p-value and conclusion?
   b. Perform a test for significant correlation at 5% between # Panels and Fee burned. What is your right-tailed p-value and conclusion?
   c. Without performing the test, what do you think the results will be between Fee Burned and Floor Area?

Fee Burned	Panel Area	Floor Area	# Panels
1759.92	2832	2482	12
2225	5670	2566	10
2885	6740	2632	23
1732.5	4478	1204	20
1662.5	4303	1680	11
390	2493	805	6
683	1804	1012	10
1138	14459	1296	9
683	2350	1184	18
748	2198	1215	10
130	3215	1209	9
163	2497	987	12
1108	4168	2270	14
857.5	2988	1160	9
928	4481	1853	11
458	1040	326	7
683	3350	1610	8

Please show all working by using Excel formulas/commands. Please write out any explanations where necessary. Please show all formulas and working out. Thank you. Please only use Excel commands and show which was used. Please show where you got your numbers from when talking about equations and etc. Thank you.

Answer 1

a)
correlation between panel area and fee burned					0.402111
n	17
df	15
T	1.700945
p-value right tailed		0.054793
b)
correlation between # panel and fee burned					0.604537
n	17
df	15
T	2.939277
p-value right tailed		0.005076

df = n-2

formula used

a)
correlation between panel area and fee burned					=CORREL(B2:B18,A2:A18)
n	17
df	=15
T	=F22*SQRT(B24/(1-F22^2))
p-value right tailed		=T.DIST.RT(B26,B24)
b)
correlation between # panel and fee burned					=CORREL(D2:D18,A2:A18)
n	17
df	=15
T	=F32*SQRT(B34/(1-F32^2))
p-value right tailed		=T.DIST.RT(B36,B34)

c)

the results should be significant be between Fee Burned and Floor Area