Hey, it is on the hour, so I think we should get started.

We've got a corner of people here.

So my name's Alistair Matchett.

I started my career at JP Morgan, um, doing m and a advisory, working in the oil and gas and the financial institutions sectors.

Then went to work in private equity before going into the education space.

So we have got about half an hour together, which is a really tight, um, timeline.

So what I'm really going to focus on is three main tasks.

One is doing just a price volume chart.

One is doing a regression chart and then one is doing a football field chart.

And maybe we'll get onto a pie chart as well.

So this is going to kind of give you the fundamentals of charting.

Let me run through some slides just at the start just to kind of give you a flavor.

So I'm gonna do that first and you should be able to see my, um, slides now.

So what I'm going to do is if you want to ask me questions, please don't hesitate to, um, put a question in the q and a section.

So if you look at the q and a, not the chat, but the q and a, um, what we, what I'm also going to do is I'm gonna put in the chat window and it's a bit confusing, but unfortunately Zoom, um, does this, I'm gonna put the file I'm gonna be working in, in the chat window.

Okay. So I'm gonna put the file I'm gonna working in in the chat window and it's gonna be a zip file.

Okay? So I've just done that right now, but if you have questions, please don't hesitate to put them in the question and answer section.

Okay, let's get started. So I'm gonna go through some slides pretty quickly just as a review.

So I'm gonna go through printing a chart, creating chart elements, formatting, working with sex and axes, adding a trend line and doing a football fo football field file.

So the file that I'm working with is in the chat window.

Let me, if you've just arrived, um, I'm just putting in again so I have to re-put the file in. Again, once you've arrived, if you look at the chat window, um, it should be that you should just be able to download the file from the chat window.

Okay. Alrighty.

So let's just quickly whi through the slides 'cause I really want this to be much more of a um, uh, a kind of practical session.

So one of the key things when you're doing charts is that you can either get Excel to come choose the chart for you, but generally speaking in banking, um, if you don't have a chat window other than this q and a, um, I know my colleague Numa is on the line.

So I dunno if we can get the file sent to the class numa by email.

That would be really great if you can do that.

Um, I, we will make sure you have the file at the end of the session.

Okay. So we will make sure we have the file being the end at the end of the session.

It may be due to some of your corporate sellings, um, not being able to access the chat window and unfortunately Zoom doesn't allow us to easily set that up, but I will sort that out afterwards.

So you can use recommended charts, you can use an auto chart or you can specify the chart and there are just some shortcuts here that we can use um, to create charts.

And then you can add chart elements.

I'm gonna go through this. I'm not gonna spend huge amounts of time in the slides 'cause I really want to show you how to do it.

But when you come to the formatting, um, with the charts you will have kind of three items and it's kind of worthwhile understanding this. You've got these three icons, little bucket which is the fill and line, the effects, which is the kind of pen and the size and properties, which is that little box with the arrows inside it.

Okay? So we will get the file probably at the end of the session, we'll email it to you and I'm really sorry about that unless auma, you can email it to the class.

Um, as we go.

Um, the best thing probably to do this is actually um, to do give you an example.

So actually what I'm gonna do is I'm actually gonna start go straight to Excel and I'm gonna start with an example.

So here what we have is we've got a dates, we've got price for the s and p 500 and we've got the volume and then we've got the total return and the price earnings ratio.

What I suggest you do if you want to do a chart, it's always best to split the data into its own area.

So what I'm gonna do is I'm gonna insert a column here and make sure my data is separated because otherwise Excel will do all sorts of things.

So I find it much, much easier when you're creating charts is literally to separate the data out.

Then what I'm going to do is I'm then going to insert a chart.

Um, and I'm gonna do that by if I do alt um, n for insert and then you can see I can choose recommended charts.

Um, but I'd actually like a line chart here.

Okay, so I mean I can choose this line chart and it's gonna gimme um, various different options. Here you can do a stack line, which I don't want for this, um, a hundred percent stack line, which I don't want, but I kind of want this first one here, which is just two line charts.

Now when you do this, if I just zoom in a bit, you can see what happens is because the volume is so much larger than the price, you can see that actually you can't see the price because it's so small in comparison to the volume.

