FIN205 Data Technologies for Finance Modelling
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIN205
Timed Online Assignment – January Semester 2022
Data Technologies for Finance Modelling
INSTRUCTIONS TO STUDENTS:
1. This Timed Online Assignment (TOA) contains FOUR (4) questions and comprises SIX (6) pages (including cover page).
2. You must answer ALL questions.
3. If you have any queries about a question, or believe there is an error in the question, briefly explain your understanding and assumptions about that question before attempting it.
4. You MUST submit your answers via Canvas (similar to TMA submission) at the end time of this TOA (as stated on this cover page). The 15 minutes grace period as shown on Canvas is strictly meant for technical issues encountered during submission. Thereafter, you will not be able to submit your answers and you will be considered as having withdrawn from the course. No appeal will be allowed.
5. Your submission should consist of only one file and must not exceed 500MB in size. The file must be a Microsoft Word file saved in .docx format. All answers are to be typed. Flowcharts and graphs may be scanned or photographed and embedded in the Word file provided it does not exceed the file size limit of 500MB. Images of handwritten answers will not be marked.
6. To prevent plagiarism and collusion, your submission will be reviewed by Turnitin. The Turnitin report will only be made available to the marker and you will not be able to view it.
7. The University takes plagiarism and collusion seriously, and your Turnitin report will be examined thoroughly as part of the marking process.
You must answer ALL the questions. (Total 100 marks)
(1) Unless specified, allfuture cashflows are at end of period.
(2) For some questions, the relevant Microsoft Excelspreadsheet student template (FIN205 TOA Jan22 Student Template.xslx) is provided. You may download this template from your respective TOA tutorial group Canvas course site “FIN205_JAN22_TXXTOA” > Module > FIN205 TOA Jan22 Student Template > FIN205 TOA Jan22 Student Template.xslx.
(3) If you are asked to show how you use the pre-built-function/user-formula in Microsoft Excel spreadsheet, please insert the screenshot that captures all relevant information needed to prove the correctness of your pre-built- function/user-formula usage. This includes, but not limited to, the row and column headings, and the cell(s) containing input(s) to the pre-built- function/user-formula used.
Question 1
You are asked to compute the intrinsic price of a Stock. A simplified data sheet is given:
Risk-free rate (annualized) = 3%
Expected Return of Equity Market = 8%
Beta of the Stock = 1.3
Dividend declared for this year = $5
(a) Solve for the Expected Return of the Stock, round off the answer to two decimal
places. List the financial model you have chosen. ( 10 marks)
You come up with two possible scenarios using the Expected Return of the Stock as the Required Rate of Return for the Stock.
Scenario 1: Assume zero growth in future dividends
Scenario 2: Assume constant growth in future dividends.
The suggested growth rate is 2%.
(b) Solve for the Intrinsic Value of the Stock for Scenario 1, round off the answer to
two decimal places. (5 marks)
(c) Solve for the Intrinsic Value of the Stock for Scenario 2, round off the answer to zero decimal place. (5 marks)
(d) Discuss a case in which both calculated intrinsic values fail to be realised. (5 marks)
Question 2
In financial accounting, the Cash Conversion Cycle measures the duration a firm will be deprived of cash if it increases its investment in inventory to improve customer sales. You are tasked to investigate the following case. (Note: 1 year has 365 days.)
Income Statement (All numbers in '000) |
||
Sales Revenue COGS Gross Profit Operating Expenses Operating Income Other Incomes Other Expenses EBIT Interest Expenses EBT Tax Expenses at 24% EAT Dividend declared Additional to RE |
2020 |
2019 |
$550,000 |
$514,405 |
|
($350,000) |
($385,301) |
|
$200,000 |
$129,104 |
|
($108,791) |
($107,147) |
|
$91,209 |
$21,957 |
|
$2,147 |
$0 |
|
$0 |
($8,151) |
|
$93,356 |
$13,806 |
|
($2,599) |
($2,346) |
|
$90,757 |
$11,460 |
|
($21,782) |
($2,750) |
|
$68,975 |
$8,710 |
|
($67,057) |
|
|
$1,918 |
|
(a) Solve for the relevant “Averages” to prepare for Cash Conversion Cycle
calculations, round off all answers to zero decimal place. (3 marks)
(b) Solve for the Days of Sales Outstanding, Days of Inventory on Hand and Number
of Days Payables, round off all answers to zero decimal place. Show the Excel formula that is used.
(c) Solve for the Cash Conversion Cycle in days, round decimal place. Show the Excel formula that is used. (2 marks)
(d) Suggest four (4) ways to improve the Cash Conversion Cycle. (8 marks)
Question 3
A client has four financial instruments to identify and evaluate. The following figure shows the numerical details. He lost the document that described their classification and
prices. He engages your consultation service to identify the financial asset class/instrument category that these financial instruments belong to, their characteristics
and ultimately calculate their prices in SGD (Singapore Dollar).
Currency |
SGD |
EUR |
SGD |
USD |
Yearly Cost of Borrowing (Annualised) |
4% |
3% |
4% |
2% |
Yield Curves are flat for all countries |
Years |
Financial Instrument 1 |
Financial Instrument 2 |
Financial Instrument 3 |
Financial Instrument 4 |
1 2 3 4 5 |
$0 $0 $0 $0 $100,000 |
$1,900 $1,500 $2,100 $1,800 $102,000 |
$2,500 $2,500 $2,500 $2,500 $102,500 |
$0 $0 $0 $0 $103,000 |
EUR |
The Euro |
USD |
United States Dollar |
SGD |
Singapore Dollar |
Spot prices (mid)
EUR/USD |
1.14 |
USD/SGD |
1.35 |
Notation
USD/SGD is |
in SGD per USD |
(a) Indicate, for the the above-mentioned instruments, whether each instrument is a
Floating Rate Note (FRN), Fixed Deposit (FD), Fixed Rate Bond (FRB) or Zero- Coupon Bond (ZCB), with suitable reasons. (12 marks)
(b) Solve for the mid-spot price of EUR/SGD (in SGD per EUR). Round off to two
decimal places. (3 marks)
(c) Solve for the current (spot) prices of each financial instrument in their respective currency using the relevant Microsoft Excel function. Show the Excel functions involved. All answers should be expressed in the original currency and round off to two decimal places. (8 marks)
(d) Convert the current (spot) price of financial instrument in EUR (namely, the financial instrument 2) to SGD and round off to two decimal places. (2 marks)
Question 4
You are asked to investigate and explore financial derivatives and their valuation.
(a) List three (3) differences between a futures and a forward contract. (12 marks)
You receive the beginning and the ending portions of a Python program. The purpose of the program is to price a financial derivative via Monte Carlo Simulation. You are asked to provide further elaborations for this Python program.
(b) Identify this financial derivative. Explain and support your view with two (2)
reasons. (7 marks)
(c) Solve for the break-even point of this financial derivative on expiry date, round off the answer to two decimal places. (4 marks)
(d) Illustrate an approach to get a more precise answer for the computation. (2 marks)
2022-11-12