ECOM1000 – Business Report and Excel Prescriptive Model

Semester 1, 2025

Analytics for Decision Making

Business Report – Semester 1, 2025

This business report aims to explore a prescriptive analytics model designed to guide investment decisions through the optimal allocation of stocks within a portfolio. The primary objective is to evaluate and compare two portfolios, determining the ideal weighting of assets by assessing the risk-reward trade-off using the Sharpe Ratio, as outlined in Harry Markowitz's Modern Portfolio Theory (MPT).

Consider the following scenario:

Imagine you have $150,000 to invest in the US stock market. After conducting extensive research and evaluating the long-term potential of various companies, you've narrowed down your options to two possible portfolios, each consisting of four stocks. Your task is to determine whether Portfolio A or B offers the better investment opportunity. The specific stocks under consideration for each portfolio are detailed below.

· Portfolio A:

· Portfolio B:

Using the principles of Modern Portfolio Theory (MPT), as outlined in the MPT – Basic Concepts and MPT - Excel Model videos on the Assessments>Assessment 2 - Business Report and Prescriptive Model folder on Blackboard, your task is to evaluate two portfolios that were shortlisted. You are to determine which portfolio offers the most favourable investment opportunity by evaluating the risk-return trade-offs by calculating Sharpe Ratios for each portfolio and providing a well-supported recommendation. Specify the exact allocation of USD 150,000 among the four stocks within the selected portfolio, ensuring that each stock receives between 10% and 50% of the total investment to maintain diversification and prevent over-concentration in a single stock.

Assessment Requirements

The group needs to address the following points as part of the written report and Excel file:

MPT Prescriptive Model – Portfolio Optimization

1. Provide a summary of the steps to create the Prescriptive Model in Excel.

2. Based on the Sharpe Ratio calculations and optimization in your Excel file, which portfolio (A or B) is better for investment? Using the Excel Solver tool, what is the optimal percentage allocation for each stock in the selected portfolio? Provide a possible explanation of the results. Elaborate your answer in 1 to 3 paragraphs.

3. If you invest the entire $150,000, what would be the dollar allocation for each stock in the chosen portfolio?

ChatGPT Analysis (Written Report Only)

4. Prompt ChatGPT to evaluate which portfolio—A (T, DUK, KO, AWK) or B (BAC, META, PFE, DIS)—is better based on Modern Portfolio Theory (MPT) and Sharpe Ratio concepts. What was the exact prompt you used? Summarize ChatGPT's response. Did ChatGPT's recommendation align with your conclusions based on your calculations? Provide a detailed explanation in 1 or 2 paragraphs.

5. Enhance your prompt by asking ChatGPT (or GPT extensions) to calculate the Sharpe Ratio and the Portfolio's Expected Return using historical monthly returns from the past five years, risk free rate of 4.45% (considered in your analysis), and the requirements for minimum and maximum allocation of each stock. What prompt did you use for this request? Do you agree with ChatGPT's response? Were there any limitations noted when using the AI tool? Elaborate on your response in a few paragraphs.

Extra Tasks (Excel File Only)

Add two additional sheets to your Excel file, labelled "Portfolio A – Extra Tasks" and "Portfolio B – Extra Tasks" and address the following points:

1. Use conditional formatting to highlight when monthly returns were greater than 10% (use green colour) and lower than -10% (use red colour).

2. Using the monthly returns dataset, calculate the cumulative monthly returns of each stock as the growth of $1 (starting on 07/2019) in a tabular format and create 2 time series graphs showing the monthly prices and the growth of $1 invested for the stocks in Portfolios A and B. Ensure both graphs are consistently and appropriately formatted.

3. Using the historical monthly returns of the portfolio stocks, calculate the correlation coefficients between all pairs of stocks. Present the results in a matrix format. Visualize the results with a colour scale (Green - Yellow).

General Information and Late Submission

1. This is a group assignment, and your assigned group will be announced in Week 3. Each group will consist of 3 students and will be identified by a group number. Groups will include a mix of students from different campuses (e.g., Bentley, Mauritius, Malaysia, and Singapore). One of the objectives of this assessment is to provide an opportunity for cross-cultural collaboration among students, allowing them to learn from diverse perspectives and enhance their teamwork skills in a global context. To facilitate collaboration, it is recommended that groups utilize Microsoft Teams, provided by Curtin University, and schedule regular meetings (at least 3) to meet the assignment requirements. Groups can choose to have their meetings in person or use an online collaboration/meeting software. It is the group's responsibility to coordinate these meetings and distribute tasks among members appropriately.

