Project 3 - Consulting for Gallo - Summer 2022

Introduction to Statistics for the Social Sciences (SBS200) Regression Analysis - Predicting Price from Wine Ratings Note: This assignment requires the Data Analysis ToolPak . Here is a video of how to install it in Windows: https://www.youtube.com/watch?v=AycQedgKAfY Here is a video of how to install it on a Mac: https://www.youtube.com/watch?v=fxRyunAQR48 Scenario Imagine that you are working as a Consultant for the E & J Gallo Winery Company. Your analysis will focus on wines priced at $100 or less. Each bottle of wine has both a price and a rating. The ratings range from 1 - 100; higher ratings mean better wine. The management at Gallo has asked you to provide descriptive statistics and to complete a regression analysis to determine how a wine's ranking influences its price and then provide a recommendation based on your findings. The final deliverable for this assignment will be an Analytics Report. Step 1: Review the organization of the Excel file Each wine has four variables: Country: All of these wines are from the US Points: This is the rating for each wine (out of 100) Price: All of these wines sell for $100 or less Variety: This is the type of wine There are two columns that will help in creating the histograms Bin for Price: This groups price into $10 intervals Bin for Points: This groups ratings into 10-point intervals Step 2: Provide descriptive statistics based on price - Provide descriptive statistics that focus on the price of wines - The output will be included in your report (with your name embedded) - Create a histogram showing how many wines sell at each price point Step-by-step help for the descriptive statistics for "Price" Use Data "Analysis ToolPak" > Data > Data Analysis > Descriptive Statistics Be sure that the "Labels in the First Row" box is checked Be sure that the "Summary statistics" box is checked Click in the Input Range box, then - Select the "Price" data (Window: Select top cell and use keyboard shortcut Ctl + Shift + down arrow) (Mac: Select top cell and use keyboard shortcut Command + Shift + down arrow) - Click OK

- To create histogram for "Price" data Use Data "Analysis ToolPak" > Data > Data Analysis > Histogram Be sure that the "Show Chart" box is checked Click in the "Bin Range" highlight values in the "Price Bin" column Click in the "Input Range" highlight values in the "Price" column Be sure that the "Labels" box is checked Be sure that the "Chart Output" box is checked Click OK and clean up the chart - Delete the final row of the frequency table that says "More" - Within the chart, delete the box that says "Frequency" - Format "Data Series" to set "Gap Width" to 0% - Title graph to include description, "Frequency Distribution of Pricing Data for US Wines Priced at $100 or Less" - Include your own name in the title Step 3: Complete Second Analysis focused on ratings (points) - Provide descriptive statistics that focus on the ratings of wines - The output will be included in your report (with your name embedded) - Create a histogram showing how many wines sell at each rating point (Please refer to instructions above.) Step 4: Complete a regression predicting price based on ratings (points) - The output will be included in your report (with your name embedded) Step-by-step help - Find complete regression to predict "Price" based on "Points" Use Data "Analysis ToolPak" > Data > Data Analysis > Regression Click in the Input Y Range box, then - Select the "Price" data (Window: Select top cell and use keyboard shortcut Ctl + Shift + down arrow) (Mac: Select top cell and use keyboard shortcut Command + Shift + down arrow) Click in the Input X Range box, then - Select the "Points" data (Window: Select top cell and use keyboard shortcut Ctl + Shift + down arrow) (Mac: Select top cell and use keyboard shortcut Command + Shift + down arrow) - Click OK - The value for the regression coefficient for "Points" is 2.6852 - The value for the intercept is -203.74

Step 5: Complete a scatterplot that includes regression equation (trend line) and r 2 Create scatterplot for "Price" x "Points" data - Highlight the two columns of data for "Price" and "Points" - Use Insert > Chart > Scatter - Add trendline and select - Display Equation on chart - Display R-squared value on chart - Title graph - Include your own name in the title Step-by-step help - How to clean up the scatterplot - Choose layout that provides labels - Label axes and title - Fix numbers on the two axes - Double click on the x-axis to open up the "Format" menu - Set "Minimum" to "75" and "Maximum" to 100 - Repeat for the y-axis to open up the "Format" menu - Set "Minimum" to "0" and "Maximum" to 100 - Right click on any of the data points - Add trendline and select - Display Equation on chart - Display R-squared value on chart - Please note we got the same values as we did using the "Regression" analysis above - The value for the regression coefficient for "Points" is 2.6852 - The value for the intercept is -203.74