DFP Module 2 Assignment: Financial Planning and Investment Analysis

This assignment focuses on investment analysis and financial planning principles within the context of Diploma of Financial Planning Module 2. It involves analyzing investment options, constructing portfolio asset allocations using Excel, and calculating weighted returns for different investment funds. The assignment requires the application of time value of money concepts to mortgage affordability scenarios, including calculating loan amounts and assessing financial feasibility. Students are expected to demonstrate proficiency in using Excel for financial modeling, including creating pie charts to visualize asset allocations and applying formulas for calculating totals and weighted returns. Additionally, the assignment explores the differences between relative and absolute cell references in Excel and their appropriate use in financial calculations. The assignment also touches upon understanding weighted returns and the factors influencing investment decisions. Desklib provides students with access to this solved assignment and a variety of other resources, including past papers and study tools, to support their learning and academic success.

DFP Module 2 Assignment 1505
Diploma of Financial Planning
Module 2 Assignment
Submission Instructions:
Key steps that must be followed:
1. Please complete the Declaration of Authenticity at the bottom of this page.
2. Once you have completed all parts of the assessment and saved it (eg. to your
desktop computer), login to the Monarch Learning Management System (LMS) to
submit your assessment.
3. In the LMS, click on the file ”Submit DFP Module 2 Assignment” in the Module 2
section of your course and upload your assessment file/s by following the
prompts.
4. Please be sure to click “Continue” after clicking “submit”. This ensures your
assessor receives notification – very important!
Declaration of Understanding and Authenticity *
I have read and understood the assessment instructions provided to me in the Learning Management System.
I certify that the attached material is my original work. No other person’s work has been used without due
acknowledgement. I understand that the work submitted may be reproduced and/or communicated for the purpose
of detecting plagiarism.
.
Student Name*: Date:
* I understand that by typing my name or inserting a digital signature into this box that I agree and am bound by the
above student declaration.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
Important assessment information
Aims of this assessment
This assessment covers the fundamentals of investment theory and application. The use of
excel spreadsheets is required to express an asset allocation in a pie chart context, with
inclusion of labels and percentages. Excel is also used to calculate a portfolio’s weighted
return. Relative and absolute cell references are also addressed. The time value of money
is covered, with specific reference to how the concepts are applied in a financial planning
modelling context. Different modelling scenarios cover the use of a present value of a lump
sum, the future value of a lump sum, the present value of an annuity and future value of
annuity to model certain advice scenarios. Loan affordability ratios and mortgage stress
are explored using time value of money methodologies. Analysing and valuing investments
using ratio analysis such as earnings per share is covered. Different types of research and
valuation methodology for investments including bottom up and top down analysis is also
addressed. Income and growth oriented investment options are canvassed, as are the use
of derivative contracts in order to manage risks. Futures and options contracts are explored
in this regard.
Marking and feedback
This assignment contains 5 assessment activities each containing specific instructions.
This particular assessment forms part of your overall assessment for the following units of
competency:
 FNSINC401
 FNSINC501
 FNSASICT503
 FNSASICW503
 FNSASICV503
 FNSFMK502
 FNSFMK503
 BSBITU402
 FNSFPL506
Grading for this assessment will be deemed “competent” or “not-yet-competent” in line
with specified educational standards under the Australian Qualifications Framework.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
What does “competent” mean?
These answers contain relevant and accurate information in response to the question/s
with limited serious errors in fact or application. If incorrect information is contained in an
answer, it must be fundamentally outweighed by the accurate information provided. This
will be assessed against a marking guide provided to assessors for their determination.
What does “not-yet-competent” mean?
This occurs when an assessment does not meet the marking guide standards provided to
assessors. These answers either do not address the question specifically, or are wrong from
a legislative perspective, or are incorrectly applied. Answers that omit to provide a
response to any significant issue (where multiple issues must be addressed in a question)
may also be deemed not-yet-competent. Answers that have faulty reasoning, a poor
standard of expression or include plagiarism may also be deemed not-yet-competent.
Please note, additional information regarding Monarch’s plagiarism policy is contained in
the Student Information Guide which can be found here:
http://www.monarch.edu.au/student-info/
What happens if you are deemed not-yet-competent?
In the event you do not achieve competency by your assessor on this assessment, you will
be given one more opportunity to re-submit the assessment after consultation with your
Trainer/ Assessor. You will know your assessment is deemed ‘not-yet-competent’ if your
grade book in the Monarch LMS says “NYC” after you have received an email from your
assessor advising your assessment has been graded.
Important: It is your responsibility to ensure your assessment resubmission addresses all
areas deemed unsatisfactory by your assessor. Please note, if you are still unsuccessful in
meeting competency after resubmitting your assessment, you will be required to repeat
those units.
In the event that you have concerns about the assessment decision then you can refer to
our Complaints & Appeals process also contained within the Student Information Guide.
Expectations from your assessor when answering different types of assessment questions
Knowledge based questions:
A knowledge based question requires you to clearly identify and cover the key subject
matter areas raised in the question in full as part of the response.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
Skill based questions:
Where you are asked to write as though you are speaking to a client, your answers must
show your ability to:
 understand your client’s concerns/perspective/views
 show empathy
 display a professional response
 explain ideas clearly and simply so your client can understand the issues
