Question: Please note: The intention of assignment one is to apply what we covered in class to real-world problem-solving. For this reason, it is meant to be more challenging and I strongly suggest you set aside enough time for this assignment. Make sure you answer all the questions in a clear and legible manner. Show your work clearly and step-by-step if you want to receive partial credits. Please name your Excel work as YourFirstName_LastName_Assignment_One.xlsx format and submit your Excel work via Canvas. No late assignments will be accepted. This assignment is to be done individually. Please bear in mind the Honor Code of the University.

No. 1 Erin O'Reilly was recently employed by the human resources department of a moderate-sized engineering firm. Management is considering the adoption of a defined-benefit pension plan in which the firm will pay 75 percent of an individual's last annual salary if the employee has worked for the firm for 25 years. The amount of the pension is to be reduced by 3 percent for every year less than 25, so that an individual who has been employed for 15 years will receive a pension of 45 percent of the last year's salary [75 percent - (10 x 3%)]. Pension payments will start at age 65, provided the individual has retired. There is no provision for early retirement. Continuing to work after age 65 may increase the individual's pension if the person has worked for less than 25 years or if the salary were to increase. One of the first tasks given O'Reilly is to estimate the amount that the firm must set aside today to fund pensions. While management plans to hire actuaries to make the final determination, the managers believe the exercise may highlight some problems that they will want to be able to discuss with the actuaries. O'Reilly was instructed to select two representative employees and estimate their annual pensions and the annual contributions necessary to fund the pensions. O'Reilly decided to select Arnold Berg and Vanessa Barber. Berg is 58 years old, has been with the firm for 27 years, and is earning $34,000. Barber is 47, has been with the firm for 3 years, and earns $42,000 annually. O'Reilly believes that Berg will be with the firm until he retires; he is a competent worker whose salary will not increase by more than 4 percent annually, and it is anticipated he will retire at age 65. Barber is a more valuable employee, and O'Reilly expects Barber's salary to rise at least 7 percent annually in order to retain her until retirement at age 65. To determine the amount that must be invested annually to fund each pension, O'Reilly needs (in addition to an estimate of the amount of the pension) an estimate of how long the pension will be distributed (i.e., life expectancy) and how much the invested funds will earn. Since the firm must pay an interest rate of 8 percent to borrow money, she decides that the invested funds should be able to earn at least that amount. While O'Reilly believes she is able to perform the assignment, she has come to you for assistance to help answer the following questions.

a. If each individual retires at age 65, how much will his or her estimated pension be? (10 points)

b. Life expectancy for both employees is 15 years at age 65. If the firm buys an annuity from an insurance company to fund each pension and the insurance company asserts it is able to earn 9 percent on the funds invested in the annuity, what is the cost or the amount required to purchase the annuity contracts? (10 points)

c. If the firm can earn 8 percent on the money it must invest annually to fund the pension, how much will the firm have to invest annually to have the funds necessary to purchase the annuities? (10 points)

No. 2 Nike had sales of $25.3 billion in 2012. Suppose you expect its sales to grow at a rate of 10% in 2013, but then slow by 1% per year to the long-run growth rate that is characteristic of the apparel industry 5% by 2018. Based on Nike's past profitability and investment needs, you expect EBIT to be 10% of sales, increases in net working capital requirements to be 10% of any increase in sales, and capital expenditures to equal depreciation expenses. If Nike has $3.3 billion in cash, $1.2 billion in debt, 893.6 million shares outstanding, a tax rate of 24%, and a weighted average cost of capital of 10%,

a. What is your estimate of the value of Nike's stock in early 2013? (10 points)

b. Suppose you believe Nike's initial revenue growth rate will be between 7% and 11% (with growth always slowing linearly to 5% by year 2018). What range of prices for Nike stock is consistent with these forecasts? (10 points)

c. Suppose you believe Nike's initial revenue EBIT margin will be between 9% and 11% of sales. What range of prices for Nike stock is consistent with these forecasts? (5 points)

d. Suppose you believe Nike's weighted average cost of capital is between 9.5% and 12%. What range of prices for Nike stock is consistent with these forecasts? (5 points)

e. What range of stock prices is consistent if you vary the estimates as in parts (b), (c), and (d) simultaneously? (10 points)

f. Use Data Table Functionality to examine the sensitivity of your valuation to both the WACC and the EBIT Margin. Examine WACC ranging from 9.5% to 12% in increments of 0.25% and EBIT margin ranging from 9% to 11% in increments of 0.25%. Use the same input figures as provided in the original question. (10 points)

No. 3 Colgate-Palmolive Company has just paid an annual dividend of $0.96. Analysts are predicting an 11% per year growth rate in earnings over the next five years. After then, Colgate's earnings are expected to grow at the current industry average of 5.2% per year. If Colgate's equity cost of capital is 8.5% per year and its dividend payout ratio remains constant, for what price does the dividend-discount model predict Colgate stock should sell? (5 points)

No. 4 Gillette Corporation will pay an annual dividend of $0.65 one year from now. Analysts expect this dividend to grow at 12% per year thereafter until the fifth year. After then, growth will level off at 2% per year. According to the dividend-discount model, what is the value of a share of Gillette stock if the firm's equity cost of capital is 8%? (5 points)

No. 5 In practice, a common way to value a share of stock when a company pays dividends is to value the dividends over the next five years or so, then find the "terminal" stock price using a benchmark PE ratio. Suppose a company just paid a dividend of $1.15. The dividends are expected to grow at 20 percent over the next five years. In five years, the estimated payout ratio is 40 percent and the benchmark PE ratio is 21. What is the target stock price in five years? What is the stock price today assuming a required return of 12 percent on this stock? (10 points)