DCF Modeling Question

Hello, I have a question about DCF modeling. In Rosenbaum's book on financial modeling in the i-banking there is an example of DCF valuation. Below I attach the sections of book that I mention further.

ValueCo DCF Analysis Output Page

So, there is some historical data for 3 previous fiscal years (ending on December 31) and additional data for 3 quarters of the present year + 1 quarter of previous year consituing LTM numbers. Then suddenly on the next exhibit there is sales data for this uncompleted year (2012) without any explanation how it was derived. Next positions on the 2012 statement are simply projected by using previous (2011) year percentages, so this is clear, but how did they derive the sales?

Another question: in the actual model, in the discounting section they are beginning the calculations at 2013 - what about the cash flow from 3 remaining years of 2012? I understand that we are at the moment where there is only 2012 Q3 statement released, so shouldn't we also project the cash flows from 2012 Q4 and add them to the valuation?

 

For the first part: the 2012 data isn't a projection and is only slightly different from the 9/30 LTM, so it's probably just a financial that was pulled from the 10k- it's in fact very close to the LTM value. It's a bit confusing that they leave out 2012 in the 'historical period', but it seems to be a historical number.

For the second part: we are using what is most likely the yearly number (3450) for 2012, as opposed to the LTM number (3385) up to September 2012. We could use the September number as a baseline, but then our periods in the DCF would be weird (instead of using 1, 2, 3, for 2013, 2014, 2015, etc. we would use some kind of stub period and probably midyear convention, so it would be like 0.25, 1.25, 2.25 to represent the last 3 months of 2012 included in the calculation, then 2013, then 2014, and so on).

Hope that helps.

 
Best Response

Thoughts on this based on the picture in the link you posted.

  • They have made a simple assumption for Q4 2012 sales that results in total revenue of $3,450 that year, implying 7.8% YoY growth. The LTM figure of $3,385 is given to provide some context or rough guidance of what 2012 sales may look like. However, 2012 Q4 sales need to be higher than 2011 Q4 sales (which are included in that LTM figure), resulting in a 2012 YE figure that is higher than the LTM figure.

  • The projection period simply assumes top-line growth continues to decline until it reaches 3.0% in 2017. Without knowing any more than the picture you provided, I assume this is the terminal year of their DCF, where 3% growth and flat cost structure represents the business at a "steady state" and an appropriate year to calculate terminal value. You'll notice that starting in the projection period (2012), the cost structure remains totally flat at a 40% gross profit margin, 15% EBIT margin, and 15% effective tax rate.

  • Cash flow adjustments also appear to be a simple assumption, where CapEx is ~5% of sales and D&A is ~6% of sales throughout the forecast period. Some people think of these assumptions differently (depends on industry, where importance of CapEx and D&A vary), so you can also think of D&A as a % assumption of CapEx. Regarding the change in NWC, I am not sure what their backup calculation for this is, but it makes sense directionally. NWC is a cash outflow every year, which is sensible if the company's top-line continues to grow. Also, the amount of cash outflow due NWC declines over the forecast, just like the rate of top-line growth continues to decline. One very simple "back of the envelope" calculation we sometimes use is assume the change in NWC is some percentage (say, 10-20%) of the change in sales, i.e. if you go from sales of $100M to $200M and assume the change in NWC is 20% change in sales, then the cash outflow would be -$20M. As I said, this is a really simplified assumption, and I don't think its being used here -- I only bring up an example of it so make the point that their forecast numbers make sense directionally.

  • EDIT. Regarding the discount period question, I think you are over-thinking on this one. From my understanding, it appears they are simply using the mid-year convention and assuming a valuation date of January 1, 2013. Even though 2012 Q4 actual figures are not yet available, this analysis is likely being conducted some time during 2012 Q4 and is assuming a valuation date as of 1/1/13 rather than 9/30/12. This makes total sense -- the analysis is clearly being done after 9/30/12 since you have actual financials through that period, so why run the DCF at a point in time that's already passed.

 

Thank you for your thoughts! About the NWC - it is spread and calculated on the seperate page, based on the balance sheet data.

But what about this missing Q4 2012 in discounting cashflows? Aren't we missing some value if we evaluate company in the point of time being some period between Q3 to Q4 2012 and don't include the cashflow from this period? I know that omitting this period simplifies calculations a lot, but if we miss like 5 or more % of the value due to this action, shouldn't it be disturbing?

 

No. You can still elect to conduct the valuation as of January 1, 2013 over something like October/November/December 1, 2012. While you are not including the NPV of that partial year of cash flow, your DCF value would reflect incremental value from using a slightly lower discount factor throughout your forecast starting on January 1, 2013 (this includes a slightly lower discount factor when calculating the present value of your terminal value). Furthermore, your performance in Q4 2012 would be accounted for since your net debt/cash balance used in the DCF for equity value would be as of January 1, 2013.

 

Sint nostrum corporis voluptatem. Cum eveniet molestias suscipit explicabo iusto qui. Repellat ullam quo sit. Id eum aut quis dolorem cum.

Qui voluptatem aliquam dicta non. Similique quo veritatis animi repellendus maxime. Itaque earum eligendi vel veritatis. Quasi et sed amet eum quibusdam aperiam earum. Ea corporis delectus nisi sint rerum et.

Iusto minima sed corrupti ea voluptas mollitia. Et quasi deleniti id enim quos nemo. Officiis iusto nemo ea molestias esse laboriosam id. Corrupti laboriosam eligendi voluptatum cumque reprehenderit aut et.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”