Microsoft Teams Help and Guide for Curtin Students

2. Three components must be submitted for your group's business report: a written report, an Excel working file, and the Group Sign-up Form. Please use the following format and naming conventions for your files: "GroupID_Report.pdf, "GroupID_MPT_Model.xlsx, and "GroupID_Signup_Form.pdf".

3. Use the ECOM1000 Group Sign-up Form.docx template located in the Assessments > Assessment 2 - Business Report and Excel Prescriptive Model folder on Blackboard to complete the Group Sign-up Form. Each group member must acknowledge their participation by e-signing this document, including a signature for their attendance at each of the required meetings.

4. Submit the group written report via the Turnitin Assignment submission point to facilitate plagiarism checking. For additional information on Turnitin, please visit the university website: Link to Student information about Turnitin. Follow the report structure proposed in the Student's Exemplar Word document (Assessments > Assessment 2 - Business Report and Excel Prescriptive Model). Include the Group ID and the last names of all group members in the report's header, formatted as follows: Group #: LastName1, LastName2, and LastName3. Please convert your report to a PDF before uploading it to the Turnitin link on Blackboard and use the following naming format: 'GroupID_Report.pdf'.

5. The submission point for the Group written report, working Excel file, and Group Sign-up Form. on Blackboard can be found under Assessments > Assessment 2 - Business Report and Excel Prescriptive Model > Group #.

6. Please be aware that penalties for late submissions are outlined in your Unit Outline for this unit. Note that penalties are calculated based on the total marks for the assessment. For instance, a 5% penalty for submitting up to 24 hours late means a deduction of 5% from the total 40 marks, which is 2 marks. A 10% penalty per additional day will result in a further deduction of 4 marks per day. These penalties are applied to the total marks, not to your final score.

Further Instructions

1. This report focuses primarily on Lecture 5 – Measures of Association but will also require Excel skills developed throughout the semester. To complete this assignment, it is essential to watch the MPT – Basic Concepts and MPT - Excel Model videos, which provides additional theoretical insights and Excel features relevant to the task. It also includes clear instructions on completing your Excel MPT Model file for submission.

2. Follow the assessment tutorial video guidelines to prepare your Excel working file:

a. Extract the stock's historical price data using the STOCKHISTORY function.

b. Calculate the Portfolio's Risk and Expected Return (annualized to align with the risk-free asset) based on the 5-year historical returns for each stock. Use historical monthly closing price data from July 2019 to July 2024.

c. Determine the Covariance Matrix and use it to calculate Portfolio Risk. Use the MMULT and TRANSPOSE matrix functions.

d. Assume the US 10-year Government Bond rate as of July 2024, 4.45% per annum, as your Risk-Free Asset.

e. Calculate the Sharpe Ratio considering a specific weight allocation (e.g., equally-weighted portfolio). Next, optimize the portfolio's Sharpe Ratio (Excel Solver tool) by adjusting the stocks' proportion.

f. Determine the portfolio with the highest optimal Sharpe Ratio and allocate USD 150,000 among the four stocks in that portfolio. Requirement: ensure that each stock receives an allocation between 10% and 50% of the total investment to maintain diversification and prevent over-concentration in a single stock. 

3. Ensure your Excel file is well-organized, with a clear and easy-to-follow layout. Refer to the tutorial video for guidance on effectively structuring your model presentation in Excel. 

4. The Business Report should follow this structure: Introduction, Prescriptive Model - Portfolio Optimization, Chat-GPT Analysis (expand on the answers to the 5 tasks/questions), and Conclusion (summarize your findings and address any limitations). Ensure that your answers to Questions 1-3 reference your work in Excel. Follow the report structure proposed in the Student's Exemplar Word document (Assessments > Assessment 2 - Business Report and Excel Prescriptive Model).

5. The report, including tables and calculations, must be at most 3,500 words. The text should be formatted with the following settings: 1.5 line spacing, Arial font, 12 font size, 2.5 cm margins on the left, right, top, and bottom.

Adherence to these formatting guidelines is mandatory. Any work exceeding the 3,500-word limit will not be marked. The word count begins with the first word on page 1 and includes all content in the report, including tables and references.

6. Assessment Rubric: Please refer to the marking rubric, which will be provided later in the semester, for a detailed breakdown of the 40 marks allocated for your Business Report PDF and Excel submissions. As a preview, 10 marks will be awarded for the reports' formatting, introduction, and conclusion, and 17 marks will be assigned for the Excel working file. Finally, 13 marks for accurate analysis and responses to the assignment questions.