https://finance.yahoo.com/quote/COST?.tsrc=fin-srch USE THIS COMPANY TO WORK ON THIS ASSIGNMENT. DO ALL THE THINGS ASKED IN POINTS 3,4 (EXCEPT OPTIONAL TASK IN POINT 4) SUBMIT 1 PDF REPORT AND 1 EXCEL WORKBOOK/n UCD MIS3003S Business Analytics Group Project (BBS38FT) Miguel Nicolau February 2024 1 Introduction The purpose of this assignment is to apply the knowledge you have acquired so far in this module to a real- world scenario. This is a group assignment. It facilitates application of several techniques, namely linear regression, time-series analysis, and linear programming, and serves to evaluate your ability to use Excel, and interpret and present Business Analytics results. 2 Financial Data Buying and selling of stocks (or bonds and other securities) is a potentially profitable business, but without proper analytics, it can lead to big financial losses. When designing a stock portfolio, some of the decisions to take are: • The capital to invest; • The risk the investor is willing to take; • The time the investor will wait for his/her returns. The use of analytics methods can help with some of these decisions. JAM • ?WM **** = **** 351 MAN & A -. ចិន **** 707 ■ quet All *Fty SHAAR ZYL 88*0* - ใย 1 **** ** **** 771 3 Assignment Description This project is a (simple) simulation of modelling and optimising an investment portfolio, stated as follows: After a strong and sustained progress throughout 2023, the stock market began a retraction after the summer, sparking fears of a return to the low levels of 2022, and leading to significant losses for some investors. However, from November 2023, a sharp climb of most market indices began. To carefully profit from this scenario, and potentially recover from the summer loss, a group of investors asked you to set up up a portfolio of companies, modelling their price behaviour and market strenght, and optimising a two-week investment strategy, from the 11th December 2023 to the 22nd December 2023, while subject to specific risk constraints. 3.1 Establish a Portfolio of Companies Your portfolio must be composed of twice as many companies as there are members in your group (e.g. eight companies for a group of four members). The first letter of the Ticker Code of each company must match the first letter of each of your team member's last (family) name, as registered with UCD. For example, Grace Hopper can choose HON (Honeywell International Inc.) and HWC (Hancock Whitney Corporation), while Alfred Kinsey can choose KHC (The Kraft Heinz Company) and KLAC (KLA Corporation). All companies must trade on the NASDAQ Stock Exchange. 3.2 Data Gathering Download daily data for each of your companies, for the period from the 30th October 2023 until the 8th December 2023 (inclusive). Based on performance during this period, choose companies likely to give a positive return on investment. Acquire your data from Yahoo Finance: http://finance.yahoo.com/1 3.3 Descriptive Analytics For each company, do the following: 1. Sequentially number each daily entry as a Trading Day (TD) (i.e. data for 30th Oct 2023 is TD = 1). 2. Calculate the 5-day Least Squares Moving Average (LSMA(5)) of the Adjusted Closing Price (ACP). The LSMA(5) at day t is calculated as follows: LSMA(5) = LR(ACP:TD,-5)(t) (1) where LR(ACP:TD,-5) is a linear regression model of ACP given TD, for the 5 previous days. In other words, to calculate the LSMA(5) for day t, build a linear regression predicting ACP given TD, using data from t – 5 to t − 1, and make a prediction for day t using the model². - 3. Create a line plot of TD versus ACP, for the period from TD=1 until TD=29, with a second line (in the same plot) showing the calculated LSMA(5) data for TD {6...29}. 4. Calculate the Daily Gain (DG) for TD = {2... 29}. The DG for day t is calculated simply as: DGt = ACP – ACPt-1 - 5. The volume of shares traded can be an indicator of market confidence. A price upwards/downwards trend with a volume increase can indicate market confidence on the trend; conversely, decreasing traded volumes over time might indicate lack of confidence on the current price trend. Capture this effect by calculating the correlation (r) between volume and the DG data calculated previously, for TD = {2...29}: VOL/DGr=r(VOL, DG) (3) ¹ Once you have found the main page of a stock, choose Historical Data, choose your dates range (make sure all required days are included), select Daily and Historical Prices, click Apply, then choose Download Data. 2The easiest way to achieve this is to use the =FORECAST. LINEAR() formula. 2 3.4 Predictive Analytics For each company, do the following: 1. Create a linear regression model to predict ACP, using TD = {1... 29} as the predictor. 2. Calculate and report the train Root Mean Squared Error (RMSE) and Coefficient of Determination (R2) of the model. 3. Calculate and report the predicted price for the 11th December 2023 (PP11). 4. Calculate and report the predicted Gain Per Share (GPS), as the difference between the predictions for 22nd December and 11th December. 5. The R² of the model is an indicator of the precision of the model, and of trust in its predictions. As such, report a Weighted Gain Per Share (WGPS), which is calculated simply as GPS * R². 3.5 Prescriptive Analytics The strategy you will employ relies on deciding how many shares to buy for each company. ● The objective is to maximimse the total expected weighted return of the investment: - The expected weighted return of each company is calculated by multiplying the WGPS of that company by the number of shares bought for the company. Add the expected weighted return of investment figures for all companies. • You will optimise how many shares to buy for each company: Ensure that the number of shares is a whole number, using Integer Linear Programming. In order to control the risk of your investment strategy, use the following constraints: • For a portfolio with P companies, invest no more than P * $10,000 (US Dollars) across all companies. • Invest no more than $10000 + $15000 * VOL/DGr, for each company. • Use the previously calculated PP11 figure for the above calculations. Calculate the optimum point (number of shares to buy for each company), using Integer Linear Programming. Report your solution, along with the total amount invested, and the Total Expected Return: • The total expected return is calculated by multiplying the GPS of each company by the number of shares bought, and adding it for all companies. Report the actual return of the investment using real data: • Download the actual market data for the investment period (11th December 2023 to 22nd December 2023); • Calculate the actual return for each company, as the close price on the 22nd December minus the open price on the 11th December, multiplied by the number of shares bought, and add it for all companies. 3 4 Deliverables 4.1 Mandatory Your deliverable will consist of a PDF Document: 1. The following is expected: (a) One professionally looking cover page, including an appropriate report title, authors (name and student ID), module name and code, and your Project Group Number; • It must also contain a short paragraph (200 words or less) stating each member's contribution, and the following statement: "This document is all our own work. We have not shown this file to any student outside the group."; (b) One introduction page, describing the contents, main results and conclusions of your report; (c) One page analysis per stock composing your portfolio, containing: • Company name and ticker code, and one-paragraph introduction to the company; • The requested time-series plot (see Section 3.3); • A verbal analysis of its share price behaviour for that period (and possible causes); • The calculated VOL/DGr value (see Eq. 3); • The resulting model, train RMSE, and train R² values. (d) Up to three pages detailing your optimisation, including: • A description of your setup, including objective function, decision variables, and constraints; • A clear description of the solution proposed; • An analysis of your predicted and observed profit, including a critical discussion of the limi- tations of the analytics methods applied. 2. The document must be at most thirteen pages long (fifteen for 5-people groups): this includes the cover page and all graphics, but does not include an optional reference list; 3. No table of contents and/or appendixes of any form are allowed. You must also submit a Microsoft Excel spreadsheet: 1. Include all your data and calculations; 2. Use one Excel tab per company (identified by its ticker code), and an extra tab for the optimisation; 3. Label cells carefully, to make it clear where objective function, constraints, and solution are. 4. Use cell references wherever possible, instead of copying values around. 5. Your Excel spreadsheet will be checked to see how your plots were made and if calculations are correct, so make sure your submission uses formulas taught in the module, and also inlucdes your Solver setup). Pay attention to the following requirements concerning your submission: • Write your PDF document as a report of your findings to the investors (in other words, do not write it as a school report). Do not make any references to formulas, calculations, cells, or implementation details of your Excel workbook: your report must be self-contained. • Submit two separate files: your PDF report and a single Excel workbook. Do not submit a zip file. • Criteria for evaluation include clarity of presentation, document consistency demonstrating team work, capacity to succinctly describe all findings, and critical analysis of results and methodology. You will not be evaluated based on the performance of your portfolio, but rather on correct simulations and calculations. • Any changes to the above methodology must be approved by the lecturer, and justified in the report. 4 4.2 Optional You can also attempt extra credits for your project with the following optional objectives. Please note that these require you to replace visualisations/calculations made before. 1. Descriptive Analytics: Calculate the daily volume percentage change for each company, for TD = 1,..., 29. The daily volume percentage change for TD = t is calculated as: VOLt VOLt-1 1 Add the above data as a bar plot to each company's graph, using a secondary vertical axis. Positive values should be shown in green, and negative values in red. Make sure your plot remains readable. 2. Predictive Analytics: For each company, predict the opening price on the 11th December 2023 (PP11) using a linearly weighted moving average across the whole range of the training data, as follows: 29 Σ (i* ACP) Σ²² i PP11 = Use the above figure for total invested amount calculations only: i.e. use the same GPS value as before. 3. Prescriptive Analytics: To further minimise risk, do a pair-wise correlation analysis of all the companies comprising your portfolio. For each pair of stocks, calculate the rank correlation between their ACP data for the training period (30th October - 8th December 2023). Include an extra constraint in your optimisation, not allowing more than $15,000 of investment amongst the pair of stocks with the highest positive correlation (r). 4. Implement the previous task (3) by using a detrended rank correlation instead: use the linear regression model for the ACP data of each company to detrend the ACP values, and use the resulting detrended ACP values for your pairwise rank correlation calculations. No extra pages are allowed, even if you attempt any of the optional objectives mentioned. Incorporate the extra objectives you attempted into your project (i.e. do not provide two implementations, a "standard" one and an "extra" one). Do not attempt any extra or alternative techniques not mentioned in this project specification, without seeking permission from the module coordinator first. 5 Submission file and your Excel file must be submitted through Brightspace by the deadline. Both your PDF • You are free to discuss your assignment with other students, but not to look at any other group's work, or show your work to any student outside your group. If you use quotes or paraphrases, images, or ideas from outside sources, you must give a citation. • Plagiarism, including accidental plagiarism, may result in penalties including grade penalties, failure of the module, and disciplinary action: http://libguides.ucd.ie/academicintegrity/plagiarismandwriting • Please be advised that your online submission will be checked with Brightspace's plagiarism detector. 5