Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Hello I need suggestion to solve the following problem in minitab: Suppose, You have been making several types of home-made beer for a number of

Hello I need suggestion to solve the following problem in minitab:

Suppose, You have been making several types of home-made beer for a number of years.

Your friends think you make excellent beer and keep telling you that you should start up a micro brewery and go into business for yourself.

Last month an investment banker friend of yours said she would consider financing your brewery but wants you to begin with only one of your beers.

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:

Percent Beer Recipe Your Cost

Name Calories Alcohol Type Origin 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

Tasks to do:

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.

REPORT PREPARATION

Prepare your report addressed as follows:

To: Suzanne Centsible, Investment Banker

From: Your Name

Re: Microbrewery Analysis

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.

CLOSING

Close your report as follows:

Please contact me should you have any questions.

Sincerely,

Your Name

Microbrews Inc.image text in transcribed

Calories PctAlc Price 9.36 7.19 Type 1 Origin 1 159 5.2 160 5 1 1 8.94 160 4.9 1 1 7.05 162 49 1 1 157 5.5 1 1 151 49 1 1 6.17 5.78 3.78 8.19 155 4.7 1 1 135 5.1 1 1 162 5.4 1 1 142 4.8 1 1 9.00 5.57 10.05 7.49 146 4.4 1 1 148 43 1 1 6.15 170 5.1 1 1 160 5 2 1 8.94 9.47 9.63 172 5.8 2 1 170 5.9 2 1 184 6 2 1 2011 2 1 5.6 4.7 155 2 1 170 5.3 2 1 11.69 7.58 7.89 8.76 10.83 9.18 11.06 9.71 158 4.9 2 1 159 5.4 2 1 150 5.1 2 177 5.6 2 1 146 3.7 2 1 151 4.9 2 1 163 5.3 2 1 10.08 11.39 9.54 9.78 9.51 16.65 186 5.9 2 1 168 5.9 2 1 142 4.7 2 1 7.17 148 5 3 150 5 3 6.95 8.12 140 5 3 9.59 150 5 3 F Brand BrooklynBrand Leinenkugel'sked Samuel Adarris Boston GeorgeKillian'siristRed RedWolf HenryWeinhard's Privatekes. Sterling Legacy Dominion LoneStar AbitaAmber YuenglingPremium BerghoffOriginal Samuel AdarsBoston SierraNevadapale FullSailAmber Liberty Elk Mountain Amber CelisPalettock Pete's Wicked Anchor Steam DockStreetAmber Bass RechookESB NewAmsterdamNewYork CatamountAmber RedNectar OldDetroitAmber BridgePortBluehleron Pale Geary'sPale Molson Golden LabattBlue Foster's Kirin DosEquis Heineken CoruniaExtra St.Pauligid Beck's Pilsner Urquell Old Milwaukee Struh's RedDog Buchweiser Icehouse Molsonlce Michelob Budice Busch CoorsOriginal GeneseeCreamAle MillerHighlife PabstBlueRibbon Milwaukee's Best Miller GerineDraft RollingRock Micheloblight Budlight NaturalLight Coorslight Millerlite AmistelLight Sharp's CoorsCutter Kingsbury Old Milwaukee O'Doul's Kaliber Clausthaler 8.28 160 4.8 3 9.57 160 5 3 8.52 148 4.6 3 8.73 148 4.9 3 F 148 43 3 160 41 3 F 145 4.5 4 1 142 4.4 4 1 8.75 11.70 4.23 4.80 5.75 6.63 5.82 7.19 147 5 4 1 148 4.9 4 1 149 5.5 4 1 155 5.6 4 159 5 4 1 148 5.5 4 1 6.00 5.93 4.91 6.03 143 4.9 4 1 137 4.6 4 1 153 4.6 4 1 4.89 4.79 143 5 4 1 144 47 4 1 133 4.6 4 1 4.35 3.54 5.90 6.38 143 5 4 1 143 4.6 4 1 6.05 134 43 5 1 6.03 110 4.2 5 1 4.29 110 4.2 5 1 6.05 105 4.2 5 1 96 4.5 5 1 6.63 9.74 95 3.6 5 58 5 1 4.86 5.40 4.49 82 0 5 1 60 5 1 4.13 72 F 5 1 5.85 20 5 1 8.13 0 5 71 96 8.45 5 Calories PctAlc Price 9.36 7.19 Type 1 Origin 1 159 5.2 160 5 1 1 8.94 160 4.9 1 1 7.05 162 49 1 1 157 5.5 1 1 151 49 1 1 6.17 5.78 3.78 8.19 155 4.7 1 1 135 5.1 1 1 162 5.4 1 1 142 4.8 1 1 9.00 5.57 10.05 7.49 146 4.4 1 1 148 43 1 1 6.15 170 5.1 1 1 160 5 2 1 8.94 9.47 9.63 172 5.8 2 1 170 5.9 2 1 184 6 2 1 2011 2 1 5.6 4.7 155 2 1 170 5.3 2 1 11.69 7.58 7.89 8.76 10.83 9.18 11.06 9.71 158 4.9 2 1 159 5.4 2 1 150 5.1 2 177 5.6 2 1 146 3.7 2 1 151 4.9 2 1 163 5.3 2 1 10.08 11.39 9.54 9.78 9.51 16.65 186 5.9 2 1 168 5.9 2 1 142 4.7 2 1 7.17 148 5 3 150 5 3 6.95 8.12 140 5 3 9.59 150 5 3 F Brand BrooklynBrand Leinenkugel'sked Samuel Adarris Boston GeorgeKillian'siristRed RedWolf HenryWeinhard's Privatekes. Sterling Legacy Dominion LoneStar AbitaAmber YuenglingPremium BerghoffOriginal Samuel AdarsBoston SierraNevadapale FullSailAmber Liberty Elk Mountain Amber CelisPalettock Pete's Wicked Anchor Steam DockStreetAmber Bass RechookESB NewAmsterdamNewYork CatamountAmber RedNectar OldDetroitAmber BridgePortBluehleron Pale Geary'sPale Molson Golden LabattBlue Foster's Kirin DosEquis Heineken CoruniaExtra St.Pauligid Beck's Pilsner Urquell Old Milwaukee Struh's RedDog Buchweiser Icehouse Molsonlce Michelob Budice Busch CoorsOriginal GeneseeCreamAle MillerHighlife PabstBlueRibbon Milwaukee's Best Miller GerineDraft RollingRock Micheloblight Budlight NaturalLight Coorslight Millerlite AmistelLight Sharp's CoorsCutter Kingsbury Old Milwaukee O'Doul's Kaliber Clausthaler 8.28 160 4.8 3 9.57 160 5 3 8.52 148 4.6 3 8.73 148 4.9 3 F 148 43 3 160 41 3 F 145 4.5 4 1 142 4.4 4 1 8.75 11.70 4.23 4.80 5.75 6.63 5.82 7.19 147 5 4 1 148 4.9 4 1 149 5.5 4 1 155 5.6 4 159 5 4 1 148 5.5 4 1 6.00 5.93 4.91 6.03 143 4.9 4 1 137 4.6 4 1 153 4.6 4 1 4.89 4.79 143 5 4 1 144 47 4 1 133 4.6 4 1 4.35 3.54 5.90 6.38 143 5 4 1 143 4.6 4 1 6.05 134 43 5 1 6.03 110 4.2 5 1 4.29 110 4.2 5 1 6.05 105 4.2 5 1 96 4.5 5 1 6.63 9.74 95 3.6 5 58 5 1 4.86 5.40 4.49 82 0 5 1 60 5 1 4.13 72 F 5 1 5.85 20 5 1 8.13 0 5 71 96 8.45 5

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

How To Audit The Process Based QMS

Authors: Arter, Dennis R., Cianfrani, Charles A, And West, John E., 'Jack'

2nd Edition

0873898443, 978-0873898447

More Books

Students also viewed these Accounting questions

Question

How is the impairment of AFS securities treated?

Answered: 1 week ago