Use Annual Income independent variable and Mortgage Amount as dependent variable, to find a simple linear regression. (You can use either regression from Data Analysis tool or using Trendline through scatter plot) Use the coefficient values to calculate the estimated Mortgage Amount in sheet2. Then calculate the forecast error and square error for each record. At last, calculate the average error and and RMSE (Root Mean Squared Error). What are average error [ Select ] and RMSE ?

A B D E F G H 1 Customer Number Age 2 Marital Status Annual Income Mortgage Amount Payments Per Year Total Amount Paid Default on Mortgage? 1 37 SINGLE S 172,125.70 $ 473,402.96 24 S 581,885.13 YES 3 2 31 SINGLE S 108,571.04 $ 300,468.60 12 S 489,320.38 NO 4 3 37 MARRIED S 124,136.41 $ 330,664.24 24 S 493,541.93 YES 5 4 24 MARRIED S 79,614.04 $ 230,222.94 24 $ 449,682.09 YES 6 5 27 SINGLE S 68,087.33 $ 282,203.53 12 $ 520,581.82 NO 7 6 30 MARRIED S 59,959.80 $ 251,242.70 24 $ 356,711.58 YES 8 7 41 SINGLE S 99,394.05 $ 282,737.29 12 S 524,053.46 NO 9 8 29 SINGLE S 38,527.35 $ 238,125.19 12 S 468,595.99 NO 10 9 31 MARRIED S 112,078.62 $ 297,133.24 24 S 399,617.40 YES 11 10 36 SINGLE S 224,899.71 $ 622,578.74 12 S 1,233,002.14 NO 12 11 31 MARRIED S 27,945.36 $ 215,440.31 24 S 285,900.10 YES 13 12 40 SINGLE S 48,929.74 $ 252,885.10 12 $ 336,574.63 NO 14 13 39 MARRIED S 82,810.92 $ 183,045.16 12 $ 262,537.23 NO 15 14 31 SINGLE S 68,216.88 $ 165,309.34 12 $ 253,633.17 NO 16 15 40 SINGLE S 59,141.13 $ 220,176.18 12 S 424,749.80 NO 17 16 45 MARRIED S 72,568.89 $ 233,146.91 12 $ 356,363.93 NO 18 17 32 MARRIED S 101,140.43 $ 245,360.02 24 S 388,429.41 YES 19 18 37 MARRIED S 124,876.53 $ 320,401.04 4 S 360,783.45 YES 20 19 32 MARRIED S 133,093.15 $ 494,395.63 12 S 861,874.67 NO 21 20 32 SINGLE S 85,268.67 $ 159,010.33 12 $ 308,656.11 NO 22 21 37 SINGLE S 92,314.96 $ 249,547.14 24 S 342,339.27 YES 23 22 29 MARRIED S 120,876.13 $ 308,618.37 12 $ 472,668.98 NO 24 23 24 SINGLE S 86,294.13 $ 258,321.78 24 $ 380,347.56 YES 25 24 32 MARRIED S 216,748.68 $ 634,609.61 24 $ 915,640.13 YES 26 25 44 SINGLE S 46,389.75 $ 194,770.91 12 S 385,288.86 NO 27 26 28 MARRIED S 132,323.76 $ 518,999.12 24 S 776,210.07 YES 28 27 41 MARRIED S 101,799.27 $ 242,974.82 12 S 519,801.54 NO 29 28 42 SINGLE S 72,708.46 S 277,473.87 4 S 351,375.45 YES 30 29 30 MARRIED S 170,494.76 S 507,883.95 12 $ 738,913.49 NO 31 30 35 SINGLE S 61,742.37 $ 178,309.62 4 S 204,693.76 YES 32 31 43 MARRIED S 92,851.73 $ 295,768.37 4 S 315,403.24 YES 33 32 27 SINGLE S 67,039.07 $ 268,602.19 12 $ 436,736.67 NO 34 33 23 SINGLE S 71,105.79 $ 188,100.06 12 S 320,195.33 NO 35 34 39 SINGLE S 58,421.01 $ 249,958.88 12 S 427,480.93 NO 36 35 40 SINGLE S 98,472.26 $ 320,908.70 12 $ 545,924.99 NO 37 36 37 MARRIED S 112,622.95 $ 337,873.51 12 $ 561,803.09 NO 38 37 37 SINGLE S 87,125.65 $ 275,337.14 4 S 278,656.59 YES 39 38 33 SINGLE S 78,298.50 $ 260,700.34 12 $ 517,794.52 NO 40 39 33 SINGLE S 31,248.32 $ 200,414.73 12 S 383,638.01 NO 41 40 35 MARRIED S 99,648.61 $ 261,283.23 24 S 510,798.86 YES 42 41 26 MARRIED S 136,035.76 $ 292,050.94 4 S 339,563.87 YES 43 42 38 MARRIED S 96.591.66 $ 189.436.48 12 S 366.573.56 NO Estimated Mortgage Amount Data + I J K L M N