Please Answers all the following questions 1-6. Create a report that uses the da
ID: 3319775 • Letter: P
Question
Please Answers all the following questions 1-6. Create a report that uses the data in the table on the previous page to determine how different nutrients interact with each other.
1. Use Microsoft Excel to determine the correlation coefficients for the following pairs of variables, then select which two variables have the strongest correlation.
Calories and Carbohydrates
Calories and Fat
Calories and Sugar
Carbohydrates and Fat
Carbohydrates and Sugar
Fat and Sugar
2. which is the response variable, and why. Create a scatter diagram for this pair of variables.
3. Use Microsoft Excel to find the equation of the regression line for the pair of variables selected in Question 2. Be sure to also comment on the meaning of the coefficient of determination that is given by Excel in regards to the model.
4. Suppose two “healthy” cereals are selected next. Kashi Go Lean has 140 calories, 30 grams of carbohydrates, 1 gram of fat, and 6 grams of sugar in a serving size of one cup. Special K Protein Plus has 120 calories, 19 grams of carbohydrates, 1 gram of fat, and 7 grams of sugar in a serving size of ¾ cup. a. How does the value predicted by the model in Question 3 compare to the actual value listed for these two different “healthy” cereals? How might this model be improved?
5. The analysis above does not take into account the different serving sizes, for example some have a serving size of one cup while the others have a serving size of ¾ cup. Repeat questions 2 through 4, but for each of the serving sizes separately. How do the two new separate models compare to the original combined model?
6.
Perhaps you have noticed how one of the linear regression models performs
better than the other in terms of the coefficient of determination. How might
the image located to the right help clarify this discrepancy?
Explanation / Answer
1) the correlatios are given for different set of variables as
Variables Correlation
Calories and Carbohydrates 0.892707
Calories and Fat 0.099462
Calories and Sugar 0.622487
Carbohydrates and Fat -0.20315
Carbohydrates and Sugar 0.575909
Fat and Sugar 0.159375
2) Since the pair for which the correlation is high is Calories and Carbohydrates and its correlation is 0.892707.
the response variable obviously will be Calories which are the result for the Carbohydrates intake in the body.
3) Regression Analysis: Calories versus Carbohydrate
The regression equation is
Calories = 37.5 + 3.11 Carbs (g)
the regression R-Sq = 79.7% R-Sq(adj) = 78.6%
4} Regression Analysis: Calories versus Carbohydrate, Fat (g), Sugar (g)
The regression equation is
Calories = 27.1 + 3.21 Carbohydrates (g) + 5.34 Fat (g) + 0.177 Sugar (g)
the model R-Sq = 88.0% R-Sq(adj) = 85.8%
Calories (140)= 27.1 + 3.21* 30 + 5.34 * 1+ 0.177* 6 = 129.8
Calories (120)= 27.1 + 3.21* 19+ 5.34 * 1+ 0.177* 7= 94.66
The model seems to be fitted good as R-Sq is more then 80%, R-Sq is also called as the coefficient of determination, coefficient of determination is the measure of how well the data is represented by the model. In our case it is 80 percent times our model is good to represent the actual data.