 Hello and welcome to the instructions for Worksheet A Part 3. This is the final tab and it teaches you how to create graphs. The graphing will be slightly different depending on which version of the software you have, but there should be enough similarities for you to be able to sort out the differences. In this worksheet I have for you data, bond yields, interest rate data. You're going to learn about what they mean and what they are during the class. But I have some data that have to do with the interest rates that are being paid on municipal bonds. Municipal bonds are bonds that states and cities issue and treasuries are bonds that the U.S. government issues. Municipal bonds are usually tax-free. We'll potentially learn about that a little bit later in the course. These are the yields and these are the dates. What I'd like you to do is create a graph that shows how the yields change across a range of dates. Historically how our bond yields priced from 1993, I think the data goes through 2011. The first exercise is to create a graph of municipal bond yields for the dates given. Usually the best way to go about doing this, and there are a number of different ways to do graphs. You find the way that works best for you. You highlight the data that you'd like to put in a graph. In this case we have the time, what's going to be on the x-axis and the left-hand column. Usually that's the best place to put what you want in the x-axis. The Excel or other spreadsheet software kind of assumes, even though you can change it if you really need to, it assumes that whatever you put in the left-most column is going to be on the x-axis. Then it assumes that if you put columns to the right of it, that those are going to be different data series on the y-axis. Since the first exercise only asks you for one line, we can simply highlight the two columns together, sort of indicating to Excel, hey look, graph this by putting the first column on the x-axis and the second column on the y-axis. Let's see how well Excel figures it out. We highlight all the way down there, and then we can move back to the top, so we can see what we're doing. In Excel 2007 what you do is you go to Insert, and you go to Graph. In other versions you might have to do it at Menu Bar. You might not be able to have these pretty pictures, but one way or another you have to find the Insert Graph. You want to choose a line graph, depending on the software again there's a few different ways to choose a line graph. You want to choose a line graph, and depending on the software again it might pop up this example, or it might ask you for a few more steps about what you're looking for. But generally what you're looking for here is this graph, and you can see that it has dates on the x-axis, that's what you want, and it has yields on the y-axis, that's what you want. Excel sort of knew how you wanted this thing to be formatted, because it's given you a fairly appropriate range on the y-axis, and it's given you an appropriate range on the x-axis, but you can change all those things, generally by right clicking on where you'd like to change them, and formatting the axis. Now depending again on the software it's all going to look different, but you can add or take away the horizontal lines, you can change the scale, you can change where the axis begins and ends. In other words, if you wanted to show negative values or positive values, you can play with all that stuff here. There's certainly no way I can go into all the details about how you can format it, but you certainly need to spend some time playing around with the formatting options. One of the things that's important though is to make sure that you've selected the right data, and that the title of the data appears. One way to do that is to right click somewhere on the graph, and if you have Excel 2007, one of the most useful things to find here is the select data button. I found that by right clicking on the graph and going to select data. This is really the heart of your graph. This is what's telling Excel where the data is. Over here it's telling Excel what data should be in the x-axis. If you click on edit here, if you ever needed to change that, you can just highlight a new range of data and it would do it for you. I didn't need to do it because it knew it right. And over here it's telling you what are the series, what are in the y-axis. In this case we have one series, but we can edit it and we can make it a little bit nicer. We can type in the series name. We can type in municipal bond yields. Push OK. And now it says municipal bond yields. That's our blue line. It's got a lot of built-in features. It also titled this graph municipal bond yields. So that pretty much takes us through the first one. Although let me do some formatting for you just to show you. These things look a little bit too tight, a little too close together. I don't like the way that looks. So we'll go to format axis and intervals. Instead of specifying that we want automatic intervals between labels, let's try maybe five instead of one. See what that looks like. No, that still doesn't look good. I mean there's a lot of trial and error. Even I've used Excel forever. You still need to try an error at 20. It doesn't look good either. And a nice one. Let's go with 10. There we go. Now we're getting a little bit nicer intervals. Now we still have too many ticks. Do you see how there are all those tick marks down there? We don't need that many tick marks. That looks pretty messy too. So we can tell it. Format the axis. See where it says interval between tick marks. Switch that to the same as the interval itself. 10. Then we'll just get a tick mark at every interval. Now that's starting to look much nicer. But you can see that certainly I can't teach you in a small tutorial all the formatting options for the graph. But the trick is to play around with clicking on different things. Clicking on this axis, right-clicking. Looking at the options. You can click on the trend line. Right-click on that. You can format the data series. You can change the color. You can change the fill. You can make it dotted or spaced or dashed or whatever. You can pretty much do anything you can think of. But that looks pretty nice. That's a nicely formatted graph. If we were going to read this graph we would say, oh look, the yields move up and down. And they spike in certain periods for certain economic reasons which you'll learn. And overall since 1994 they've been trending downward. But there's this big spike right here. 2011. And if you read the newspaper, you've been following the state and local governments have been having a lot of problems with their municipal bond offerings. And this spike here is suggesting that the risk of owning those bonds is going up. But you're going to learn more about that in Chapter 7. Okay, let's move on to the next question which is, format it so it looks nice. We did that. Create a graph of municipal bond yields and treasuries on the same graph. There's two ways to do this. We could just delete this graph, start over, highlight both of these lines, and highlight this whole range here. Excel would know what to do. Since we've already gone to the trouble of formatting this graph, what might be easier is to right click on the graph, go to select data, then all important select data area, and just add another series. Right click on add. This will pop up the series name. We want it to be treasuries. And the series values, we click on this little box so that we can highlight, and we just highlight the whole range of treasuries that we want included in our graph. And push enter. Okay. Now we've added another series, and that should put another line right in our graph. Perfect, that's just what we wanted. We're going to call the treasuries. All right, there's another line in there. We lost the title now because Excel doesn't know what we want to call it. If we go to our graphing... Oh, there we go. If we go to our graphing tab... Oh, well, I can't find it now anyway. Change chart type. Oh, never mind. I'm trying to figure out how to add a title in there. I have to play a little bit more than I desire to at this moment. But you can see that it's added a second line. It's a different color. We can manually go back and change the color if we wanted. That's fine. The third thing it asks you to do is create a graph of the municipal bonds yield minus treasury bond yields. Now this one is important. You're going to have to do it for your homework. So I want to start from the very beginning. So we'll delete this graph. Delete. So the first step is, look, you don't have... You need a column of data that has the treasury bond yields, the municipal bond yields minus the treasury bond yields. The first thing you have to do... This is called a spread. You're going to learn about spreads in chapters. I can't remember the chapter. You need to create that column of data. And we want municipal bond yields minus treasury bond yields. So we enter a formula equals municipal bond yields minus treasury bond yields. So we have that... So on this particular day in 1993, the difference was negative 0.82. That's fine. But we need to calculate that for all that. So what we do is we copy this formula. I'm going to use the shortcuts, control C. Then I'm going to move my cursor down to the very bottom of this column. You could also just highlight the whole range, but I'll show you a little shortcut. By holding down control and pushing the down arrow key, it gets you there. By the way, I have shortcuts on this page telling you how to do all that. Move this up, hold down control and shift and push up. Highlights that area. And then I'm going to paste. And that's the fast way to paste a whole column of data. You can do it the slow way if you want, but that's the fast way. Now we have the right column of data. This we're going to call the spread of municipals over treasuries. This is the column of data that we want to graph, the spread of municipals over treasuries. Again, you're going to learn exactly what a spread is in one of the chapters of the text. Right now, I just want to teach you how to graph it. Well, we have a little problem in the sense that if we highlight this whole area, we're going to get too many lines, but that's okay. We'll just delete the ones we don't want. So we'll highlight the whole area. Line graph, line graph. See, it showed us three lines. We don't want all three lines. We just want the green line. So what we're going to do is we're going to right click on there. We are going to go to select data. We are going to delete the first two, assuming those are the ones to the left, which we'll check in a second to find our graph again. It's at the bottom of the page. That's right. We left that one there. We should probably name it. If we name it, select data. We go to edit the series, and we click on the series name. Now, we don't have to type that whole long thing in again. We can also use a cell reference. We can just click on that cell. Type okay. Now, whatever we type in that cell, we'll change the title of the series in the graph. Okay. There it is. Now, it doesn't look very nice. Why does it not look very nice? It doesn't look very nice because we have some negative numbers and we have some positive numbers, and Excel has stuck the axis at zero right in the middle of the graph. We don't like that. We would rather have the axis down below at a different value like negative two. The way that you fix that is you would think that you would right click on the horizontal, on this vertical axis, sort of move it down, but you don't do that. You right click on the x-axis. Then you click on format axis, and a little button will appear that says vertical... No, I'm sorry. You click on this one. I have to try and fail every time. You right click on this one and you click on horizontal axis crosses at. And here it says automatic, but we want to select a special value. Let's move it down to negative two. If we tell it horizontal axis crosses at negative two, push close, that will move the axis down to negative two. And then we can format again the way we like and we can get rid of these little tick marks and it will look nice and pretty. The last thing you need to do is to copy and paste this into a Word document. I'll show you that really quickly. Click on the graph. Control-Z for copy or right click for copy. Oops, I pushed paste. I'm going to control-Z to undo that. Copy, open up a Word document or open Office or whatever. I'm going to show you another little trick. When you paste a graph, you want to paste special that graph. You don't want to paste it. Pasting it, unless you really want to, if you just paste it, especially in Word, it will link the graph in the Excel file or it will put an Excel file in your graph and you'll be able to edit the graph and change some things in the graph in the Word file. I don't like that. I prefer pasting my graphs in as pictures so they can only be changed in Excel so I know where the changes come from. I go to paste special and I choose picture. Any picture file is fine. And I push OK. Now this thing is a picture. It can be resized like a picture. You can make it smaller in an easy straightforward way and nothing you do will mess up the data because it's a picture. You can't change the numbers anymore. You can't do anything but keep it as a picture. In my opinion, that's the neatest and easiest way to paste graphs into Excel. Let me click back to our worksheet. Make sure we've covered everything. You just have to do again what I just did to remember that the challenge for you is going to be playing around with the formatting, playing around with where in your particular version of the software all that stuff is located.