In this workouts we're asked to produce an XY scatter graph of return on equity (ROE) versus the price to book value ratio or P/BV. This can often be called the price to tangible book value and we've got that shorthanded here. Now this particular example looks at banks and what it means is if you have a higher return on equity, you tend to have a higher share price. And we use price to tangible book value as a proxy for that, but it's not restricted to banks in any way. We could be looking at any two things here that have some kind of link or causal link between them. So let's see if we can do this XY scatter graph. I'm going to select the data, then go to the insert ribbon, and there's an XY scatter option here. But if I click on them, none of them are really giving me what I expected. So instead, I'm going to go to the recommended charts and the second one down here, that is showing me a much better graph. That's what I expected, as my return on equity at the bottom goes up, my multiple of price to book value goes up as well. So recommended charts can be useful. I press okay, and there's my chart.

Let's put some titles on it. So chart title equals that. I need to add some axes title. So make sure you've got something in the graph selected, then go to the chart design ribbon, add chart element, axes title, primary and primary vertical. Axis title at the bottom, that's my return on equity, or ROE and access title on the left, click on it press equals that's the price to tangible book value. Great. And I can see this relationship, a low return on equity of 6% gives me a low multiple of 0.5, whereas a higher return on equity of like 12 or 14% up here gives me a higher multiple of two. But I'd really like to see a trends line. So I'm going to click on the data points, right click and then add a trends line. And there it is. I've got a linear line. I'm going to display the equation and I'd like to just move that up to the top, right so it's easier to see. And if you want to change how the line is looking, you can click on the paint tins, the fill in line button. And then the dash type for instance, you could change that to a solid line and you could change the color if you wanted. That's looking really good.

The big thing I'd like to identify though is which bank is which data points, because the data points below the line, they show me that that for a relatively high return on equity, I've got a low price. So those banks are undervalued, whereas banks above the line, again, might have a very high ROE and a very high price. So they're perhaps a little overvalued. So how do we do this? We click on the data points and I'd like to add data labels. So I could right click, I could add data labels, or I could go to chart design, add chart elements, and add data labels here. Now there's lots of options. We could go center, left, right above. I'm going to go with above to start with, but unfortunately it's given me the numbers. I didn't want the numbers. I need the names of the banks. So I'm going to right click on those again. And then I'm going to format the data labels. And that appears on the right hand side, the label contains currently has the Y value ticked. I'd like to change that to X value instead. So tick the X untick the Y, but again, it's still just giving me numbers. So instead of that, I'm going to untick the X value and go for value from cells. And this dialogue box pops up asking me where my data label range should be from, and I'll click on the bank names. I press enter, ah that's much better. I can now identify which bank is which data points. It's not looking beautiful though, but the great thing is I can now just click on our data label and move it around and Excel will add some lines so we can identify which one is which.

So I just click on them. Then I just find the line around it, click and hold on the line and drag.

Great. And now I can do some great analysis and perhaps decide which banks are over or undervalued.