Please help with questions on page 14 assignment 01 - urgently need help tonight

DSC2604/101/3/2016 Tutorial letter 101/3/2016 Financial Modelling DSC2604 Semesters 1 & 2 Department of Decision Sciences IMPORTANT INFORMATION: This tutorial letter contains important information about your module. 2 CONTENTS DSC2604/101 Page 1. INTRODUCTION AND WELCOME 3 2. PURPOSE AND OUTCOMES OF THE MODULE IN FINANCIAL MODELLING 3 3. LECTURER AND CONTACT DETAILS 3.1 Lecturer 3.2 Department 4 4 4 4. MODULE RELATED RESOURCES 4.1 Prescribed book/Study Guide 4.2 Additional Study Material 4 5 5 5. STUDENT SUPPORT SERVICES FOR THE MODULE 5 6. MODULE SPECIFIC STUDY PLAN 5 7. ASSESSMENT 7.1 Compulsory Assignments 7.2 Evaluation exercises and supplementary exercises 7.3 Examination 6 6 6 7 8. EXAMINATION INFORMATION 7 List of Formulae 9. COMPULSORY ASSIGNMENTS 9.1 Semester 1 Assignment 01 9.2 Semester 1 Assignment 02 9.3 Semester 2 Assignment 01 9.4 Semester 2 Assignment 02 8 9 10 12 14 16 3 DSC2604/101 1. INTRODUCTION AND WELCOME Dear Student It is a pleasure to welcome you as a student to FINANCIAL MODELLING. We trust that you will find this module interesting and stimulating. The module can be completed during the first OR second semester of 2016. You only have to register for one of the two semesters. It is thus not necessary to re-register for the module if you completed the module successfully in the first semester. You may, however, repeat the module in the second semester if you failed it in the first semester. In such a case, you MUST re-register for the second semester. It is essential that you read this tutorial letter and Tutorial Letter 301, 2016 very carefully. Tutorial Letter 101 for 2016 (this tutorial letter) contains information about this particular module, the compulsory assignments and also supplementary exercises and their solutions. Tutorial Letter 301, 2016 contains general information relevant to all undergraduate students in the Department of Decision Sciences. Please register on myUnisa (http://my.unisa.ac.za) and make sure you obtain an Unisa e-mail address. 2. PURPOSE AND OUTCOMES OF THE MODULE IN FINANCIAL MODELLING The purpose of the module is to familiarise you, the learner, with the essential approaches and concepts of decision making in a financial environment. You should be able to: use the systems approach to describe and give structure to problems of a financial nature; understand the basic concepts and definitions of the time value of money and apply them in decision making; understand the capital budgeting process and apply the basic concepts of time value of money and the systems approach under conditions of certainty; understand the capital budgeting process and apply the basic concepts of time value of money and the systems approach under conditions of uncertainty; analyse securities in terms of risk and return and select an appropriate investment portfolio. 3. LECTURER AND CONTACT DETAILS 3.1 Lecturer The lecturer will assist you if you experience any difficulties with the study material. Lecturers will be available at specified times during the week. Please contact the lecturer to make the necessary appointment. 4 DSC2604/101 Information on your lecturers will be published in Tutorial Letter 301, 2016. Please consult Tutorial Letter 301, 2016 immediately and write down your lecturer's information in the box below. If you need to contact Unisa about any administrative matters, please consult the brochure, My studies @ Unisa.Always use your student number when you contact the University. 3.2 Department Department of Decision Sciences Tel: +27 12 433 4684 Fax: +27 86 232 9697 E-mail: qm@unisa.ac.za 4. MODULE RELATED RESOURCES 4.1 Prescribed book/Study Guide There is no prescribed textbook for this module. The Department of Despatch should supply you with the following tutorial matter for this unit of study. The letters will also be available on myUnisa. one Study Guide Tutorial letter 101, which contains the compulsory assignments. Tutorial letters 201 and 202 will be sent to you later in the semester. (These letters contain the solutions of the compulsory assignments.) Tutorial letter 301 which contains general information. 4.2 Additional Study Material You will also find additional study material on myUnisa, under Additional Resources. This material will include comments, some supplementary exercises, and an example of an examination paper with complete solutions. 5 5. DSC2604/101 STUDENT SUPPORT SERVICES FOR THE MODULE You can consult the brochure, My studies@ Unisa, for details on student support services rendered. The lecturer can assist you with any enquiries related to the study material, the assignment questions and the solutions to them. However, note that neither the Department of Decision Sciences nor School of Economic Sciences will be able to answer your general assignment enquiries. If you wish to find out whether your assignment has been received, registered, processed or returned, please check on myUnisa at https://my.unisa.ac.za. For detailed information on and requirements for assignments, see the brochure My studies @ Unisa. 6. MODULE SPECIFIC STUDY PLAN In view of the fact that your study time is limited, you will have to plan carefully. Divide your study material into sections. Assign a completion time, for example every second week, to each section. The completion time is the time by which you should have worked through a certain section of the study material you should have completed the evaluation exercises at the end of the chapter you should have worked through the supplementary exercise relevant to the study material any enquiries concerning the particular section of the study material should have been ironed out with the lecturers. To help you complete the compulsory assignments with success, we suggest you do the chapters in the Study Guide in the following order: Appendix A and Chapters 1,2 and 3 first, then Chapters 5 and 6 and Appendix B, and finally Chapter 4. DSC2604/101 6 7. ASSESSMENT Assessment consists of compulsory assignments, self-evaluation exercises, and an examination. 7.1 Compulsory Assignment 01 and Assignment 02 For students to fully benefit from our formative tuition and assessment, the Management of the University decided to introduce compulsory assignments in all modules. Assignment 01 contributes 50% towards the semester mark. Assignment 02 contributes 50% towards the semester mark. Together the two compulsory assignments make up 20% of your final mark. You must hand in both compulsory assignments before the due dates to build up your semester mark. Late submissions will not be marked. Make sure that you do the correct assignment for the correct semester! Please ensure that the assignments reach the University before the due dates - late submission of the assignments will result in you not being admitted to the examination. Note that neither the Department nor the School of Economic Sciences will be able to confirm whether the University has received your assignment or not. Please use the following e-mail for all assignment enquiries: E-mail: studyinfo@unisa.ac.za You must send in the relevant spreadsheet pages of the assignments according to the procedures as explained in the Unisa guide: My studies @ Unisa. This means: electronically through myUnisa, or per post in the official envelopes supplied by Unisa. DO NOT send assignments to lecturers or to the Department of Decision Sciences. Due Dates and Unique numbers: Period Semester 1 Semester 2 Assignment no 01 02 01 02 Due date 18 March 18 April 2 September 7 October Unique number 732411 715809 845081 587302 The assignment questions are given in section 9 of this tutorial letter. 7.2 Evaluation exercises and supplementary exercises The duration of a module is four months (15 weeks). In this short period you have to work through your study material and prepare yourself for the exams. To help you in your preparations we provide you with extra exercises (called evaluation exercises), and solutions at the end of chapters in the Study Guide, and also supplementary exercises in myUnisa, under Additional Resources. These exercises are important for the following reasons: Exercises assist you in understanding and mastering the study material and its practical implications. They are therefore an integral part of the study material. 7 DSC2604/101 Exercises will force you to work consistently throughout the semester (an essential ingredient for studying this subject successfully). Exercises test your knowledge and understanding of the study material, and enable you to evaluate your progress. 7.3 Examination 8. Only students that have submitted Assignment 01 will be admitted to the examination. Assignment mark = Assignment 01 (50%) + Assignment 02 (50%). The examination will be written during May/June and October/November, depending on the semester in which you are registered. Only your calculator (and pens, of course) may be taken into the examination hall. The examination constitutes 80% of your final mark. So: Final mark = Examination (80%) + Assignments (20%). EXAMINATION INFORMATION To prepare for the examination, you must study and master the study material, and assignments. Please note: You will not be examined on Excel and VBA. You must, however, be able to use a calculator (financial, ordinary or programmable) in the examination. Please note the following: Make sure of the exact date, time and venue for the examination. The duration of the examination is two hours. In order to pass, you need to obtain at least 50%. You must answer all the questions. Read the questions very carefully before you answer them. Justify your answers, and give reasons for your arguments. You must bring along a pocket calculator, programmable or ordinary. Remember that you must be able to use your calculator instead of Excel to calculate certain values, for example, NPV, statistical values, etc. Do not panic if you are unable to answer a question continue with the next question. N.B: The following formulae will be provided as part of the examination paper. It is, however, your responsibility to understand how and where the formulas can be used. Not all formulas that are provided may be necessary to answer the exam paper and the list does not necessarily represent the only formulae that you will use in the exam - you must know all relevant formulae. DSC2604/101 8 FORMULAE 1 ARR = N CF t t E ( Rq= ) Rf + I CF N = t =1 (1 + IRR)t I 0 m i = 1 + 1 100 m FV = C (1 + i) n t NPV = CFt I (1 + i )t I Ct for Ct I Ct +1 PB =t + Ct +1 Ct E ( Ri ) = bi E ( Rm ) + ai = X i i pi X i 2 i varx vary N t =1 Ct I (1 + i )t 1 r PB CFt (1 + i )t PI when CFt 0 for all t > 0 = I R= bi Rmt + ai + eei it FV (1 + i ) n R= p R1 + (1 p ) R2 p (X NPV1 (r2 r1 ) NPV1 NPV2 N r =(1 + Rr )(1 + I r ) 1 PV = 2 (X ) = q FV = PV (1 + i ) n t NPV = t m cov X ,Y standard deviation of X X ,Y = E( X ) CV ( X ) I eff IRR r1 + E ( Rm ) R f X ) pi t bi2 m2 + ei2 i2 = 2 ( R p ) p 2 2 ( R1 ) + (1 p ) 2 2 ( R2 ) + 2 p (1 p ) cov( R1 , R2 ) = 2 ( Rp ) = x + 2 i 2 i N 1 =i 1 N = j i 1 2 xi x j cov( Ri , R j ) 9 DSC2604/101 9. COMPULSORY ASSIGNMENTS The compulsory assignments also test your knowledge of the use of Excel and Visual Basic Applications (VBA) in doing spreadsheet modelling of financial processes. Appendices A and B of the Study Guide provide an introduction to Excel and VBA. It is the student's responsibility to obtain and master the basics of a spreadsheet package such as Excel. You can also obtain a textbook from the library that will help you to master VBA (for instance, Advanced Modelling in Finance Using Excel and VBA, co-authored by Mary Jackson and Mike Staunton may be useful, and many others.) If you have Excel version 2003, simply follow the instructions in the Study Guide. Remember the Help button if you are stuck. If you have Excel version 2007, then please make sure of the following: Go to Excel and click on the round Microsoft button top left. Click Excel Options at the bottom, Click Add-ins and in the Manage box, select Excel Add-ins. Click Go. In the Add-ins Available box, select Analysis ToolPak box, also Analysis ToolPak - VBA box, also Solver Add-in box. Click OK. In Excel 2007 (under the title Book 1) you should now see the following tabs at the top: Home; Insert; Page Layout; Formulas; Data; Review; View; Developer. The Insert tab contains charts; the Data tab contains Data Analysis and Solver; and the Developer tab contains Visual Basic. If you cannot see the Developer tab, again click on the round Microsoft button top left. Click Excel Options at the bottom, click Popular and then select Show Developer Tab in the Ribbon. The Developer tab should now show at the top in the list of tabs. You can now follow the instructions in the Study Guide with minor adaptations. Remember the Help button! If you do not have Excel you can download the free software OpenOffice from http://www.openoffice.org. It is then your responsibility to learn to use it. 10 DSC2604/101 SEMESTER 1 Assignment 01 (Compulsory) Due date: Unique number: Marks: 18 March 2016. 732411 50 Question 1 [10] Use your pocket calculator (financial, ordinary or programmable) to answer this question. You have R80 000 to invest for six years. There are two investment options: A and B. Investment A is a six-year investment which promises a monthly compounded interest rate of 13% per annum. The total value of investment is paid out at the end of year six and there are no annual cash inflows at the end of years one to five. Investment B is a bank account which gives the investor an annual cash inflow of interest at a quarterly compounded interest rate of 15% per annum. The annual interest is paid out at the end of each of the six years and recalculated only on the initial (original) capital for each year. The initial capital is paid back at maturity, together with the last interest payment. Assume that the discount rate for the period is 9% per annum. 1.1 For each investment, draw the time-line which indicates their cash flows. (2) 1.2 Calculate the net present value (NPV), profitability index (PI) and payback period (PB) for the two investments. (6) 1.3 Which investment would you prefer? Give your answers based on the financial indicators (variables) calculated in 1.2. (2) 11 DSC2604/101 Question 2 [40] Use your pocket calculator (financial, ordinary or programmable) OR Excel to answer this question. If you use Excel (or any other suitable spreadsheet package), then send in the printouts of the relevant spreadsheet pages as explained in My studies @ Unisa. Consider two projects with cash flows as shown in the table below: Year 0 1 2 3 4 5 Project 1 R1 000 000 R 300 000 R 300 000 R 300 000 R 300 000 R 300 000 Project 2 R1 450 000 R 420 000 R 420 000 R 420 000 R 420 000 R 420 000 2.1 Determine the internal rates of return (IRRs) of the two projects. (4) 2.2 Determine the net present value (NPV) for each project at a discount rate of 8% per annum. (4) 2.3 Determine the profitability index (PI) of each project. (2) 2.4 Compute the payback period (PB) of each project. Include the table which has the necessary columns. (4) 2.5 Compute the discounted PB, at a discount rate of 8% per annum, for each project. Include the table which has the necessary columns. (4) 2.6 Which quantities (IRR, NPV, PI, PB) will you use to make a recommendation as to which project is the best investment? Which quantity will not lead to a good decision? Explain your answer. (3) 2.7 Which investment will you recommend? Justify your answer. (2) 2.8 Assume that the useful life of the project is at least 10 years. Find the approximate rates of return on the projects. (2) 2.9 Suppose that the possible discount rates are 3%, 4%, 5%, 6%, 7%, 8%, 9%, 10% and 11%. Do the sensitivity analysis of the NPV of each project. Represent the NPVs of the two projects against the discount rates on a graph. Discuss your findings. (15) DSC2604/101 12 SEMESTER 1 Assignment 02 (Compulsory) Due date: Unique number: Marks: 18 April 2016 715809 50 Do the following in VBA and Excel and send in the printouts of the relevant spreadsheet pages as explained in the Unisa guide, My studies @ Unisa. The following numbers are the share prices (in rand) of eight traded shares S1, S2, S3, S4, S5, S6, S7and S8 collected daily at the close of the trading day for 21 days in January 2016. Day S1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 S2 22.4 20.7 24.45 23.9 25 24 25.85 28.5 28 29.3 27.6 32.55 34.9 30.45 31.75 28.5 29.9 28 35.4 38 45.75 S3 6 6.1 6.4 6.9 8 10.4 11.9 12.6 13.7 11.9 13.5 14.4 16.8 21.5 20 19.75 20.25 19 20.75 24.75 27.05 S4 23 22.65 23.85 23.05 25 26 24 22.4 25.5 26.5 24.3 27 27.75 25.25 24.5 20.75 20.1 15.2 15 16.5 18.8 S5 1.3 1.34 1.3 1 1 0.9 0.8 0.8 0.81 0.95 1.04 1.5 1.16 1.1 0.96 0.8 0.8 0.65 0.75 0.7 0.64 S6 13.1 12.3 14.4 14.3 15.1 14 15.9 16.5 18.5 23.5 28 31 35 33 34 33 38.3 39.75 45 43 45 S7 16 14 15 16 14.9 16 15.25 16.6 16.55 16.5 15.5 14.9 14.3 16.35 12.6 12.9 12.8 13.1 12.25 12 13.5 S8 7 6.3 5.88 5.78 4.7 4.6 4.9 5.5 6.2 7.1 8.2 9.1 13.5 11.6 12.7 11.5 12.1 12.8 12.78 12.3 13.2 7.75 6.75 6.45 6.7 5.55 5.15 5.65 5.6 7.1 7.7 7.85 8.9 10.8 9.45 9.45 8.5 8.5 9.5 8.25 9 10.9 S t +1 S t . The period from t to t+1 is one business (trading) day. St Therefore, it is the daily return. The expected return and variance of return of the portfolio consisting of n stocks are given as follows: The simple return on a share S is: Portfolio expected daily return:= E[rp ] n xi E[ri ] T = i =1 (See Additional Resources for Matrix Algebra Review.) (1) DSC2604/101 13 Portfolio variance: 2 var(rp= ) = p = n n x x =i 1 =j 1 n i j covar(ri , rj ) n = xx =i 1 =j 1 i j i, j (2) C T Note that i2 = ii is the variance of ri E[ri ] denotes the expected daily return for the ith share. i is the (1-day) risk (standard deviation of daily returns) for the ith share. Cis the variance-covariance matrix of the returns of shares.In particular, Cis given by 11 12 21 22 = C [= i, j n1 n 2 x1 x2 X = = and M xn 1n 2 n 1n E[r1 ] E[r2 ] , where x ' s are the weights of the shares in the portfolio. i E[rn ] Formulas (1) and (2) can easily be implemented with Excel array functions: Portfolio return: X T M = SUMPRODUCT(X, M) T = MMULT (TRANSPOSE (X), MMULT(C, X)) Portfolio variance: X CX Question 1 [40] On a separate sheet (VBA Module), write a VBA program that computes the following: 1.1 The daily returns, expected daily return and daily standard deviation of return of each share. (20) 1.2 The variance-covariance matrix of the daily returns. (20) Question 2 [10] In an Excel sheet, use the results obtained in Question 1 to compute the expected return and standard deviation of return of the portfolio. Assume an equally weighted average portfolio, i.e. xi= 1/8 for each i. 14 DSC2604/101 SEMESTER 2 Assignment 01 (Compulsory) Due date: Unique number: Marks: 2 September 2016 845081 50 Question 1 [10] Use your pocket calculator (financial, ordinary or programmable) to answer this question. You have R130 000 to invest for six years. There are two investment options: A and B. Investment A is a six-year investment which promises a monthly compounded interest rate of 10% per annum. The total value of investment is paid out at the end of year six and there are no annual cash inflows at the end of years one to five. Investment B is a bank account which gives the investor an annual cash inflow of interest at a quarterly compounded interest rate of 12% per annum. The annual interest is paid out at the end of each of the six years and recalculated only on the initial (original) capital for each year. The initial capital is paid back at maturity, together with the last interest payment. Assume that the discount rate for the period is 11% per annum. 1.1 For each investment, draw the time-line which indicates their cash flows. (2) 1.2 Calculate the net present value (NPV), profitability index (PI) and payback period (PB) for the two investments. (6) 1.3 Which investment would you prefer? Give your answers based on the financial indicators (variables) calculated in 1.2. (2) DSC2604/101 15 Question 2 [40] Use your pocket calculator (financial, ordinary or programmable) OR Excel to answer this question. If you use Excel (or any other suitable spreadsheet package), then send in the printouts of the relevant spreadsheet pages as explained in My studies @ Unisa. Consider the two projects whose cash flows are shown in the table below: Year Project 1 Project 2 0 -R13 000 -R17 000 1 R4 000 R5 000 2 R4 000 R5 000 3 R4 000 R5 000 4 R4 000 R5 000 5 R4 000 R5 000 2.1 Determine the internal rates of return (IRRs) of the two projects. (4) 2.2 Determine the net present value (NPV) for each project at a discount rate of 8% per annum. (4) 2.3 Determine the profitability index (PI) of each project. (2) 2.4 Compute the payback period (PB) of each project. Include the table which has the necessary columns. (4) 2.5 Compute the discounted PB, at a discount rate of 8% per annum, for each project. Include the table which has the necessary columns. (4) 2.6 Which quantities (IRR, NPV, PI, PB) will you use to make a recommendation as to which project is the best investment? Which quantity will not lead to a good decision? Explain your answer. (3) 2.7 Which investment will you recommend? Justify your answer. (2) 2.8 Assume that the useful life of the project is at least 10 years. Find the approximate rates of return on the projects. (2) 2.9 Suppose that the possible discount rates are 3%, 4%, 5%, 6%, 7%, 8%, 9%, 10% and 11%. Do the sensitivity analysis of the NPV of each project. Represent the NPVs of the two projects against the discount rates on a graph. Discuss your findings. (15) DSC2604/101 16 SEMESTER 2 Assignment 02 (Compulsory) Due date: Unique number: Marks: 7 October 2016 587302 50 Do the following in VBA and Excel and send in the printouts of the relevant spreadsheet pages as explained in the Unisa guide, My studies @ Unisa. The following numbers are the share prices (in rand) of eight shares S1, S2, S3, S4, S5, S6, S7and S8 collected daily at the close of the trading day for 21 days in September 2016. Day S1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 S2 3 2.65 2.8 3.3 3.58 3.3 3.2 3.2 3.45 3.45 3.7 4.4 5 4.4 4.38 4.4 3.88 4.4 4.5 4.16 5.08 S3 10.3 11.5 12.3 13 13 13.1 12.65 12.2 12.45 13.9 15.3 17.5 17.8 17 18 16 15.9 17 16.9 17.25 18.7 S4 39.75 38.5 35 33.5 32.25 32.5 31 35.25 36.5 37 38 46 51 45.25 45 50.5 47.4 44.4 45.6 45.4 47 S5 5.5 5.2 5.45 5.5 5 4.8 4.8 4.8 5.3 5.8 6.1 6.1 5.9 5.5 5.15 5.2 4.65 4.9 4.75 4.8 4.85 S6 45 47.5 49.25 50.25 52 54 52 54 53 59.5 65 63 69 67.5 62 59.75 59.75 60.75 55.5 56 64 S7 6 6.85 6.85 6.75 6.5 6.75 6.5 7 7.3 7.7 7.85 8.35 12 12.5 13 14 14 14.2 14.5 17 17.5 S8 41.75 39 43.25 45 41.5 40.75 44.75 46 45 50.5 55.5 62 66 63 58.75 59.75 54.25 55.5 52 53.75 56.25 4.75 4.65 4.69 4.75 4.38 4.75 4 3.75 3.75 3.63 3.5 3.13 4.44 4.13 3.75 4.13 5.25 6.03 6.75 6.88 6.8 DSC2604/101 17 The simple return on a share Sis: S t +1 S t . The period from t to t+1 is one business (trading) day. St Therefore, it is the daily return. The expected return and variance of return of the portfolio consisting of n stocks are given as follows: n xi E[ri ] T = Portfolio expected daily return:= E[rp ] (1) i =1 (See Additional Resources for Matrix Algebra Review.) Portfolio variance: 2 var(rp= ) = p = n n x x =i 1 =j 1 n i j covar(ri , rj ) n = xx =i 1 =j 1 i j i, j (2) C T Note that i2 = ii is the variance of ri E[ri ] denotes the expected daily return for the ith share. i is the (1-day) risk (standard deviation of daily returns) for the ith share. Cis the variance-covariance matrix of the returns of shares.In particular, Cis given by 11 12 21 22 = C [= i, j ] n1 n 2 x1 x2 X = = and M xn 1n 2 n 1n E[r1 ] E[r2 ] , where x ' s are the weights of the shares in the portfolio. i E[rn ] Formulas (1) and (2) can easily be implemented with Excel array functions: Portfolio return: X T M = SUMPRODUCT(X, M) T Portfolio variance: X CX = MMULT (TRANSPOSE (X), MMULT(C, X)) 18 DSC2604/101 Question 1 [40] On a separate sheet (VBA Module), write a VBA program that computes the following: 1.1 Daily returns, expected daily return and daily standard deviation of return of each share. (20) 1.2 Variance-covariance matrix of the daily returns. (20) Question 2 [10] In an Excel sheet, use the results obtained in Question 1 to compute the expected return and standard deviation of return of the portfolio. Assume an equally weighted average portfolio, i.e. xi= 1/8 for each i. Unisa 2016