Question

In: Statistics and Probability

Regression Analysis (use Excel) Need to determine if there is a relationship in the amount a...

Regression Analysis (use Excel)

Need to determine if there is a relationship in the amount a household spends on prepared foods to family size and income. Need to be able to answer if the data is a good fit and what the exact relationship is between the dependent variable and the independent variables. Please use the below data and Excel to determine the equation that represents the relationship and explain the goodness of fit. Based on the data, how might this data be used?

Dollars spent on Prepared food Family size Gross monthly income
495.86 4 3126
642.77 5 3933
364.81 3 1925
619.3 5 3736
238.71 1 1453
378.94 2 2538
302.58 1 1798
231.74 2 1189
428.67 3 2247
286.99 3 1460
268.81 1 1567
329.81 2 1622
627.25 5 3828
421.52 3 2782
656.38 5 3978
400.64 3 2493
603.41 6 3753
560.69 4 3778
623 5 3609
416.12 2 2262
323.9 1 1966
418.78 3 2736
506.46 4 3274
552.53 2 3480
586.46 4 3741
637.18 8 3684
244.49 2 1476
507.19 5 2835
512.56 5 2873
312.89 1 1618
329.05 2 1565
243.49 2 1582
560.37 8 3380
599.9 5 3922
657.09 5 3845
394.82 2 2233
556.42 4 3098
596.05 8 3707
365.8 4 2071
489.08 3 3166

Please use excel when solving and provide steps. I'm working through the problem now, but am getting stuck on a few excel interpretations and would like to know what I am missing.

Keep in mind to show Ordinary Least Squares Method, minimizing error, individual y values and the Regression Line, measures of goodness of fit, correlation coefficient r, coefficient of determination, deriving r2 , and standard error of the estimate (se).

Solutions

Expert Solution


Related Solutions

Use Excel to prepare a Linear Regression Analysis. Use data samples below for populations and determine...
Use Excel to prepare a Linear Regression Analysis. Use data samples below for populations and determine if the selected independent variable is affecting the dependent variable. Use an alpha of 5% for ANOVA and Correlation Coefficient. Explain the results. Data samples Group A 104,103,101,99,97,101,101 Group B 101,100,95,99,101,103,97 Group C 100,96,99,95,99,102,106 Group D 97,99,99,101,105,100,99
You will need to use Excel Regression Data Analysis to estimate the following linear model of...
You will need to use Excel Regression Data Analysis to estimate the following linear model of Texas Natural Gas Utility Residential Demand: (1) QRES = α + β1*PRES + β2*RES + β3*INCOME where QRES = Quantity demand (mcfs) of residential customers PRES = Price per mcf, RES = number of residential customers, INCOME = per capita income, The data below are actual publicly available residential demand data for city/town natural gas distribution utilities in Texas. You can copy and paste...
What are influential observations and outliers in regression and how to determine them? Use Excel Analysis...
What are influential observations and outliers in regression and how to determine them? Use Excel Analysis ToolPak to solve the following problem. The following data show the annual revenue ($ millions) and the estimated team value ($ millions) for the 32 teams in the National Football League (Forbes website, February 2009). Team   Revenue   Value Arizona Cardinals   203   914 Atlanta Falcons   203   872 Baltimore Ravens   226   1062 Buffalo Bills   206   885 Carolina Panthers   221   1040 Chicago Bears   226   1064 Cincinnati Bengals  ...
Excel Lab 2: Regression and Goal Seek In this lab, you will use Excel to determine...
Excel Lab 2: Regression and Goal Seek In this lab, you will use Excel to determine the equation of the model which best fits a set of ordered pairs obtained from data sets. You will enter data, graph the data, find the equation for the regression model, and then use that equation to make predictions for the dependent variable. You will use the goal seek to make predictions for the independent variable. Then you will consider how accurate your predictions...
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as...
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as the response variable and Helpfulness and Clarity as the explanatory variables. Write down the corresponding coefficient estimates and provide the regression output. j. Perform an F-test for the overall usefulness of the model in part i) using a 5% significance level. Make sure you follow all the steps for hypothesis testing indicated in the Instructions section and clearly state your conclusion. k. Test manually...
In this problem, we will use linear regression and residual analysis to study the relationship between...
In this problem, we will use linear regression and residual analysis to study the relationship between square footage of a house and the home sales price. (a) Go to the course webpage and under Datasets, download the CSV file “homes.csv” and follow the accompanying Minitab instructions. Copy and paste the Fitted Line Plots and the Residual Plots in a blank document. Print these out and attach them to your homework. (b) Based on the fitted line and residual plots for...
You may need to use the appropriate technology to answer this question. In a regression analysis...
You may need to use the appropriate technology to answer this question. In a regression analysis involving 27 observations, the following estimated regression equation was developed. ŷ = 25.2 + 5.5x1 For this estimated regression equation SST = 1,500 and SSE = 590. (a) At α = 0.05, test whether x1 is significant. State the null and alternative hypotheses. H0: β1 ≠ 0 Ha: β1 = 0H0: β1 = 0 Ha: β1 ≠ 0    H0: β0 ≠ 0 Ha: β0 =...
From the table below, use a simple linear regression analysis to establish the relationship that may...
From the table below, use a simple linear regression analysis to establish the relationship that may exist between a) number of confirmed cases and deaths; b) number of confirmed cases and number of tests performed; c) number of confirmed cases and number of recoveries; and d) number of deaths and number of recoveries. Briefly discuss these relations. Date Total confirmed Death Recoveries Test 1-Apr 195 5 3 12046 2-Apr 204 5 3 12046 3-Apr 205 5 3 12046 4-Apr 214...
Use Excel to compute the Beta Coefficient for the following security (J), using first, regression analysis...
Use Excel to compute the Beta Coefficient for the following security (J), using first, regression analysis to find the slope of the Characteristic Line for J, given the returns for J and the Market Portfolio Below. Stock J Market 0.14 0.13 0.07 0.1 0.05 0.12 0.01 0.01 0.11 0.15 0.12 1.6 0.11 0.14 0.15 0.11 0.2 0.06 0.02 0.01 0.01 0.11 Use the data from B. above and Excel to compute the Beta coefficient using the covariation equation.
QUESTION A: A regression was run to determine if there is a relationship between hours of...
QUESTION A: A regression was run to determine if there is a relationship between hours of TV watched per day (x) and number of situps a person can do (y). The results of the regression were: y=a+bx a=26.695 b=-0.65 r2=0.531441 r=-0.729 Assume the correlation is significant (p-value < α), and use this to predict the number of situps a person who watches 13.5 hours of TV can do (to one decimal place) QUESTION PART B: Run a regression analysis on...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT