BE314 Financial Modelling Main Coursework Marking Scheme (Due 2021 May-June Semester) Background You are a fresh university graduate in a local university and is working as a business consultant, specializing in the performance of various imported chocolate brands. Your client, who is a manufacture of a local chocolate brand, have engaged you to analyse various issues relating to other chocolate brands’ performance. For this exercise, there are ten major brands in the retail chocolate market, as outlined below: Name Base Category Description Haigh’s Chocolate(HC) Adelaide Budget Contains milk. Zokoko(ZOK) NSW Budget Contains milk. Jasper + Myrtle(J&M) Canberra Mainstream Contains milk. Bahen & Co(B&C) Margaret River Mainstream Contains certain amount of milk. Metiisto(MET) Toowoomba Mainstream Contains milk. Monsieur Truffe(MT) Melbourne Premium The chocolate does not contain milk. The Smooth Chocolator(TSC) Geelong Premium The chocolate does not contain milk. Sweet Pea & Poppy(SPP) Campbell Premium The chocolate does not contain milk. 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 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: 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 (60 marks) 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 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. 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 compare 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. [15 marks] 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 t Pktc = 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. [20 marks] PART B (40 marks) 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. [10 marks] 2) address each of your director’s key areas of interest. [5 marks] 3) include at least 2 normality test for your data. [10 marks] 4) apply any 2 tests that you had learn during the course of this module, describe your steps, and the assumptions/conditions for conducting these tests. [10 marks] Overall presentation. [5 marks] [Total marks 100 marks] Submission Criteria: This assignment contributes 40% to the final assessment of the course. Please refer to the timetable for the assignment due date.




































































































































































学霸联盟