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
     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%