Name: ____________________________________
Individual Assignment #2
This exam has both written and spreadsheet components; you must turn both pieces in order to receive full credit.
You may consult any of the resources posted on D2L or on the websites we’ve discussed in class but may not discuss this assignment with anyone other than the instructor.
Full credit will only be given for answers that are clearly explained and supported by estimates or data. Take time to explain your reasoning.
The due date for this assignment is Friday, March 6th at midnight. I encourage you to start this assignment well before then, however, so that you leave yourself time to ask questions.
Part 1: Applied Valuation
This assignment asks you to analyze and value a company called Tiffany & Co (TIF). They are a luxury goods retailer that specializes in selling high-end jewelry and other accessories, and are cu
ently in the process of being acquired by LVMH Holdings.
This assignment asks you to use the spreadsheet provided with this assignment to forecast the company’s future cash flows and estimate the company’s value.
1) As before, you have been provided with a model that contains forecasts for the Income Statement & Balance Sheet; you will need to fill in missing pieces of the model in order to answer the questions below.
a) Complete the cash flow and ratio analysis portions of of this model and take a look at what you’ve forecast: is this company generating enough cash flow to pay its dividend, and have you predicted any meaningful changes in their profitability or free cash flow?
) Now complete the cost of capital estimates: what are your estimates for the Cost of Equity, the Cost of Debt, and the Weighted Average Cost of Capital (WACC) based on the assumptions in this model?
c) You are now ready to complete the valuation section: use your free cash flow and cost of capital estimates to estimate the value of this company. How much should we be willing to pay for one share of Tiffany stock based on the assumptions in this model?
2) Exploring different possibilities: you will need to experiment with the assumptions in this model in order to answer the questions below.
a) Tiffany was trading at a share price of $90 prior to LVMH’s offer to acquire it last October: what assumptions would
ing your estimated value to that level?
[ie: what do you think investors at the time were expecting?]
) LVMH has agreed to acquire all of Tiffany’s stock at a price of $135 per share: a 50% premium to the share price prior to the announcement.
What assumptions could you build into this model that would
ing your estimated value to this level, and do you think they are plausible? Discuss whether you think LVMH is paying a reasonable price for Tiffany.
c) It is worth noticing that this model assumes Tiffany can maintain an ROIC above its WACC forever: how does this assumption affect our estimate, and do you think it is a reasonable expectation in this case? Explain your reasoning.
d) Build your own assumptions into this model and decide what YOU think this company is worth: how much do YOU think we should be willing to pay for 1 share of Tiffany’s stock, and why?
Name: _____________________________________
Discussion Brief #2: Forecasting Exercises
The following assignment asks you to work through some of the elements of our forecasting problem so that we can discuss how to build a set of connected forecasts next week.
It is meant to be used with the spreadsheet that accompanies this assignment, but may also be worked by hand using the data in Appendix 1&2.
Questions:
1) One of the most important aspects of financial modeling is understanding how the financial statements connect together.
This exercise asks you to use information from both the income statement & balance sheet to build a cash flow forecast.
a) Use the forecasts provided for 2020 to estimate this company’s operating cash flow using the formula: Operating Cash Flow = EBIT * (1-t) + Depreciation & Amortization.
Show your work below.
) Use the forecasts provided for 2020 to estimate this company’s investment spending, by calculating both Net Working Capital Investment & Capital Investment.
Show your work below.
c) Use your answers to questions (1a) & (1b) to estimate Free Cash Flow to the Firm
in 2020 and comment on whether this firm appears to need any outside financing.
Show your work below.
d) Now estimate Cash Paid to Creditors: what are this firm’s after-tax interest expenses, and how has Net Debt Issued (or Retired) affected its cash flow?
Show your work below.
e) Use your answers to questions (1c) & (1d) to estimate Free Cash Flow Available to Shareholders in 2020.
Show your work below.
2) Connecting the model:
Notice that you now have a decision to make! There are three things that this firm can do with Free Cash Flow Available to Shareholders: it can pay it out as a dividend; use it to buy back shares; or save it for later.
a) First, notice what happens in this model if their firm chooses NOT to pay out a dividend and also chooses NOT to buy back any stock.
i) How will this assumption affect our forecast for the firm’s cash balance, and
how will this affect our balance sheet projections for 2020?
ii) How will this assumption affect our forecast for shareholder’s equity, and can you explain WHY it has or has not changed from year to year?
) Now let’s look at what happens if this company pays out 100% of FCF Available to Shareholders as a dividend.
i) How will this assumption affect our forecast for the firm’s cash balance, and
how will this affect our balance sheet projections for 2020?
ii) How will this assumption affect our forecast for shareholder’s equity, and can you explain WHY it has or has not changed from year to year?
c) Now let’s look at what happens if this company uses 100% of FCF Available to Shareholders to buy back stock.
i) How will this assumption affect our forecast for the firm’s cash balance, and
how will this affect our balance sheet projections for 2020?
ii) How will this assumption affect our forecast for shareholder’s equity, and can you explain WHY it has or has not changed from year to year?
iii) How will this assumption affect our forecast for the firm’s share count, and how will it affect our forecasts for both Earnings Per Share & the Dividend Per Share?
Note: you may assume that this firm can buy back its shares at a market price of $50 per share.
3) Take a minute to look at what this model predicts:
a) Have we predicted that this firm’s ROIC will change or stay the same?
Use the DuPont Formulas to explain what we’ve predicted about this firm’s asset efficiency, profit margins, and tax rate.
) Does this firm have the ability to increase its dividend if it wants to?
c) What is this firm’s Times Interest Earned Ratio (Operating Profit/Interest Expense), and are we predicting that its credit quality will change or stay the same?
d) What do YOU think this firm ought to do with its free cash flow, and why?
Note: We will be using the concepts in this exercise to build forecasts during class this week. Feel free to note any questions that you have about how we connect our projections in different financial statements here.
Appendix 1: Income Statement & Balance Sheet Forecasts
Appendix 2: Building Cash Flow Forecasts
Please submit this assignment to D2L when it is complete.
Actual Forecast
INCOME STATEMENT ($m XXXXXXXXXX
Revenues 500.0 525.0
Operating Profit 125.0 131.3
Interest Expense 25.0 25.0
Pretax Profit 100.0 106.3
Income Taxes 20.0 21.3
Net Income 80.0 85.0
average tax rate 20% 20%
Average shares outstanding (m) 20.0 20.0
Earnings Per Share 4.00
Dividend Per Share 1.50
Key Non-GAAP Measures
EBIT 125
EBITDA 185
BALANCE SHEET ($m XXXXXXXXXX
Key Non-GAAP Measures
Cash & Cash Equivalents 50.0 50.0
Net Working Capital (Excluding Cash) 150.0 165.0
Total Net Working Capital 200.0 215.0
Gross PP&E 1,200.0 1,300.0
Accumulated Depreciation (400.0) (465.0)
Net Property, Plant & Equipment 800.0 835.0
TOTAL: Net Assets 1,000.0 1,050.0
Interest Bearing Debt 475.0 500.0
Common Shareholder's Equity 525.0
Invested Capital (est) 1,000.0
ActualForecast
INCOME STATEMENT ($m XXXXXXXXXX
Revenues 500.0 525.0
Operating Profit 125.0 131.3
Interest Expense 25.0 25.0
Pretax Profit 100.0 106.3
Income Taxes 20.0 21.3
Net Income 80.0 85.0
average tax rate 20% 20%
Average shares outstanding (m) 20.0 20.0
Earnings Per Share 4.00
Dividend Per Share 1.50
Key Non-GAAP Measures
EBIT 125
EBITDA 185
BALANCE SHEET ($m XXXXXXXXXX
Key Non-GAAP Measures
Cash & Cash Equivalents 50.0 50.0
Net Working Capital (Excluding Cash) 150.0 165.0
Total Net Working Capital 200.0 215.0
Gross PP&E 1,200.0 1,300.0
Accumulated Depreciation (400.0) (465.0)
Net Property, Plant & Equipment 800.0 835.0
TOTAL: Net Assets 1,000.0 1,050.0
Interest Bearing Debt 475.0 500.0
Common Shareholder's Equity 525.0
Invested Capital (est) 1,000.0
CASH FLOW [Non-GAAP] XXXXXXXXXX
Step 1: Estimate Operating Cash Flow
EBIT * (1-tax rate) 100.0
Plus Depreciation & Amortization 60.0
Operating Cash Flow 160.0
Step 2: Estimate Investment Spending
Net Working Capital Investment (10.0)
Capital Investment (CAPX) (90.0)
Total Investment Spending (100.0)
Step 3: Estimate FCF to the Firm 60.0
Step 4: Estimate Cash paid to Creditors
Net Debt Issued (or Retired) -
Less After-Tax Interest Expenses (25.0)
Cash From Creditors (25.0)
Step 5: Estimate FCF Available to Shareholders 35.0
Step 6: Estimate Cash paid to Shareholders
Net Equity Issued (or Repurchased) -
Less Dividends on Common Stock (30.0)
Cash From Shareholders (30.0)
Step 7: Change in Cash Balance 5.0
CASH FLOW [Non-GAAP] XXXXXXXXXX
Step 1: Estimate Operating Cash Flow
EBIT * (1-tax rate) 100.0
Plus Depreciation & Amortization 60.0
Operating Cash Flow 160.0
Step 2: Estimate Investment Spending
Net Working Capital Investment (10.0)
Capital Investment (CAPX) (90.0)
Total Investment Spending (100.0)
Step 3: Estimate FCF to the Firm 60.0
Step 4: Estimate Cash paid to Creditors
Net Debt Issued (or Retired) -
Less After-Tax Interest Expenses (25.0)
Cash From Creditors (25.0)
Step 5: Estimate FCF Available to Shareholders 35.0
Step 6: Estimate Cash paid to Shareholders
Net Equity Issued (or Repurchased) -
Less Dividends on Common Stock (30.0)
Cash From Shareholders (30.0)
Step 7: Change in Cash Balance 5.0
Name: _____________________________________
Discussion Brief #3: A Full DCF
This assignment builds on the work that we did last week, and asks you to turn a set of income statement & balance sheet forecasts into a full discounted cash flow model.
It is meant to be used with the spreadsheet that accompanies this assignment; you will need to fill in the cash flow section and complete the cost of capital estimates in order to produce an estimate of value.
Questions:
1) In order to answer this question, you will need to complete the cash flow section of the spreadsheet provided.
The easiest way to answer these questions is simply to copy and paste your forecasts from the spreadsheet. (The “paste special” command will allow you to control your formatting.)
This question asks you to use the forecasts provided for both the income statement & balance sheet to build a set of cash flow forecasts for the years XXXXXXXXXX.
a) Use the income statement & balance sheet forecasts provided for XXXXXXXXXXto estimate expected Free Cash Flow to the Firm, and show your full calculation here.
Your answer should contain all 5 years of forecasts
) Estimate Cash Paid to Creditors for XXXXXXXXXX: what are this firm’s after-tax interest expenses, and how has the use of debt financing affected your forecast?
Your answer should contain all 5 years of forecasts.
c) Stop
iefly