Need to solve this problem urgently using Minitab:

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

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 perform 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 perform 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, perform 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 recommendTHE BEERyou 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.