60-Minute and 2-Hour 3-Statement Modeling Case Studies: Add-On Acquisition Projections
In this lesson, we are going to move into Part 3 of the Atlassian case study where we
are projecting the acquisitions, the debt, and the company's taxes. This whole section
is supposed to take about 30 minutes, so you can expect to spend maybe about 10 or 15 minutes
on the acquisition part because it's a little bit more complex than the debt and tax schedule.
So, we're going into the acquisition projection section right here. We'll divide this lesson
into four main parts. First, we'll talk about some of the data sources for these acquisitions
so you can see where the numbers are coming from. Then we'll discuss how to project the
acquisition spending and the amortization of intangibles, and you'll see the formulas
for all those. Then you'll learn how to project the revenue and the EBIT or operating income
from acquisitions. And then finally in Step 4 you'll link the add-on acquisitions on the
income statement and cash flow statement. We're not going to finish this process completely
in this lesson because we'll have a wrap-up lesson later on where we link together everything,
but we will at least flesh out much of what we need for that final linking process.
Let's go to Part 1 first and discuss where this data is coming from. So, we have quite
a few numbers here. We have the company's past acquisitions and how much they've spent.
We have the revenue multiple for Trello here, and then for this other company, OpsGenie
right here, we have the amortization percentages. So, for the newly acquired intangibles, we're
assuming a three-year useful life and straight-line amortization over those three years. The starting
initial balance of intangibles will amortize over two years.
And then if you go to the top, you can see where we have the assumptions for the revenue
multiple for the acquisition of 20x, the initial revenue growth rate of 100%, and the annual
change in growth rate of negative 10%, meaning that it goes from 100% to 90% to 80% and so
on.
You might be wondering where all this comes from, and the short answer is we're mostly
basing it on what the company really has done historically. So, if you go up to the OpsGenie
PDF and article there, they list the purchase price of $295 million. And then if you scroll
down, they say that this suggests that OpsGenie has revenue of at least $10 million. So, clearly,
this is a very, very high multiple that they are paying by any metric for this company.
If you go to the press release on Trello, they have some information and estimates here.
They estimate a total enterprise value to invested capital multiple of 41.1x, but if
you keep scrolling down, you'll see that they estimate $10 million of revenue for Trello,
which implies a revenue multiple of somewhere around 40x for the company.
And then they give another few examples here, but the overall point is that multiples in
this sector are very high. These types of high-growth software companies are very expensive
to acquire and companies will pay revenue multiples that would be unheard of in pretty
much any other sector out there.
That spending may benefit Atlassian in the long term, but our concern here would be that
if it takes a decade to realize the benefits, are we really going to see much of a benefit
in the next four years if we invest $2 billion in the company and then plan to exit in that
four-year timeframe? We're not sure about that part.
The purchase price allocation numbers, for example, the 65% that goes to goodwill here,
the 30% that goes to other intangibles, and the 10% that goes to other non-current assets,
these are all based on the actual purchase price allocation Atlassian gives in its filings
for Trello. We've roughly followed this. And we're also getting the useful life assets
here, and we're seeing an average of three years for newly created and intangible assets
from acquisitions. So, that's where all of the numbers are coming
from. I just wanted to point this out first to show you that these are not just being
made up out of thin air. They are actually being sourced based on real world data.
It's not too surprising that the vast majority of the purchase price goes to goodwill and
other intangibles. These are tech startups with very little in terms of PP&E and
other hard assets. And Atlassian and other companies are paying huge premiums for them,
so of course, we're going to get a lot of goodwill from these acquisitions.
Let's go to Part 2 now and talk about the acquisition spending and the amortization
of intangibles. Now, the case document tells us that this
should go from $700 million to $775 million by the end. That's not just an arbitrary number.
We can go and fill that out. This is coming from the fact that, first off, we're investing
$2 billion into the company, so that should give them at least $2 billion to spend right
there. Now, obviously, the $700 to $775 million here
adds up to more than $2 billion. If you do that math, it comes out to more like $3 billion.
But remember, the company is also generating positive free cash flow here. So, they have
some positive free cash flow. They already have a large cash and investment balance,
so we think it's reasonable to have acquisition spending in this range.
For the amortization of intangibles, let's go down to the balance sheet and get the starting
balance from right there. So, other intangible assets, and then we'll multiply by these percentages,
so we have that. So, the initial balance goes away very quickly, but it's not that big to
begin with. And then for the amortization, let's take
our acquisitions net of cash acquired, multiply by the 30% allocated to other intangibles,
and then multiply by this percentage so we can get to this. So, we have that.
Let's copy this down and then let's change around the rows here. So, we have that. And
let's copy this down and do it again.
There's probably a smarter way to do this, but this is a relatively short and simple
schedule so I'm not going to worry about that for now.
And then let's copy this down again. And
let's copy it down one final time right here. So, we have that. Then let's just add up everything.
And be careful with the exact areas we're linking to here. So, we get to total amortization.
And that's pretty much it for Part 2. We have the acquisition spending and the amortization
of intangibles and we've added up everything here at the bottom.
For this next part, we're going to project their revenue and EBIT from these acquisitions.
So, for the expected revenue multiple, let's go up to where we have it listed right here,
and then for the expected revenue growth rate, let's go up and get it from right here. And
we're going to assume this decreases by 10% per year, so we go to a 90%, 80%, 70%, and
60% growth rate. And then for the margins, once again, we have this up at the top, so
we start at 10% and then we increase this by 2.5% per year. And so, we have that.
So, for the revenue from acquisitions for Year 1, let's go up and get our acquisition
spending right here. We'll anchor the 147 part because we don't want that to change
as we copy this down, and then we'll divide by the revenue multiple right here of 25x
and we'll anchor the 164 part. So, we have this.
And then I am just going to copy this down across the diagonal this way for now. The
reason is that because of the way we set up the formulas, these are actually all correct
as is copied down. And then we have to just take this number
and multiply it by one plus the appropriate revenue growth rate. I'll anchor the 165 part
there so that this does not shift down as we copy this down and around.
And in each column, I just have to move it back by one row to get this working exactly
correctly. And so, we have that. And then let's add this up and we get our total revenue
from acquisitions.
Now, for the EBIT from acquisitions, we can take our revenue from acquisitions in Year
1 and then we can multiply by the margin in Year 1. I'll anchor the 166 part so that does
not shift around. So, we have that. Let's copy it down again. We need to move
this over one to the side, and then we will just keep doing that. And then we'll
add up the EBIT from acquisitions here at the bottom.
So, without even linking in anything in here, you can tell that these acquisitions are going
to make a relatively small impact on Atlassian simply because the margins are so small and
the revenue multiples here are so high. In any case though, that takes us to the end
of Part 3, projecting revenue and EBIT from acquisitions. Let's go to Part 4 and link
the add-on acquisitions on the income statement and cash flow statement.
On the income statement, we have revenue from acquired companies, and so we can just link
up to this one right here and copy this across. You can see the revenue now goes up by around
$600 million.
For the operating expenses, we have amortization and then we have expenses from acquired companies.
So, let's go up and get the amortization here first. And then expenses from acquired companies,
for this one the easiest approach is to go up and take the revenue from acquisitions
and then subtract the EBIT from acquisitions. Because remember, we're showing these expenses
with a positive sign here, so that's how we're going to handle that.
So, that's about all we have to do on the income statement here with linking these add-on
acquisitions. If we go down to the balance sheet, you'll see that the items that we had
before are still there and still filled in. Some of these, like accounts receivable, have
changed a bit because of the extra revenue from these add-on acquisitions, but there
isn't too much else to do here for now. I'm going to save the non-recurring assets
and the long-term debt and the equity for the lesson where we actually finish linking
the statements together. Some of these, like goodwill and other intangible assets, for
example, will flow in from the add-on acquisitions, but we'll set up those calculations when we
get to the lesson on linking the statements.
And then moving down once again to the statement of cash flows, here, we can link in the new
amortization from these add-on acquisitions now. So, we can just take the depreciation
formula and copy it one row below, and we can have that.
We're going to leave the amortization of debt discount and issuance fees blank for now.
Also deferred income taxes will depend on our tax schedule, so we don't have that yet.
I will not sum up our net cash flow from operations because we don't have all of the items here
filled in yet. We can link to the acquisitions net of cash
acquired. So, to get this, let's just go up to the top where we have our acquisition spending
projected right here and then we can copy this across. And if we want to, we could sum
up net cash flow from investing because we actually have something for all of these items.
Proceeds from share issuances, we already have, and net debt proceeds, we need our debt
schedule for. FX rate effects is already filled in.
And so, with that, we're at the end of what we can do for linking these add-on acquisitions
to the rest of the statement for now. We're at the end of this lesson, so let's do a recap
and summary.
I started out by explaining some of the data sources for these acquisitions. Everything
comes from real life, whether it's press releases and the purchase prices and revenue disclosed
there, or the company's filings and purchase price allocations for past acquisitions.
We then projected the acquisition spending based partially on the $2 billion equity investment
here but also based on the fact that the company has a high cash and investment balance and
generates positive free cash flow. And then we separated out the amortization
here and had an annual amount based on the percentage of the purchase price that corresponds
to other intangible assets, and we summed those up for each year of acquisition spending.
Then we forecast the revenue from these acquisitions and summed them up for each year. We calculated
the first year's numbers by taking the revenue multiple, and then after that, we used growth
rates to get the total revenue beyond the first year.
Then we looked at the EBIT from acquisitions by assuming a margin on this based on the
year after the acquisition, and we summed up all those.
And then we went through the process of linking these on the income statement, including the
revenue from acquired companies, the amortization associated with these acquisitions, and the
expenses from acquired companies. We left the balance sheet mostly alone. It's
already set up. And then we linked our amortization on the statement of cash flows and the acquisition
spending down here to reflect both of these. That's it for this lesson. Coming up next,
we'll get into the debt schedule and then we'll take a look at the tax schedule and
calculate the company’s deferred taxes after that.