Financial modelling – Guangzhou Mars

You are a consultant working in the fast moving consumer goods (FMCG) sector. Your client competes in the confectionary sub-sector, in particular the chocolate market. You have been engaged to analyse various issues relating to your client’s brand performance. For this exercise, there are nine major brands in the retail chocolate market, as outlined below: Brand Category Description Mars Budget The chocolate does not contain milk. Kit Kat Budget The chocolate does not contain milk. Ferrero Rocher Budget This product does not contain milk. Cadbury Mainstream The chocolate contains a fair amount of milk. Patchi Mainstream Contains milk. Galaxy Mainstream Uses small amount of milk Guy Lian Premium Contains bull milk. Toblerone Premium Contains milk. Lindt & Sprungli Premium Contains milk.

These nine brands of chocolate are stocked in all major grocery stores in China. Your group will be assigned one of nine brands to analyse. Your client is interested in various aspects of brand performance. You must analyse the data and write a report explaining your findings. The objectives of this assignment are to test your understanding of the various data modelling (regression) techniques covered in the course, expose you to the kind of work you may encounter in your professional career and test your ability to write reports that have technical depth but can be understood by a non-technical audience.

Data The data can be found in the file: China_Chocolate.xls. The data file contains time series data in monthly frequency from January 1997 to December 2017. The first nine columns contain volume sales (in tonnes) for each of the various brands of chocolate. The next nine columns contain the price per 100g (in US dollars and cents). In addition to the existing data, you will need to create additional time series variables, as required by the questions below. 

QUESTION 1

By using MS Excel, construct a line chart for your given brand of chocolate. Comment on any features of interest. [10 marks]

QUESTION 2

Estimate a model of volume sales using a trend component. In other words, estimate the following: Where yt = Volume sales in period t T = Trend Comment on your findings. How do your findings for the trend variable compare to your observations in Question1? [10 marks] Hint : To complete this step, you must create a trend variable.

QUESTION 3

Refine the model you created in Question 2 by adding a seasonal index. In other words, estimate the following: 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 previous questions? Comment on your findings. [10 marks] Hint : To complete this step, you must create a seasonal index.