The date was July 24, 2012 and Nike, Inc. had just released its latest annual report for the fiscal year ended May 31, 2012. Working as an analyst at the private equity firm of Diamond Capital Partners, you have received word that the firm, flush with cash, is interested in taking Nike private. Your bosses have asked you to put together a discounted cash flow valuation model based on Nike's most recent financial statements to help assess the company's current value. A colleague has compiled the necessary data for the assignment and saved it in an Excel workbook named "PROJ_Data - FIN360." The workbook includes Nike's income statement and balance sheet, equity data (current share price and number of shares outstanding), and historical dividend per share data.

The senior manager overseeing the potential acquisition of Nike has requested that you make a sales driven model to project pro forma financial statements for the years 2013-2017.[1] She has also provided the following guidance:

• Annual sales growth during this period is expected to equal the average annual sales growth in 2011 and 2012.
• Current assets, current liabilities, net fixed assets, other assets, other liabilities, cost of goods sold, and selling and administrative expense are all modeled as a percentage of each year's sales.
• The ratio of each of these items to sales is expected to equal the average ratio across 2011 and 2012, e.g. COGS/Sales equaled 52.7% in 2011 and 54.9% in 2012. Going forward, we expect COGS/Sales to equal 53.8% (=(52.7%+54.9%)/2).
• Annual depreciation expense equals x% of the average cost of the fixed assets on the books during the year, where x is the percentage based on 2012 data.
• Interest income equals y% of the average book value of cash + marketable securities during the year, where y is the percentage based on 2012 data.
• Interest expense equals z% of the average of the firm's total debt (short-term debt + long-term debt) during the year, where z is the percentage based on 2012 data. z% also reflects the firm's cost of debt during this period.
• The firm's tax rate is expected to equal the firm's average tax rate across 2011 and 2012.
• During the period 2010-2012, Nike returned capital to shareholders in the form of both dividends and share repurchases. Going forward the firm does not expect to repurchase anymore shares.
• The dividend payout ratio in 2013 is expected to equal the 2012 dividend payout ratio (=2012 dividend per share x shares outstanding / 2012 net income).
• For the years 2014-2017, dividend growth is expected to be constant and equal to the average dividend growth rate during the period 2008-2012.
• To value cash flows in years after 2017, assume free cash flows grow at a constant rate of 5% starting in 2018.
• Nike expects to pay off all short-term debt in 2013 and not incur any additional short-term debt in the future.
• Nike expects to repay 10% of the beginning balance of long-term debt in each year and not issue any additional long-term debt.
• Nike does not expect to issue or repurchase any stock during this period.
• Annual capital expenditures equal the change in fixed assets, at cost, each year
• Use the Gordon dividend growth model to estimate the firm's cost of equity.
• Use book value of total debt (short-term + long-term) as value of debt in the WACC calculation.
• Use end-of-year discounting to value the firm's cash flows.
• Use the cash + marketable securities account as the "plug" in your model.

Your model should calculate both Nike's firm value and its per-share equity value.

In addition to your base model, conduct three sensitivity analyses to analyze the drivers of Nike's value. First, make a two-way data table that calculates Nike's per-share equity value for sales growth rates ranging from 4% to 20% and free-cash flow growth rates from 1% to 10%. Second, create a one-way data table that calculates Nike's WACC for dividend growth rates ranging from 6% t0 15%. Third, create a one-way data table that calculates Nike's per-share equity value for dividend growth rates ranging from 6% to 15%. In all data tables, use a 1% increment for the input values.

Along with your model, draft a brief memo that summarizes your findings. In particular, use your sensitivity analyses to identify and assess the conditions under which you would recommend acquiring Nike. Assume the per-share takeover price of Nike would include a 20% premium, i.e. the takeover price is 120% of Nike's stock price as of July 24, 2012. Include in the memo any assumptions you made in setting up your model.

[1] For purposes of your model, fiscal year ended May 31, 2012 is considered year 2012.