Good luck
Finally, good luck with your learning and assessments and remember your trainers are here
to assist you 

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
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

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
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.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
Required:
1) 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.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
2) 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
3) Write the formula in the box below that you used in the cell that gives the Total as being 100%
(cell C8)
C3+C4+C5+C6+C7
4) 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)
SUMPRODUCT(C4,D4)/C8
5) 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)
SUM(E3:E7)
6) 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.
There are two sorts of cell references and they are absolute and relative. Absolute and relative
references perform in a different manner when they are copied to any other cell. Relative
references on the other hand change when any formula is copied to any other different cell. It is
seen that absolute references remain unchanged even when they are copied.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
7) 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.
Relative cell reference is taken into consideration when any kind of value is copied among
multiple cells and this becomes the scenario that can be used at the time of auto filling.
8) 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.
An instance can be provided in accordance to the fact that in order to keep the sign of dollar in
all the cells, it is seen that absolute references are done.
9) Explain what is meant by a weighted return.
It looks to address the process with the assistance of which the present value of the cash flows
and the value that is terminal in nature becomes equal to the investment value that was made
primarily.

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
10) Ensure you submit your excel spreadsheet including the pie chart when you submit your
assignment.
Assessment Activity 2
Calculation Exercise
Time Value of money - Mortgage
Activity instructions to candidates
 This is an open book assessment activity.
 You are required to read this assessment and answer all 7 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: 1-2 hours

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
Tip: Appendix 4 of Module 2 has a similar question with workings.
Background
Kyle and Julia are considering whether or not to buy a particular property valued at $720,000. They
have $200,000 of their own funds to commit towards the purchase and they expect to incur an
additional $50,000 in fees and taxes on the purchase itself. They are able to borrow at an interest rate
of 7.20 per cent per annum with interest compounded monthly. Loan repayments would be monthly
with the first payment due at the end of the first month after purchasing the property. The term of the
home loan is 30 years. They both work full-time earning a combined after-tax salary of $12,000 per
month.
1. How much is the monthly mortgage payment Kyle and Julia will be required to pay for their loan?
Hint: because the interest is compounded monthly, we need to use the number of months for the
mortgage loan, not the number of years in determining the regular payments to be made. We also
need to use the monthly interest rate (7.2% /12 = 0.6%)
Using the present value of an annuity formula we can rearrange the following formula to obtain the
regular monthly mortgage payment PMT.
PV = PMT
[1−( 1+r )−n
]
r
PMT = PV /
[1−( 1+r )−n
]
r
Alternatively, you can use a financial calculator to determine PMT.
PV = amountborrowed from the bank
r = interest rate per month
n = number of months over the entire loan period
If we select the payment key (PMT) on the financial calculator, we will know the amount of
the regular monthly mortgage payments the bank requires us to pay.
If you prefer to use an online mortgage calculator, you can choose one of hundreds
available. An example of one such mortgage calculator is:
http://www.nab.com.au/personal/loans/home-loans/loan-calculators/loan-repayments-
calculator

DFP Module 2 Assignment 1505
Units: FNSINC401, FNSINC501, FNSASICT503, FNSASICW503, FNSASICV503, FNSFMK502, FNSFMK503, BSBITU402, FNSFPL506
Provide your answer here
$3869
2. A loan affordability ratio is equal to the monthly home loan repayment divided by a couple’s
household after-tax monthly income. A key threshold for ‘mortgage stress’ is when the loan
affordability ratio reaches 35%. Will Kyle and Julia face mortgage stress at current interest rates.
Provide your answer here
$3869/$12,000* 100= 32%.
It is seen that the loan affordability percentage has been 32% and this explains that they are closer to
the stress limit of 35%.
3. After 1 year, the bank informs Kyle and Julia that $564,429 is still owing on their loan. How much
in total have Kyle and Julia paid in mortgage payments during the first year?
Provide your answer here
$3,869* 12= $46,428