The data in this project are the beer sales by unit (6 pack) for a local brewer. The brewer sells three lines of beer: Premium, Specialty, and Teetotal (alcohol free). The goal of this project is to build a model that will project sales for new stores based on a number of variables included in the data. Along with the units sold for each location we know the following continuous variable information from our credit card vendor data: zip code median income of the store location, number of competing products, store size in square feet, distance to the nearest grocery store in yards, and the price of our beer relative to our competitors. We also know if the store sells food (0 = no, 1 = yes), if the store is urban, if the store is near a campus, and if the store has a dedicated display for our beer.

Using the data analysis toolpak for Excel you should do the following:

Run a regression using the beer sales as Y and the other variables as X coefficients for each line of beer, report the coefficients and p-values of each model

Use the coefficients from the model to predict the sales for each beer line for two new stores:New Gas Station

Zip income: 37,000

Products: 4

Store Size 3000sf

Grocery Distance 5000

Food: No

Local Ad: No

Urban: No

Campus: No

Price Relative 1.2

Display: Yes

New Grocery Store

Zip income: 86,000

Products: 20

Store Size 15000sf

Grocery Distance 0

Food: Yes

Local Ad: Yes

Urban: No

Campus: Yes

Price Relative .9

Display: No