financial modelling (excel)

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:

𝑦𝑡 = 𝛽0 + 𝛽1𝑇 + 𝛽2𝑆𝐼 + 𝛽3𝐷1 + 𝛽4𝐷2

Where 𝐷1 = Disease dummy variable for the affected period 1

𝐷2 = Disease dummy variable for the affected period 2

Comment on your findings. [15 marks]

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:

𝑐 𝑐 𝑐

𝑦𝑡 = 𝛽0 + 𝛽1𝑇 + 𝛽2𝑆𝐼 + 𝛽3𝐷1 + 𝛽4𝐷2+ 𝛽5𝑃𝑡 + 𝛽6𝑃1𝑡 + 𝛽7𝑃2𝑡 + ⋯ + 𝛽𝑘𝑃𝑘𝑡

Where Pt = Own price in period t

Pkt = Price of the k competitor in period t

c th

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 and not others? [15 marks]

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:

𝑐 𝑐 𝑐 𝐼𝐶

𝑦𝑡 = 𝛽0 + 𝛽1𝑇 + 𝛽2𝑆𝐼 + 𝛽3𝐷1 + 𝛽4𝐷2 + 𝛽5𝑃𝑡 + 𝛽6𝑃1𝑡 + 𝛽7𝑃2𝑡 + ⋯ + 𝛽𝑘𝑃𝑘𝑡 + 𝛽8𝑃𝑡

Where Pt = Average price of muffin in period t

IC

Is your director’s hypothesis valid? Are coffee and muffin complementary goods? [5 marks]

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! [5 marks]

Question 9

To ensure your final model is robust to the assumptions underpinning the theory of Financial Modelling, conduct 2 types of normality test. [8 marks]

Question 10

Conduct significance test for the coefficient of own coffee brand with α = 5% [7 marks]

Question 11

Report writing style, structure, coherency, presentation, references. [15 marks]

[Total marks = 100]

Submission Criteria:

1. Write a report detailing your findings. Your report will need to contain an Executive Summary/Introduction, Body, Conclusion/Recommendation and References (at least 4 credible sources). Appendices are optional, but should not be more than 4 pages. In your report, be sure to address each of your client’s key areas of interest according to each numbered part in this assignment. You may crop/copy–paste the relevant Excel output (e.g. charts/tables, etc.) into appropriate parts in your report.

2. To ensure a concise report this assignment will have a word limit of 3000 – 3500 words (from Executive Summary/Introduction –> Conclusion/Recommendation inclusive), and total page limit of 13 pages (from Table of Content -> Appendices inclusive)

3. You are to submit exactly the following 2 items :

Item 1: A MS Word file that includes Cover sheets => Report => references => Appendices Item 2: A MS Excel file with folders labelled as Orig_Data, Q1, Q2, and Q3…., Q9

4. Exactly ONE (1) report submission is allowed. There will not be any re-submission for missing items/documents (e.g. charts, tables, etc.). You are therefore advised to exercise extra cautious before submission.

5. Reports found with any form of plagiarism will be dealt with seriously, in accordance to University’s policy.