Need urgent help to solve the following problem using Minitab.

(The data given in JPG format. Please let me know how to attach Excel format as I didnt find any other option to upload. Thanks.)

Assignment:

Suppose, Your friend has given you a data set (beer.mtw) for analysis.

The data set contains some collected information on 69 beers presently sold in the United States:

The variables in this data set include:

1. Brand Name

2. Price per 6-pack (in dollars)

3. Calories

4. Percent Alcohol Content

5. Recipe Origination: Domestic =1 Foreign=0

6. Type of Beer: craft lager=1, craft ale=2, imported lager=3, regular and ice beer=4, light and no alcohol beer=5.

The four home-made brands you presently brew, number of calories, percent alcohol content, beer type, origin of recipe, and your production cost per 6-pack are as follows:

Name Calories Percent Alcohol Beer Type Recipe Origin Your Cost per 6-pack

1. Red Dragon Ale 166 4.9 Craft Ale Foreign $8.25

2. Skull Crusher 180 5.9 Craft Lager Domestic $4.88

3. Lordstown Ice 145 4.5 Ice Beer Domestic $5.63

4. Crimson Apple 90 0 No Alcohol Foreign $4.50

Non-alcoholic

ASSIGNMENT

In order to be considered for financing to start Microbrews Inc., your investment banker friend wants you to do an analysis of this data. You need to analyze, discuss, and recommend which of your home-made beers you are proposing to brew and how much profit per 6-pack do you expect to make.

To do this, you should build a multiple regression model using price as the dependent variable. All the remaining variables are independent variables and are potential predictors for your model. Some of these variables may be continuous and some of them are categorical. You need to decide.

Once you have a "good" model (significant predictors and acceptable diagnostics), you should use your model and examine each of your four home-made beers in order to estimate what price you can charge. In order to have a good model, all predictors must be significant. When working with categorical predictors, if you have a non-significant predictor you should look at the overall result. For example, if 1 and 2 are significant, but 3 is not, you need to see if overall that entire predictor is significant. If it is, keep everything in the model. If it is not, the entire predictor needs to be removed.

Once you have your model, create a 95% confidence interval that will predict the lowest and highest mean price you can charge. Subtract your cost per 6-pack from both your lower and upper CI to determine your estimated lower and upper profit per 6-pack. Identify those beers that have an anticipated profit, rather than a loss. You will also be asked to estimate your return on investment (ROI) for each of these beers (profit divided by expenses). From this, you should recommend THE BEER you would like to start brewing for resale. Since you already have the brewing equipment (sunk cost), we will only be examining the variable costs of actually making the beer. Although later on we should also examine the fixed cost component of this investment, this is not a cost accounting class, so we wont do it now.

EXECUTIVE SUMMARY

Provide the results of your analysis by first discussing the model you have chosen to predict beer price. What factors have statistical significance with regards to beer price? Discuss the model you have selected and why it is valid. Next, analyze each of your home-made beers in terms of their profit potential. Make a final recommendation of the beer(s) you believe have the best potential for profit and what you anticipate your profit and return on investment (profit/cost) to be per 6-pack.

