Fin代写 | Excel模型代写 | Financial Modeling

Financial Modeling
Spring 2020
Prof. Salgado
Final Project:

• Select and do your final project from one of the companies below
  o Create a 5-year projections model for your case study company and value it using discounted cash flow
  analysis
   At a minimum, you have to have the following schedules in your model
   Income Statement (IS)
   Balance Sheet (BS)
   Cash Flow Statement (CFS)
   Statements / schedules can all be on one Excel sheet or in multiple sheets (but same workbook)
   You can include other schedules (but it’s not mandatory) such as working capital, depreciation, equity,
  debt & interest
   HINT: take one of the templates from class (WeCo sample company or Tapestry) and replace financials
  with your case study company
   At least one historical year of income statement and balance sheet data (CFS historical data is not
  necessary). You will find the latest 10K on your own
   You will be making Make your own assumptions on
   Growth rates (e.g. sales)
   Margins (e.g. gross profit, SG&A)
   Others (e.g. capex)
   On a separate sheet (Excel or Word) defend those assumptions
   The goal of the model is to get to unlevered free cash flows and ultimately value those cash flows to
  obtain the company’s firm value (enterprise value)
  o The DCF valuation
   Should be presented as a range of values which you feel (based on all your assumptions) is the
  company’s intrinsic value (value derived from company’s growth prospects, risk profile etc.)
   Consider doing sensitivity analysis with data tables (not mandatory but helpful)
   Feel free to use the WACC range (below) or calculate WACC on your own
   Formulate an assumption for growth rate into perpetuity (if using perpetuity growth rate
  methodology) or an EBITDA multiple assumption (if using exit multiple methodology)
   HINT: move / copy sheet (Home, Format, Move or Copy Sheet) the DCF template into the case study
  company’s Excel model and re-link as appropriate
  o Deliverables (email me)
   Excel model including at a minimum
   Income Statement
   Balance Sheet
   Cash Flow
   Discounted Cash Flow analysis
   Range of enterprise values
   A few points defending your assumptions
   Your opinion on weather case study company is fairly valued, overvalued, undervalued
  compared to its market price as of valuation date (sometime between April 21, 2020 and
  May 1, 2020)
   10K used for the model
  FINAL PROJECT TO BE SUBMITTED BY 23:59 (THAT’S 11:59PM) on Friday MAY 1, 2020
  Company Ticker Simple WACC Range
  Capri Holdings Limited CPRI 9.0% 10.0%
  Estee Lauder Companies Inc. EL 8.0% 9.0%
  Guess? Inc GES 6.5% 7.5%
  Kering KER 5.5% 6.5%
  L Brands Inc. LB 8.0% 9.0%
  PVH Corp. PVH 8.5% 9.5%
  Ralph Lauren Corporation RL 9.5% 10.5%
  Vera Bradley VRA 7.5% 8.5%
  VF Corp VFC 9.5% 10.5%