| Assessment Activity 1
Project Balanced Funds Comparison |
Activity instructions to candidates
- This is an open book assessment activity.
- You are required to read this assessment and answer all 10 questions that follow.
- Please type your answers in the spaces provided.
- Please ensure you have read “Important assessment information” at the front of this assessment
- Estimated time for completion of this assessment activity: 3 hours
Background
Your client is considering investing $50,000 in one of 2 funds offered through BCG, a notable investment fund manager. One fund under consideration is the BCG Balanced Fund which has the following asset allocation.
| Cash | 10% |
| Fixed Interest | 15% |
| Listed Property | 20% |
| Australian Shares | 35% |
| International Shares | 20% |
The alternative fund is the BCG Growth fund which has the following asset allocation:
| Cash | 3% |
| Fixed Interest | 7% |
| Listed Property | 15% |
| Australian Shares | 45% |
| International Shares | 30% |
From your research you have also been able to determine the average returns that these funds have generated in the past three years for each asset class. The returns have been:
| Cash | 4.00% |
| Fixed Interest | 6.00% |
| Listed Property | 8.00% |
| Australian Shares | 9.50% |
| International Shares | 7.30% |
At your next client meeting you have decided to present to your client an excel spreadsheet that shows the overall weighted return that would have been received by investors in the BCG Balanced Fund compared to returns that would have been received by investors in the BCG Growth Fund, over the previous three years. You will also show a visual representation of the asset allocation by including a pie chart for each fund. (In Appendix 5 of Module 2 are tips to help you with excel.)
Please save all parts to this assessment activity and upload these to the LMS when submitting your assignment for grading.
Required:
- Using excel, and the data provided on the previous page, open a new spreadsheet in excel and replicate the spreadsheet below where you will see the format we require. Show the asset allocation, returns and weighted returns for both the BCG Balanced fund and the BCG Growth fund. (Save the spreadsheet for submission.)
- Ensure that formulae are used in the excel spreadsheet to find the ‘Total’ asset allocation of 100% .
- Use formulae in the excel spreadsheet to calculate the weighted returns for each funds.
- Use formulae in the excel spreadsheet to calculate the total weighted return for each fund.
- Create a pie chart for each fund showing the asset allocation of each fund.
- Include a legend showing which asset class each colour represents
- Include data labels on the pie charts to show the actual percentages allocated to each asset class
- Include a title for each fund
- Write the formula in the box below that you used in the cell that gives the Total as being 100% (cell C8)
- Write the formula in the box below that you used in the cell that gives the weighted return for Cash in the BCG Balanced Fund (cell E2)
- Write the formula in the box below that you used in the cell that gives the Total weighted return for the BCG Growth Fund (cell E22)
- When looking at a cell reference, how do you know if the cell is a “relative” cell reference in excel or an ‘’absolute” cell reference in excel? Remember, you can google or youtube this, in addition to the notes we have provided in Appendix 5 if you need additional help.
- Provide an example of when you might use a relative cell reference in excel. Remember, you can google or youtube this, in addition to the notes we have provided if you need additional help.
- Provide an example of when you might use an absolute cell reference in excel. Remember, you can google or youtube this, in addition to the notes we have provided if you need additional help.
- Explain what is meant by a weighted return.
- Ensure you submit your excel spreadsheet including the pie chart when you submit your assignment.
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.