Okay, so let me just make sure I'm just getting a message from Nuer. There we go. I just sent her the file.

So what I'm gonna do first is I'm gonna click on the volume line.

I've just clicked on that using my mouse.

And then the easiest thing to do is if you right click it then you can, you can add a um, second axis. So if we just go to format the the data series, I'm going to choose secondary axis.

And what this will do is it will put the volume on a secondary axis and that means now you can see the price is showing up.

Okay? So you can see the price is showing up and you have the volume.

So let's just take a look at that. So what we've done is we've got the volume on the right axis here and we've got the price on the left axis here.

Now this does look a bit messy and generally speaking what I prefer to do is if I just go and see if I can change the axis first.

So this is the volume axis and I'm going to right click it and just choose format access.

And what I'm going to do is it gives me a kind of ma maximum and minimum here.

And if I just move to the right, what I want to try and do is make the maximum quite high 'cause I want the volume to be underneath the price.

So at the moment it's got about 7 million.

So what I'm gonna do is make it probably, um, let's make it 12 million there.

Can you see it pushes the kind of volume down? Maybe even make it um, 13 million.

Oh that's a little bit too much.

It's actually um, no that's just too much.

Again, it's 'cause it's in, let me just uh, make it save 12 million.

We go, there we go uh, make it 13 million.

So this kind of pushes pushes that down so it doesn't kind of cross the price axis. You can actually do it on the other axis as well.

You can make this, if I just go to the left hand side, I can make the minimum, the maximum smaller.

So if I just click on this and um, oh sorry this axis and then I just choose format access, then what I can do is I can choose a maximum of make it slightly smaller, maybe make it 5,000 and hopefully that will push that up a bit. There we go. So you can kind of play around with that um, over to try and make the graph look right.

The other thing that I'd like to do is I like to change the formatting of this number and if I come down to the number at the bottom here, can you see if I just take the zeros out, I still want to keep the whole number 'cause that's the actually s and p price.

And then choose add.

Can you see it just takes the decimals off and if I go to the right and in this case what I'm going to do is I'm going to, it says a general format code.

I'm actually going to do, I'm actually gonna choose a number code or or custom.

Let's do a custom that we go.

And if I do, um, zero, oops, sorry, hash Comma hash zero, but I want it to be in thousands.

If I just put a comma there, can you see that's actually in millions.

But if I actually want it in millions I'll put another comma.

So a comma will truncate the number by three digits.

And then what I want to do in quotes put millions.

So mm close quotes and then if I add that, that will be millions.

It actually looks a little bit messy there.

What I could do is I could do um, actually I could just put that in the label.

So if I just take out the mm, 'cause that does look a bit messy and then I just click add that is the actual digit but in millions. So it kind of makes it a little bit more, um, useful.

Now if I just come out a bit, I want to add a few other items to this chart.

I want to do some axes.

So what I'm going to do is if you see in this chart design tab, the top, I'm gonna add a chart element.

So the axes titles, I'm gonna add a primary vertical and then I'm also going to add a primary horizontal down at the bottom.

Well actually no I'm not going to do that. Let me undo.

So I just did control z.

I'm gonna add an access title on the secondary vertical.

There we go. So once I've created these items what I can then do is I can just click in these items.

So this is going to be the s and p price or s and p 500 price.

And then if I click on this, Just control A and this is going to be the volume in millions.

There we go. Now for the chart title, what I'm going to show you here is if I just come up to the chart title, if I just click on that box so I've not clicked in the box, I've clicked on that, that box.

If I just hit the equals and then click on a cell in the spreadsheet with the title I want in it and hit enter, what will happen is that if I change this, the title will also change.

So I could change this to the s and p 500 over time with volume.

And when I change that, you'll see the title change on the chart as well.

The last thing I'm going to do in this chart is I'm just going to change the dates.

So I'm gonna format the access and in this case what I want to do is I want to change the dates and you can see you've got the minimum maximum and I'm gonna change the major units to three and that will just reduce the number of dates there.

So that's my first chart.

It's a pretty simple little chart there, which is just share price over volume.

Now the next chart I'm going to do for you is the would be useful if you are working in financial institutions because in this case you've got a situation where there's a strong relationship between a company's or banks return on equity and it's priced to tangible book value.

