Financial Analysis Exercise #1 (100 Points) This exercise will require both a written and Excel spreadsheet component. It is critical to demonstrate the appropriate Excel skills. There are 10 mini projects that you are required to complete. Carefully read additional requirements for each mini project. Using APA format, you must also complete a written part for each mini-project, making a recommendation based on the results that you obtain in Excel. Be thorough, include an introduction and conclusion to the whole body of the mini-project Submit a Word file for the written component, addressing all the key points along with appropriate and relevant examples. APA format is required. Submit a spreadsheet Excel file for the spreadsheet component, demonstrating the use of formulas, cell referencing, and calculation with appropriate set up. This exercise #1 is due by the end of Module 2. Scenario You are the newest Financial Analyst in Investments, you need to demonstrate your prowess in Excel, your outstanding written skills and ability to communicate. Mr. Richards is the Executive Vice President and Chief Investment officer in your new firm. You are being asked to complete a series of pet projects for Mr. Richards. You have been told not to try to impress him, just do the work and stick to the facts. There are ten mini-projects for you to complete. Each mini-project needs to have its own tab in an Excel spreadsheet. It is critical to use cell referencing and the standard setup for TVM problems provided, hes a stickler and wants to see all of the detail. Mini-Projects 1. A friend of Mr. Richards recently won a law suit for $30 million. They have the ability to either take the payments over 10 years or settle today for cash of $25 million today. Mr. Richard is optimistic that he can earn a 6% return on the money and that they should settle for $25 million today and he will invest it for them. Excel: Youll need to demonstrate the present value of the $30 million today versus the future value of the $25 million in 5 years to make your argument. 2 Written: Briefly describe which settlement is maximizing the value for the client and explain why? 2. A client of Mr. Richards wants to purchase a large commercial building. The building costs $20 million and he will make a down payment of 15% and finance the rest with a local bank. The bank terms are 10-year bullet loan with 30-year amortization at 4.5% interest. The building earns annual rent of $2,500,000 and it pays annual taxes of $1,000,000 per year. What will be the annual cash flow from the building? What will be the ROI on the investment? Given only this information, what are some of the obvious pieces missing that also need to be considered? What is your recommendation? Excel: Using the basic TVM setup, calculate the mortgage for the property and calculate the cash flow, and ROI for this project. Written: Briefly describe the analysis that you have performed, why how you calculated the cash flow, mortgage payment and ROI. Briefly give your recommendation and the list any missing risks that should be considered. 3. A client of Mr. Richards wants to purchase one of three bonds: a) 10-year corporate bond with a 2.00% coupon, paying annually, and par value of $1,000. b) 7-year corporate bond with a 1.75% coupon, paying annually and par value of $1,000. c) 5-year corporate bond with a 1.50% coupon, paying annually and par value of $1,000. What are the current prices for each of these bonds? How will the value of these bonds change if the respective market rates increase by 50 basis points? How will the value of these bonds change if their respective market rates decrease by 50 basis points? What recommendation would you make about purchasing one of these three bonds? Would you suggest any further analysis that might include the use of a relative interest rate risk measure used for bonds? Excel: Using the Basic TVM setup, calculate the value of these bonds, where FV is the par value, pmt is the current coupon (rate * par / payment frequency), PV is the current price, rate is the current market rate or in this case the coupon and rate changes, NPR is the years * M. For the rate changes, literally, copy the three bonds and then change the Rate to +/- .005 or 50 BPS. Setup a summary to show the average sensitivity of each bond and for the rate changes. Include this summary in the written analysis. Written: Briefly describe the analysis that you have performed detailing the relative prices. Also explain how the value of the bonds change in the up/down rate changes. Provide your recommendation about which bond to buy. Discuss what risk measures should be considered. 4. Mr. Richards wants additional analysis on these bonds. He wants you to assume that a year has transpired and to make the following assumptions about the bonds: each bond is exactly 1 year shorter in term rate levels are 1.75% for 9 years, 1.50% for 6 years and 3 1.25% for 4 years. Calculate the value of each bond and their relative rate sensitivity from a +/- 50 BPS rate change. Excel: Using the Basic TVM setup from question 3. Now change both NPER and rates to those indicated above. Compare the change in value from par. Summarize this potential gain in a table and include it in the written analysis. Written: Briefly describe the analysis that you have performed detailing how the values changed as they rolled down the yield curve. Based on each one of these bonds rolling down the yield curve and having a gain, which bond looks to have the most gain in market value and the highest overall yield? Based on this analysis which bond would you now recommend and what additional analysis should have been performed before purchase? 5. Mr. Richard now wants you to apply the effective duration formula he learned in CFA training to the bonds at issue and 1 year forward from questions 3 and 4. = 234567 934567 2 <=>? 50 He would also like a marginal analysis performed for both the original and the forward bond analysis. This should show the base duration and yield for the shortest bond and then the change in yield and duration for each longer bond. He explains that the 5 year is the base and the change shows the additional risk/reward for buying the longer maturities. Excel: Using the results from questions 3 and 4 and calculate the effective duration as shown in the formula. Be very careful to use the brackets as shown above. The durations for these bonds should all be between 1 and 3 as a range so any larger or smaller values means a formula problem. Perform the marginal analysis for both sets of bonds as indicated above and include the tables in the written analysis. Written: Briefly describe the analysis that you have performed detailing how the effective durations changes as the bonds down the yield curve. Based on the short bonds yield and duration, which bond appears to provide the most marginal yield for the least marginal duration? Look at the years of yield and duration (divided both by the term and then look at the marginal change for the longer bonds.) Based on this analysis which bond would you recommend at issue and why? 6. Mr. Richards has a rich client that has come to him for advice, purchase or lease a new Porsche Carrara? The car costs $108,000 and he would finance it for 84 months at a 3.5% rate with 20% down plus 6% sales tax. The lease would be for 36 months, require $10,000 cash buy down and it would cost $1,393.08 including tax each month. The client will purchase this as a company vehicle. Leasing allows full deduction of the capital buy down and the lease payment and all other related expenses. The purchase is subject to MACSR depreciation limits and deduction of interest. More importantly, the clients company earns an ROE of 10%. Should he lease or purchase? What is the real cost of the lease each month if the company tax rate is 35%? 4 Excel: Use the standard TVM setup to determine the monthly payments for the purchasing the vehicle given the information provided. Calculate the total out of pocket expenses for both the purchase and the lease. Calculate the opportunity cost (not keeping capital in the company earning 10%) of each transaction. Calculate the cost of the lease after taxes. Written: Briefly describe the analysis that you have performed detailing the comparison of the purchase versus lease. Explain the difference in out of pocket expenses and the opportunity cost of each. Intuitively, what would be your recommendation to purchase or lease? Describe the real cost of the lease after tax and why that is important. 7. Mr. Richards is trying to update his client presentations. He would like you to perform some future value calculations showing how great his returns are and how compounding works. He would like to show how $100,000 invested for 10, 20 and 30 years grows with his superior record of 8% annual returns. Just to underscore this he would also like the same compounding information with the historical annual market returns of 4%. Excel: Use the standard TVM setup to determine the FV value given the PV of $100,000, rate of .04 and .08 and NPR of 10, 20, 30. Set this up and then copy and change values as described for each of the additional five scenarios. Create a summary table showing the years and rates and the compound interest earned. Also, create a summary table showing the year and rates and calculate the overall growth of each FV net of the PV. Use both of these tables in the write up. Written: Briefly describe the analysis that you have performed and explain the effect of compound interest. Describe what the growth rate of the money over, 10, 20 and 30 year spans. What is the difference in growth between the 4% and 8% rates? What do these rates say about the level of overall returns required to grow money and create wealth? 8. Mr. Richards needs more analysis to update his client presentations. He would like additional future value calculations. Using the same framework as in the last mini-project, calculate the future value of bank CDs that would pay 1% and 2% annual interest. Excel: Copy the framework from the last mini-project and change the rates. Combine the summary tables from these analyses with the prior analysis to show the compound interest earned and overall growth rates for 10, 20, 30 years and annual returns of 1, 2, 4 and 8%. Written: Briefly describe the analysis that you have performed and explain the effect of compound interest. Explain how the compound interest and growth rates differ across the different returns. Provide 3 summary points that could be used in a presentation about these returns. 9. Mr. Richards has a new client will earn much higher returns than normal because of their risk profile. The client will role $75,000 into an account with the firm and then they will make additional monthly deposits of $2,000 per month for the next 25 years. He also wants to illustrate the returns for a bank CD at 2%, market returns at 4% and normal returns for him of 8%. To further illustrate the power of his returns, he wants to show the returns at 12% for 20 years. 5 Excel: Use the standard TVM setup. Remember M is 12 for monthly. Setup a summary table showing the rates and overall portfolio value and one additional 20-year return. Also show the overall growth rates as in the previous analysis. Written: Briefly describe the analysis that you have performed and explain the effect of compound interest. Explain how the compound interest and growth rates differ across the different returns. Provide 3 summary points to sell this client for Mr. Richards. 10. Mr. Richards has a client ready to retire. They have approximately, $1.5 million in an account with the firm. They are very conservative clients and he wants to give them options. The first is to have the money paid out over 30 years earning a 2% rate, then 4%. He also wants to show them that a little risk could preserve their equity so he wants to show the monthly payout of interest only on full balance with rates of 4% and 8%. Excel: Use the standard TVM setup and calculate the pmt using the two different rates. Calculate the monthly income by multiplying the rate by the account balance for each different rate. Create a summary table show the four different methods, the rates and another column showing the balance after 30 years for each different method. Written: Briefly describe the analysis that you have performed and explain the effect of compound interest on the payouts. What are the benefits of paying interest only and what are the risks? If this client were retiring in good health with a life expectancy of 40 years, and current income needs of $80,000 per year (not eligible for social security) which payout strategy would you recommend for these clients and why? 6 Financial Analysis Exercise #2, #3 and #4 Overview Financial analysis exercise #2, #3 and #4 will all build on the frame work of exercise #2. During these three projects you will layout the strategy, selection and hedging of $10,000,000 stock/bond/option portfolio. In exercise #2, you will develop an investment strategy to outperform the S&P 500 by selecting sector(s) and industries that will outperform the overall market in the next 12 months. This will be based on economic forecast and likely scenarios proved as well as research into the historic performance of the sectors and industries. In exercise #3, using the sector and industry strategy developed in exercise #2, you will select specific firms in the industries that will make up your portfolio. The portfolio must consist of 50% stocks and 50% bonds. The selection process must be supported by historic performance and fundamental valuation techniques to identify firms that will outperform the market because they are undervalued or because their earnings will improve. In exercise #4, you will develop an options strategy for your portfolio to enhance profitability and to hedge risk in the portfolio. Financial Analysis Exercise #2 (100 Points) Background The S&P 500 is a market index that also defines firms into sectors, industries that contain specific firms. A link is provided below with the standard sector/industry breakdown used by the S&P. https://eresearch.fidelity.com/eresearch/markets_sectors/sectors/sectors_in_market.jhtml The S&P 500 identifies ten different sectors and industries within each sector: Consumer Discretionary (12 Industries) Consumer Staples (6 Industries) Energy (2 Industries) Financials (8 Industries) Health Care (6 Industries) Industrials (14 Industries) Information Technology (8 Industries) Materials (5 Industries) Telecommunication Service (2 Industries) Utilities (5 Industries) If you go to the link above, it will allow you to see each industry within the sectors. Sector analysis is fundamental to managing an investment portfolio that provides returns that are superior to market returns. This requires an understanding of the current economic conditions, forecasts about the future, and consensus forecasts about future earnings prospects within sectors/industries and specific firms. 7 Requirements Exercise #2 is due by the end of Module 4. In this exercise, you will need to select 1 to 3 sectors and then 1 industry within each sector to that you think will outperform the S&P 500 over the next year. In order to meet all the requirements for the financial analysis exercise #2, you are required to complete the following tasks: 1. Identify sectors and industry: The exercise #2 requires you to identify 1 to 3 sectors and 1 industry within each sector that will be used complete this exercise. The sectors selected should be on the basis of their past behavior that was pro cyclical, counter cyclical or a neutral sector sector/industry. The best place to start would be to scan the firms in each industry and look for familiar names from your own consumer experience or other sources. Then, roll back up to the industry and sector. 2. Perform fully analysis: The first part of the analysis is to perform a SWOT analysis on each sector/industry selected. Identifying its strengths, weakness, opportunities and threats. Look at the sector, such as utilities, they are neutral. People always need to electricity, water and gas. They are all highly regulated industries. A SWOT analysis might indicate that their strength is stability and neutrality but it would also be a threat to future growth and earnings potential. Opportunities could be to expand their base or acquire other firms, which could also be threat. Keep it simple but be thoughtful and provide perspective and insight. 3. Analyze past performance: This will require you to research on past performances during recessions and periods of growth, which will help you determine whether they are pro, counter, or neutral sector cyclically. This type of information should be readily available from numerous reliable internet sources, which includes: S&P, investment firms, but no Wikipedia or other nonacademically acceptable sources. Graphs should be readily available from these sources but any graphs should be sighted as to the source. 4. Present research on future economic scenarios: Another source of information would be consensus forecasts of the sector and industry performance. Many investment firms publish their predictions of earnings by firm, industry and sector. Do not rely on a single source but bring together the broad view of as many sources as possible. 5. Demonstrate why these particular sector(s)/industries will outperform the overall market: The last part of this particular project is to demonstrate why these particular sector(s)/industries will outperform the overall market. This requires understanding some of the key drivers in the industry such as consumer spending, consumer spending on durable products. This further requires using a combination of the historic data and interpolation about those relationships and how they will impact the industry in the future. For example, for this section if Consumer Discretionary sector were chosen and the industry Hotels, Restaurants and Leisure, there are likely parts of that industry that are counter cyclical such as McDonalds in the restaurant industry. They typically perform better in low growth or recessionary times while Chilis and other sit down restaurants generally see a slowdown. 8 Financial Analysis Exercise #3 (100 Points) Background Financial Analysis Exercise #3 will build on exercise #2 and the sector/industry analysis. This exercise #3 is due by the end of Module 6. In this exercise, it will require selection of 3 firms with each sector/industry analyzed to invest $10,000,000 of a special portfolio into both stocks and bonds evenly. The objective of the portfolio is to produce superior returns to those of the S&P 500 over the next 12 months. Therefore, given the economic environment, the sector sensitivity and performance expected, 3 firms will need to be selected per industry that will outperform their sector. Requirements In order to meet all the requirements for the financial analysis exercise #3, you are required to complete the following tasks: Select stocks and bonds to purchase based on sector and industry analysis Use 3 methods to calculate relative value of stocks: dividend discount, Gordon model and P/E or other approved method. Obtain the consensus earnings forecast for the firms Explain how this will affect the expected future price Obtain a consensus interest rate forecast How will this effect bond prices? How will this affect the roll down the yield curve? 9 Financial Analysis Project 4 (100 Points) Background Financial Analysis Exercise #4 will build on exercise #2. This exercise #4 is due by the end of Module 8. In this exercise, you will develop different strategies to hedge and enhance your portfolio returns. In addition, you will select, value and analyze the different options and determine how they will perform in three different markets characterized by different volatility. Requirements In order to meet all the requirements for the financial analysis exercise #4, you are required to complete the following tasks: Develop a strategy to use options or futures to hedge the market value and enhance the profitability of the portfolio. Identify strategies that will work best in each of the following markets: o Flat market low volatility o Rising market moderate volatility o Rising/Falling market high volatility Value the selected options for the strategies with the CBOE calculator found in Module 8. Identify options that are in liquidity markets and describe the characteristics of the market and the brokers trade book. Identify the implied volatility of each option. Identify the Greeks for these options. How will the Greeks affect your decision to purchase these options in the different markets identified above? Additional Instructions The first step is to develop a strategies using options or futures. The hedge strategies could include, selling calls, buy puts, bull spreads, collars or whatever strategy you choose from those available. Explain the strategy and how it will work, for example selling calls. Explain what you hope to accomplish with the strategy and how it will work in the three different markets listed below. The strategy be developed for both stocks and bonds and with options or futures could be used. Discuss strategies that would work best in each of these different markets and explain why it would work. Next, discuss the option the value of the options in the market and those calculated with the CBOE calculator. Calculate the implied volatility of each option in your strategy. In particular, discuss the Greeks and how they help understand the changes in the options value as result of those 5 different factors. Discuss how volatility in particular would affect the value of the options/futures selected. Also, look discuss the open interest in the options/futures and whether that would affect your decision to trade that option. Discuss the effect of open interest and trading volume. Discuss the brokers trade execution book and what affect this could have on filling your option/future orders.

Need help with Question 3 and 4