The file Comm172 Assignment1 Data 2021.xlsx contains store sales data for Heineken beer. The data for this question is in the Question 2 sheet. The respective variables are Qc - the total unit sales of Heineken beer (six-packs, in cans, 72oz) across a large number of U.S. stores Pc - the price per liter of Heineken beer in cans Pb - the price per liter of Heineken beer in glass bottles 3 A - the total cost of advertising in the U.S. (in $1,000) The data cover 52 weeks from January 1, 2001, to December 24, 2001. You seek to understand the demand for a six-pack Heineken beer in cans. You decide to identify the underlying factors that explain the sales and measure the relative importance of these factors. (a) (2 points) Estimate the linear demand function of this form: Qc = 30 + B1Pc + U(1) where u denotes the error term. Please copy and paste your regression results from your Excel file and write down the estimated coefficients and the estimated demand function. Do not submit your Excel file for this question. (b) (1 point) Evaluate the regression coefficients statistically (i.e., which estimated coefficients are statistically significant at the 5% level?). (c) (1 point) What does the sign of the estimated coefficient 31 indicate? What does its magnitude indicate? (d) (3 points) Calculate own-price elasticity of demand at Pc = $15.00 and Pc = $30.00. How do the elasticity values differ and why? (e) (5 points) Based on the elasticity estimates, predict how the total revenue will change if Pc is increased above $15.00 and explain why. How will the profit change? What if Pc is dropped below $30.00? How will the total revenue and profit change? Is the optimal price below or above $15.00? Is it below or above $30.00? Please provide your answers and reasoning. (f) (1 point) Now consider the following demand function: Qc = e^30 x Pc^B1... (2) Write down this function in a log-linear form. (g) (2 points) Now transform the data into logs and estimate the log-linear function. Please copy and paste your regression results from your Excel file. Using the estimated coefficients, write down the estimated demand function as it appears in (2). (h) (2 points) What does the coefficient 31 measure? What does the magnitude of the estimated coefficient 31 indicate? (1) (1 point) You realized that the demand function (2) could be misspecified since it omits other variables. Add the log of Pb and the log of A into the regression equation estimated in part (g) and re-estimate the equation. Please copy and paste your regression results from your Excel file. (i) (0.5 points) Which model fits the data better, the one in part (g) or in part (1)? Hint: look at the adjusted B2 4 (k) (2.5 points) Ace the two products (canned and bottled beer) complements or substitutes? How will the demand for a six-pack Heineken beer in can change if the price of bottled beer Pb is increased by 1%? Explain using the estimation results from part (i). (1) (1 point) Estimate this equation: Qc = b0 + b1T ime + u, where Time is the week number, and write down the estimated function. (m) (2 points) What does the magnitude of the coefficient b1 indicate? What is the absolute change in the total unit sales per 10 weeks? (n) (1 point) Now estimate this equation: In Qc = b0 + b1T ime + u. Write down the estimated function. (0) (2 points) Calculate the percentage sales growth rate g per 10 weeks. Q 338 361 382 406 308 324 311 311 301 296 321 550 461 283 377 360 314 314 310 334 306 305 293 527 L 5215 6623 6859 7948 4560 5081 5383 4410 4294 3913 6116 25411 18043 3351 7038 5719 4248 5036 4278 5175 4343 4675 3985 16359 527 559 319 375 342 408 357 317 330 288 315 291 487 533 320 297 333 359 329 308 323 276 308 293 428 16359 27389 5875 7021 7123 10989 6626 4389 5530 3586 4721 4685 19869 22663 4700 3723 6745 7876 5181 4838 6014 3424 4634 3523 12484