Assignment 2– Statistical Models
Due date: 11.59pm (Sunday, 27 Octo ber 2024)
Weighting: 25%
Type: Group
Submission: Electronic Submission via Canvas
Instructions for this ass ignment
A table with companies assigned for each group to analyse
| Group | Code | Company Name | Download Date |
|---|---|---|---|
| 1 | XOM | Exxon Mobil Corp | 1 September 2024 |
| 2 | WMT | Walmart Inc | 1 September 2024 |
| 3 | PG | Procter & Gamble Co | 1 September 2024 |
| 4 | MA | Mastercard Inc | 1 September 2024 |
| 5 | JPM | JPMorgan Chase & Co | 1 September 2024 |
| 6 | CVX | Chevron Corp | 1 September 2024 |
| 7 | HD | Home Depot Inc | 1 September 2024 |
| 8 | LLY | Eli Lilly and Co | 1 September 2024 |
| 9 | PFE | Pfizer Inc | 1 September 2024 |
| 10 | KO | Coca-Cola Co | 1 September 2024 |
| 11 | BAC | Bank of America Corp | 1 September 2024 |
| 12 | ABBV | Abbvie Inc | 1 September 2024 |
| 13 | PEP | PepsiCo Inc | 1 September 2024 |
| 14 | COST | Costco Wholesale Corp | 1 September 2024 |
| 15 | TMO | Thermo Fisher Scientific Inc | 1 September 2024 |
| 16 | MRK | Merck & Co Inc | 1 September 2024 |
| 17 | AVGO | Broadcom Inc | 1 September 2024 |
| 18 | DHR | Danaher Corp | 1 September 2024 |
| 19 | ORCL | Oracle Corp | 1 September 2024 |
| 20 | MCD | McDonald's Corp | 1 September 2024 |
| 21 | ADBE | Adobe Inc | 1 September 2024 |
| 22 | ACN | Accenture PLC | 1 September 2024 |
| 23 | DIS | Walt Disney Co | 1 September 2024 |
| 24 | VZ | Verizon Communications Inc | 1 September 2024 |
| 25 | ABT | Abbott Laboratories | 1 September 2024 |
| 26 | CSCO | Cisco Systems Inc | 1 September 2024 |
| 27 | CRM | Salesforce Inc | 1 September 2024 |
| 28 | TMUS | T-Mobile US Inc | 1 September 2024 |
| 29 | WBA | Walgreens Boots Alliance Inc | 1 September 2024 |
| 30 | BA | Boeing Co | 1 September 2024 |
INSTRUCTIONS
Answer the following questions. You will need to submit an Excel file with your numerical answers and a report in Word or PDF format. Calculate your answers in the Excel file and explain your approach and interpret your results in the report.
Download daily price data from yahoo finance (nz.finance.yahoo.com) for your chosen stock during the period between 1st September 2023 and 1st September 2024. Use the data to complete the following tasks:
a. Run the linear regression in which the daily stock price is the dependent variable (y)
and the time series (from 1st September 2023 and 1st September 2024) is the independent variable (x) ( You **should **choose **the **daily **data **from **1 st **September 2023 and 1 st **September 2024 as the independent **variables) . Draw the graph to show the linear relationship and display the equation and the R-square on the graph.
b. Apply the “ TREND” function to forecast the stock price in the next 2 weeks from 1st September 2024 to 15th September 2024. Download the data for these 2 weeks’ time from 1st September 2024. Then, compare the forecasted prices with the actual ones. Comment and make suggestions based on your results.
c. Apply the “Forecast.ETS” function to forecast the stock price in the next 2 weeks from 1st 代 写Statistical Models yahoo finance September 2024 to 15th September 2024. You should consider the impact of seasonality (such as monthly, and quarterly) on stock prices. Compare the forecasted prices with the actual ones. Comment and make suggestions based on your results.
d. Apply the Autoregressive models to stock price of your chosen stock from 1st
September 2023 and 1st September 2024. You can estimate different order for the Autoregressive models, including AR **(1) **, AR (2) , andAR **(3) **. Determine which one is the best suitable model for your stock and justify your conclusion.
e. Assume that you have invested 50,000 NZD in this period. Apply historical simulation to estimate the Value-at-Risk of your chosen stock from 1st September 2023 and 1st September 2024 with 95% confidence level.
(Hints: **You **should consider **the **file **“(4) **Ex ample **(Historical **Simulation_VaR)” **in Week 9 **for **the **references . **You **can **apply **both **“ PERCENTILE ” **and “ NORMINV ” functions **in **excel **to **identify **the **VaR **with **95% **confidence **level . First , **you **calculate the **average **of **returns **from **1 st **September 2023 **and 1 st **September 2024 **by **applying AVERAGE **function **in **excel . Then , **you **calculate **the **standard **deviation **of **returns **by applying **STDEV . P **function **in **excel . **After **this , **you **can **apply **the **NORMINVfunction in excel to identify the **VaR with 95% **confidence **lev el.)
f. Apply the Monte Carlo Simulation with 1,000 simulations to estimate the average price, median price, min, max, standard deviation of your chosen stock in the next 30 trading days starting from 1st September 2024.
(Hints: You **should **consider the **file **“(5) **Example (Monte Carlo Simulation)” in **Week **9 for **the **references . First , the **starting **price **is **the **price **on 1 st **September 2024. Then , you **calculate **the **daily **volatility **by **applying **STDEV . P **function **in **excel . Next , you simulate **the **price **in **the **next 30 trading **days . After **this , **you **apply **the 1,000 simulations **and **estimate **the **average **price , **median **price , **min , **max **and **standard deviation **of **your **simulated **data ”)
g. Estimate the stock return for your chosen stock. Apply the Monte Carlo Simulation with 1,000 simulations to estimate the average, median, min, max, standard deviation and the chance of loss of the return for your chosen stock in the next 30 trading days.
( Hints : First , **you **estimate **the **stock **return . **Then , **you **estimate **the **daily **volatility **of the **stock **return **by **applying **STDEV . P **function **in **excel . **Next , **you **simulate **the **return in **the **next 30 trading **days . After **this , **you **apply **the **1,000 simulations **and **calculate the **average return, median return, minimum and maximum re turn, and the chance o f **loss of **the return)
MARKING / GRADING
Team Performance: This will be graded on the basis of the following criteria (equally weighted) where applicable:
****Research: collecting, understanding, and interpreting information and data from relevant sources.
****Application: use appropriate theories and concepts relevant to your case; analyse them properly; draw appropriate conclusions.
****Calculation: use appropriate formulas and relevant information and data for computational purposes.
****Presentation: check for correct grammar and spelling; provide a table of contents; use spreadsheets where relevant (attach spreadsheets as exhibits and refer to the exhibits in the main text); put references at the end, acknowledging the sources of citations; justify any assumptions made; present well-formulated arguments in support of statements made.
WX：codinghelp
