| FIN361:Financial Modelling |
Background
Having recently graduated from a local university, you were hired as a business consultant to specialize in the performance of various imported chocolate brands. Your client, who is a manufacturer of a local chocolate brand, have engaged you to analyze various issues relating to other chocolate brands’ performance.
For this exercise, there are ten major brands in the retail chocolate market as outlined below:
These eight brands of chocolate are stocked in all grocery stores in the major cities in Australia. You will be assigned one of eight brands to analyse. Your client is interested in various aspects of brand performance. You must analyze the data and write a report to explain your findings.
The objectives of this assignment are to test your understanding of the various data modeling (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: Aust_Choc.xls. The data file contains time series data in monthly frequency from January 1997 to December 2017. The first nine columns contain monthly volume sales (in tonnes) for each of the various brands of chocolate. The next eight columns contain the price per 100g (in US dollars and cents) of the corresponding brand of chocolate. The last column, ‘Berries’, is the price (per 100g) of common berries, a matched
fruit that traditionally eaten with chocolate.
In addition to the existing data, you may need to create additional time series variables, as required by the questions below.
PART A
QUESTION 1
By using MS Excel, construct a line chart for your given brand of chocolate. Comment on any features of interest. [5 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 Question 1?
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.
Hint: To complete this step, you must create a seasonal index.
QUESTION 4
In the year 2007, the outbreak of a new disease affecting cattle had a devastating impact on the farm’s dairy industry across the world Known as ‘whooping cow disease’, fear of the virus quickly spread throughout the world causing people to avoid consumption of cattle and cattle-related products such as dairy goods. How do you think this virus would affect chocolate sales? At the same time, certain brands of chocolate do not use milk in their production. How do you think their sales will be affected compared to the brand assigned to you?
Refine the model you created in previous questions by adding a dummy variable to represent the one-off occurrence of ‘whooping cow disease’. In other words, estimate the following:
Where D = Disease dummy variable
What is the overall impact of the disease? How do your findings compare to your observations in previous questions? Comment on your findings.
Hint: To complete this step, you must create a dummy variable.
QUESTION 5
Having accounted for various time series effects, your client is now interested in how pricing affects sales. You must investigate own price effects as well as the price effects of various competitors. In other words, you need to estimate the following, taking into consideration only competitors’ pricing which are significant:
Where Pt = Own price in period
= Price of the kth competitor’s brand in period t
What is the effect of increasing the price of your own brand? What about the effect from competitors’ brands? Which competitors are relevant and which ones are not? Research on relevant information to compute the various elasticities. Why do you think your brand is affected by some competitors and not others? Comment on the nature of the product – ice cream.
PART B
Write a separate report summarising your findings for the above analysis. In your report, be sure to:
1) recommend a model to present to your director and explain why you have chosen this model. You must also interpret the model so your client can understand its meaning.
2) address each of your director’s key areas of interest.
3) include at least 2 normality tests for your data.
4) apply any 2 tests that you had learnt during the course of this module, describe your steps, and the assumptions/conditions for conducting these tests.
5) Overall presentation
