i have the answer attached i need you to create an excel sheets with the answers in it !

Question 6 answer steps :

Step 1 :

Given:

We want to know whether paying more for a bottle of wine will result in a better wine.

To create a regression model, we will be using two variables names Price and Score.

Explanation:

Here, the dependent variable is Score and the independent variable is Price.

Step 2 :

Part 1)

The table of count and average is created as follows:

There appear to be a relationship between the price of the wine and the Wine Spectator rating. There isn't any "Not recommended" wine in the data set.

So, we can't calculate the average for this rating.

Explanation:

The number of wines is calculated by using the Excel function COUNTIF(range,criteria). For range, we select the column of ratings and criteria will be the particular rating.

To calculate the average for each rating, we use AVERAGEIF(range,criteria,[average range]) function from Excel.

For range, we select the column of ratings, the criteria will be the particular rating, and for the average range we select the column of price.

Step 3 :

Part 2)

To draw a scatterplot, follow the below steps.

1 select the columns of "Price" and "Score."

2 Click on "Insert" tab.

3 From "Charts", select the "Scatter"

The scatterplot is,

From the above scatterplot, the relationship between price and score appear to be linear.

Part 3)

An estimated regression equation to predict the score is,

y=0.028x+87.763

And the value of R2 is 0.4062

Explanation:

To get the regression equation,

right click on the scatterplot and select the option "Add trendline"

Select "linear" also, check the boxes for "Display Equation On Chart" and "Display R-Square value on chart."

Step 4 :

Part 4)

The estimated second order model is,

y=−0.0001x2+0.0713x+86.166

And R2=0.5233

Explanation:

To get the second-order model,

right click on the scatterplot and select the option "Add trendline"

Select "Polynomial" with degree=2 also, check the boxes for "Display Equation On Chart" and "Display R-Square value on chart."

Part 5)

Here, we can see that the R squared value for the second-order model is high. So, the second-order model is better fit to predict the score from the price of wine.

Explanation:

Higher the R2 value, better the model is for prediction.

Step 5 :

Part 6)

A model using the natural logarithm of price as the independent variable is,

y=3.1559 ln⁡(x)+77.731

and R2=0.5758

Here, we can see that the R squared value using natural logarithm is higher than the R squared value for the second-order model.

Therefore, the logarithmic model is better fit to predict the score from the price of wine.

Explanation:

To get a model using the natural logarithm of price as the independent variable,

right click on the scatterplot and select the option "Add trendline"

Select "Logarithmic" also, check the boxes for "Display Equation On Chart" and "Display R-Square value on chart."

Step 6 :

Part 7)

Since R squared value is around 0.58, we can say that approximately 58% of the variation in score is explained by the price.

So, we can say that spending more for a bottle of wine will provide a better wine.

Part 8)

Yes, spending closer to the upper limit will provide a better wine than spending much lower for a bottle of wine.

Explanation:

The R square value of 0.58 is descent value. So, as we spend more for a bottle, we might get a better wine.

Final answers :

Part 1)

Part 2)

The scatterplot is,

From the scatterplot, the relationship between price and score appear to be linear.

Part 3)

An estimated regression equation to predict the score is,

y=0.028x+87.763

Part 4)

The estimated second order model is,

y=−0.0001x2+0.0713x+86.166

Part 5)

Here, we can see that the R squared value for the second-order model is high. So, the second-order model is better fit to predict the score from the price of wine.

Part 6)

A model using the natural logarithm of price as the independent variable is,

y=3.1559 ln⁡(x)+77.731

The logarithmic model is better fit to predict the score from the price of wine.

Part 7)

We can say that spending more for a bottle of wine will provide a better wine.

Part 8)

Yes, spending closer to the upper limit result in a better wine than a much lower price.