Turn on thread page Beta

# m&a: guide to simple dcf valuation [excel] watch

1. done another thread here as to what an m&a analyst actually does (powerpoint decks)

i recently decided to check the vailidity (fair price) of a recent valuation on a us based food processing company.. kraft foods. decided to share one of these quick and dirty valuations with a friend, and thought may as well make a quick guide on the logical steps needed to produce a discounted cash flow in 1-2 hours. this should be useful for those applying to ibd, moreso corporate finance and/or m&a.

*spoilers are visuals to what's being written*

Step 1, understand what you're trying to value

simply understand the metrics of the company and industry, what drives revenue growth.. what are major factors to consider when valuing this company, does it generate large capex uncertanties? what stage of the company lifecycle (mature/growing/declining) is the target firm at?

this will generally be the baseline of your assumptions.

Step 2, gather financial statement figures

Spoiler:
Show

i also like to calculate things like averages, to see whether we can use historic averages to potentially aid in calculating how things will grow in the future.. however as you'll see next this is more art then science

Step 3, making the assumptions

Spoiler:
Show

i'd argue this is the fun part.. this is where you use your knowledge gathered of the inudstry and market and apply some sense into forward looking projections. this is where you can start building out your FCFF formula, which is a quick google away. essentially trying to find the cash amount remainder each year available to those who have a monetary stake in the company.. essentially looking at raw operating earnings after tax and adjusting back for d&a as a non-cash expense, and taking out any yearly occuring expenses.

note: in more complex models you can adjust for non-recurring expenses etc.. when doing the calculations, but this is not relevant in the case of building quick models for sense checks and/or practice

the general practice here is to have various options and sensitivity cases in order to project what could happen under different scenarios. for the purpose of ease, i've built out two conditions.. projections under base assumptions (generally derived from historics and the assumption that future performance will be better than the past, given the wider macro-conditions at the time of acquisition) and downside assumptions, which are a bit more conservative. you can play around with the margins in your assumptions, until you're satisfied you can justify why you chose hem specifically. the formula in the bar essentially looks at what is selected in the drop down next to scenario (top of the page) and matches the case to produce the results on the fcff calculation table.

you calculate the margins for all your line items, and them link them through the table to calculate your nominal cash figure amounts which is used to calculate down to your cash flow line, which is what you'll be taking over to do your finite calculations. your last year will be the year you calculate your terminal value (i.e cash flow base value you'll apply a perpetuity formula with).

step 3, discount rate you'll apply (weighted average cost of capital)

Spoiler:
Show

the wacc is probably one of, if not the most important component of the dcf, as it determines the discount rate you'll apply in both your finite and infitnite series of cash flows:

Cost of Equity
the numbers here are quite easy to gather.. the risk free rate is simply the rate at which you can borrow from the government (generally a 10y bond), your company beta can be found online/bloomberg if it's a public company.. or you can approximate the beta by finding the average of comparables. you then want to un-lever that beta, so it's not distorted by the companies current capital structure.. and then re-lever with your proposed target capital structure to capture a more appropriate risk level, as the more debt the company holds, the less likely it is that equity holders will get paid, so they would in theory demand a higher rate of return. the market return can be found from damodaran (nyu professor who publshes alot of great corp finance info for free) or you can use bloomberg and simply look at the required market return over a long-time horizon or whatever makes sense for your valuation.

Cost of Debt
Simply the rate at which the company can borrow.. this again can be found on bloomberg, or you can check the companies issued bonds and what they yield to determine their cost of debt funding. then given that debt is tax deductible you simply account for that in your after-tax cost of debt calculation which simply states: rate at which corporation can borrow*(1-tax)

Now once we have WACC, you can go a step further and do WACC sensitivity tables to compute various WACC figures under different debt assumptions and what not, but in this quick model we can skip that part and just use a percentage point difference to assume various scenarios, as evidenced in the next part.

Step 4, synergy benefits

Spoiler:
Show

for this particular model, i was looking at it on the point of view of Heinz, and the deal was backed by a PE company who were looking to extract operational, credit and geographical synergies.. won't go deeply into the calculations but they released target synergy figures.. and then i simply attributed value deriving from kraft foods (for instance \$150bn cost savings from 2017, attributed 50% to kraft) and added them togther with the FCFF currently calculated to gain new more appropriate FCFF figures to account for overall benefits.

Step 5, valuation

Spoiler:
Show

lastly we can combine all our assumptions, to get a final enterprise value that would be fair to pay according to our calculations.

firstly you calculate the present valuees of our cash flows, which simply discounts all future cash flows by a static WACC which was previously calculated, i also assumed variations of the WACC to produce some sensitivity tables to get a range of numbers that could occur under various scenarios. this alongside our switch regarding base/downside assumptions give a range of values we can use to justify our final figure.

we then calculate our terminal value under a long term growth rate (another very important figure) that we justified, i used 1.6% taking into account the projected performance of the US economy and the contestibality of the food processing industry, whilst accounting for the decline in performance for kraft foods as a sole entity. and then again used some sensitivity in that number to get a range of figures. adding the finite pv with the tv, gives me a range of enterprise values for the various scenarios.. and my final value sits at around \$45bn.. which was approximately the amount that was actually paid, so i was happy to conclude that in my belief the value paid to kraft shareholders by heinz was a fair amount.

again, this is a failry simplistic quick and dirty dcf which should take 1-2 hours to build. but hopefully should give you a rough understanding of the basic concepts used to derive a company value. any questions feel free to ask
2. p.s: want to clarify there are various ways of building models, and people have personal approaches. as said, this whole process is more art than science
3. Is this what you do in the extra free time you gain by not attending lectures?

Posted from TSR Mobile
4. (Original post by Commercial Paper)
Is this what you do in the extra free time you gain by not attending lectures?

Posted from TSR Mobile
may aswell make use of my life right, even if seemingly unproductive
5. I've been wanting to brush up on my Excel. In.
6. (Original post by l'etranger)
I've been wanting to brush up on my Excel. In.
purely a matter of practice and doing it yourself from a blank spreadsheet document

Turn on thread page Beta
TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

This forum is supported by:
Updated: November 14, 2016
The home of Results and Clearing

### 1,182

people online now

### 1,567,000

students helped last year
Today on TSR

### A-level results day chat: 2 days to go!

Chat about results here

Poll
Useful resources

## Articles:

Guide to investment bankingGuide to consultancy

## Featured recruiter profiles:

### Deutsche Bank is recruiting

"Thrive in an international banking environment"