So what I'm going to do here is I'm gonna select the data.

So I prefer to select the data first and then I'm going to insert a chart. But I'm in going to insert a specific chart here.

If I go to insert, I'm going to insert an XY scatter chart.

And if I do this, often what will happen is you can see it hasn't actually done the XY scatter, right? So I'm going to delete that again and I'm gonna going to see if I can get a recommended chart.

So I'm going to insert a recommended chart and now in the recommended chart it actually does do it properly.

So sometimes a recommended chart is quite a good place to start.

Now if you look here, what this is showing us is this is all on the left axis. It's the price to a tangible book value on the bottom axis at the return on equity.

And what this means is, is a bank's price to book value is driven by its return on equity.

So I'm just do a um, title, which is going to be ROE versus price to book value.

Hit enter. Then I'm gonna click on the title and do equals and then I'll click on that title in the spreadsheet.

Then what I'm going to do is I'm gonna quickly adding some axes titles. So I'm gonna go to the chart design and I'm gonna add um, axis titles on the primary horizontal and on the primary vertical.

And this is going to be price to book value and this is going to be return on equity.

So it's quite quick to do this.

Then what I'm going to do is I want to add a regression line to this chart.

So if I click on the chart and go to add element and I go down to trend line, I want to add a linear trend line here and you can see that.

Now if I click on that, that line and I right click it, this is a bit messy to do this and I format the trend line, um, firstly I don't want it to be a dotted line. So I'm gonna change that to a fixed line. Okay? So that's just in the paint box there.

Then if I go back to the chart options, I want to display the linear equation on the chart and the r squared, which gives you the strength of the relationship.

Now once you've done that, you can click on that and just drag it to the top.

So things you can really move stuff around charts, it's really, really helpful.

So I've just moved that to the, to the top right.

Then what I'm also gonna do here, and this is actually really important for these type of charts, is I'm gonna change the axis.

'cause actually the axis is too wide.

So at the moment it goes to maximum 14, but I want a minimum of probably 4%.

So if I just hit four, can you see that makes it much better? And what I also want to do is I want to add some data labels.

So I'm gonna select the data points and then excuse me, go to data labels and then I'm going to add, and I'm gonna put them, it doesn't really matter, I'm gonna put them above and you can see I've got all the data labels there.

Now I actually want to change this because I have got the numbers, but the numbers are really kind of irrelevant. What I really want to see are the bank names next to these items.

So if I go to the format, data labels, options and under charts, what I'm going to do is I'm gonna deselect the Y value, I could use an X value, but again that's useless.

But instead I'm gonna choose value from sales and it's gonna ask me for a range.

So I'm then going to select a range of the bank names and click on, okay, now when you've got this, what you then can do is you can manipulate this by just clicking and dragging a box and it will give you a really nice little arrow so you can actually see if the names are over each other.

It makes it much easier to see.

So this is very, very helpful if you are in financial institutions 'cause this is a regression line and it's showing bank's valuation driven by the return on equity.

If a bank is below this line, it's undervalued.

If it's above this line, it's overvalued.

So that's how you could do a regression line with data points.

Okay, I know we're running through this, but there's just a couple more things that I want to show you.

One chart that I know people really love in banking is a football field chart.

And what I'm going to do here is I've got my valuation methodologies here.

I'll call this the valuation methodologies.

Okay? And I've got all of them listed here and then I've got the low value and then the high value is the difference between the low and high because what I want to do is a stacked bar to get a floating football field chart and this nice bells and whistles I'm gonna show you here.

So if I just select the data, I'm going to insert a chart, alt ih, and I want the stacked bar.

So the recommended chart is quite good 'cause generally it will sort things out for you.

So I've done a stacked bar and then I'm gonna click on, okay, now the first thing I want to do is I want to hide that blue bar.

So I'm gonna click on the blue bar because that is actually the first set of numbers and I'm going to make it, I'm going to make no border and no fill.

And if I do that you can see it disappears. So that is actually a stacked bar, but now it's a football field chart kind of valuation summary chart where you've got the valuation ranges.

Now you can um, you can, if you want, you can put the um, data labels in.

