i have the answer attached i need you to create an excel sheets with the answers in it !
Question :
Complete the case problem, “Rating Wines from the Piedmont Region of Italy” located at the end of chapter 16. Upload your original Excel worksheet with a separate tab for each question. In the discussion forum, discuss your findings, forecasts, and recommendations. Uploading any other format type ie. pdf , jpeg, .docx will result in an automatic failing grade for this post. Copying/pasting of tables and/or figures is not permitted, and all tables/figures must be original work completed within the Excel file.
(There are many same/similar questions posted already. But I need the EXCEL Version. I don't know how to use EXCEL to do that. Please include what function should I use in EXCEL
Case Problem 2Rating Wines from the Piedmont Region of Italy
Wine Spectator magazine contains articles and reviews on every aspect of the wine industry, including ratings of wine from around the world. In a recent issue they reviewed and scored 475 wines from the Piedmont region of Italy using a 100-point scale. The following table shows how the Wine Spectator score each wine received is used to rate each wine as being classic, outstanding, very good, good, mediocre, or not recommended.
| Score | Rating |
| 95–100 | Classic: a great wine |
| 90–94 | Outstanding: a wine of superior character and style |
| 85–89 | Very good: a wine with special qualities |
| 80–84 | Good: a solid, well-made wine |
| 75–79 | Mediocre: a drinkable wine that may have minor flaws |
| below 75 | Not recommended |
A key question for most consumers is whether paying more for a bottle of wine will result in a better wine. To investigate this question for wines from the Piedmont region we selected a random sample of 100 wines from the 475 wines that Wine Spectator reviewed. The data, contained in the file WineRatings, shows the price ($), the Wine Spectator score, and the rating for each wine.
Managerial Report
1.Develop a table that shows the number of wines that were classified as classic, outstanding, very good, good, mediocre, and not recommended and the average price. Does there appear to be any relationship between the price of the wine and the Wine Spectator rating? Are there any other aspects of your initial summary of the data that stand out?
2.Develop a scatter diagram with price on the horizontal axis and the Wine Spectator score on the vertical axis. Does the relationship between price and score appear to be linear?
3.Using linear regression, develop an estimated regression equation that can be used to predict the score given the price of the wine.
4.Using a second-order model, develop an estimated regression equation that can be used to predict the score given the price of the wine.
5.Compare the results from fitting a linear model and fitting a second-order model.
6.As an alternative to fitting a second-order model, fit a model using the natural logarithm of price as the independent variable. Compare the results with the second-order model.
7.Based upon your analysis, would you say that spending more for a bottle of wine will provide a better wine?
8.Suppose that you want to spend a maximum of $30 for a bottle of wine. In this case, will spending closer to your upper limit for price result in a better wine than a much lower price?
| Wine | Price | Score | Rating |
| Barolo 2006 | 52 | 91 | Outstanding |
| Barbera d'Alba Superiore 2007 | 30 | 90 | Outstanding |
| Barbera Piemonte Castelvero 2009 | 10 | 80 | Good |
| Barolo Cannubi 2006 | 120 | 93 | Outstanding |
| Barolo San Pietro 2006 | 75 | 91 | Outstanding |
| Chardonnay Langhe 2009 | 20 | 83 | Good |
| Gavi Minaia 2009 | 39 | 87 | Very Good |
| Langhe Faletto 2007 | 50 | 91 | Outstanding |
| Favorita Langhe White 2008 | 18 | 87 | Very Good |
| Langhe Brandini & Brandini 2008 | 31 | 88 | Very Good |
| Arneis Langhe 2009 | 18 | 84 | Good |
| Barolo Vigneto La Villa 2006 | 48 | 93 | Outstanding |
| Barbaresco Currá 2007 | 55 | 92 | Outstanding |
| Barolo Monfortino Riserva 2002 | 375 | 98 | Classic |
| Barbera d'Alba Lirano Soprano 2007 | 56 | 91 | Outstanding |
| Barbera d'Alba Superiore 2006 | 38 | 90 | Outstanding |
| Barolo Rocche dell'Annunziata 2006 | 100 | 93 | Outstanding |
| Barbaresco Silvio Giamello Vicenziana 2005 | 42 | 92 | Outstanding |
| Roero Arneis Renesio 2009 | 35 | 89 | Very Good |
| Langhe Sorì San Lorenzo 2007 | 440 | 97 | Classic |
| Barbaresco Silvio Giamello Vicenziana 2006 | 39 | 89 | Very Good |
| Barbera d'Alba La Gamberaja 2007 | 35 | 87 | Very Good |
| Langhe White Cinerino 2008 | 38 | 86 | Very Good |
| Barolo Bricco Viole 2006 | 53 | 88 | Very Good |
| Barbera d'Alba Tradizione 2007 | 20 | 88 | Very Good |
| Barbera d'Alba 2009 | 25 | 89 | Very Good |
| Roero Arneis Valle dei Lunghi 2009 | 20 | 84 | Good |
| Piemonte La Valletta 2008 | 29 | 82 | Good |
| Barolo Brunate 2006 | 90 | 91 | Outstanding |
| Barolo Vigna Rionda 2004 | 125 | 91 | Outstanding |
| Barolo Gramolere 2006 | 55 | 90 | Outstanding |
| Barbera d'Asti Superiore Chersì 2006 | 25 | 92 | Outstanding |
| Barbera d'Alba 2008 | 25 | 93 | Outstanding |
| Barbera d'Alba Serraboella 2008 | 32 | 89 | Very Good |
| Cortese Piemonte Castelvero 2009 | 10 | 84 | Good |
| Barolo Bussia Romirasco 2006 | 180 | 97 | Classic |
| Roero Arneis Saglietto 2008 | 27 | 90 | Outstanding |
| Barbaresco Bricco Asili 2007 | 147 | 92 | Outstanding |
| Barbera d'Alba Superiore 2008 | 38 | 89 | Very Good |
| Barolo Prapò 2005 | 57 | 89 | Very Good |
| Moscato d'Asti 2009 | 22 | 89 | Very Good |
| Gavi Vigna del Lago 2009 | 19 | 87 | Very Good |
| Barbaresco Serraboella 2007 | 72 | 94 | Outstanding |
| Barolo Bussia Colonnello 2006 | 150 | 94 | Outstanding |
| Barolo Vigneto Arborina 2006 | 60 | 93 | Outstanding |
| Dolcetto d'Alba Fontanazza 2009 | 17 | 88 | Very Good |
| Barolo 2006 | 55 | 88 | Very Good |
| Gavi 2009 | 18 | 86 | Very Good |
| Gavi 2009 | 19 | 88 | Very Good |
| Barbaresco Il Bricco 2006 | 109 | 91 | Outstanding |
| Barbera d'Alba Filatura 2008 | 12 | 87 | Very Good |
| Barbera d'Alba Zio Nando 2008 | 35 | 89 | Very Good |
| Dolcetto d'Alba 2009 | 20 | 89 | Very Good |
| Barbaresco 2007 | 210 | 93 | Outstanding |
| Chardonnay Langhe 2009 | 21 | 78 | Mediocre |
| Barolo 2006 | 37 | 93 | Outstanding |
| Langhe 2006 | 16 | 89 | Very Good |
| Barbaresco Ovello 2006 | 58 | 88 | Very Good |
| Nebbiolo Langhe Cerretta 2008 | 75 | 92 | Outstanding |
| Barbera d'Asti Superiore Montruc 2008 | 39 | 88 | Very Good |
| Barolo Le Rocche del Falletto Riserva 2004 | 300 | 96 | Classic |
| Barbaresco 2007 | 35 | 90 | Outstanding |
| Dolcetto d'Alba 2009 | 22 | 90 | Outstanding |
| Barolo 2006 | 90 | 92 | Outstanding |
| Barolo Preda 2006 | 95 | 93 | Outstanding |
| Barbera del Monferrato Superiore Perlydia 2003 | 21 | 89 | Very Good |
| Grangia NV | 10 | 83 | Good |
| Langhe White Le Pernici 2009 | 25 | 86 | Very Good |
| Barbera d'Asti Superiore Il Cascinone Rive 2007 | 29 | 90 | Outstanding |
| Barbera d'Alba Piani 2008 | 23 | 89 | Very Good |
| Barolo Ciabot Mentin Ginestra 2006 | 90 | 96 | Classic |
| Dolcetto d'Alba 2009 | 18 | 88 | Very Good |
| Barbera d'Asti Bricco dell'Uccellone 2007 | 54 | 89 | Very Good |
| Barolo Marenca 2006 | 62 | 96 | Classic |
| Chardonnay Piemonte L'Altro 2009 | 21 | 88 | Very Good |
| Barolo Serralunga 2006 | 37 | 88 | Very Good |
| Dolcetto d'Alba Superiore 2008 | 24 | 86 | Very Good |
| Barolo 2006 | 52 | 91 | Outstanding |
| Gavi Black Label 2009 | 55 | 88 | Very Good |
| Langhe Sorì Tildìn 2007 | 440 | 95 | Classic |
| Barolo Mosconi 2006 | 105 | 90 | Outstanding |
| Nebbiolo Langhe Ginestrino 2008 | 40 | 89 | Very Good |
| Barolo La Rosa 2006 | 120 | 92 | Outstanding |
| Nebbiolo Langhe 2008 | 26 | 90 | Outstanding |
| Dogliani Papà Celso 2008 | 28 | 88 | Very Good |
| Barolo Lazzarito 2006 | 150 | 93 | Outstanding |
| Barolo 2006 | 37 | 91 | Outstanding |
| Barbaresco Asij 2007 | 43 | 89 | Very Good |
| Barbaresco Vanotu 1999 | 120 | 91 | Outstanding |
| Langhe Barilin 2005 | 25 | 87 | Very Good |
| Barbera d'Alba Pistìn 2009 | 25 | 88 | Very Good |
| Barolo Pì Vigne 2006 | 40 | 90 | Outstanding |
| Barolo Broglio 2005 | 57 | 90 | Outstanding |
| Dogliani Sirì d'Jermu 2008 | 27 | 89 | Very Good |
| Dolcetto d'Alba 2008 | 19 | 88 | Very Good |
| Gavi La Luciana 2009 | 15 | 87 | Very Good |
| Dolcetto d'Alba Autinot 2009 | 13 | 88 | Very Good |
| Barolo Rocche di Castiglione 2006 | 69 | 92 | Outstanding |
| Dolcetto d'Alba 2008 | 13 | 87 | Very Good |
| Chardonnay Langhe Bastia 2008 | 50 | 87 | Very Good |
