individual assignment 2 is due on the 6th and the discussion briefs are do by the 15th of march
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 currently 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? b) 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 bring your estimated value to that level? [ie: what do you think investors at the time were expecting?] b) 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 bring 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. b) 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? b) 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. b) 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 INCOMESTATEMENT($m) 2019 2020 Revenues 500.0 525.0 OperatingProfit 125.0 131.3 InterestExpense 25.0 25.0 PretaxProfit 100.0 106.3 IncomeTaxes 20.0 21.3 NetIncome 80.0 85.0 averagetaxrate 20% 20% Averagesharesoutstanding(m) 20.0 20.0 EarningsPerShare 4.00 DividendPerShare 1.50 KeyNon-GAAPMeasures EBIT 125 EBITDA 185 BALANCESHEET($m) 2019 2020 KeyNon-GAAPMeasures Cash&CashEquivalents 50.0 50.0 NetWorkingCapital(ExcludingCash) 150.0 165.0 TotalNetWorkingCapital 200.0 215.0 GrossPP&E 1,200.0 1,300.0 AccumulatedDepreciation (400.0) (465.0) NetProperty,Plant&Equipment 800.0 835.0 TOTAL:NetAssets 1,000.0 1,050.0 InterestBearingDebt 475.0 500.0 CommonShareholder'sEquity 525.0 InvestedCapital(est) 1,000.0 ActualForecast INCOMESTATEMENT($m) 2019 2020 Revenues 500.0 525.0 OperatingProfit 125.0 131.3 InterestExpense 25.0 25.0 PretaxProfit 100.0 106.3 IncomeTaxes 20.0 21.3 NetIncome 80.0 85.0 averagetaxrate 20% 20% Averagesharesoutstanding(m) 20.0 20.0 EarningsPerShare 4.00 DividendPerShare 1.50 KeyNon-GAAPMeasures EBIT 125 EBITDA 185 BALANCESHEET($m) 2019 2020 KeyNon-GAAPMeasures Cash&CashEquivalents 50.0 50.0 NetWorkingCapital(ExcludingCash) 150.0 165.0 TotalNetWorkingCapital 200.0 215.0 GrossPP&E 1,200.0 1,300.0 AccumulatedDepreciation (400.0) (465.0) NetProperty,Plant&Equipment 800.0 835.0 TOTAL:NetAssets 1,000.0 1,050.0 InterestBearingDebt 475.0 500.0 CommonShareholder'sEquity 525.0 InvestedCapital(est) 1,000.0 CASHFLOW[Non-GAAP] 2019 2020 Step1:EstimateOperatingCashFlow EBIT*(1-taxrate) 100.0 PlusDepreciation&Amortization 60.0 OperatingCashFlow 160.0 Step2:EstimateInvestmentSpending NetWorkingCapitalInvestment (10.0) CapitalInvestment(CAPX) (90.0) TotalInvestmentSpending (100.0) Step3:EstimateFCFtotheFirm 60.0 Step4:EstimateCashpaidtoCreditors NetDebtIssued(orRetired) - LessAfter-TaxInterestExpenses (25.0) CashFromCreditors (25.0) Step5:EstimateFCFAvailabletoShareholders 35.0 Step6:EstimateCashpaidtoShareholders NetEquityIssued(orRepurchased) - LessDividendsonCommonStock (30.0) CashFromShareholders (30.0) Step7:ChangeinCashBalance 5.0 CASHFLOW[Non-GAAP] 2019 2020 Step1:EstimateOperatingCashFlow EBIT*(1-taxrate) 100.0 PlusDepreciation&Amortization 60.0 OperatingCashFlow 160.0 Step2:EstimateInvestmentSpending NetWorkingCapitalInvestment (10.0) CapitalInvestment(CAPX) (90.0) TotalInvestmentSpending (100.0) Step3:EstimateFCFtotheFirm 60.0 Step4:EstimateCashpaidtoCreditors NetDebtIssued(orRetired) - LessAfter-TaxInterestExpenses (25.0) CashFromCreditors (25.0) Step5:EstimateFCFAvailabletoShareholders 35.0 Step6:EstimateCashpaidtoShareholders NetEquityIssued(orRepurchased) - LessDividendsonCommonStock (30.0) CashFromShareholders (30.0) Step7:ChangeinCashBalance 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 2020-2025. a) Use the income statement & balance sheet forecasts provided for 2020-2025 to estimate expected Free Cash Flow to the Firm, and show your full calculation here. Your answer should contain all 5 years of forecasts b) Estimate Cash Paid to Creditors for 2020-2025: 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 briefly