Merger Model Help

Looking to get a better conceptual understanding of merger models. I've done a couple practice ones but never a full model on the job yet, so questions may be rudimentary.

Is excess cash put towards the equity purchase price only the Acquirer's cash, or can it include the Target's as well? (already leaning one way on this, but my rationale is weak)

To calculate how much leverage the Acquirer can use to buy the Target, would finance-able EBITDA be the
Acquirer's standalone EBITDA;
Acquirer's EBITDA + Target's EBITDA; or
Acquirer's EBITDA + Target's EBITDA + expected synergies?

Not urgent but really appreciate if you guys could shed some light onto this.

 

I am trying to do a return on equity analysis - could I get some help from someone please? I am a new analyst at a small boutique.

The initial investment is $6,000,000.

The free cash flows are as follows:

2013 = 0 2014 = 0 2015 = 0 2016 = 600,000 2017 = 600,000 2018 = 600,000 2019 = 600,000 2020 = 600,000 2021 = 600,000 2022 = 600,000 2023 = 52,315,912 2024 = 64,588,052

The discount rate is 10%

The terminal value assigned to this project is 6x free cash flow.

The reason why the final two years of the projection period are so much higher is that in 2023, the debt is paid off and all free cash can flow down for equity.

Could someone give me some guidance asap in doing this calculation in excel? Thank you so much.

 
Jacob W Hughes:
I am trying to do a return on equity analysis - could I get some help from someone please? I am a new analyst at a small boutique.

The initial investment is $6,000,000.

The free cash flows are as follows:

2013 = 0 2014 = 0 2015 = 0 2016 = 600,000 2017 = 600,000 2018 = 600,000 2019 = 600,000 2020 = 600,000 2021 = 600,000 2022 = 600,000 2023 = 52,315,912 2024 = 64,588,052

The discount rate is 10%

The terminal value assigned to this project is 6x free cash flow.

The reason why the final two years of the projection period are so much higher is that in 2023, the debt is paid off and all free cash can flow down for equity.

Could someone give me some guidance asap in doing this calculation in excel? Thank you so much.

Why are you posting this all over the place? Once is enough.

First of all, the last 2 years are diesel because the CFs arent being used for debt service anymore? How could you possibly leverage up $6M to the point where you're borrowing ~100M. Not very plausible, also the CFs you stated are absurd which means your 10% discount rate is absurd... you're not really employed.

To answer your actual question, use the IRR function in excel using -6M as your first CF, and all the rest you listed coming next. Or discount all your cashflows back to PV and PV of CF/Initial outlay.

 
Jacob W Hughes:
I am trying to do a return on equity analysis - could I get some help from someone please? I am a new analyst at a small boutique.

The initial investment is $6,000,000.

The free cash flows are as follows:

2013 = 0 2014 = 0 2015 = 0 2016 = 600,000 2017 = 600,000 2018 = 600,000 2019 = 600,000 2020 = 600,000 2021 = 600,000 2022 = 600,000 2023 = 52,315,912 2024 = 64,588,052

The discount rate is 10%

The terminal value assigned to this project is 6x free cash flow.

The reason why the final two years of the projection period are so much higher is that in 2023, the debt is paid off and all free cash can flow down for equity.

Could someone give me some guidance asap in doing this calculation in excel? Thank you so much.

Given your absurd assumptions, your IRR is 30.88%.

 

1- Why do you need to value the merged entity? You're valuing the Target. In any case, if you did actually want to value the NewCo, you'd build a pro-forma NewCo, and value it based on its CF and/or EBITDA.

2- Its not really that simple, but yeah, I guess you could just take a historic average of each companies gross margins and weight them according to how much revenue they contribute to NewCo.

3- In my experience, some assumptions are pretty standard and its your responsibility to not fuck it up or you'll look stupid when a SR sees the model and ask why the fuck the perpetual growth rate is 8%. Usually its a combination, typically you build the model and the assumptions can more or less fall into 3 categories:

1- common sense assumptions you need to get right 2- assumptions you come up with which are generally somewhat feasible. Down the line, SRs will fine tune this... maybe you used a 37.5% tax rate, SRs decide to use a historic average of effective tax, maybe they decide to build out a full tax schedule, etc... 3- then there are assumptions where you wouldn't have the slightest idea where to begin, so from step 1 you're conferring with your SRs and they're coming up with the assumption. 4- The models depend on what it is being used for. Some firms have templates, others build all models from scratch, and a lot of in between where you take an old model you used and modify it to use on a new transaction, maybe take out an NOL build-up out of an old model, etc...

 

1) No, because the valuation yielded by a DCF is a control value. 2) Yes for trading multiples analysis, but you only add it to the equity components; No for precedent transactions analysis, as the synergies are priced into the purchase price.

 

Nobis dolor aut ea eligendi unde expedita id. Suscipit fuga sed commodi debitis. Distinctio natus rerum quae ad sunt et molestiae.

Et laboriosam iusto odit voluptatum neque ut. Eveniet non atque ut. Aut natus quibusdam nam aut voluptatibus et ut. Quam dolorem architecto repellat 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...”