You are an analyst at the corporate banking department of a consulting firm. You are required to build a valuation forecast model and arrive at a valuation calculation for NEXT plc. The data Collect all data required from internet and company’s annual reports. Tasks In Excel worksheet, complete the following tasks: a) Clearly indicate your assumptions of Capex for fade period. b) Completeanincomestatementforecastingmodel. c) Completeabalancesheetforecastingmodel. d) Completeacashflowstatementforecastingmodel. e) Calculatekeyratios,WACC. f) Perform cash flow valuation. 1 Project MND012 Dec 2017 g) Performsensitivityanalysis. A.2 – Modelling the yield curve You are a corporate finance analyst in an investment bank. One of the bank’s clients is interested in raising $5m of debt financing through the issue of a bond. The bond would have a face value of $1000, a maturity of 15 years, and a coupon rate of 5.5%. The company has a credit rating of A. Your manager wants you to estimate the number of bonds that the company would have to issue in order to fulfil the financing requirement. The data The data file contains details on all Arated bonds issued by US companies, except for those with very short maturity. Tasks In Excel worksheet, complete the following tasks: Assume that the current date is 14 November 2014. h) Plot and estimate a third order polynomial trend for the YTM. i) Use the estimated yield curve to estimate the YTM that the new bond would have. j) Use the estimated YTM to compute the fair price of the bond. k) Calculatethenumberofbondsthatthefirmwouldhavetoissuetoraise$5m. A.3 – Portfolio optimisation You are an analyst in a US fund management company that specialises in global equity investment. The company is considering launching a new fund, the ‘G7 Equity Fund’, which will invest in a broad selection of large stocks in the equity markets of the G7 countries (US, UK, Japan, Germany, France, Canada and Italy). Your manager has asked you to establish the efficient frontier for these markets, both with 2 Project MND012 Dec 2017 and without a risk free asset. As a proxy for the investment in each market, you decide to use the aggregate equity return index for each market. The data The data file contains the adjusted close prices for the seven indices. Ignore exchange rate fluctuations (you could assume that the currency risk is perfectly hedged). Tasks In Excel worksheet, complete the following tasks: a) Use the return index data to compute monthly simple returns. b) Use the return data to compute the covariance matrix for the seven markets. c) A significant problem with applying portfolio optimisation in practice is that sample estimates of expected returns are very noisy, and hence not reliable. The fund company has, using another approach, estimated the following expected monthly returns for the G7 markets: Using the above information, compute the efficient set. Graphically estimate the efficient frontier of the seven equity markets both with and without a risk free asset. Obtain the US risk free rate from Yahoo! Finance for a threemonth holding period. Part B – Creating the report Write a report that provides a critical summary of the computations and results from Part A. In particular, you should describe in detail how you carried out the tasks in US UK Japan Germany France Canada Italy Expected Return 0.48% 0.39% 0.55% 0.51% 0.43% 0.45% 0.42% 3 Project MND012 Dec 2017 Part A and a discussion of the results obtained. Your report should also include your analysis of the potential limitations of financial statement forecasting model, the yield curve model, and the portfolio optimisation model used in Part A and possible improvements. The report can include figures and tables to support your statements. References at the end should also be included.