Hello , this assignment is to create a mini DCF for Ross Stores. It should not take more than an hour or so with somebody who has experience.
DCF for DISNEY EBITDA Multiple for CV Operating Scenario201820192020202120222023 Sales585896087463613667947046874343 % Sales Growth (Bloomberg for first two years)6.3%3.9%4.5%5.0%5.5%5.5% EBITDA1796317957.819084.020038.221140.322303.0 EBITDA Margin30.7%29.5%30.0%30.0%30.0%30.0% Depreciation and Amortization 28372947.62989.83139.33312.03494.1 D&A/Sales4.8%4.8%4.7%4.7%4.7%4.7% EBIT1512615010.216094.216898.917828.318808.9 EBIT Margin25.8%24.7%25.3%25.3%25.3%25.3% Taxes36003152.13379.83548.83743.93949.9 Tax Rate23.8%21.0%21.0%21.0%21.0%21.0% Unlevered Free Cash Flow201820192020202120222023 EBIAT1152611858.012714.413350.114084.414859.0 Depreciation and Amortization 28372947.62989.83139.33312.03494.1 D&A/Sales4.8%4.8%5.0%5.0%5.0%5.0% Other Non-Cash-585609636668705743 ONC/Sales-1.0%1.0%1.0%1.0%1.0%1.0% Capex(3564)(4565.5)(5152.7)(5410.3)(5707.9)(6021.8) Capex/Sales-6.1%-7.5%-8.1%-8.1%-8.1%-8.1% Less Increase (or Plus Decrease) in NWC**10991157636668705743 NWC/Sales1.9%1.9%1.0%1.0%1.0%1.0% Unlevered Free Cash Flow to the Firm1189812005.511823.812415.013097.813818.2 Discounting of Annual Free Cash Flow201820192020202120222023 Discount Period (Mid-Year Convention)0.51.52.53.54.5 WACC8.9%8.9%8.9%8.9%8.9% PVLS Factor0.960.880.810.740.68 Present Value of Free Cash Flow11504.410404.410031.89718.59415.1 Terminal (Continuing) Value Exit Year EBITDA22303.0 Exit Multiple11.60 Terminal Value258714.9 Discount Factor0.65 Enterprise Value: Cumulative Present Value of Unlevered Free Cash Flow to the Firm51074.2 Present Value of Terminal Value168920.4 % of Enterprise Value76.8% Enterprise Value219994.6 Implied Equity Value Enterprise Value219994.6 Short Term Debt(6184.0) Long Term Debt(19119.0) Operating Lease Obligations(3348.0) Other Long Term Liabilities(10923.0) Preferred Stock0.0 Non-Controlling Interest(4837.0) Cash and Cash Equivalents4017.0 Misc Long Term Assets9563.0 Hidden Assets Implied Equity Value (Market Cap)189163.6 Fully Diluted Shares Outstanding*1521 Implied Price Per Share124.37 Market Price$100.06 SALES GROWTHDisney experienced fluctuations in their Sales Growth during the past 5 years which ranged between -0.89% and 8.37%, and the CAGR for that period is 5.5%. The forecast for 2018and 2019 was taken from Bloomberg, and taking Disney's CAGR and future projects into consideration, an upward trend was applied to reach the indicated CAGR of 5.5%. EBITDA MARGINEBITDA had a 4.3% increase within 2012 and 2016, has remained fairly steady within 30% over the last 3 years, and is projected at 29.5% for 2019.Due to this data, a 30% EBITDA is a good conservatie margin to use for EBITDA for the next 5 years.DEPRECIATION & AMORTIZATIONDepreciation & Amortization has hovered between 4.5% and 4.9% and although it reached 5.0% in 2018, it is projected to return to 4.8%.Therefore, 4.7% was used for future projections.CASHTAXESCash Taxes paid from 2012 to 2017 ranged from 29.7% and 33.2%.However, due to the new tax law, a 21% tax margin was applied for the upcoming years.OTHERNON-CASHOther Non-Cash has hovered closely to 1.0% throughout the years.Therefore ,a 1.0% assumption was used.Comcast's NWC had similar margins. Time Warner had extremely high margins largely from non-cash adjustments.CAPEXDisney's Capex has fluctuated between 6.8% to 9% these past 5 years and is projected to become 6.5% in 2019. Therefore, given the past margin and projection, a Capex of 6.1% was applied. Comcast's Capex is much higher than Disney,currently at 13.5%, due to having largerinvestment projects ever since their acquisition of Universal in 2012. However, Disney's Capex-to-Depreciation ratio is still higher than Comcast. Time Warner's Capex is much lower than Disney due to the company not investing in any major projects. Time Warner's Capex is less than their depreciation.NWCNWC has fluctuated through the past years and is projected to increase to 1.9% in 2019, remaining within the normal historical range for this company. Therefore,an assumption of 1.0% was applied.WACCDisney's current WACC as per Bloomberg is 8.9%.Terminal ValueThe Enterprise Value EBITDA from the comparable comps valuation was used for this DCF valuation. The EBITDA multiple of 11.60 accounts for 77% of Enterprise Value and results in an Implied Price Per Share of $124.37, relatively close to the current market price of $100.06. The Gordon Model was also computed for comparison. The terminal value accounted for 81% of Enterprise Value, slightly greater than the EBITDA Multiple. However, the model resulted in an Implied Price Per Share of $162.43. DCF Mini Assignment DUE Monday November 9th , by midnight EST, submitted electronically to aheuson@miami.edu. Please turn in an EXCEL file named with your last name and the name of your firm. Make sure your name and the name of your firm appears at the top of EVERY WORKSHEET and in the name of that worksheet!!! The easiest way to do this is with the header in the Zeroed DCF file you choose for continuing value. 1)Prepare a table of historical values for your target firm and its two closest competitors similar to the ones we have been using for the DCF examples. The competitors should be the firms your comps analysis said are most similar to your target firm from an operating perspective. Make sure that your historical values table shows the name of the firm that is covered somewhere close to the historical percentages. 2) Explain each assumption you will use to build your DCF for the target. A text box works well for this. I have posted a sample of a fabulous way to present your DCF and the assumptions in an Excel page all on one page. You can also look at the DCF section of the posted sample projects. The Revenue Growth forecast is especially important since it determines whether you can use a multiple (of Sales, EBITDA or EBIT) (as for WMT), or a constant growth model (as for Costco) or a two-stage growth model (as for Costar). Explain your thinking carefully here! If the firm is not growing at something close to your assumed long-term normal growth rate you have to use a two-stage growth model to compute Continuing Value, as for Costar. EXPLAIN YOUR LOGIC for each of the DCF annual assumptions and your choice for the Continuing Value calculation and each of the assumptions in the Continuing Value calculation. 3) Build your DCF with your initial set of assumptions. Show the current and past two year price history of the stock at the bottom of the DCF, either with an inserted graph or with a text box. 5) Send both the Inputs worksheets (the ones for your target and each of the comps) and the DCF page to me electronically by midnight on the 9th in a single Excel file. 6) Name your file. Include your last name and the name of the firm in both the title of the file and the top of EVERY Worksheet. DO NOT CALL IT DCF OR 425 PLEASE… Note: you don’t have to worry about sensitivity analysis yet. You will have a chance to put that into the final version after the DCF First Pass is checked.. A