1. Building a Football Field Chart
So we've moved over to the last tab in the business valuation model,
the tab labeled football field chart. As you can see, the football field
chart is actually already built for you, but we will build one from
scratch as well. And this is just a very, very common way of
presenting valuation analysis. You can see we have comparable companies,
we have precedent transactions, we have DCF using the perpetuity growth
method. We have a DCF using a terminal multiple, and then we have
a 52 week high and low. Now this is a fictitious company,
so the 52 week high and lows were made up.
And you can see we have an average line in there as well.
Couple things to note as to be expected, precedent transactions should give
you the highest valuation because of that control premium.
Now, let's talk about building one of these charts from scratch.
So there's a bit of a trick when creating these football field charts.
We will select all of the data beneath the chart
and we will actually select a stacked column chart.
However, the stacked column chart adds all of the data together
and we want our evaluation to float like you see on the screen.
The trick is to hide the low and high numbers.
Let's walk through an example. The first thing we want to do is
we want to make sure the data is set up properly.
And so that requires us to have a low
valuation and a high valuation. You can see that we have our four
different methodologies. We also have a 52 week high, a 52 week low.
Again, the 52 week high and low, those are made up numbers,
but for instance comparable companies to get the low valuation we can use
the MIN function to again, arrive at the low valuation under that methodology
and then we can use the MAX function to
pull in the highest valuation in that methodology. And then we take the
difference. Okay. That's the first step is setting the data up properly.
Let's go ahead and build our stacked column chart. We'll select the data,
our valuation methodologies and our 52 week high and low. We are leaving
out the average valuation at this point, but what we're gonna do is we'll
go to insert, and again, the trick here is you want a stacked
column chart. You'll see it like so, just a couple of minor formatting
things. Not a huge fan of chart titles, so select chart title, delete it.
We can also get rid of this legend at the bottom.
Also not a huge fan of borders, so we can do control one, click border and
say no line. And let's go ahead and get rid of those grid
lines. Select the grid line, hit delete. Let's go ahead and move this
chart up so that we can see it next to the completed version
because we're going to try to mimic it as much as possible.
Again, formatting, it's in the eye of the beholder, but
I do want this chart to look fairly similar to the already completed
version. But you'll notice again, we have a stacked column chart.
We don't have the floating columns. Here's how to achieve that floating
look. You select blue in this case, those are the low values in
the table of data that we built this chart off of.
You can do control one, click the paint bucket, go to fill and
just say no fill. There's still data there, you just can't see it
'cause there's no fill color. We can do the same thing with the
high number. Select no fill. Okay, again, the data still exists,
we just can't see it. That's how we get a floating column chart.
We use a stacked column chart and we hide the low
and the high numbers. We just are left with that middle number that
we calculated. Let's go ahead and make this font
similar to what we have in the completed version.
We'll just use this dark blue that we like to use at CFI.
Okay, now let's make this even better. First of all,
if you'll notice on our vertical axis here, it goes all the way
up to 30. And that's because again, with a stacked column chart,
Excel is adding up all of this data in a vertical column,
but we don't want to see that. Again, we just want to see
a normal floating range, so we're going to use a maximum number of
14 and a minimum of zero. So I've got the access selected.
Again, do control one and we'll do a maximum of 14.
And while we're here, we might as well change the number formatting 'cause
we want it to be a currency. We're going to use the
dollar and two decimal places which we can specify
like so. We'll also go ahead and bold that data
and we'll go ahead and bold our horizontal access as well.
Now we actually want to see the high and the low valuation
like you see on the completed side. What we can do,
we can select again, the low data and we'll go to chart design,
add chart element, data labels. And in this case,
on the low end we want to select inside end. And when we
select inside end, it will put the low valuation number
pretty close to the bottom of the blue floating column. So let's click
inside end and then we want to see the high valuation.
Again, we select the data, high data select data labels, and in this
case we wanna select inside base so that the number will be close
to the top of the floating blue, like so. Okay, let's format this better.
Again, select, we do control one, go to number,
again we'll just do currency. Let's go ahead and bold that as well.
Then we'll select these data labels. Again, select currency, bold one more
time. And so now we have a floating
column chart, a football field chart. We still want to add the average
valuation line and the text box, but overall
we're looking pretty good. Okay, now let's go ahead and draw in that
average line. What we can do is we can go to insert,
illustrations, shapes, and we'll select the straight line
and we have to eyeball it a little bit. We know the average
valuation is 8.77, so we'll start approximately here. And here's a trick
when drawing lines in Excel, you can hold down the shift key and
you will get a straight line. So I'm holding down the shift,
I draw the line, and there we go. We have a perfectly straight
line and you can always verify that. Go to shape format and you'll see
the height is zero. Let's go ahead and make that
outline a little heavier, we'll to 2 1/4. We'll add some dashes,
just select that. And then we'll use the
bright orange CFI color that you see in the complete version.
Again, there we have our average valuation line. Again, it's not maybe exactly
8.77, but it's pretty close. Now let's talk about building in that text
box. And there's a really cool feature in Excel that most people don't
know how to use, but we'll cover that
next. Let's add the text box. We will
simply go to insert, do text, text box,
draw anywhere we like. And here's the really cool trick. We don't actually
have to type the text in there. What we can do is we
can go into the formula bar equals and we can reference an actual
cell in our model. We're gonna reference D42. You can see that it's
automatically anchored and we'll go ahead and we'll just press enter.
And so if we go to cell D42 and make any changes,
those changes will automatically be reflected in the text box, so a really
cool feature if you have to use a lot of text boxes and
charts. Again, you can link them to individual cells and just enter or
change the data in the cells. Okay. Let's go ahead and format it
a little bit better so that we match what's on the left.
We'll bold, we'll do again the bright orange. Okay. And then we can
move it around. Okay? Now, a couple of things,
you can see in this case there is no fill to this text
box, which is what we want. Sometimes when you draw a text box,
it will have a border and a fill color, but again,
this one did not. So if it did have a fill color, you can go
to shape fill and just select no fill. If it had a border,
again, you do shape outline, no outline. Okay? So now
our chart on the right looks pretty close to the chart on the
left. But let's talk about the formula that's in cell D42.
Okay. So let's scroll down to D42 and see what we've done.
This is actually a formula, zoom in a little bit so it's easier
to see. You have equals, and then in quotes, average valuation with a
colon, a space. And then we have the ampersand sign. And the ampersand
sign is concatenate. Basically, it takes average valuation and then adds
something to it. In this case E42. But notice that we're using the
text function. If we took the text function out,
you could see our data is really messy, right? 8.7683, et cetera. We
don't want that. We want to see again, a currency symbol and two
decimal places, so that's why we use the text function. Just control Z
to undo that. Again, you just reference E42 as our average valuation of
$8.77. And then the second argument in the formatting of text,
again, we want the dollar symbol and we want two decimal places.
We put those in quotes. And so it looks nice and clean.
Average valuation, colon, space, dollar symbol, $8.77. And so there you
have it a properly designed and formatted football field chart.
Of course, it's more of a American football field chart than international
football, of course. But again, the trick there is to use
stack columns and then hide the low valuation, hide the high valuation,
so we only show that middle range that is calculated.
And then we just drew a straight line and added a dynamic text
box. Now you know how to build a football field chart.
And again, this is a great way of presenting valuation analyses to clients.
2. Let's practice!