I am working on a excel two input data table and having issues getting it work properly. It's Question 2D on the uploaded documents. One is the word version with question, 2nd is the excel version. I need to figure out if the input variables are incorrect and need to ensure that it is set up correctly. It needs to properly recalculate when input variables change.

Finance 5405 - EMBA17 Group Problem Set #1 Financial Management J. Houston Group Members: Team 5 - David Costa, Joe Forkum, Eric Malnove, Robbin Seago, Chris Wynn 1. You are in the process of obtaining a 20-year fixed rate mortgage on a house that you are buying. The mortgage amount is $250,000 and the mortgage rate is 6%. (This is the nominal annual rate.) Assume that all monthly payments are made at the end of every month. a. b. c. d. e. What is the monthly payment on the mortgage? (2 points) $1,791.08 What percentage of your payments the first year goes toward repayment of principal? (2 points) 31.05% After 2 years (24 months), what will be the remaining balance on your mortgage? (2 points) $236,239.34 What is the effective annual rate of the mortgage? (2 points) 6.17% Another bank has calculated that based on your financial situation, your household can obtain a mortgage with a maximum payment of $1,500 a month. However, this bank will offer you a 25-year fixed-rate mortgage with a nominal annual rate of 6.6%. Again, assume that all payments are made at the end of the month. Given these terms, what is the maximum amount that you can borrow from this bank? (2 points) $220,112.69 2. You have recently been hired as a consultant for a personal financial planning firm. One of your first projects is creating a retirement plan for a couple, Lauren and Steve LaGrange. They have just celebrated their 50th birthdays and after paying for their children's education, they have decided to get serious about saving for retirement. Lauren and Steve hope to retire 15 years from now (on their 65th birthdays), and they expect to live until age 85. Their hope is to be able to withdraw $120,000 a year from their retirement account - the first withdrawal will occur on their 65th birthdays, and the 20th and final withdrawal will occur on their 84th birthdays. After their final withdrawal, the account is expected to have a zero value (i.e., they don't expect to have any remaining funds left for their children's inheritance). Lauren and Steve currently have $300,000 saved in a retirement account, which consists of a portfolio of mutual funds that is expected to produce an annual return of 7%. In order to accomplish their goals, they would like to deposit an equal annual amount into their account starting one year from today (on their 51st birthdays) and continue to make those deposits through age 65. (Again, the account has an expected annual return of 7%.) Thus, they will make 15 annual end-of-year deposits to this account. a. How much do Lauren and Steve need to contribute to the account at the end of each of the next 15 years in order to accomplish their goals? (5 points) 1 b. c. d. e. $17,651.79 If they wanted to leave their children $500,000 for inheritance when they die at age 85, how much would they need to contribute to the account at the end of each of the next 15 years? (Assume everything else stays the same.) (3 points) $22,793.63 If they instead expected to earn only 5% a year from their mutual funds, how much would they need to contribute to the account at the end of each of the next 15 years? (Continue to assume that they want to have $500,000 available for inheritance when they die at age 85.) (3 points) $49,133.55 Lauren and Steve realize that there are a lot of variables in their retirement plan. The two variables that they are particularly interested in are the expected return of their mutual funds and the amount they have available for inheritance. Create in Excel a two-input Data Table that tests the sensitivity of their annual deposit amount by varying the expected returns from 3% to 11% in 1% increments and varying the inheritance level from 0 to $4,000,000 in $500,000 increments. The data table should be constructed with the expected returns shown on the side of the table and the amount available for inheritance shown across the table. [Hint: An Excel file (in xlsx format) explaining how to create a Data Table is contained in the Calculator Tutorials & Excel Tools folder on the class web page.] You MUST set up a correctly working Excel data table using Excel's Data Table feature to receive credit. When correctly set up, an Excel Data Table will automatically recalculate when input variables are changed. (5 points) NOT FINISHED Lauren and Steve have one last concern. They recognize that the value of their $120,000 annual withdrawals during retirement will steadily decline because of expected inflation. Assume that they want to have the value of these withdrawals increase by 3% a year during retirement to account for expected inflation. In other words, they want to withdraw $120,000 at age 65, $123,600 at age 66, and 123,600 1.03 at age 67, and etc. Going back to the other original assumptions (7% return and no expected inheritance), how much would they need to contribute to the account at the end of each of the next 15 years in order to meet this revised goal which protects them against rising inflation? Set up this problem using Excel. As a guide, it will be helpful to refer to the Growing Annuity Example spreadsheet that we went over in class during Visit 1. (6 points) $30,725.04 3. Last year, Powers Auto Parts Company (PAPC) had net operating profit after-taxes (NOPAT) of $750 million. Its EBITDA was $1,800 million and net income amounted to $300 million. During the year, PAPC made $225 million in net capital expenditures (remember that net capital expenditures equal gross capital expenditures less depreciation), and its net operating working capital increased by $10 million. Finally, PAPC's finance staff has concluded that the firm's total after-tax capital costs were $575 million and its tax rate is 40%. Assume that the company does not have any amortization charges. Based upon this information, answer the following four questions. (3 points each) 2 a. b. c. d. What is the company's depreciation expense? What is the company's interest expense? What is the company's free cash flow? What is the company's EVA? $550 MM $750 MM $515 MM $175 MM 4. Last year, Isola Manufacturing Industries had an ROE = 10% and its ROA = 7.5%. Isola's total assets equal total debt plus common equity (i.e., there is no preferred stock). Furthermore, we know that the company's profit margin is 4%. Answer the following questions on the basis of this information. (4 points each) a. b. What is the company's debt ratio (as measured by debt to total assets)? 25% What is the company's total assets turnover? 1.875 5. In today's market you observe the following yield curve for government securities: Maturity 1 year 2 years 3 years 5 years 7 years 8 years 10 years Yield 1.00% 1.20% 1.60% 2.75% 4.20% 5.20% 7.00% Assume that the pure expectations hypothesis holds (i.e., the maturity risk premium = 0). What does the market expect will be the interest rate on 3-year securities five years from now? (6 points) 9.41% 6. You are considering an investment in two different bonds. One bond matures in six years and has a face value of $1,000. The bond pays an annual coupon of 8.5% and has a 7% yield to maturity. The other bond is a 5-year zero coupon bond with a face value of $1,000 and also has a yield to maturity of 7%. a. b. c. What is the price of each bond? (2 points) Bond 1 = $1,071.50 Bond 2 = $712.99 What is the duration of each bond? (3 points) Bond 1 = 4.98 years Bond 2 = 5 years If the yield to maturity of each bond were to immediately increase to 9%, what would be the percentage change (including the correct sign) in the price of each bond (from the price found in part a)? (2 points) 3 d. e. Bond 1 = -8.77% Bond 2 = -8.84% If the yield to maturity of each bond were to immediately decrease to 5%, what would be the percentage change (including the correct sign) in the price of each bond (from the price found in part a)? (2 points) Bond 1 = 9.91% Bond 2 = 9.89% Assume that you plan on holding the coupon bond for six years and reinvesting all of the coupons when they are received at the going interest rate (which is the yield to maturity). Assume that after the zero matures you invest in a 1-year security that earns the going interest rate. (10 points) i. Set up a table where you show what happens to the value of each investment (zero and coupon bond) over time if the yield to maturity remains at 7%. Specifically, show what the cumulative value of each investment (including the value of the reinvested coupons for the coupon bond) would be at the end of each of the next six years. For example, at the end of Year 1, you would calculate the value of the coupon bond (with one year less remaining until maturity) and you would receive the interest coupon payment. However, no interest would be earned on that interest coupon payment in Year 1. (However, that coupon payment will remain in your account and earn interest in the years that follow.) The sum of these 2 amounts would be the cumulative value for the coupon bond at the end of Year 1. ii. Set up a table where you show what happens to the value of each investment (zero and coupon bond) over time if the yield to maturity immediately and permanently increases to 9%. Specifically, show what the cumulative value of each investment (including the value of the reinvested coupons for the coupon bond) would be at the end of each of the next six years. 4 iii. Set up a table where you show what happens to the value of each investment (zero and coupon bond) over time if the yield to maturity immediately and permanently decreases to 5%. Specifically, show what the cumulative value of each investment (including the value of the reinvested coupons for the coupon bond) would be at the end of each of the next six years. iv. Plot on two graphs (one for the coupon bond and one for the zero) the cumulative value of the investment over the next six years under each of the three scenarios outlined above. 5 v. Roughly speaking, after how many years would the value of the investment be the same regardless of what happens to interest rates? What can explain this? NOT FINISHED 7. You are a small money manager managing $20,000,000 in assets. Your investment portfolio consists of 15% T-bills (with an estimated beta = 0), 20% bonds (with an estimated beta = 0.60), 30% mid-cap stocks (with an estimated beta = 1.00), and 35% growth stocks (with an estimated beta = 1.10). 6 a. b. The risk-free rate, rRF, is 2.5%. The market risk premium, (rM - rRF), is 6%. What is the required rate of return on your investment portfolio? (3 points) 7.33% If you switch $800,000 out of T-bills and invest $500,000 of it in growth stocks and $300,000 of it in mid-cap stocks, what would be the required rate of return on your portfolio? (3 points) 7.59% 8. A recent Value Line report for McDonald's Corporation (its ticker symbol = MCD) is provided with this assignment on the e-Learning Canvas site. Based on the information in this report, use the nonconstant dividend growth model to estimate the intrinsic value of the stock. (Please do not use an updated Value Line report to work this problem.) More specifically: a. b. c. Based on Value Line's estimate of beta, what is the required return on McDonald's stock? Use the CAPM, and assume that the risk-free rate is 2.75% and the market risk premium (rM - rRF) is 5%. (2 points) 6.25% Assume that today is January 1, 2016 and that at t = 1 the company will pay the dividend per share that Value Line is forecasting for 2016. (That is, assume that this forecasted 2016 dividend is D1, and that this is the first cash flow you receive as an investor. This information is provided in the columns designated by year.) Now, look on the left-hand side of the report, note the section where Value Line's analyst forecasts annual growth rates - in particular, look at the estimated dividends growth rate from '12-'14 to '19-'21. Assume that this is the dividend growth rate for the three years after 2016 (2017-2019, which corresponds to Years 2, 3, and 4). Assume that after 2019 (after Year 4), the dividend grows at a long-run constant growth rate of 3% a year. (i) Given these assumptions, what is your estimate of the stock's intrinsic value? (4 points) $116.95 (ii) Compared to its current stock price and your analysis above, would you conclude that the stock is undervalued or overvalued? (1 point) Overvalued Set up a simple Excel data table where you show how the estimated intrinsic value varies as the long-run growth rate varies over the following range (1.25%, 1.50%, 1.75%, 2.00%, 2.25%, 2.50%, 2.75%, 3.00%, 3.25%, 3.50%, 3.75%, and 4.00%) - assuming everything else stays constant. You MUST use Excel's Data Table feature to receive credit for this question and the Data Table must be working correctly. When correctly set up, an Excel Data Table will automatically recalculate when input variables are changed. Refer to the Calculator_Excel Tools subdirectory under the Files page on the Canvas website. (3 points) 7 9. A stock market analyst is evaluating the common stock of Morris Manufacturing Products Inc. (MMPI). She estimates that the company's operating income (EBIT) for the next year will be $500 million. Furthermore, she predicts that MMPI will require $300 million in capital expenditures next year. The depreciation expense for next year is expected to be $60 million and changes in net operating working capital are expected to be $20 million. Free cash flow is expected to grow at a constant annual rate of 5% a year and the company's WACC is 9%. The company has $275 million of debt (market value equals book value), $125 million of preferred stock (market value equals book value), and has 50 million shares of common stock. The firm's tax rate is 40%. a. b. What is the estimated value of free cash flow the first year? (That is, what is FCF1?) (3 points) $40 MM Using the free cash flow valuation method, what is its expected stock price today? (4 points) $12.00 8 present value of lum 300,000 annuity payments ($22,793.63) return 7% number of payments 15 fv at 65th birthday ($1,400,491.21) pv at 65th birthday $1,400,491.21 annuities due withdra 120,000 number of withdrawal 20 expected rate 7% fv at 85 bd 500,000 annual depo expected return ($22,793.63) 3% 4% 5% 6% 7% 8% 9% 10% 11% 0 amount available for inheritance 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 4,000,000 present value of lum 300,000 annuity payments ($26,334.95) return 7% number of payments 15 fv at 65th birthday ($1,489,480.93) pv at 65th birthday $1,489,480.93 annuities due withdra 120,000 number of withdrawal 20 expected rate 7% fv at 85 bd 500,000 annual depo expected return ($26,334.95) 3% 4% 5% 6% 7% 8% 9% 10% 11% 0 amount available for inheritance 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 4,000,000 present value of lum 300,000 annuity payments ($22,793.63) return 7% number of payments 15 fv at 65th birthday ($1,400,491.21) pv at 65th birthday $1,400,491.21 annuities due withdra 120,000 number of withdrawal 20 expected rate 7% fv at 85 bd 500,000 annual depo expected return ($22,793.63) 3% 4% 5% 6% 7% 8% 9% 10% 11% 0 amount available for inheritance 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 4,000,000 present value of lum 300,000 annuity payments ($26,334.95) return 7% number of payments 15 fv at 65th birthday ($1,489,480.93) pv at 65th birthday $1,489,480.93 annuities due withdra 120,000 number of withdrawal 20 expected rate 7% fv at 85 bd 500,000 annual depo expected return ($26,334.95) 3% 4% 5% 6% 7% 8% 9% 10% 11% 0 amount available for inheritance 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 4,000,000