Financial Modelling Assignment: Risk Management and Best Practices

Financial Modelling
(FIN280)
Assignment2
Total marks: 100
Personal ID: 10043847
I have read the Assignment Guide in the ‘General assessment information’ and have
applied the word count principles to my work.
My word count for this
assignment is:
[Enter your word count]
words
Your assignment should be loaded into KapLearn by 11.30 pm on the due
date.
All times are based on AEDT/AEST time zones.
Refer to ‘Time remaining’ on the ‘Assignment’ page in KapLearn to ensure
you submit your assignment by the specified due date and time.
Checklist
 I have completed my assignment using Word and Excel.
 I have completed my assignment using Calibri, Arial, Times New Roman or
Verdana fonts.
 I have added my Personal ID on this page.
 I have added my word count on this page.
 I have added my Personal ID in front of the filename in the footer on the second
page.
 I have saved the file to be uploaded as PersonalID_FIN280_AS2_v6.
 Each question of my assignment is within the word limit guidelines for that
question as per the ‘General assessment information’ (Assessment Assignment
General assessment information).
 My assignment file size is no larger than 2 MB.
 If tables were required, they are visible as text, not as links or images.

 I have not removed the marking grid from the footer.
 I have submitted my assignment as per the instructions in KapLearn.
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 2 © Kaplan Higher Education

Marker feedback
Comment on overall performance:
For marker use only.
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 3 © Kaplan Higher Education

Instructions to students
• This assignment covers Topics 1 to 10 and accounts for 60% of your final
grade.
• There are five (5) questions in this assignment. You should answer
all questions.
• The overall word limit for the assignment is as follows:
– Question 1 is limited to 750 words
– Question 3 limited to 50 words
– Question 5 is limited to 225 words.
Marks will only be awarded for answers up to the word limit (plus 10%) for
each question. Any material written after this will not be counted towards your
mark for that question. Headings, quotes and references within the body of the
answer are included in the word count. Numerical tables, calculations, and
reference lists are not included. For more information on word counts and their
rationale, go to Assessment  Assignment General assessment information.
• You are required to construct a number of spreadsheets for this assignment. Submit
only one (1) Excel file for marking containing all your spreadsheets, clearly
marking the worksheets relevant for each question. The Excel file must be uploaded
separately to the Word document.
• The excel template provided (released at the end of Week 7) is an additional
resource for Questions 2 and 3. The template is
not essential for completing this
assignment (i.e. it is possible to answer Questions 2 and 3 without this template).
To encourage students to develop their own responses, this template will not be
released until the end of Week 7.
• Data for use in Question 5 will be available to download as a separate file to the
assignment.
• Refer to the Criteria-based Marking Guide for guidelines on what is expected for
each question.
• The ‘General assessment information’ section inKapLearn contains information
about format and presentation, word limits, citations and referencing, collusion,
plagiarism and other policies, useful resources, submitting your assignment and
accessing your results.
• Full workings must be shown for all calculations. Show
all calculations in the text of
your assignment and not attached as an appendix. Appendices to assignments will
not be read.
• Answers are to be in your own words. Reference and cite all your sources (within
the text of your answer) when quoting or using material from external sources.
Include a reference list at the end ofyour assignment. Refer to the ‘Referencing and
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 4 © Kaplan Higher Education

Citations Guide’ available from the ‘Library Learning Hub’ in KapLearn for further
information on referencing.
• Indicative weightings are noted beside each question. Use these weightings to
assist you with your allocation of time and resources. The weightings indicate the
relative importance of each question.
• State
all assumptions used in providing your answer.
• Requests for special consideration or information pertaining to special consideration
written in the body ofthe assignment will not be considered by the marker. Refer to
the ‘special consideration’ section of the Assessment Policy on Kaplan’s website for
more information.
Learning outcomes (LO) mapping Marks
2. Use Excel to effectively model business problems and extract essential
data from spreadsheets. 28
3. Illustrate best practice modelling techniques by applying strategies for
user-friendly design, consistency, error reduction and data security. 25
4. Effectively summarise and communicate the assumptions, implications
and results of spreadsheet models. 22
5. Interpret and audit model results in the context of the business situation
and address the inherent assumptions through stress testing. 25
Total marks 0
Criteria-based Marking Guide
The Criteria-based Marking Guide provided at the end of each question is designed to
assist students to understand what is expected of them in each question and to let
them know how their performance will be judged. It provides advice about the criteria
used in the marking of the question and what discriminates between an excellent,
satisfactory and unsatisfactory answer.
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 5 © Kaplan Higher Education

