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. 1. 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 % 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. 2. 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 % for 9 years, % for 6 years and 2 % 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? 3. 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? 4. 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 % 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, 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%? 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.
Hide
