Demand can be estimated with experimental data, time-series data, or cross-section data. In this case, cross-section data appear in the Excel file. Soft drink consumption in cans per capita per year is related to six-pack price, income per capita, and mean temperature across the 48 contiguous states in the United States. Questions Given the data, please construct a multiple linear regression program by MS Excel. Interpret each coefficient of independent variable in the soft drink demand estimated function in question 1. Given your answer in question 1, please comment on whether the regression estimated function is a good fit or not. What is the interpretation of coefficient of determination (R-square) ? May we use the estimated function to predict for the future demand ? Explain why. Howmanycans/capita/yearonsoftdrinkshouldbeforastateinwhich6-packprice=$1.95,Income/Capita=$23,500, and Mean Temp= 68 F ? Now omit the price and temperature from the regression equation. Should a marketing plan for soft drinks be designed that relocates most canned drink machines into low-income neighborhoods ? Why or why not ? TABLE 1. SOFT DRINK DEMAND DATA State Cans/Capita/Yr 6-Pack Price ($) Income/Capita ($1,000) Mean Temp. (F) Alabama 200 2.19 11.7 66 Arizona 150 1.99 15.3 62 Arkansas 237 1.93 9.9 63 California 135 2.59 22.5 56 Colorado 121 2.29 17.1 52 Connecticut 118 2.49 24.3 50 Delaware 217 1.99 25.2 52 Florida 242 2.29 16.2 72 Georgia 295 1.89 12.6 64 Idaho 85 2.39 14.4 46 Illinois 114 2.35 21.6 52 Indiana 184 2.19 18 52 Iowa 104 2.21 14.4 50 Kansas 143 2.17 15.3 56 Kentucky 230 2.05 11.7 56 Louisiana 269 1.97 13.5 69 Maine 111 2.19 14.4 41 Maryland 217 2.11 18.9 54 Massachusetts 114 2.29 19.8 47 Michigan 108 2.25 18.9 47 Minnesota 108 2.31 16.2 41 Mississippi 248 1.98 9 65 Missouri 203 1.94 17.1 57 Montana 77 2.31 17.1 44 Nebraska 97 2.28 14.4 49 Nevada 166 2.19 21.6 48 New Hampshire 177 2.27 16.2 35 New Jersey 143 2.31 21.6 54 New Mexico 157 2.17 13.5 56 New York 111 2.43 22.5 48 North Carolina 330 1.89 11.7 59 North Dakota 63 2.33 12.6 39 Ohio 165 2.21 19.8 51 Oklahoma 184 2.19 14.4 82 Oregon 68 2.25 17.1 51 Pennsylvania 121 2.31 18 50 Rhode Island 138 2.23 18 50 South Carolina 237 1.93 10.8 65 South Dakota 95 2.34 11.7 45 Tennessee 236 2.19 11.7 60 Texas 222 2.08 15.3 69 Utah 100 2.37 14.4 50 Vermont 64 2.36 14.4 44 Virginia 270 2.04 14.4 58 Washington 77 2.19 18 49 West Virginia 144 2.11 13.5 55 Wisconsin 97 2.38 17.1 46 Wyoming 102 2.31 17.1 46
Correlation
Correlation defines a relationship between two independent variables. It tells the degree to which variables move in relation to each other. When two sets of data are related to each other, there is a correlation between them.
Linear Correlation
A correlation is used to determine the relationships between numerical and categorical variables. In other words, it is an indicator of how things are connected to one another. The correlation analysis is the study of how variables are related.
Regression Analysis
Regression analysis is a statistical method in which it estimates the relationship between a dependent variable and one or more independent variable. In simple terms dependent variable is called as outcome variable and independent variable is called as predictors. Regression analysis is one of the methods to find the trends in data. The independent variable used in Regression analysis is named Predictor variable. It offers data of an associated dependent variable regarding a particular outcome.
Demand can be estimated with experimental data, time-series data, or cross-section data. In this case, cross-section data appear in the Excel file. Soft drink consumption in cans per capita per year is related to six-pack price, income per capita, and mean temperature across the 48 contiguous states in the United States.
Questions
-
Given the data, please construct a multiple linear regression program by MS Excel.
-
Interpret each coefficient of independent variable in the soft drink demand estimated
function in question 1. -
Given your answer in question 1, please comment on whether the regression estimated function is a good fit or not. What is the interpretation of coefficient of determination (R-square) ? May we use the estimated function to predict for the future demand ? Explain why.
-
Howmanycans/capita/yearonsoftdrinkshouldbeforastateinwhich6-packprice=$1.95,Income/Capita=$23,500, and Mean Temp= 68 F ?
-
Now omit the price and temperature from the regression equation. Should a marketing plan for soft drinks be designed that relocates most canned drink machines into low-income neighborhoods ? Why or why not ?
| TABLE 1. SOFT DRINK DEMAND DATA | ||||
| State | Cans/Capita/Yr | 6-Pack Price ($) | Income/Capita ($1,000) | Mean Temp. (F) |
| Alabama | 200 | 2.19 | 11.7 | 66 |
| Arizona | 150 | 1.99 | 15.3 | 62 |
| Arkansas | 237 | 1.93 | 9.9 | 63 |
| California | 135 | 2.59 | 22.5 | 56 |
| Colorado | 121 | 2.29 | 17.1 | 52 |
| Connecticut | 118 | 2.49 | 24.3 | 50 |
| Delaware | 217 | 1.99 | 25.2 | 52 |
| Florida | 242 | 2.29 | 16.2 | 72 |
| Georgia | 295 | 1.89 | 12.6 | 64 |
| Idaho | 85 | 2.39 | 14.4 | 46 |
| Illinois | 114 | 2.35 | 21.6 | 52 |
| Indiana | 184 | 2.19 | 18 | 52 |
| Iowa | 104 | 2.21 | 14.4 | 50 |
| Kansas | 143 | 2.17 | 15.3 | 56 |
| Kentucky | 230 | 2.05 | 11.7 | 56 |
| Louisiana | 269 | 1.97 | 13.5 | 69 |
| Maine | 111 | 2.19 | 14.4 | 41 |
| Maryland | 217 | 2.11 | 18.9 | 54 |
| Massachusetts | 114 | 2.29 | 19.8 | 47 |
| Michigan | 108 | 2.25 | 18.9 | 47 |
| Minnesota | 108 | 2.31 | 16.2 | 41 |
| Mississippi | 248 | 1.98 | 9 | 65 |
| Missouri | 203 | 1.94 | 17.1 | 57 |
| Montana | 77 | 2.31 | 17.1 | 44 |
| Nebraska | 97 | 2.28 | 14.4 | 49 |
| Nevada | 166 | 2.19 | 21.6 | 48 |
| New Hampshire | 177 | 2.27 | 16.2 | 35 |
| New Jersey | 143 | 2.31 | 21.6 | 54 |
| New Mexico | 157 | 2.17 | 13.5 | 56 |
| New York | 111 | 2.43 | 22.5 | 48 |
| North Carolina | 330 | 1.89 | 11.7 | 59 |
| North Dakota | 63 | 2.33 | 12.6 | 39 |
| Ohio | 165 | 2.21 | 19.8 | 51 |
| Oklahoma | 184 | 2.19 | 14.4 | 82 |
| Oregon | 68 | 2.25 | 17.1 | 51 |
| Pennsylvania | 121 | 2.31 | 18 | 50 |
| Rhode Island | 138 | 2.23 | 18 | 50 |
| South Carolina | 237 | 1.93 | 10.8 | 65 |
| South Dakota | 95 | 2.34 | 11.7 | 45 |
| Tennessee | 236 | 2.19 | 11.7 | 60 |
| Texas | 222 | 2.08 | 15.3 | 69 |
| Utah | 100 | 2.37 | 14.4 | 50 |
| Vermont | 64 | 2.36 | 14.4 | 44 |
| Virginia | 270 | 2.04 | 14.4 | 58 |
| Washington | 77 | 2.19 | 18 | 49 |
| West Virginia | 144 | 2.11 | 13.5 | 55 |
| Wisconsin | 97 | 2.38 | 17.1 | 46 |
| Wyoming | 102 | 2.31 | 17.1 | 46 |
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 3 images
