In: Statistics and Probability
You are in charge of pricing for a California wine company. Because nearly 90% of U.S. wines are produced in California, you think that California wines might be perceived differently from those produced in other states, thus affecting the price. You decide to see how both wine rating and whether or not the wine is from California affect the pricing of wines in the U.S.
For this question, you will need to download the Wine Rating Data and then use the data analysis tool pack in Excel to run a regression. Note that for the dummy variable, you will need to use an IF command to make that column.
https://arizona.grtep.com/core/uploadfiles/components/287971/files/Wine%20Rating%20Data.xlsx (Wine Data)
Run a regression to estimate the following equation.
Price = β0+β1Rating + β2California + β3(Rating×California) + ε
“California” is a dummy variable that equals 1 if the wine is from California. Round your answers to 2 decimal places.
Priceˆ= ________ + ________ Rating− _______ California+ _______ Rating∗California
What was the reported R2 of the model? Round your answer to 4 decimal place.
What would be the difference in predicted price of two wines that both have a rating of 93, but one is produced in California and one is produced in Oregon? Round your answers to 2 decimal places.
Hint, it might be helpful to write out the equation for when the California dummy variable equals 0 and then for when it equals 1 like we did in class for other dummy variables.
The California wine is $_______ higher than the Oregon wine.
Based on the model you estimated, at what rating do California wines become more expensive than wines from other states? Round your answers to 2 decimal places.
Regression Model
Regression Equation
Price = -215.62 + 2.78 * Rating – 67.33 * California + 0.86 * Rating * California
R Square of the model is 0.2368 or 23.68% ie only 23.68% of the variation in the dependent variable can be explained by the independent variables.
Difference in Price = -67.33 * (1-0) + 0.86 * 93 * (1-0) = -67.33 + 0.86 * 93 = 12.65
The California wine is $12.65 higher than the Oregon wine.
For all ratings, California wines become more expensive than wines from other states