Financial Modelling

Order Details;

1 Module Title: Financial Modelling Module code: NBS-MC76 Credit value: 20 Academic Year: 2014/2015 Semester: 2 INDIVIDUAL ASSIGNMENT BRIEF Title: Financial Modelling Coursework Assessment Objective: This assignment asks you to apply theory and concepts learned in the course to a variety of theoretical and practical problems that are likely to arise in a real world setting. Data: You will draw the necessary data for this coursework from Yahoo! UK & Ireland Finance and such other sources as you find it necessary to use. Description: All the relevant tables and figures should be contained and discussed in the main body of your coursework assignment. The coursework assignment should not exceed 3,000 words in total (excluding figures and diagrams) and should include all of the following five sections: Assessment Type Word Length Percentage (%) counting towards overall module mark Date Assessment commences Assignment Deadline Return date and nature of feedback |Individual Coursework 3,000 words (excluding figures and diagrams) 100% 26/01/2015 20/04/2015 Feedback sheet comments will be returned by 18/05/2015 2 Section 1 (Capital Budgeting) Mr. Colt, Finance Director of the Coventry Car Company, informs you that he has the opportunity of purchasing a machine costing £7,000 which will earn the following sequence of net cash flows over the coming five years: Year Net Cash Flow 1 £3,100 2 2,400 3 1,900 4 1,500 5 1,100 Total £10,000 The scrap (salvage) value of the machine at the end of each of the five years is estimated to be the following: Year Estimated Scrap Value 1 £5,000 2 4,000 3 3,000 4 1,000 5 0 The Coventry Car Company uses a 16% (per annum) discount rate and you may assume that the company does not pay taxation. Required: (a) Assuming the machine is used for five years: (i) Compute the net present value of the above sequence of cash flows using appropriate functions in the EXCEL spreadsheet package. (ii) Compute the time adjusted (internal) rate of return using appropriate functions in the EXCEL spreadsheet package. (iii) Compute the payback period for Mr. Colt’s proposed machine. (6 marks) (b) If Mr. Colt wants to maximise the net present value of the machine, for how long should the machine be held and explain in detail why? (3 marks) 3 (c) Discuss the advantages and disadvantages of the different methods which may be used to value the capital project as in part a) of this section. (3 marks) (d) Suggest two other methods for evaluating the viability or otherwise of the above capital project. Explain each method fully. (3 marks) NOTE: Marks will be allocated for the presentation of the EXCEL spreadsheet as well as for the correct use of EXCEL functions. (15 marks in total) Section 2 (Financial data analysis and the use of EXCEL functions)) (i) Suppose a British exporter is expecting to receive W = $US160,000 in United States dollars in three months’ time. The current exchange rate is $US1 = £0.625 = S0 . However, the exchange rate in three months’ time, S3 , is a random variable with a mean of $US1 = £0.65 = E(S3 ) and a variance of 2 = 1 25 . (ii) The three months’ forward exchange rate is $US1 = £0.60 = F3 . This means the British exporter could agree now (that is, immediately) to receive WF3 = 160,0000.6 = £96,000 in three months’ time for their W = $US160,000 of US currency. There would then be no uncertainty associated with the amount of British currency the exporter would receive – that is, the British exporter would receive £96,000 in three months’ time with absolute certainty. (iii) However, the British exporter can also diversify; that is, sell part of the W = $US160,000 forward whilst bearing the foreign exchange risk on the rest. This means the British exporter will profit (lose) from any upward (downward) movements in the rate at which the US dollar can be converted into the British £ on the proportion of their W = $US160,000 which they choose not to sell forward. Thus, suppose the British exporter chooses to bear the foreign exchange risk on a proportion, x, of their W = $US160,000 and sells the remaining proportion, (1 – x), of their W = $US160,000 on the forward market. Then the expected export proceeds in three months’ time will be: (x) = W[xE(S3 ) + (1 – x)F3 ] = 160,000[0.65x + 0.6(1 – x)] = 160,000( 3 5 + x 20) Recall that the forward rate, F3 = £0.60, is known with certainty and so the variance of the export income proceeds depends only on the exchange rate, S3 , in three months’ time. Recall from (i) that S3 has a mean of E(S3 ) = £0.65 and a variance of Var(S3 ) = 2 = 1 25 . This will mean that the variance of the export proceeds to be received in three months’ will be: Var[xW.S3 + (1 – x)W.F3 ] = Var(xW.S3 ) = W2x 2Var(S3 ) = W2x 2 2 = 1600002x 2 25 (iv) The British exporter decides to determine the proportion, x, of the W = $US160,000 on which they will bear the foreign exchange risk, by maximising the following expression: [(x)] 2 – .W2x 2 2 where   0 is a parameter. 4 Required: (a) Explain in detail why the British exporter might determine the proportion, x, of the W = $US160,000 on which they will bear the foreign exchange risk, by maximising the expression [(x)] 2 – .W2x 2 2 . (4 marks) (b) Use the appropriate function on the EXCEL spreadsheet package to determine the amount on which the British exporter will choose to bear the foreign exchange risk when (i)  = 5, (ii)  = 10 and (iii)  = 20. (6 marks) NOTE: Marks will be allocated for the presentation of the EXCEL spreadsheet as well as for the correct use of EXCEL functions. . (10 marks in total) Section 3 (Monte Carlo Simulation) Define P(t) to be a stock’s price at time t in which case the continuously compounded return on the stock over the period from time (t – t) until time t will be: r(t) = log[P(t)] – log[P(t – t)] t where t is measured in units of a year. Thus, if the return on the stock is measured on a monthly basis then t = 1 12. During the period from time t until time (t + t) the return on the stock will either increase to: r(t + t) = r(t) + k 2 1 + k2 2 ( +  – r(t)) 2 . t or it will decline to: r(t + t) = r(t) – k 2 1 + k2 2 ( +  – r(t)) 2 . t where  is the expected long run return on the stock, k 2 1 and k 2 2 are strictly positive variance parameters and θ is a parameter that captures the skewness in the stock’s return. Furthermore, the probability of an increase in the stock’s return over the period from time t until time (t + t) is given by: 1 2 {1 + ( – r(t)) t k 2 1 + k2 2 ( +  – r(t)) 2 } where  is known as the speed of adjustment coefficient. This will mean that the probability of a decrease in the return on the stock is: 1 2 {1 – ( – r(t)) t k 2 1 + k2 2 ( +  – r(t)) 2 } 5 Required: (a) Write down an expression for the expected change in the return on the stock over the period from time t until time (t + t). That is, determine an expression for E[r(t)] = E[r(t + t) – r(t)]. (6 marks) (b) Write down an expression for the variance of the change in the return on the stock over the period from time t until time (t + t). That is, determine an expression for Var[r(t)] =Var[r(t + t) – r(t)]. (8 marks) (c) Consider a stock with the following parameter values:  = 0.00318505,  = 0.1,  = 0.6802, k 2 1 = 0.055 and k 2 2 = 0.00356. Use the RAND() function in the EXCEL spreadsheet package in conjunction with the expression for the probability of an increase in the stock’s return and the expression for the probability of a decrease in the stock’s return to prepare a spreadsheet that simulates the daily returns on the stock over a full year (comprising 365 daily returns – one return for each day of the year). The EXCEL spreadsheet is to contain a graph of the daily returns in the following minimal format: (6 marks) (d) Us the simulated returns for the stock determined in part (c) of this section to prepare a graph of the stock’s price over a full year (comprising 365 daily prices – one price for each day of the year). Your EXCEL spreadsheet
is to contain a graph of the daily stock prices in the following minimal format: -0.2000 -0.1500 -0.1000 -0.0500 0.0000 0.0500 0.1000 0.1500 0.2000 1163146617691 106 121 136 151 166 181 196 211 226 241 256 271 286 301 316 331 346 361 TIME (DAYS) RETURN 6 (4 marks) (e) Explain fully what it means to say that there is momentum in a given stock’s price. Explain how the returns generating model developed in this section could be used to model the momentum in a stock’s price. (6 marks) NOTE: Marks will be allocated for the presentation of the EXCEL spreadsheet as well as for the correct use of EXCEL functions. (30 marks in total) Section 4 (Regression Analysis) The Dimson (1979) technique for reducing the adverse effects of thin trading on the estimation of a stock’s beta is based on the following regression equation: Rjt = aj + bj1Rmt-2 + bj2Rmt-1 + bj3Rmt + bj4Rmt+1 + bj5Rmt+2 +  t where Rjt is the return on the j th stock over the tth time period, Rmt-2 is the return on the market index over the (t – 2) th time period, Rmt-1 is the return on the market index over the (t – 1) th time period, Rmt is the return on the market index over the tth time period, Rmt+1 is the return on the market index over the (t + 1) th time period and Rmt+2 is the return on the market index over the (t + 2) th time period. Thus if returns are measured on a daily basis, then Rjt is the return on the jth stock over the tth day, Rmt-2 is the return on the market index over the (t – 2) th day, Rmt-1 is the return on the market index over the (t – 1) th day, Rmt is the return on the market index over the tth day, Rmt+1 is the return on the market index over the (t + 1) th day and Rmt+2 is the return on the market index over the (t + 2) th day. Moreover, aj , bj1 , bj2 , bj3 , bj4 and bj5 are parameters to be estimated whilst  t is the stochastic error term for the tth day. Finally, the estimate of the jth stock’s beta, j , is given by: j =  k=1 5 bjk = bj1 + bj2 + bj3 + bj4 + bj5 9.80 9.90 10.00 10.10 10.20 10.30 10.40 1163146617691 106 121 136 151 166 181 196 211 226 241 256 271 286 301 316 331 346 361 TIME (DAYS) STOCK PRICE 7 Required: (a) Randomly choose five stocks from the FTSE100 Index on the Yahoo! UK & Ireland Finance website. Calculate the daily returns on each stock over the two-year period beginning 1 January 2013. Also calculate the daily returns on the FTSE100 Index over the two-year period beginning 1 January 2013. Use the Regression function in the EXCEL spreadsheet package to estimate the parameters j and j of the following Ordinary Least Squares (OLS) regression model for each of the five stocks: Rjt = j + jRmt + t where Rjt is the return on the jth stock over the tth day, Rmt is the return on the FTSE100 Index over the tth day, and t is the stochastic error term from the regression. (10 marks) (b) Use the Regression function in the EXCEL spreadsheet package to estimate the parameters aj , bj1 , bj2 , bj3 , bj4 and bj5 of the Dimson (1979) technique for each of the five stocks selected in part (a) of this section. Use your estimates of the parameters bj1 , bj2 , bj3 , bj4 and bj5 to estimate the beta, j , for each of the five stocks. (10 marks) (c) Compare the estimates of beta you obtain for each of the five stocks under the Dimson (1979) technique in Part b of this Section and the OLS technique in Part a of this Section and come to a conclusion about whether there is any evidence of thin trading for the five stocks. Support your conclusions with detailed reasons. (5 marks) (c) Briefly catalogue some of the difficulties that arise when estimating the betas of stocks. (5 marks) NOTE: Marks will be allocated for the presentation of the EXCEL spreadsheet as well as for the correct use of EXCEL functions. (30 marks in total) Section 5 (Stock Valuation) Let the dividend, D(t), received from a stock during the t th year be of the form: D(t) = a + bt for t = 1, 2, _____,  and where a and b are parameters. It then follows that the present value, P(0), of the stream of future dividends will be: P(0) =  t=1  D(t) (1 + r) t =  t=1  a + bt (1 + r) t = b + (a + b)R R2 where r is the cost of equity capital for the stock. 8 Required: (a) Randomly choose five dividend paying stocks from the FTSE100 Index on the Yahoo! UK & Ireland Finance website. Obtain the most recent 10 years of dividend history for each of the five stocks and then use the Regression function in the EXCEL spreadsheet package to estimate the parameters a and b for each stock. (5 marks) (b) Use the appropriate function in the EXCEL spreadsheet package in conjunction with the expression for P(0) and the market price for each of the five stocks you have selected to estimate the cost of equity capital, r, for each of the five stocks. (5 marks) (c) Discuss the limitations of using the methods employed in Part (a) and Part (b) of this Section to estimate the cost of equity capital for stocks. In particular, discuss the sensitivity of the estimates obtained for the cost of equity capital in part (b) of this section to alternative specifications of the dividend function, D(t). (5 marks) NOTE: Marks will be allocated for the presentation of the EXCEL spreadsheet as well as for the correct use of EXCEL functions. (15 marks in total) Coursework Guidelines:  You should submit a PDF version of their coursework to the PG HUB via E-Vision.  Students also need to submit an electronic version of their coursework to the “Assignments” folder under “Grade Centre” tab of Blackboard site.  Any questions related to the coursework should be submitted to a specialised forum within the Q&A forum on the Blackboard Site of the module and the module orgainser will answer questions students have.  The module organiser will answer questions related to coursework assignment in person in the last lecture.  Word limit: The Coursework Assignment should not exceed 3,000 words. This limit excludes the Excel workbooks included as part of each section of the coursework assignment.  Coursework Presentation: Font must be Times New Roman with a size of 12 points; character scale must be 100% and spacing and position should be normal (the default options); use normal margins (2.5cm on top, bottom, left and right); line spacing must be 1.5 lines with an extra line between paragraphs and headings; alignment must be justified; do not use colour in your graphs and text since the printout will be in grayscale. Coursework must be word processed. All sections, tables and figures must be clearly titled and sequentially numbered. All tables and figures should self-explanatory and should include necessary axis definitions and legends. Do not copy-paste tables and figures directly from excel into word and apply appropriate formatting. Numbers in the text, tables and figures should have a reasonable number of decimal points in order to allow an accurate representation and comparisons (usually between 2 and 4 decimal points). For more details on successful presentation see: http://www.uea.ac.uk/polopoly_fs/1.91281!essay_presentation.pdf  Include at least 5 references to articles in credible professional magazines or academic journals. Do not include articles that you do not cite within your report or that you have not at least partly read. Do not use material that exists exclusively on the internet (eg.., websites, blogs, etc) or module lecture notes/slides as sources in your references. Academic coursework in the Norwich Business School requires the use of the Harvard System of referencing. Your coursework should clearly distinguish between your original words and ideas, and those of others. When referring to the work of others, from books, journals or any other source (including the internet), it is essential that you make this clear by acknowledging your source and referencing correctly. Failure to reference correctly will lose you marks and may constitute plagiarism or collusion. For more details on referencing see: http://www.uea.ac.uk/~d051/plagiarism/Referencing.pdf 9  As a general principle, you should always cite your source whenever you
are reporting someone else’s work, using their terminology or quoting directly from their work. You lose no marks for borrowing their concepts or terminology. On the contrary, you probably will gain marks, as you are demonstrating to the reader that you have read the literature and assimilated the ideas of others. In fact, you may well lose marks for not citing, as the reader/assessor is usually familiar with most of the relevant literature in a given area, and may be concerned that you have not cited the relevant literature.  It is important that you avoid plagiarism and collusion. You must be fully aware of the relevant UEA rules and regulations can be found at: http://www.uea.ac.uk/plagiarism  Individual study skills support and advice on referencing is also provided by the Learning Enhancement Service at the Dean of Students Office. You may be requested to provide a digital copy of your coursework (after an initial examination of the hard copy by the coursework assessors) for use with plagiarism detection software. Instances where plagiarism is suspected will be investigated by the NBS Plagiarism Officer. Students who are found to have plagiarised will be penalised. In proven cases offenders will be punished and the punishment may extend to degree failure, temporary suspension or expulsion from further study if the case comes before a Discipline Committee of the University.  In each section of the report, you should also include details about the data you used and your computations.  The calculations should be performed using Microsoft Excel in a single workbook with multiple worksheets.  You should present your models in Word and Excel in a helpful and clear way. You should also use an appropriate format for the Excel cells.  Please preserve anonymity by using your university number, not your name.  In line with UEA policy, the word count for coursework, written assignments, projects, reports and dissertations shall include: Footnotes and endnotes, references (in the main text), tables and illustrations and if applicable the abstract, title page and contents page. Any appendicised material and the bibliography or reference list shall be excluded from the word count. Where it is agreed that bibliographic referencing will take the form of footnotes and/or endnotes this will not be included in the word count – any additional notes within the body of the text will be counted. You should declare the word count of the text of their assignment on the coversheet (electronic or hard copy) submitted with their piece of work. Markers who suspect an assignment is over the word limit should assign it an unpenalised mark, and return it to the Learning and Teaching Hub, flagged appropriately, for investigation and application of any resulting penalty. Penalties will be applied if work exceeds the word limit, with a 10% tolerance allowance. Cases of intentional misrepresentation of the word count will result in the mark being capped at the pass mark. When an assignment is excessively over the word limit, the marker is obliged to read up to the limit but is not obliged to read beyond it. It is recommended that a 10% allowance is made in determining the cut-off point, which should be clearly identified on the script by the marker. The awarded mark will reflect the assignment content up to that cut-off point. In addition, this awarded mark will have a 10 mark deduction penalty applied by Learning and Teaching Service staff. For Pass/Fail assignments where the word count is found to exceed the word limit plus 10%, the judgement on whether the grade is a pass or a fail should made only on the text up to the word limit plus 10%. The penalties for exceeding the word limit are: Up to 10% over word limit (No Penalty), 10% or more over the word limit (Deduction of 10 marks off original mark), Failure to provide an electronic copy when requested (Mark capped to the pass mark), Intentional misrepresentation of the word count on the coversheet (Mark capped to the pass mark). When the original mark is within 10 marks of the pass mark, the penalty will be capped at the pass mark. Original marks below the pass mark will not be penalised. See also: http://www.uea.ac.uk/calendar/section3/regs(gen)/submission-of-work-for-assessment-(taught-programmes) See also “Guidance for Students on the Procedures for Coursework Submission and Return” 2012/3: http://www.uea.ac.uk/learningandteaching/documents/assessment/StudentGuide_CourseworkSubmissionandReturn  In preparing the coursework, students should be aware that they will be assessed by reference to the extent to which their answers meet the following criteria: Criterion Weight Identification and application of knowledge to the question asked 15% 10 Evidence of reading and research 10% Capacity to find data from online resources 10% Ability to construct financial models related to the question asked 15% Ability to correctly implement these models using spreadsheet software 25% Demonstration of ability to structure and present work in a clear and coherent way 15% Clear and accurate expression 10% Coursework Assessment The table below shows how numerical grades relate to class of performance and gives some of the criteria used in the determination of grades. A close study of the descriptors will enable you to understand the standards that you should be aiming at in coursework and to interpret the marks that you are awarded.  80-100% = outstanding work at distinction level: A mark in this range will be given for work which shows the qualities described below (70-79% range). In addition it will show evidence of intellectual rigour, independence of judgement and inventiveness, and will convey a firm impression of originality of mind. It should demonstrate insight which forces reconsideration of existing knowledge and understanding in the reader.  70-79% = excellent work at distinction level: Work in this range will display a full understanding of its subject, a firm grasp of factual details and of the relevant theory and literature and techniques. It will be clearly argued and presented, with evidence of insight and some originality of thought and expression. It will demonstrate wider reading, and draw upon relevant source material which goes beyond the core material required for the module. Application of appropriate theory and techniques to management situations is anticipated, with evidence of the ability to critically evaluate their relevance.  65-69% = work of a very high standard: Work in this range will show a thorough grasp of the topic (though a lesser ability to apply theory and techniques) and will be clearly argued and presented. It should demonstrate wider reading, and draw upon relevant source material which goes beyond the core material required for the module. It will show an appropriate awareness of the relevant theory, literature, and techniques without quite achieving that intellectual independence and originality that distinguishes distinction level work. It should demonstrate the ability to use theory to transfer learning from one context or situation to another.  60-64% = very good work: Work in this range will be intelligently argued, although there may be flaws in the structure or in the use of information. There may also be some unevenness in the quality of the work, and theory and techniques may be less well applied than in work of a very high standard.  55-59% = good work: Work in this range will show a broad knowledge and understanding of its subject. It may lack sophistication in its argument or be somewhat too descriptive in its treatment, and consequently ignores relevant theoretical foundations and technical applications. It should demonstrate accurate and a more complete recycling of the material covered.  50-54% = acceptable work to an adequate level: Work in this range will show a satisfactory knowledge but may be vague or very descriptive in its interpretation of the subject. It may also be structurally weak, and lack a sense of argumentative purpose, and be less extensively argued. There will be little evidence of managerial applicability. I
t should demonstrate accurate recycling of the material covered. Pass Mark is 50%.  40-49% = Marginal fail; work which does not reach an acceptable level: Work in this range will only partly address the question there will be a lack in synthesis of ideas and a tendency to description rather than analysis. The work shows a restricted range of sources consulted, only a basic understanding of evidence, a limited range of examples, sometimes inappropriate ones and a limited understanding of key concepts. There will also be poor typography and layout, a considerable number of grammatical errors, a limited vocabulary, inaccurate citation and a bibliography with significant omissions. Given a mark in this category, the Exam Board may require the student to re-sit / resubmit the assessment.  30-39% = Clear Fail: Work in the range will have a weak structure, be largely irrelevant to the set question and contain considerable misunderstanding of key concepts. A minimal range of sources will have been consulted, very 11 limited understanding of evidence, minimal range use of examples; little use of sources beyond direct paraphrase of lectures, easily available texts or web pages. The work will show poor presentation; numerous and significant grammatical errors; highly restricted vocabulary; inadequate citation and bibliography.  20-29% = Well below passable standard: Work in this range only marginally addresses the question; contains a fundamental misunderstanding of key concepts; is mostly irrelevant; and contains no line of argument. There will be little attempt to support any assertions; no use of sources beyond direct paraphrase of lectures or easily available texts or web pages. Poor grammar and vocabulary makes it difficult to decipher any intended meaning; no citation; no relevant bibliography.  10-19% = Very few learning outcomes met: Work in this range will contain few relevant elements; only fragmentary arguments; only slight evidence of understanding of key concepts. There will be no attempt to support assertions. Poor grammar and vocabulary makes it very difficult to understand the intended meaning.  1-9% = Far from meeting any learning outcome: Work in this range will have no evidence of learning anything from the module, although there may be elements derived from general knowledge. Short answer; note form; mostly incomprehensible.  0% = No work submitted: The correct citation practices should be adopted for all coursework and references should be correctly and consistently structured. The Harvard System of Referencing is the preferred model in the school and should always be used unless you have been explicitly told otherwise in writing by the module organiser. Further information about these requirements, and general help on how to write reports and essays, can be found on the Dean of Students website at: http://www.uea.ac.uk/dos