Question

How do I include the data? The site keeps telling me the question is too long when I include it and when I try to add the 2 pictures it will only let me add one pic.

With milk sales sagging of late, The Milk Processor Education Program (MPEP) decided to move on from the famous "Got Milk" ad slogan in favor of a new one, "Milk Life." The new tagline emphasizes milk's nutritional benefits, including its protein content. MPEP began collecting data on the number of gallons of milk households consumed weekly (in millions), weekly price per gallon, and weekly expenditures on milk advertising (in hundreds of dollars) for the period following the launch of the new campaign. These data, in forms to estimate both a linear model and log-linear model, are available via the link below. Use these data to perform two regressions: a linear regression and a log-linear regression. Which model does a better job fitting the data?
The (Linear) or (Log-linear) model. Suppose that the weekly price of milk is $3.40 per gallon and MPEP decides to ramp up weekly advertising by 35 percent to $150 (in hundreds). Use the best-fitting regression model to estimate the weekly quantity of milk consumed after this advertising increase. Instructions: Round your intermediate calculations and enter your response rounded to three decimal places. _____million gallons per week

Answer 1

For linear regression model, the regression equation is defined as,

Y = B0 +B1X, B2X2u

Under the Homoskedasticity assumption, the expected value of error term u_i will be zero

Now, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

A B C 1 Linear Model 2 Q P 4.76 3 2.46 472.68 4 0.9 4.28 326.41 3.72 5 1.74 357.36 475.82 6 0.96 4.2 2.38 7 4.14 494.25 4.59 8 1.28 458.62 2.86 3.3 421.67

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

E PAGE LAYOUT INSERT FORMULAS DATA REVIEW Data Analysis AL ZA Data Analysis Analysis Tools OK Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances Cancel Help 36.6

Step 3: Select Input Y Range: 'Q' column, Input X Range: 'P and A' column then OK. The screenshot is shown below,

A C F G H P Regression 472.6 A X 4.76 3 2.46 Input OK 0.9 326.4 4 4.28 Input Y Range SA$2:SAS102 1.74 357.3 5 3.72 Cancel SBS2:SCS102 Input X Range: 0.96i 475.8 6 4.2 Help 2.38 494.2 7 4.14 Labels Constant is Zero 458.6 1.28! 4.59 Confidence Level 95 % 2,86 421.6 3.3 1.87 534.8 Output options 10 4,34 2.19 524.7 11 3.31 Output Range: 370.3 1.38! 12 3.35 O New Worksheet Ply: 0.21 13 420.1 4,53 O New Workbook 333.7 14 3.55 2.63 Residuals 2.44 437.3 Residual Plots 15 4.4 Residuals Standardized Residuals Line Fit Plots 1.94 442. 16 4.36 375.6 2.5! 17 3.24 Normal Probability N

The result is obtained. The screenshot is shown below,

A C D F 1 SUMMARY OUTPUT 2 Regression Statistics 3 4 Multiple R 0.739724 R Square 0.547191 6 Adjusted R Square 0.537855 7 Standard Error 1.063236 8 Observations 100 9 10 ANOVA 11 df gnificance F SS MS F 12 Regression 2.05E-17 2 132.5121 66.25604 58.60925 13 Residual 97 109.6557 1.130471 14 Total 99 242.1677 15 P-value Lower 95%Upper 95% Coefficientsandard Err 16 t Stat 17 Intercept 6.519839 0.823091 7.921166 3.94E-12 4.886232 8.153446 18 P -10.6577 5.13E-18 -1,61438 0151476 -1,91502 -1.31374 19 A 0.004664 0.001575 2.961507 0.003849 0.001538 0.00779 LLI LO n

The regression equation is,

Q6.5198 1.61438 x P +0.00466 x A

The R square value is,

R2 0.54719

Now,

For log linear regression model, the regression equation is defined as,

In(Y = B, X1 +B2X2

(since it is a log-linear, we will use log of variable Q and linear of variable P and A, while if asked for log-log model we will log of each variable)

the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

A C 1 Log-Linear Model 2 InQ P A 3 1.56 2.46 472.68 4 -0.1 4.28 326.41 3.72 0.55 357.36 475.82 6 -0.04 4.2 494.25 7 0.87 4.14 4.59 0.25 458.62 9. 1.05 3.3 421.67

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'ln(Q)' column, Input X Range: 'P and A' column then OK. The screenshot is shown below,

1P 2 InQ 1.56i 3 2.46 472.68 -0.1 0.55 Regression 4 4.28 326.41 357.36 3.72 6 X 7 Input OK 8 SAS2:SA$102 Input Y Range: Cancel Input X Range: 10 SBS2:SCS102 Help 11 Labels Constant is Zero 12 95 Confidence Level: 13 Output options 14 Qutput Range 15 New Worksheet Ply: 16 17 New Workbook 18 Residuals Residual Plots Line Fit Plots Residuals 19 Standardized Residuals 20 21 Normal Probability Normal Probability Plots 22 HA st LO N N

The result is obtained. The screenshot is shown below,

G A B D SUMMARY OUTPUT 2 Regression Statistics 3 4 Multiple R 5 R Square 0.643082 0.413554 6 Adjusted R Square 0.401463 7 Standard Error 0.580886 8 Observations 100 9 10 ANOVA gnificance F df 11 SS MS F 12 Regression 2 23.08121 11.54061 34.20161 5.74E-12 13 Residual 97 32.73059 0.337429 14 Total 99 55.8118 15 Coefficientsandard Err Lower 95%Upper 95%E P-value 16 t Stat 17 Intercept 2.216701 0.449686 4.929444 3.4E-06 1.324199 3.109203 18 P 1.92E-12 -0.66768 0.082757 -8.06797 -0.83193 -0,50343 19 A 0.002252 0.00086 2.616896 0.010293 0.000544 0.00396

The regression equation is,

In (Q2.2167 0.66768 x P 0.002252 x A

The R square value is,

R2 0.41355

From the above two model, the R square value for linear model is greater than to log linear model hence linear model is better model compare to log linear model.

For linear model, the regression equation is,

Q6.5198 1.61438 x P +0.00466 x A

For 35% ($150 in hundreds) increase in advertisement cost, while keeping the price same.

AQ 0.00466 x AA 0.00466 x 150 0.699657

consumption of milk will increase by 0.699657 gallons of milk (in millions) weekly