Determine the equation of the least squares trend line for

Question

Assignment

Data

The data - "Coffee Break" - contains time series monthly data from March 1998 to November 2016. For each of the nine brands of coffee you will have monthly data on: volume of sales (in '00 tonnes), pricing (in USD) and average price per standard size muffin - an accompaniment for drinking coffee.

Assignment Instructions

By this stage, you should have been assigned one of the nine brands of coffee to analyse.

Through extensive consultation with your director, you have identified 9 key items of interest. These are listed below as questions to be answered. Perform the necessary analyses and write a report explaining your findings to potential readers such as the sales and marketing divisions of your company as well as other industry professionals.

Question 1

Construct a line chart for your given brand of coffee. Comment on any features of interest.

Question 2

Estimate a model of volume of sales using a trend component. In other words, estimate the following:

y_t= β₀ + β₁T

Where  yt  = Volume of sales in period t

T = Trend

Comment on your findings. I.e. what is the interpretation of the coefficient estimates? Are the variables statistically significant? What about the overallmodelfit?

Note: To complete this step, you must create a trend variable.

Question 3

Refine the model you created in step 2 by adding a seasonal index. In other words, estimate the following:

y_t= β₀ + β₁T + β₂SI

Where SI = Seasonal Index

Are there any months that exhibit above average sales? What do you think is the reason? How do your findings compare to your observations in step 1? You may wish to report the seasonal indices you constructed. Comment onyourfindings.

Note 1: To complete this step, you must create a seasonal index.

Note 2: Including the seasonal index may cause the intercept to become statistically insignificant. However, the intercept should be retained as the line chart indicates that the data does not pass through the origin.

QUESTION 4

Determine the equation of the least squares trend line for the de-seasonalized data, and hence calculate the de-seasonalized sales prediction for December 2016.

Henceforth, use the de-seasonalized sales figures for the rest of this assignment. Note: Re-do Question 2 using de-seasonalized data.

Question 5

During the period Mar'98 to Nov'16, there were occasional outbreaks of a Bacterial blight - Bacterium Pseudomonas syringae - affecting the coffee plantation globally. The fear of the bacteria caused people to avoid consumption of some brands of coffee as it was reported in the media that not all brands of coffee were affected by the bacterial attacks. Based on the data, analyse how your assigned coffee brand was affected. It was recorded that the period of the bacteria outbreak were in 2005 Jan-Dec and 2012 Jan-Dec.

Refine the model you created in step 3 by adding an appropriate number(s) of dummy variable(s) to represent the occurrence of bacteria attack. In other words, estimate the following:

y_t= β₀ + β₁T + β₂SI + β₃D₁ + β₄D₂

Where D₁    = Disease dummy variable for the affected period 1

D₂  = Disease dummy variable for the affected period 2

Comment onyourfindings.

Note: To complete this step, you must create dummy variables.

Question 6

Having accounted for various time series effects, your director is now interested in knowing how pricing affects sales. You must investigate own price effects as well as the price effects of various competitive brands. Use either forward or backward selection to determine which competitors are relevant. Estimate the following:

y_t= β₀ + β₁T + β₂SI + β₃D₁ + β₄D₂+ β₅P_t+ β₆P^c_1t+ β₇P^c_2t+ ? +β_kP_kt^c

Where  P_t  = Own price in period t
P_kt^c   = Price of the k^th   competitor in period t

What is the effect of increasing the price of your own brand? What about the effect of a unit of price reduction from your major competitors? Which competitors are relevant and which ones are not?

Why do you think your brand is affected by some competitors andnotothers?

Question 7

Your director believes that sales may be affected by other savoury items and not just other brands of coffee. In particular, your client believes that the volatility of the price of muffin - a 'must-have' accompaniment in coffee drinking, may affect sales as they may be regarded as a complementary good.  Investigate the impact of muffin prices on the sales of your brand. Estimate the following:

y_t= β₀ + β₁T + β₂SI + β₃D₁ + β₄D₂ + β₅P_t^c + β₆P_1t^c + β7P_2t^c+ ? + β_kP^c_kt + β8P_t^IC

Where  P_t^IC = Average price of muffin in period t

Is your director's hypothesis valid? Are coffee and muffincomplementarygoods?

Question 8

Final Model: Now that you have done your analysis, recommend a model for your brand performance and justify your recommendation. Remember foremost your director is not an Econometrician!

Question 9

To ensure your final model is robust to the assumptions underpinning the theory of Financial Modelling, conduct 2 types ofnormalitytest.

Question 10

Conduct significance test for the coefficient of own coffee brand with α=5%

Question 11

Report writing style, structure, coherency,presentation,references.

Attachment:- Coffee-Bbreak-Data.rar