Question 1 Written content (25 marks | Word limit: 750
words)
LO5: Interpret and audit model results in the context of the business situation and
address the inherent assumptions through stress testing.
You are the CFO of an ASX-listed company and have been impressed by the recent
presentation from a member of the Finance and Shared Services (FSS) team, which
advocated the benefits and reasons for employing financial modelling internally or
through an external provider. As such, you are progressing this proposal to the board
risk management sub-committee.
You are aware of the need to manage risk and overcome potential scepticism of
the use of financial modelling, given the financial and reputational damage that has
been known to occur through unchecked reliance on financial models, such as the
‘London Whale’ trading loss (summarised below).
Using another real-world example of risk management failure, prepare a presentation
to contrast how you propose your company should manage model risk. You will need
to communicate to the risk management sub-committee the following:
• a brief summary of a real-world example of risk management failure (other than the
‘London Whale’ case study)
• how financial models can be used beneficially in the business
• what actions should be taken to mitigate the risks involved with financial modelling?
• how would you suggest they formally and informally review and stress test financial
models?
• what model review and audit process should be in place, if any?
• other improvements that were overlooked in the real-world example you have
identified.
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 6 © Kaplan Higher Education

JP Morgan Chase ‘London Whale’case study summary
In 2012 an estimate trading loss of at least US$2 billion was incurred based on
transactions booked through JP Morgan’s London branch.Derivative transactions
involving credit default swaps were entered as part of a ‘hedging’ strategy.This led to
a number of investigations examining risk management systems and internal
controls.It is considered this came about through a cumulative result of many
contributing factors, not limited to:
• absence of regulatory and corporate oversight
• ineffective internal risk management: reporting line and disclosure deficiencies
• pricing deficiencies
• inexperienced and unqualified personnel
• conflicts of interest
• approval and implementation of the synthetic credit Value at Risk (VaR) Model was
inadequate:
– VaR model was the first built by personnel who had never before built or
implemented a VaR model
– VaR model built was less conservative than the existing model VaR, which was
already outside of the VaR limits set for the portfolio
– manual updates were required in Excel spreadsheets
– formula errors in calculations
– lack of fully independent review
– model implemented under pressure, without resolution of issues identified during
a probationary review period.
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 7 © Kaplan Higher Education

Criteria-based marking guide for Question 1: Written content
Exemplary
(Range: 20.5–25
marks)
Above
expectations
(Range: 15.5–20
marks)
Meets
expectations
(Range: 10.5–15
marks)
Below
expectations
(Range: 5.5–10
marks)
Well below
expectations
(Range: 0–5 marks)
• demonstrates a
balanced and very
high level of
detailed
knowledge of core
concepts by
providing a high
level of analysis
• student provides
significant
insights into the
issues
• quality of writing
at a very high
standard (i.e. use
of a clear and well
thought-out
introduction,
the ideas are
arranged in an
extremely logical,
structured and
coherent manner,
a clear and
concise
concluding
summary of the
area of interest is
provided)
• correct grammar,
spelling and
punctuation
• demonstrates a
balanced and
high level of
knowledge of
core concepts by
providing a high
level of analysis
• student provides
good insight into
the issues
• quality of writing
is of a high
standard (i.e. a
mostly well
thought-out
introduction,
the ideas are
arranged in a
fairly logical,
structured and
coherent manner
and a mostly
clear and concise
concluding
summary of the
area of interest is
provided)
• few grammar,
spelling and
punctuation
mistakes
• demonstrates a
good level of
knowledge of
some of the core
concepts by
providing some
level of analysis
• quality of writing
is of a good
standard (i.e. an
introduction is
provided,
however, some
areas are
omitted, the ideas
are arranged in a
logical somewhat
structured and
coherent manner,
a mostly clear
and concise
concluding
summary of the
area of interest is
provided)
• few grammar,
spelling and
punctuation
mistakes
• demonstrates
limited knowledge
of core concepts
by providing a
limited level of
analysis
• student provides
few insights into
the issues
• some problems
with sentence
structure and
presentation
(i.e. an
introduction is
included, however
brief and at times
irrelevant to the
subject matter,
ideas are lacking
a logical structure
and are presented
in a fairly
incoherent
manner,
a summary is
provided although
lacks detail and/or
reflects
information
irrelevant to the
actual document)
• many grammar,
punctuation and
spelling mistakes
• demonstrates little,
if any, knowledge of
the core concepts
with extremely
limited, if any,
analysis
• student provides
misinformed/misgui
ded insight into the
core concepts
• quality of writing is
at a very poor
standard and barely
understandable
(i.e. limited or no
introduction
included, there is
little, if any,
coherent structure
to the answer, the
answer appears
very confusing,
a concluding
summary does not
address the main
areas of focus or is
not included)
• many grammar,
punctuation and
spelling mistakes,
little or no evidence
of proofreading
For office use only
#
1 2 2 3a 3a 3b 3b
Written
content
Calculation
s
Spreadshee
t structure
Calculation
s and
summary Charting
Calculation
s
Spreadshee
t structure
Max 25 5 10 7 3 5 10
Awarded
#
3c 4 4 5a 5b
TotalWritten
content
Calculation
s
Spreadshee
t structure Charting
Written
content
Max 3 8 10 8 6
** Expression is faulty
**
Awarded
** Expression is faulty
**
10043847_FIN280_AS2_v6 8 © Kaplan Higher Education