-
Requirements: Submit your final softcopy of Word file (Format other than Word will NOT be accepted for marking; please use this template file to present all answers) via Turnitin link on Blackboard before due date. Please remember to sign the cover sheet (on the first page of this Word template) declaring that your submission is based on your own work and any sources quoted or used are properly acknowledged and referenced. Your work will NOT be marked without this signed cover sheet. Please be reminded that AUT highly values integrity and any plagiarism will not be taken lightly. IMPORTANT: It is the student’s responsibility to submit the correct file and format. Please ensure that your answer file is saved in Word format so that the marker can access and assess your inserted Excel worksheets. Format other than Word will NOT be accepted for marking. Only one answer file in Word is to be submitted. Only answers presented in the designated space will be marked. To ensure that your work is not lost or mixed up with other students’ submissions, please save your file in Word format (this is the only format acceptable for marking) and label your file name and cover sheet in this format: “your full name (first name_last name)_stream number_student ID_FINA501 Assignment”. Note: Submissions received after the above date and time will be assessed as late. Penalty applies for late submissions as specified in the Study Guide. After 5 days from the due date, submission will no longer be accepted for marking. To be fair to all students, the teaching team will not view students’ work before the deadline or give additional hints to individual students. Assessment Instructions (please read and follow instructions carefully): · Questions: Please read through all questions and clarify any part that you find unclear as early as possible and by the above clarification due date at the latest. · Answers: Please present your work and solutions in the designated template/space under each question. The answers presented in this Word document will be marked as the final answers. Only answers presented in the Word template/space will be marked. Please do NOT use red colour in your answers. Answers must be clearly identified and easy to follow. All answers must be typed (formulae can be copied and pasted from the Formulae Sheet on Blackboard). Save all Excel calculations in one file (present calculations for each question in a separate worksheet in the same Excel file). Submit only one Word file and one Excel file for this assignment, along with the signed cover sheet. · Formulae and calculations: Answers to each question must be supported by formulae, values of the components and/or calculations, where applicable. In this assignment, you are given the choice to use Excel and/or formula. If formula is used, copy and paste the formula from the Formulae Sheet (on Blackboard for this Paper). Where applicable, for each variable in the formula, you should identify the number that you entered into the formula, and any intermediate calculation that was required. o i.e. if a question asks about a 10-year investment with monthly compounding, n (or NPER, the number of periods) will be equal to 120 (10*12). This should be shown in the answer as n =120(=10*12) or n =10*12=120. If calculations are done using Excel, where applicable, please specify the Excel function(s) used and the values of relevant variables. Rounding: Please do not round your intermediate calculations. Round only the final answer for a given question to 2 decimal places, unless otherwise stated. · Presentation and Format: Every question must be presented in a fresh new page. Assignments must be 1.15 line spacing, at least 12-point Times New Roman. Save your file in Word format and label your file name as: “your full name (first name_last name)_stream number_student ID_FINA501 Assignment”. Question One: Mini Research on Stock and Return (Total 12 marks) Search online for Air New Zealand Limited’s (AIR.NZ) daily stock price from August 12, 2019 to August 11, 2020 (twelve months). a) Download the above stock’s daily closing price (you could use either “closing price” or “adjusted closing price”) for the specified period. Save the data in an Excel file to perform the following calculations. Clearly state the data source and weblink. (1 mark) State the source name and link here: [Type here] b) Using Excel, calculate the daily rate of change (daily rate of return or daily holding period return) of the above downloaded stock prices. ‘Daily’ rate of change refers to the percentage change in closing prices from one trading day to another. [For instance, the closing prices on September 23rd and September 24th, 2020 can be used to calculate one daily rate of change (so for 100 trading days, 99 daily rates of change can be calculated).] Present the formula for calculating the rate of change here (1 mark): [Type here] Excel table: Set up your Excel table columns as specified below. Show respective closing prices and rates below each column. (2 marks) Date (Adjusted) Daily Closing Price Calculated daily rate of change Insert your Excel worksheet for this question here (double click on the following table to access the blank worksheet, then paste your Excel answers): c) Next, using the same worksheet, use Excel’s Data/Sort function to sort the calculated daily rate of change from “largest to smallest”. Identify the largest positive and negative change and their corresponding dates. Present your answers in the following space (the rates and dates must be consistent with the figures in your Excel worksheet). (3 marks) Largest positive change (%) and date: [Type here] Largest negative change (%) and date: [Type here] Insert your Excel worksheet for this question here (double click on the following table to access the blank worksheet, then paste your Excel answers): d) Conduct an online search for documented news or announcements that are likely to have influenced the largest stock price changes identified above [Part (c)]. In your own words, describe the major cause(s) of the largest positive and negative stock price changes. State the sources and websites as instructed in the space below. (5 marks) i. Discussion on the news for the largest positive change [The discussion must be in your own words. Quoted text from another source is not acceptable. Word limit: 60 words; answers beyond the word limit will not be marked]: [Type here] Title of source/article (expressed in italic or in quotes): [Type here] Source website(s): [Type here] ii. Discussion on the news for the largest negative change [The discussion must be in your own words. Quoted text from another source is not acceptable. Word limit: 60 words; answers beyond the word limit will not be marked]: [Type here] Title of source/article (expressed in italic or in quotes): [Type here] Source website(s): [Type here] Question Two: Portfolio Beta and Expected Return (Total 10 marks) Assume that CAPM holds and that both Apple and Yahoo plot on the SML. Apple has a beta of 2.6 and Yahoo has a beta of 0.9. The expected return on the market portfolio is 9.25% and the risk-free rate is 1.0%. a) Based on the above data, what is the expected return for Apple? (Express your answer in percentage, rounded to 2 decimal places.) (1 mark) Expected return for Apple (in %): [Type here] Show the formula and calculations here: [Type here] b) Based on the above data, what is the expected return for Yahoo? (Express your answer in percentage, rounded to 2 decimal places.) (1 mark) Expected return for Yahoo (in %): [Type here] Show the formula and calculations here: [Type here] c) Suppose that you wish to hold a portfolio consisting of only Apple and Yahoo and have your portfolio beta equal to 1.2. i. What proportion of your portfolio must be in Apple? (Express your answers in percentage, rounded to 2 decimal places.) (2 marks) [Hint: Review: The sum of investment proportions (weights, ) must equal to one.] Proportion of investment in Apple (in %): [Type here] Show your calculations here: [Type here] ii. What proportion of your portfolio must be in Yahoo? (Express your answer in percentage, rounded to 2 decimal places.) (1 mark) Proportion of investment in Yahoo (in %): [Type here] Show your calculations here: [Type here] d) Based on the above figures, what is the expected return on your portfolio? (Express the final answer in percentage, rounded to 2 decimal places.) (1 mark) Expected return for your portfolio (in %): [Type here] Show the formula and calculations here: [Type here] e) Compare and comment on your portfolio expected return with the expected return of each of the individual stock. Is the expected return of your portfolio closer to that of Apple or Yahoo? Explain your answer. Include the investment weights (proportions calculated above) for each stock and relevant figures in your explanations. [Word limit: 120 words; answers beyond the word limit will not be marked.] (4 marks) [Type here] Question Three: Capital Budgeting