In this video, we want to create a football field chart, which is a classic valuation chart. We've put some data together first. We've got a series of valuation methods listed here, all the different methodologies including a transaction comps, DCF with synergies, DCF, price earnings to growth ratio, and EBITDA multiples and revenue multiples, and a 52 week price range. We've got the low values in column B, we've got the high values in column D, and then in between the two we've calculated the range because we'll be building a stacked bar, the low range and high value. And the reason we're including the high value is that we need to make sure that we can have data labels at both the bottom and the top end of the bar. In addition, I've got three additional pieces of information, the current share price, which is 16, and we've got the lower valuation range at 25 and the upper valuation range at 28. This information, these three components are going to be three charts that we will superimpose onto the stack bar chart, and these are going to be three XY scatter charts. One with the share price, one with the low range, and one with the high range. So the first thing we'll do is we will insert a chart using the information that we've got here with the different ranges. So I'll do alt IH to go to insert chart and I want to make sure I have a stacked bar chart. So it's actually the, in the recommended charts I'm going to choose the stacked bar. And you can see here it's visualizing what the chart's going to look like. And if I just choose, okay, that will come onto the screen. So you can see we've got a stacked bar and we've got three series. We don't really care about the series, so I'm going to click that legend. And if I don't like it, I'm just going to hit delete.

Also with the chart title, if I click on the chart title, then you can type equals and then click on a cell on the spreadsheet. And if I hit enter, that means that that will give a dynamic label so I can change this to valuation of A, B, C company. And if I do that, you'll notice that the chart will update automatically. And that's really helpful to having something dynamic like that. The next step in this process is to make sure that we have a stacked bar that effectively hides the low end and the high end. So I'm going to click on that section here and I'm going to right click and choose format data series.

And then I'm going to go to the fill bucket and under fill I'm going to choose no fill and then in border I'm going to choose no lines. You need to do both. You don't want to make it white because if you make it white, you'll see those vertical lines. It will be obvious that there's something there because they won't show up. Then for the upper range, I'm going to select that and I'm going to do the same thing, no fill and no line. And it now looks like the ranges are hovering in midair because we've hidden the low and high end.

Now if I click that mid range that we've got there, what I can do is I can change the settings here and I can reduce the gap width. And this is kind of useful if you want to make the bars slightly wider. So if I go all the way down, you can kind of see that then growing. because a lot of people when they do this, they want to have some commentary to the right. If I just grab this and go down a bit, you'll see that. Then you can kind of elongate it because in a PowerPoint deck on the right hand side you can document the different methodologies. You'll notice now that the chart looks kind of strange because it's got a really long x axis at the bottom. So what we can do is we can change the bounds of that axis. So if I just click on the axis now if you have that little dialogue box or that little section on the right hand screen, you'll notice it changes and I'm going to go to the chart item and choose access options. And at the moment it's a maximum of 70, but that's way ahead of where we want it to be. Probably we want the high end to be 40, so I'm going to choose 40. And you can see that makes much more sense because it isn't such a wide variance on the axis and the chart or the ranges now in the middle of the chart. Next what I'd like to do is I'd like to put some numbers on each bar. So I want the low end and the high end and I'm going to click on the hidden low bar. Then I'm going to go to chart design and add chart element. And then I'm going to put data labels and I'm going to do inside end. Now you can see that it's showing the bottom end of the range as a number. You can always change the font size of these. So if I choose inside end you can see that that's giving us the low end. Next I'm going to click on the higher bar and I'm going to again go to chart design, add chart element, choose data labels. And in this case I need to do inside base.

