ECOM1000 – Analytics for Decision Making
Business Report – Assessment 2
Prescriptive Analytics Model – Optimization of Portfolio via MPT
Semester 1 - 2025
1. Introduction
· Explain the purpose of the report and introduce the theoretical model (e.g., Modern Portfolio Theory, MPT) that will be used for the analysis.
· State the study’s objective and include tables highlighting the differences between the analysed portfolios.
· Briefly explain how your group planned and worked together, including how meetings were scheduled and conducted (e.g., face-to-face, online via Teams).
· Include a brief paragraph from each student introducing a group member, highlighting their background, including work experience, field of study, and cultural identity.
Suggestion: 600 – 800 words
2. Prescriptive Model – Portfolio Optimization
In this section, we will begin addressing the tasks and questions outlined in the Assessment Requirements.
2.1 Modern Portfolio Theory – Prescriptive Model
1. Provide a summary of the steps to create the Prescriptive Model in Excel. Elaborate a short description of each step.
The following steps outline constructing a prescriptive model designed to optimize portfolio allocation based on Modern Portfolio Theory (MPT). This process incorporates key Excel functions and tools, including historical data analysis, risk-return calculations, and optimization techniques, to determine…
Suggestion: 500 – 700 words
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 short paragraphs.
The optimal portfolio allocation and Sharpe Ratio for portfolio A and B is as follows:
· Sharpe Ratio:
|
Portfolio A
|
Optimal Allocation (%)
|
|
T
|
|
|
DUK
|
|
|
KO
|
|
|
AWK
|
|
|
Portfolio B
|
Optimal Allocation (%)
|
|
BAC
|
|
|
META.
|
|
|
PFE
|
|
|
DIS
|
|
Based on the Modern Portfolio Theory (MPT) prescriptive model results and the Sharpe Ratio calculations performed in Excel, Portfolio…
Suggestion: 200 – 400 words
3. If you invest the entire $150,000, what would be the dollar allocation for each stock in the chosen portfolio?
Present the optimal allocation for Portfolio A in a table format, showing each stock's percentage and dollar investment.
Suggestion: 40 – 70 words
2.2 ChatGPT Analysis
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.
To address this task, we developed the following prompt to ChatGPT:
Prompt:
ChatGPT Answer:
Suggestion: 250 – 350 words
Did ChatGPT's recommendation align with your conclusions based on your calculations? Provide a detailed explanation in 1 or 2 paragraphs.
Suggestion: 100 – 150 words
3. 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 (July 2019 – July 2024), risk free rate of 4.45%, and the requirements for minimum and maximum allocation of each stock. What prompt did you use for this request?
To address this task, we developed the following prompt to ChatGPT:
Prompt:
ChatGPT Answer:
Suggestion: 250 – 350 words
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.
Suggestion: 200 – 300 words
3. Conclusion
· Summarise your findings and address any limitations.
Suggestion: 200 – 300 words