BFF3121/BFB3121– Investments and Portfolio Management
Semester 1, 2025
Instructions for Portfolio Investment Assignment
Note the following important points before proceeding to the document:
This Document has three sections outlining the assignment requirements. Read each section carefully multiple times to understand the requirements. Discuss and divide the work among your team.
Assessment Type: Group
Weight: 20%. Marks: out of 125. Marks for each analysis and/or calculation is mentioned individually in each section of this document.
Page/Word Limit: For the written report Maximum 5 pages. Check Word limits for specific question. Font: Times New Roman; Font size: 12; Line space: 1
Late Submission Penalty: Initial penalty is 5% if assignments are delayed by up to 72 hours. Then 5% penalty per day (including weekends and holidays) of the total marks earned. Students get zero marks if the submission is delayed by 7 days (100% penalty).
Submission Guideline: Check out the submission guidelines (page 6) and follow accordingly.
• Follow the instructions and requirements as they appear in this document in conjunction with the Excel Assignment Template.
• All the calculations and analyses MUST be done via Excel Assignment Template which is available in Moodle. This Excel spreadsheet must be submitted with the written report.
• All the return calculations should be presented as decimals, not percentages, and approximated to six decimal points.
• All Excel works will be checked to ensure correctness of the procedure and to validate your portfolio returns and performance. If your calculation goes wrong, your findings/interpretation in the report will also go wrong for which you will lose marks.
• You must supply your personal Excel for Portfolio Values Record.
• Follow the recommended structure for your written report. Don’t forget to sign and attach the team agreement inside your report.
• Teaching team will not provide feedback on your assignments or calculations prior to submission. They can only give you general guidance about your assignment criteria.
Section A:
Some Preliminary Workouts: (20 Marks)
See instructions and requirements inside the Excel Template – start with the “Getting Started” tab.
To start your calculations and analysis, go to “Analysis Tab”. Some preliminary work will be required. Insert your weekly portfolio values, ASX200 and S&P500 values in the relevant columns.
You need to collect ASX200 and S&P500 values by yourselves (5 Marks).
Sources you may use either Yahoo Finance or Google Finance for capturing the necessary data. Collecting wrong data will result in losing marks. Collect the Adjusted Closing Value for the end of each Week for both ASX200 and S&P500.
“Calculations Tab” will require you to show calculations on various aspects related to your portfolio. Follow the requirements as presented in the tab.
“Portfolio Values Tab” will require you to collect your trading history and portfolio values in AUD from your own Excel File and then copy-paste them in the respective columns in the Excel Assignment Template (2 Marks).
Make sure you are converting USD values into AUD for reporting all your weekly portfolio values. You were given $200,000 AUD to trade with, so converting all your P/L and portfolio values in AUD will make more sense. Rather than converting each position and their individual USD values, you may simply convert the Closing Portfolio Value for each week into AUD. Just add necessary rows/columns and exchange rate details in your file to show the calculations.
There are numerous sources to find any historical foreign exchange values. This can be found from yahoo finance, google finance or even from IG itself. Check out this LINK (Use the adjusted closing).
“IG Trading History (CSV) Tab” –DOWNLOAD the CSV from IG website using the “Trade Analytics/History” option and copy paste the CSV file in this tab (3 marks). Please present the table in a neat and tidy way and format the table with proper heading, borders and fonts etc. as required.
Supplying no or incorrect CSV will make you lose marks.
“Extra Calculations Tab” – If you have any additional calculations that you think play significant role in your analysis, please show them here. Anything beyond required analysis is welcome as that may reflect any out of the box way of thinking.
Organising the Excel Files:
On the top corner of the “Getting Started” Tab, you need to provide the following information: (1) Your group number (2) List of your group members, student names and IDs and (3) IG Trade Account Details. You need to use necessary Excel functions/formulas in your calculations and analyses.
Your Personal Excel File will carry 10 Marks which comprises of meeting each weekly trade requirements and instructions (i.e. IG trade alert), correct organisation of data, currency conversions and portfolio values and % of equity value.
Your Personal Excel File for “Portfolio Value Recording” must be submitted along with the report and Excel Template. If any of the required Excel files is not submitted, you will get zero marks for the assignment. Your written report relies on excel calculations and outcomes.
Section B:
Excel Assignment Template Calculations (40 Marks)
All the required formulas and calculations must be done in the Excel Assignment Template.
All the calculation requirements are outlined in the “Analysis Tab” and “Calculations Tab”.
All the return calculations should be presented as decimals, not percentages, and approximated to six decimal points. You should not change the format of the template provided. You may add additional columns/rows beyond the given template if needed. NO FORMULA NO MARKS.
Benchmark Portfolio:
Your portfolio must be benchmarked against the ASX200 Index (Australia) and S&P500 (USA).
Risk and Return Calculation:
From the Preliminary work, you have the following:
Weekly and Monthly values and returns of your portfolio, ASX200 and S&P500 Index, and risk-free asset. Now using the above data, calculate the arithmetic average, variance and standard deviation of monthly returns of the portfolio, ASX200 and S&P500 Index and report them in the template. (3 Marks)
[Hint: Treat the monthly arithmetic return of your portfolio for further calculations where necessary].
Performance Evaluation: Analysis and Calculations Tab
Recall that during your week 4-8 tutorials, you have learned how to calculate risk, return, how to run, interpret and analyse regression, applying Index and CAPM model and portfolio performance tools via excel applications. You are required to apply the relevant model(s) to run, analyse and interpret the regression output, relevant ratios and variables of your dataset. Follow the instructions in the Excel Template. Run both Rregressions in the “Analysis Tab”. (5 Marks)
Carefully read the following requirements to calculate and evaluate your portfolio’s performance with respect to ASX200 and S&P500: (9 x 3 Marks = 27 Marks)
(i) Calculate and interpret the Sharpe measure for your portfolio, ASX200 and S&P500.
(ii) Calculate and interpret the M2 measure for your portfolio.
(iii) Report and interpret the beta of your portfolio along with a reference to its statistical significance. (iv) The Correlation Coefficient between your portfolio and each of the market (ASX200 and S&P500), and the proportion of the variability of portfolio return explained by the market movements.
(v) Calculate and show that Total risk is the sum of Systematic and Unsystematic Risks. Also, show that the variance of your portfolio is equal to the total risk of your portfolio (i.e. they closely match).
(vi) Calculate the following ratios (with respect to ASX200 and S&P500):
a. Treynor measure.
b. Jensen’s alpha.
c. Information ratio.
(vii) Calculate Expected return using CAPM.
(viii) Calculate your 'Utility' if you have an aversion score of A= 3.
(ix) Plot your portfolio's return with respect to SML (ASX and S&P500). You may Expand the box if necessary.
Nicely present all your calculations and tables in a neat & tidy way with necessary formatting. (5 Marks)
Section C:
Writing the Report (65 Marks)
Written Report Structure:
This will be a standard report based on your trading history and analysis. When preparing the report, you need to refer to the Excel Template spreadsheet whenever necessary. It is important that you reproduce & interpret Excel results and any other necessary details while writing this report.
This written report should be submitted as a pdf file.
Report should start with a cover/titlepage (mention group number, student names and IDs). You then attach the team agreement in the next page. This will be then followed by a table of contents and an executive summary. A structured flow of the report is important.
Report Structure and Team Agreement (3 Marks).
Main Report should not exceed 5 pages (excluding cover page, team agreement, table of contents and executive summary). Anything beyond 5 pages will be disregarded and will not be graded. You may add a maximum of 3 pages of appendices which you may refer for any relevant discussions. However, these appendices will not carry any marks. Use footnotes/citations etc. where necessary.
Executive Summary: (5 Marks) (Maximum 1 page)
Reflect your learning in the executive summary section. This should include your trading experience, how your experience bridges the theory into practice and your overall exposure and understanding of investment and portfolio management. You can relate and reflect how you faced the real market turmoils, uncertainties, your investment strategies, any alternative strategies and risk management. As a beginner trader/analyst how this experience may shape your future investment and portfolio management goals.
Portfolio performance: (Each Question 50-100 words)
Measuring Portfolio Performance (6 x 2 = 12 marks):
Using the relevant Excel Calculations, critically evaluate the performance of your portfolio and where necessary compare it with ASX and S&P500.
(i) Did your portfolio under/over perform based on M2? What you could have done to improve the overall performance?
(ii) Comparing your portfolio’s utility score for ASX vs S&P500, which one offers better value?
(iii) Based on your Correlation Coefficient and Beta value, can you interpret your portfolio’s risk exposure compared to market? Is your finding statistically significant?
(iv) Interpret the values for Treynor and Information ratio for your portfolio.
(v) Based on your CAPM calculation, is your portfolio’s actual return above/below compared to ASX and S&P500?
(vi) Is your portfolio generating any alpha? Interpret any positive/negative alpha of your portfolio.
Investment Strategy: (Each Question 150-250 words)
Risk Management & Portfolio Rebalancing (5 x 4 =20 Marks):
(i) During the market instability, how did you manage your portfolio exposure? Did you use any
traditional risk-hedging instruments (like investing into gold, bonds, and volatility indices) during the recent meltdown? If yes, how effective were those strategies?
(ii) During the market crisis, in terms of risk management, did diversification across asset classes, geographies, or sectors minimise/mitigate your portfolio losses? Or did systemic risks override your diversification benefits?
(iii) In light of your own trading experience, how should investors reassess their risk tolerance and asset allocation in the aftermath of the downturn?
(iv) Is “buying the dip” still a viable strategy in a high-volatility, uncertain macroeconomic
environment? Share and justify your opinion. For your portfolio, what investment strategies performed best during the crisis and why?
Opportunities & Forward-Looking: (5 x 3 = 15 Marks)
(i) In your opinion, which sectors or asset classes are likely to offer the best rebound potential or defensive value post-meltdown? Justify your opinion.
(ii) How can investors balance short-term volatility against long-term fundamentals in their recovery strategy? How should you adjust your portfolio to reduce risks for future?
(iii) As an active investor, can you identify undervalued assets during market downturns caused by trade disputes?
Conclusion (5 marks): (100-200 words)
This section concludes your report. In this section you should discuss whether your portfolio strategy, diversification efforts and risk reduction strategies (specially during meltdoen) worked or not. In other words, you wrap up your findings, learnings and overall key intakes in this assignment.
Presentation and Referencing (3+2=5 marks):
Overall report presentation, flow of the report/responses, professional style, Use of relevant graphs/tables/charts etc (where applicable); Reference to appendices etc.
Refer to Learn HQ for relevant resources. For referencing, any standard style is fine.
Note that you must submit CSV trading history and your personal Excel portfolio values in the relevant tab of Excel template, not in your appendix. Appendices are excluded from five-page limit. Cite any references used following a standard referencing style. Recall that, Executive Summary, Reference list and Appendix are excluded from five-page limit.
Submission Guidelines
Use the submission link available on Moodle site to submit your report and other documents.
Only one submission per group.
Before hitting the submit button, make sure that you have attached the following files:
1. Written Report submitted as a pdf file. Don’t forget to add the team agreement within it. (Named as follows: BFF3121/BFB3121-Portfolio Assignment Report-Group Number)
2. Excel Assignment Template File containing all calculations and analyses. You must use the template provided in Moodle for your assignment purposes.
(Named as follows: BFF3121/BFB3121-Portfolio Assignment Excel-Group Number)
3. Submit your own Excel File for Portfolio Value Recording.
(Named as follows: BFF3121/BFB3121-Portfolio Values Record-Group Number)