So now we have got the bottom end of the range and the top end of the range as well. So that gives us the actual data points on the chart and you can always reformat those, make them smaller or larger as you particularly want. I'm also, while I'm here, going to add an axis title and I'm going to add a a primary horizontal title. And I'm going to change this to US dollars billion because we also want to make sure that we have the indication there. So we've got our different methodologies on our chart and we've got the high and low end of the ranges. Next I'm going to add some content to the chart. I'm going to get rid of the formula bar and I'm just going to move the chart so I can see these ranges that I've got here because I'm going to add three sets of data to this chart. The current share price, the lower range, and the upper range. So if I right click the chart and I choose select data, I'm going to add a new series. Now when you add a series, Excel won't accept it as an XY scatter because it thinks this is a bar chart. So we have to kind of put it in as a bar chart first. So type in the series name, I'll do the current share price first and then the series values. I'm just going to choose that 16 range. I will change that later. Click add, then I'll click add again. Now I'm going to put in the lower range and the values again, I'll just choose 25 and then choose. Okay, and then I'll add a last range, which is going to be the upper range and the values are going to be 28. And I'll change those in just a moment. So I've got all the additional items that I want for this chart. And when I click on, okay, these are actually being presented on the chart, you can see that's actually a bar series at the very bottom, it's actually added to the bar. So if I select one of the data lines and you can see that's added some bars to the other items. And then I right click this and I change series chart type. What I can now do is for those additional items, you can see they've come in as a stacked bar. Well, I want to change this, so I don't want a stack bar. I want an XY scatter and I want one without nodules and it needs to be straight lines. So I'm going to click on that and I'll do the same thing with all three of the additional data items that I added. So for the current share price, the lower range in the upper range, I've changed to scatter with straight lines. And then I'll click on, okay, it looks a bit weird at the moment, but don't worry, we're going to change that. So next I'm going to right click the chart and I'm going to select data. And for these three different methodologies for the current share price, I'm now going to edit This because it's recognized that this is an XY scatter given. We changed that option. The X values is the bottom axis, so that's 16 billion. The Y values are zero to eight, and the reason is zero to eight is because they're eight different valuation methodologies. It'll make more sense when I explain it in just a moment. Then for the lower range, I'll edit that. And again, the x axis for the lower range is the billions and the Y axis is the zero to eight.

And then the upper range, if I edit that, the X values are 28 billion, the Y values zero to eight. So we had to do this now because previously when we added the data, Excel thinks that this is a stacked bar, we need to now tell it as an XY scatter. And then I'll choose okay. Let's take a look at what the chart looks like now. So what's happening is that this data item here is actually two points in an XY scatter, two points at about 15 billion. That's the low value and the corresponding Y axis is at zero there. Then this point here, 15 billion and the corresponding Y axis is eight here, it's on the secondary axis and it's just linked those two items by putting a line in there. And then it's done the same thing for the low range and the high range. This is our bid range on the right. Now it looks a bit weird because it doesn't actually go to the top of the chart, but we can fix that. If I click on the Y axis, then I right click it, I'm then going to format the axis and I'm going to do a maximum bound, not of nine, I'm going to choose eight because remember we want it to be the same as the left hand side. And you can see now those lines go up to the very top. I then can actually get rid of that axis. So I'm just going to click on that axis and hit delete and it will just hide it. It's still there, but it's just hidden. Next I want to put some data labels on these items because it's always quite useful to have some data items. So I'm going to go to the chart design and I'll add a chart element, choose data labels, and I'm going to put it just above there and I'll do the same for the next item. So I'll go to chart design, add chart, element data labels above, add chart element data labels. And it's going to be above, not below. So I'm going to select the data labels at the top and then I'm going to come to the right here. And under label options, I'm going to change it from Y value to just the X value because that's the actual digit down at the bottom. And then the same with the next item. I'm going to do the same. And then finally the third item, I'll do the same. So we just have the X values. The next thing I'm going to do at the bottom, I'm going to click on one of the data labels because I don't need the ones at the bottom. So now I'm going to also do the ones at the bottom. You've got to be slightly careful to only select one of the data labels, otherwise you'll delete the ones at the top as well. And we can always expand that up a bit just so we can see it. Or I can change the formatting of the whole chart. So I'll just change that to perhaps nine characters wide or maybe even eight characters wide. So things can fit in. You can kind of play around with that. Or sometimes actually if I click on that title, move it to the left. There we go. And then you can actually see the numbers at the top. If you want to change these, these are pretty horrible formatting here, then we can just come to the fill. I'm going to choose a slightly different, let me just choose red here, but I'm going to make it a much smaller line. This is quite big, so I'll make it say one point wide. And then for my valuation range, I'm going to change this to black. And again, I'll make it one point wide and I'll do the same thing here. So you can play around with the formatting to your heart's content just to make it look a little bit better and choose that to one point. So this gives us a floating bar chart. And the good thing here is that you've got dynamic ranges. So if you wanted to change your valuation ranges, so let's assume that you are not quite as bullish. Let's say you want the low range to be 26.

I changed that. You can see the line gets a little bit skew if, but then if you make them both 26, the line will automatically move. So this is a dynamic floating bar chart with not just the ranges, but also the share price and the bid range on it too.