C2 Price 9.36 7.19 8.94 C5 Type 1 C3 Calarles 159 160 160 162 1 1 1 C6 Origin 1 1 1 1 1 1 1 1 157 1 1 151 1 1 1 1 7.05 6.17 5.78 3.78 8.19 9.00 5.57 10.05 7.49 5.15 8.94 9.47 9.63 11.59 7.58 7.89 CA PctAlc 5.2 5 4.9 4.9 5.5 4.9 4.7 5.1 5.4 4.8 4.4 4.3 5.1 5 5.8 5.9 6 5.6 4,7 5.3 4.9 5.4 1 1 1 1 2 155 135 162 142 146 148 170 160 172 170 184 201 155 170 158 159 150 177 146 151 163 186 168 142 148 150 140 2 2 2 2 2 2 2 8.76 1 1 1 1 1 1 1 1 1 1 1 1 1 a 1 1 1 1 1 1 1 5.1 5.6 3.7 4.9 5.3 5.9 5.9 2 2 2 2 2 2 Z 4.7 Z 2 3 3 5 0 0 C1-T Brand BrooklynBrand Leinenkugel'sRed SamuelAdamsBoston George Killian'sirishRed Red Wolf Henry Weinhard's PrivateRes. Sterling Legacy Dominion LoneStar AbitaAmber YuenglingPremium BerghoffOriginal SamuelAdams Boston Sierra Nevadapale FullSailAmber Liberty Elk MountainAmber Cells PaleBock Pete's Wicked AnchorSteam DockStreetAmber Bass RedhookESB NewAmsterdam NewYork CatamountAmber Red Nectar Old Detroit Amber BildgeportBlue Heronpale Geary'sPale Molson Golden LabattBlue Foster's Kirin DosEquis Heineken CoronaExtra St. Pauligirl Beck's Pilsnerurquell Old Milwaukee Stroh's RedDog Budweiser Icehouse Molsonice Michelob Budice Busch CoorsOriginal GeneseeCreamAle Miller High Life PabstBlueRibbon Milwaukee's Best MillerGenuine Draft RollingRock Micheloblight Bud Light NaturalLight Coors Light Millerute Amstel Light Sharp's CoorsCutter Kingsbury Old Milwaukee O'Doul's Kaliber Clausthaler 10.83 9.18 11.06 9.71 10.08 11.39 9.54 9.78 9,51 10.65 7.17 6.95 8.12 9.59 8,28 9.57 8.52 8.73 8.75 11.70 4.23 4.80 5.75 6.03 5.82 7.19 5 5 5 4.8 3 3 0 150 160 0 3 3 0 160 148 3 3 0 0 148 0 3 3 0 1 148 160 145 142 147 148 149 4 4 4 1 1 1 1 4 4 4 0 6.00 5.93 4.91 4 4 1 1 4 4 4 4 4 4 5 4.6 4.9 4.3 4.1 4.5 4.4 5 4.9 5.5 5.6 5 5.5 4.9 4.6 4.6 5 4.7 4.6 5 4.6 4.3 4.2 4.2 4.2 4,5 3.6 a 0 a 0 0 155 159 148 143 137 153 143 144 133 143 143 134 110 110 105 96 95 58 82 60 72 70 71 96 6.03 4.89 4.79 4.35 3.54 5.90 6.38 5.05 6.03 4.29 6.05 5.05 9.74 4,86 5.40 4.49 4.13 5.85 8.13 8.45 4 4 5 5 1 1 1 1 1 1 1 1 1 1 1 1 1 0 5 5 5 5 5 5 5 1 1 1 1 1 5 5 5 5 0 0 0 0 C2 Price 9.36 7.19 8.94 C5 Type 1 C3 Calarles 159 160 160 162 1 1 1 C6 Origin 1 1 1 1 1 1 1 1 157 1 1 151 1 1 1 1 7.05 6.17 5.78 3.78 8.19 9.00 5.57 10.05 7.49 5.15 8.94 9.47 9.63 11.59 7.58 7.89 CA PctAlc 5.2 5 4.9 4.9 5.5 4.9 4.7 5.1 5.4 4.8 4.4 4.3 5.1 5 5.8 5.9 6 5.6 4,7 5.3 4.9 5.4 1 1 1 1 2 155 135 162 142 146 148 170 160 172 170 184 201 155 170 158 159 150 177 146 151 163 186 168 142 148 150 140 2 2 2 2 2 2 2 8.76 1 1 1 1 1 1 1 1 1 1 1 1 1 a 1 1 1 1 1 1 1 5.1 5.6 3.7 4.9 5.3 5.9 5.9 2 2 2 2 2 2 Z 4.7 Z 2 3 3 5 0 0 C1-T Brand BrooklynBrand Leinenkugel'sRed SamuelAdamsBoston George Killian'sirishRed Red Wolf Henry Weinhard's PrivateRes. Sterling Legacy Dominion LoneStar AbitaAmber YuenglingPremium BerghoffOriginal SamuelAdams Boston Sierra Nevadapale FullSailAmber Liberty Elk MountainAmber Cells PaleBock Pete's Wicked AnchorSteam DockStreetAmber Bass RedhookESB NewAmsterdam NewYork CatamountAmber Red Nectar Old Detroit Amber BildgeportBlue Heronpale Geary'sPale Molson Golden LabattBlue Foster's Kirin DosEquis Heineken CoronaExtra St. Pauligirl Beck's Pilsnerurquell Old Milwaukee Stroh's RedDog Budweiser Icehouse Molsonice Michelob Budice Busch CoorsOriginal GeneseeCreamAle Miller High Life PabstBlueRibbon Milwaukee's Best MillerGenuine Draft RollingRock Micheloblight Bud Light NaturalLight Coors Light Millerute Amstel Light Sharp's CoorsCutter Kingsbury Old Milwaukee O'Doul's Kaliber Clausthaler 10.83 9.18 11.06 9.71 10.08 11.39 9.54 9.78 9,51 10.65 7.17 6.95 8.12 9.59 8,28 9.57 8.52 8.73 8.75 11.70 4.23 4.80 5.75 6.03 5.82 7.19 5 5 5 4.8 3 3 0 150 160 0 3 3 0 160 148 3 3 0 0 148 0 3 3 0 1 148 160 145 142 147 148 149 4 4 4 1 1 1 1 4 4 4 0 6.00 5.93 4.91 4 4 1 1 4 4 4 4 4 4 5 4.6 4.9 4.3 4.1 4.5 4.4 5 4.9 5.5 5.6 5 5.5 4.9 4.6 4.6 5 4.7 4.6 5 4.6 4.3 4.2 4.2 4.2 4,5 3.6 a 0 a 0 0 155 159 148 143 137 153 143 144 133 143 143 134 110 110 105 96 95 58 82 60 72 70 71 96 6.03 4.89 4.79 4.35 3.54 5.90 6.38 5.05 6.03 4.29 6.05 5.05 9.74 4,86 5.40 4.49 4.13 5.85 8.13 8.45 4 4 5 5 1 1 1 1 1 1 1 1 1 1 1 1 1 0 5 5 5 5 5 5 5 1 1 1 1 1 5 5 5 5 0 0 0 0