Please answer all of question 3:Portfolio analysis in the attachment below. To answer the question use the excel spread sheet return data attachment and complete all the answers on the excel spreadsheet. MAKE sure to follow the instructions word by word and use the EXACT excel function that they tell you to use in the questions.

No plagiarism and please reference any citations

Thanks

Bachelor of Business (Incorporating Graduate Diploma and Graduate Certificate in Business) Managerial Finance (ACCT 706) Semester Two, 2016 Assignment # 2 Due Date: Week 8, 03/05/16, 12.00 noon Weighting: 25% of the final grade Type: Individual Assignment Length: Approximately 2,000 - 3,000 words excluding appendices Submission: Students are expected to submit a hard copy of the assignment along with Arion generated barcoded assignment cover sheet in the drop box located in WF building (ground floor). All assignments should be submitted via turnitin and a turnitin report should accompany the assignment (Please note: Turnitin submission should precede physical submission and only the hardcopy will be marked). Penalty for late submissions: 10% of the mark obtained for each 24-hour delay including weekends. Page 1 of 6 QUESTION 1: VALUATION OF SHARES (35 MARKS) A. In the world of trendsetting fashion, instinct and marketing savvy are prerequisites to success. Jordan Ellis had both. During 2015, his international casual-wear company, Encore, rocketed to $300 million in sales after 10 years in business. His fashion line covered the young woman from head to toe with hats, sweaters, dresses, blouses, skirts, pants, sweatshirts, socks, and shoes. The Encore shops are now a standard feature in every town in New Zealand. Encore had made it. The company's historical growth was so spectacular that no one could have predicted it. However, securities analysts speculated that Encore could not keep up the pace. They warned that competition is fierce in the fashion industry and that the firm might encounter little or no growth in the future. They estimated that shareholders also should expect no growth in future dividends. Contrary to the conservative securities analysts, Jordan Ellis felt that the company could maintain a constant annual growth rate in dividends per share of 6% in the future, or possibly 8% for the next 2 years and 6% thereafter. Ellis based his estimates on an established long-term expansion plan into European and Latin American markets. Venturing into these markets was expected to cause the risk of the firm, as measured by risk premium on its share, to increase immediately from 8.8% to 10%. Currently, the risk free rate is 6%. In preparing the long-term financial plan, Encore's chief financial officer has assigned a junior financial analyst, Marc Scott, to evaluate the firm's current share price. He has asked Marc to consider the conservative predictions of the securities analysts and the aggressive predictions of the company founder, Jordan Ellis. Marc has compiled these 2015 financial data to aid his analysis: Data item Earnings per share Price per ordinary share Book value of equity Total ordinary shares outstanding Ordinary dividend per share 2015 value $ 6.25 $ 40.00 $ 60,000,000 2,500,000 $ 4.00 Required: a. What is the firm's current book value per share? (2 Marks) b. What is the firm's current P/E ratio? (2 Marks) c. (1) What is the current required rate of return for Encore's shares? (2) What will be the new required rate of return for Encore's shares assuming that they expand into European and Latin American markets as planned? (6 Marks) d. If the securities analysts are correct and there is no growth in future dividends, what will be the value per share? (Use the new required rate of return computed in part [C (2)] above). (2 Marks) Page 2 of 6 e. (1) If Jordan Ellis's predictions are correct, what will be the value per share if the firm maintains a constant annual 6% growth rate in future dividends? (Use the new required rate of return computed in part [C (2)] above). (2 Marks) (2) If Jordan Ellis's predictions are correct, what will be the value per share if the firm maintains a constant annual 8% growth rate in dividends per share over the next 2 years and 6% thereafter? (Use the new required rate of return computed in part [C (2)] above) (4 marks) f. Compare the current (2015) price of the share and the share values found in parts (a) to (e). Discuss why these values differ. Which valuation method do you believe most clearly represents the true value of Encore's Share? (7 Marks) B. Answer the following questions: a. How does a bond issuer decide on the appropriate coupon rate to set on its bonds? Explain the difference between the coupon rate and the required return on a bond. (5 Marks) b. Companies pay rating agencies such as the Standard and Poor Rating Service, to rate their bonds, and the costs can be substantial. However, companies are not required to have their bonds rated in the first place; doing so is strictly voluntary. Why do you think they do it? (5 Marks) Page 3 of 6 QUESTION 2: WEIGHTED AVERAGE COST OF CAPITAL (22 Marks) Defence Electronics International (DEI) a large publicly listed company is the market leader in radar detection systems (RDSs). The company is looking to set up a manufacturing plant overseas to produce a new line of RDSs. This will be a five year project. The company bought a piece of land three years ago for $ 7 million in anticipation of using it as a toxic dump site for waste chemicals, but instead built a piping system to discard chemicals safely. If the company sold the land today it would receive $ 6.5 million after taxes. In five years the land can be sold for $4.5 million after taxes and reclamation costs. DEI wants to build a new manufacturing plant on this land. The plant will cost $15 million to build. The following market data on DEI's securities are current: Debt 150,000, 12% coupon bonds outstanding with 15 years to maturity redeemable at par, selling for 80 percent of par; the bonds have a $100 par value each and make semi-annual coupon interest payments. Equity 300,000 ordinary shares, selling for $75 per share Non-redeemable Preference shares 20,000 shares (par value $ 100 per share) with 7.2% dividends (before taxes), selling for $72 per share The following information is relevant: DEI's tax rate is 30% The company had been paying dividends on its ordinary shares consistently. Dividends paid during the past five years is as follows Year (-5) ($) Year (-4) ($) Year (-3) ($) Year (-2) ($) Year (-1) ($) 2.2 2.5 2.8 3.3 3.6 The project requires $ 900,000 in initial net working capital investment in year 0 to become operational. Work all solutions to the nearest two decimals. Required: 1. Calculate the project's initial, (time 0) cash flows, taking into account all side effects. (2 MARKS) 2. Compute the current weighted average cost of capital (WACC) of DEI. Show all workings and state clearly the assumptions underlying your computations. (12 MARKS) 3. Using the WACC computed in part (2) above and assuming the following, compute the project's Net Present Value (NPV), Internal Rate of Return (IRR) and the Profitability Index (PI) a. The manufacturing plant has a ten-year tax life and DEI uses straight line method of depreciation for the plant. At the end of the project, (i.e. at the end of year 5), the plant can be scrapped for $ 5 million. Page 4 of 6 b. The project will incur $400,000 per annum in fixed costs c. DEI will manufacture 15,000 RDSs per year in each of the years and sell them at $ 1,000 per machine. d. The variable production costs are $ 500 per RDS. e. At the end of year 5, the company will sell the land. (6 MARKS) 4. The new RDS project is somewhat riskier than a typical project for DEI, primarily because the plant is being located overseas. Explain briefly how DEI could accommodate this additional risk factor in the determination of its discount factor? (2 MARKS) QUESTION 3: PORTFOLIO ANALYSIS (43 Marks) Jane is considering investing in three different securities or creating three distinctive two-factor portfolios. She is able to obtain the monthly returns of securities A, B & C for the years 2007 to 2014 (The attached file (Stock returns 2007-14) gives 96 monthly returns for three securities along with the returns of the NZX all share index for the period January 2007 and December 2014). In any of the possible two factor portfolios, the weight of each security in the portfolio will be 50%. The three possible portfolio combinations are AB, AC and BC. Required: a) Determine, using the appropriate Excel function (see fx) the average monthly return, the standard deviation and variance for each of the companies. (Use the 96 months returns data in the calculations and use the Excel functions identified as \"Variance P\" and \"Standard Deviation P\".) (3 Marks) b) Determine, using the appropriate Excel function the covariances between securities A&B; B&C; A&C. (Use the 96 months returns data in the calculations and use the Excel function\"COVARIANCE P\".) (3 Marks) c) Calculate using the two-factor portfolio equations, the portfolio returns and risks (standard deviation and variance) for the following portfolios: a. A and B b. B and C c. C and A (12 Marks) d) Would you recommend that Jane invest in the single securities of A, B or C or the portfolios consisting of securities A&B or A&C or B&C? Explain your answer from a risk-return view point. (10 Marks) Page 5 of 6 e) Determine the betas Security A, a utility company, security B, a construction company, and security C, a manufacturing company by regressing the returns foreach of the companies on the returns for the NZX ALL Share Index (the first column in the spread-sheet). (Regression Calculation: Go to Data Analysis - far right under Data- and choose regression. If Data Analysis does not appear it must be added, it will be available in Excel. Go to Options under File and choose Add-ins and then Data Analysis the company returns constitute the Y input and the index returns the X input. Alternatively, the \"slope\" found in f(x) also represents Beta). a. Explain what the values of the betas (the slope coefficients in the regression) indicate and discuss the factors that might explain the differences in the values of the betas of the three companies. b. Comment on the implications of the estimated value of beta for investors and the cost of capital for the two companies (15 Marks) Page 6 of 6 Return Data for a sample of companies drawn from NZX from January 2007 to December 2014. Month All Share index Security A Security B Security C 1/31/2007 -0.00253 -0.05098 -0.04292 0.01445 2/28/2007 -0.00169 0.02200 -0.00338 0.08828 3/30/2007 0.03343 0.12628 -0.05250 0.10917 4/30/2007 0.02449 -0.00298 -0.01719 -0.00354 5/31/2007 0.02820 0.13863 0.00460 0.08947 6/29/2007 -0.00757 -0.03093 -0.08983 0.13008 7/31/2007 -0.03326 -0.04255 -0.05841 0.05970 8/31/2007 -0.00270 -0.01387 -0.00695 -0.00950 9/28/2007 0.01893 0.15647 -0.19440 0.21041 10/31/2007 0.04347 0.00760 -0.09519 0.04629 11/30/2007 -0.04747 -0.00845 -0.28697 -0.12233 12/31/2007 0.00260 -0.06269 -0.02096 -0.03796 1/31/2008 -0.08666 -0.10714 -0.07355 -0.04463 2/29/2008 0.00769 0.17600 -0.02844 0.11327 3/31/2008 -0.02054 -0.05114 0.00854 -0.08283 4/30/2008 0.06257 0.07959 -0.30724 0.20402 5/30/2008 -0.00199 0.04588 -0.33937 0.06334 6/30/2008 -0.07065 0.03130 -0.68219 0.00313 7/31/2008 -0.03601 -0.17158 0.63793 -0.11823 8/29/2008 0.04995 0.01238 0.54737 0.01477 9/30/2008 -0.13238 -0.36732 -0.28741 -0.25610 10/31/2008 -0.11897 -0.17413 -0.26492 -0.16362 11/28/2008 -0.01669 0.00130 -0.39935 0.12916 12/31/2008 0.03673 0.00782 0.51351 0.08831 1/30/2009 -0.05829 -0.18111 -0.00714 -0.08733 2/27/2009 -0.06520 -0.20931 0.23022 -0.03940 3/31/2009 0.03279 0.18481 0.03801 0.25226 4/30/2009 0.09944 0.25125 0.57746 0.02816 5/29/2009 0.04191 0.19744 0.13214 0.03652 6/30/2009 -0.03207 -0.00759 -0.06940 -0.07588 7/31/2009 0.08503 0.09441 0.34576 0.14589 8/31/2009 0.07701 0.04716 0.23829 0.04159 9/30/2009 0.04722 -0.01386 -0.00163 0.06613 10/30/2009 -0.01821 0.11139 -0.16045 -0.03776 11/30/2009 0.02960 0.17517 -0.12509 0.13022 12/31/2009 0.04342 0.04149 0.04907 0.07402 1/29/2010 -0.03572 -0.14349 -0.03468 -0.06466 2/26/2010 0.03373 0.02928 -0.04929 0.07557 3/31/2010 0.06758 0.20251 0.08963 0.14067 4/30/2010 -0.01389 -0.02383 0.00565 -0.10377 5/31/2010 -0.06225 -0.05150 -0.13392 -0.05554 6/30/2010 -0.04625 -0.11668 -0.12222 -0.08285 7/30/2010 0.06895 0.07402 0.01530 0.11228 8/31/2010 -0.00225 -0.06695 -0.00208 -0.06149 9/30/2010 0.06541 0.07928 0.02811 0.12265 10/29/2010 0.02534 0.15146 -0.20911 0.09308 11/30/2010 12/31/2010 1/31/2011 2/28/2011 3/31/2011 4/29/2011 5/31/2011 6/30/2011 7/29/2011 8/31/2011 9/30/2011 10/31/2011 11/30/2011 12/30/2011 1/31/2012 2/29/2012 3/30/2012 4/30/2012 5/31/2012 6/29/2012 7/31/2012 8/31/2012 9/28/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/29/2013 4/30/2013 5/31/2013 6/28/2013 7/31/2013 8/30/2013 9/30/2013 10/31/2013 11/29/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/30/2014 6/30/2014 7/31/2014 8/29/2014 9/30/2014 10/31/2014 -0.02256 0.07141 -0.00528 0.02398 -0.00811 0.03111 -0.00716 -0.00450 -0.02203 -0.06887 -0.05007 0.07891 -0.00373 0.00840 0.02715 0.04289 -0.00949 -0.00328 -0.06797 0.04819 0.01331 0.02212 0.01086 0.01034 0.01754 0.00988 0.06372 0.02278 0.01405 0.00579 0.02912 -0.04990 0.06783 -0.02213 0.01116 0.04286 -0.00700 0.01836 -0.03057 0.05242 -0.02605 0.02162 0.01367 -0.01277 -0.00299 0.02152 -0.02769 -0.00686 -0.03009 0.18260 -0.08185 0.08849 -0.03052 -0.02697 -0.02900 0.01897 -0.06073 -0.10860 -0.13184 0.02893 0.05516 -0.01694 0.10299 0.00972 -0.10704 0.01327 -0.16765 0.05961 -0.09026 -0.06927 0.03828 0.04734 -0.08960 0.09321 -0.00395 0.01882 -0.10228 -0.07506 -0.02460 -0.17066 0.11217 0.06406 0.02672 -0.02174 -0.09158 -0.02150 0.08788 0.06616 0.01995 0.03636 -0.07869 -0.01888 0.11818 -0.03157 -0.09542 -0.04877 -0.10307 0.26553 0.03948 0.16549 0.02514 0.01544 0.01431 0.00705 -0.13748 -0.16497 -0.04620 0.13193 0.11993 -0.06586 0.17546 0.34799 -0.04212 -0.05177 -0.10322 0.16013 -0.04960 0.13464 0.13000 0.11858 0.02901 0.06407 0.04817 0.10340 0.14161 0.13535 0.02764 -0.03190 0.05297 -0.06902 0.01680 0.09387 -0.01761 0.06015 0.08481 0.16218 -0.06250 -0.09639 -0.03329 0.04619 -0.06556 0.05928 0.07083 0.07563 0.03230 0.11641 -0.06703 0.02248 0.02293 0.02622 -0.04813 0.02039 -0.07300 -0.07347 -0.16076 0.13205 -0.00941 -0.03668 0.13076 -0.02406 -0.06380 0.03513 -0.14181 0.06580 0.03488 -0.01766 0.06960 0.03143 -0.01184 0.08537 0.01291 -0.03153 -0.06659 -0.06475 0.06952 -0.12191 0.11713 -0.00053 -0.01165 0.06071 -0.03704 0.00538 -0.03932 0.07407 -0.02591 0.04067 -0.02658 0.01150 0.07383 -0.05988 -0.08300 -0.06093 11/28/2014 12/31/2014 0.02918 -0.01600 0.00380 -0.09157 0.09909 0.02325 -0.05806 -0.08470