So actually let's not do the data labels because you can kind of see how to do that. You can just add data labels, but you've gotta make sure that you take the data labels from the high to to the right and then from the low in the inside of the bar.

I can maybe do that if we have some time, but I'd actually prefer to show you some other stuff first.

I'm just gonna do a bit of a cleanup.

I don't need the legend.

So if you don't need something in charting, just click it and hit the delete key and it just disappears.

The second thing I want to do, and for the chart title, I'm just gonna click on that box at the top, do equals and then click on a cell with the title in.

And that means I've got a very flexible title.

Now what I'd really like to do here is I want to add some share price lines, a low and high share price line, which will mean that I've got a, um, range of valuation that I can put in.

So what I have to do is I've got to add some data, and this is gonna feel a little bit weird when I first do it.

So if I just, um, I'll call this low and then the series values are just going to be the 1450.

So this is going to be um, the value range.

Oh, let me just see if this is, oh let me go, let me just change that, just delete it Now actually get my selection, right? So this is going to be these two here, right? So what I've done is I've added some data into the chart.

Now this seems a bit weird, but lemme explain what I'm trying to do here.

What I'm trying to do is I'm trying to do a long line with a low share price and then a long line which is gonna be vertical with a high share price.

So what this means is I'm going to overlay two types of charts on top of each other, a a stacked bar chart and an XY chart.

This is very, very helpful because you can do lots of things with this.

So if I click on this and if I just um, format data series, actually I want to go into the um, if I go to the select data, no I want to go and make this into a change chart type. Hopefully I just need, oh gosh, let me just come in here.

I'm going to, there we go.

Change series data change series chart type.

So I've added those two numbers and I'm gonna change a series chart type.

And currently I've got the stack bar for the first two ranges, but for the third range I want to do an XY scatter chart.

Okay? So I'm do an XY scatter chart, then I'm going to click on, okay, and it's kind of disappeared but it actually hasn't.

It's there and you see that low point there.

And so what I'm also going to do is I'm gonna add some axes and I'm gonna add a secondary vertical, okay? And a secondary horizontal let we go and a secondary vertical.

So these are axes that I'm, there we go.

Now what's happening here is I now need for these ranges, I actually need to swap these AEs around.

So what I'm going to do is if I just select this data here and what I want to do is I want to edit the low. If I go to edit the low, so the series name is low, that's fine, but actually it's the X values, which should be the 1450.

You'll see how this changes in a moment. Okay? And then the Y values is that zero and seven.

It doesn't like me when I just select that we go, my value is that zero and seven.

So can you now see that there's two points, which is the kind of two points which we're going to collect with a line here and then I'm gonna choose, okay? And then I'm gonna add another series, which is going to be high and the X values are gonna be the 1800.

So two points of 1800 and then the Y values is going to be the zero and seven.

Again, it hates me when I put that in.

Just do that again now it keeps adding it to just delete it and try again.

There we go. And then click on. Okay, now if you can see here, I've actually got these two points.

So what I now need to do is format these.

So I'm gonna click on the first one and I'm gonna choose a solid line.

And then I want to get rid of the marker.

So no fill and no line on the marker.

Then I'm gonna do the second one.

I'm going to do the line and it's gonna be a solid line blue again, marker, no fill and no line, which will get rid of those little nodules at each end.

So I've got these two lines here.

So this is, if I did a valuation range, it doesn't look very good, but I'm gonna fix that in a moment of let's say, um, 1400.

There we go. I need to change this axis on the top.

That's something I need to do.

So let me just quickly change that because I need to make it consistent with the one below.

So the maximum is gonna be 2,500. So lemme just change that. There we go. And then it's consistent with the item below.

Then what I also need to do is I can then change this.

And let's say this is going to be, I dunno, 1700.

So this allows me to kind of do a valuation range, but it doesn't look very nice.

And the reason it doesn't look very nice is that I've got to change this axis.

So the maximum on this axis is seven because there's seven lines on the chart.

So if I change this to seven, you can see now, now those lines go right up to the very top and then I'm going to delete those AEs. They'll still be there but you won't see them.

So if I click on the top axis and hit delete, so click on the top axis, hit delete, goes away, click on the right axis, hit delete, that goes away.

But actually those axes are still there.

Now it gets even better 'cause now what I can do is I can add some data labels to these.

So I'm gonna go to my chart design, I'll add some data labels and I'm gonna choose them above.

Okay. And because it's reversed, lemme just go and click on that seven and right click it and then I'll select the data and need to change that.

Okay? And then there we go.

Take the X value off but choose the value from cells.

And for the low it's going to be those two a blank sell.

And the 1700 or 1400, sorry.

And then for this one I'm going to add data labels above.

I'm gonna click on that seven.

I'm going to go and change it from the y value to value from cells.

And I'm gonna select a blank cell and the 1700 and then click on.

Okay, so what that allows you to do is have these lines on the chart that will keep up to date when your valuation range changes with labels.

So you could say, well actually I think the value should be 1300 is a low point and maybe it should be um, 1400 as a high point.

And the that doesn't look great. Let me make it 1500 as a high point.

And you can see the labels change maybe 1600. There we go.

And you can see that the lines move and the high and low number is at the top of the chart as well.

So that, that's a good way in which you can add flexibility to a floating bar chart where you have your valuation lines and your literally high and low numbers at the very top of the chart.

The last thing I'm gonna show you is that if I do a quick, um, pie chart, so I'll do insert chart and we'll do a pie chart.

What you can do here is I'm just going to click put a title in do equals and then click on that label.

Hit enter. I'm going to copy one of these logos.

So actually apple and these are not very good quality logos.

And I'm gonna click on the actual segment of the pie.

And that could be a bar or it could be a pie.

And then Ctrl V and it puts the logo in.

The next largest is Microsoft.

Copy that logo, click on this slice of the pie and get the actual slice, not the whole chart, paste that in.

The third one is alphabet, that's the Google one.

Click on that, make sure you just select the pie paste.

The next is Amazon, copy that.

Click on the next slice of pie and you can paste that in.

So you can actually customize your charts pretty nicely with logo, logos or um, company names.

And that could be either pie charts or it could be um, bars as well.

Let me just add a data label in here.

So I'll add data labels and I'm gonna choose, um, outside end.

There we go. And let's see if I can get the, it doesn't have the company name, which is a little bit annoying.

Um, let me just quickly click on these and see if I can, um, change this.

That gives me the, so I can choose a series name, category name.

Let me just add the series name.

Oh no I don't want to do the category name. There we go.

Category, name and the number.

So that's how you can add logos to charts as well.

So we did art in half an hour.

Vice history, just manipulating the chart.

XY scatter with data labels, football field with automatic value ranges that can shift up and down.

We could have done data labels on that and then logos in a chart.

And that's really flexible.

You can use that in lots of different ways.

We are up in our 30 minutes.

Um, we will make sure that you have, um, the full file.

So I'm actually gonna put the full file, um, into the chat and if you have, I think some people may have the chat window.

If you do have the chat window, then you should be able to get the file.

If you don't have the chat window, don't worry, we will email it to you.

Okay. Um, at the end of the talk, can you, if you ever find the paste format useful for charts? Yes, I do. Actually that's a really, really great point.

Um, is if you have a chart, um, let me just, let me just do another chart here.

So if I just select this other data here, I did, let me just show you this 'cause this is really neat.

So if you just do a, let me put some dates next to this data here and insert a row.

So if you do a new chart, so if I'm going to do a chart here, just do alt ih, insert a chart and then click on, okay.

What's really neat is that if you have a chart, which is pretty similar, let me just move this up to the top to another chart that you've already made, particularly if it's got things like corporate colors.

What you can do if I just click this chart here, if I click the first chart control alt EST for formats, what that should do, oh, it's not done a very nice job there, but it, but in terms of colors and um, formatting like font size, it will change everything.

So if you just copy one chart, control C, select the chart you want to apply the formatting to, and then alt ees and then choose formats and click on, okay, then it will change all the font formatting and the coloring from one chart to another.

So that is actually pretty useful, particularly if the um, if you have a client who is really pernickety about their font colors and um, the actual colors used in the chart.

So that's something that I find very helpful. Okay.

But we are pretty much done.

Thank you very much for listening.

And the answer file will be sent to you, um, in the next half hour.

Okay. I hope you have a great rest of your day and have a wonderful weekend.

I.