 Hello and welcome to the second part of the instructions for the first Excel worksheet A. We left off over here with copy and pasting formulas. If you recall down at the bottom, you can switch between tabs. And now we're going to move on to the data table. The data table is a very useful tool, but it is a little bit complicated to use and takes some practice. So I'm going to walk you through this whole sheet as an example. And I think you'll see what it's designed to do. The way that it works is you need to start with a model. You need to start with any model that has variables that you are interested in changing. In this case, what we have is a simple model of B minus A. In other words, we have input cell B right here. That's 2. We have input cell A right here. That's 1. And we're interested in calculating B minus A. We're obviously not interested in 2 minus 1. We're interested in what happens to the difference between B and A if B and A change. This is a simple example. But in a more complex model, you might be interested in understanding, for example, what happens to the price of a bond as interest rates change from maybe 1% to 5% and you'd like to understand how the interest rate changes in half percentage increments. You're going to be doing that in one of your future homeworks. But this is just a basic orientation. So how do we begin? We begin by building our model. You have a part of it already built in front of you for you. These are hardwired into Excel, meaning there's just a simple number in there. There's no formula. You can remember you can look up to your formula bar to see that. But here, the model, the Excel working parts, has a formula. And I told it what I want the formula to be is B minus A. I would like this cell minus this cell. So what I did is I typed equals if you recall, then I clicked on B10, then minus, then I clicked on B9. So we have a nice formula in there that gives us B minus A. And you can see that it works because if I change input cell B to say 5, right away the formula changes and the answer is 4. So now what the data table does is if you look down here, it's going to populate, once we tell it what to do, it's going to populate these cells in here with B minus A. So in other words, take a look at this cell right here, B22. Once we build the data table, it's going to give us the result of 5 minus 1. That's going to be 4. And over here, it's going to give us the result of 4 minus 2, and that's going to be 2. So in order to build the data table, the first thing we need to do is establish what it is that we want Excel to change, what it is that we want our analysis to be sensitive to. And these numbers, that's what these numbers are. And those are just entered in there. I'll show you an example of how we do that in just one second. So these are the numbers I'd like Excel to test out, sort of try out for B. And these are the numbers I'd like Excel to try out for A. And it's very important that they're constructed in just this way. There's a column right here. It has to be a row that begins right here. And it has to have this empty cell or this cell with a 4 in it at the upper left-hand corner of the table. That is absolutely critical because that is the cell that Excel is going to look for to construct the data table. And what needs to be in that cell, if you click on it, needs to be the formula that you're interested in having the sensitivity analysis on. Now, the way that I've done that in this particular case is I have just used another formula to tell Excel, hey, look, what I'm interested in is cell B12. So I just said, I'll type it again for you so you can see it, equals B12. So I told Excel, hey, this is the formula that we care about the analysis and it's actually located in this part of our worksheet. So I just created a direct link using the equals. You can see that in this explanatory bar right here. We could have reconstructed the formula down here simply by typing equals this minus this. We could have done that, it would have worked just the same. But if it's a quite complex formula that you've already spent the time to develop and build, it's much simpler just to type equals and the reference to the formula. Okay, now that we have the basic down, we need to show you how to enter the data table. It's slightly different in each version of Excel or other software that you might be using. But what you need to do is you need to highlight the entire table, including the cell, the main, the corner cell there in the upper left-hand corner. Highlight the whole range that you'd like the data table to occupy, including the columns that you've done, but not the labels of the columns, just the numbers themselves. In this version of Excel, you go up, if you're starting on the home tab, you go up to the data tab and you click on what if analysis and you find data table. On other versions of Excel and on OpenOffice, I believe it's under tools, data, a data table or something like that. You just have to fish around for it, maybe do a search on the help for the data table. Once you locate it, you click on it, this should appear. And this is very important. What it's asking you is it saying, it's saying, look, you've provided me a row of numbers. These are the numbers in the row, okay? The numbers on top are in the row. You provided me a row of numbers. I am going to take those numbers and plug them into your worksheet for you, but I want to know where you want me to plug those in. And we're going to say, look, Excel, I'll take these numbers that are in my row and plug them into this cell. So Excel knows, plug those numbers into there. And then the column input cell, this is the column input, plug these numbers into here. And then run the formula as many times as it takes to populate the table and give me the results. So I push okay and look what it does. It fills in this space with the answer, which we specified up here, B minus A. So you can see, if you click on any cell, what should equal B minus A. 3 minus 2 is 1, that's correct. 3 minus 3 is 0, that's correct. So this data table is working properly. Let's do another example just so you can get a sense of how it works and then you can do them again on your own. The first size involves creating a division table and it's asking you to create a table that divides the A column by the B column. So what do we do? Here I've set up a template for you to begin entering your model. So before we even move to the table, we should think, okay, we need to create our model, we need to create our formula first. And what we want is A divided by B. But there's nothing in these cells. In fact, what's in these cells, it doesn't really matter what numbers you put in there because what numbers are going to come on the data table are the numbers you specify on the rows in the column. So you can really choose any numbers to begin working with your data table. You can choose even something obscure like 47 and 18, even though later we're not going to ask you to do that. But as long as the formula works, that's what matters. Now we need to enter our formula. We type equals A divided by B. Enter. So I'm going to secure a number. It doesn't really matter. Now we want to begin to build our data table. The first thing we want to do is specify the range over which we'd like Excel to calculate the value of the A divided by the B. In this case, I've asked you to do it to 12. So I want you to construct a table that goes from 1 to 12. So first we need to delete my instructions. And then you can start typing 3, 4, 5. But you remember there's a shortcut to Excel. Notice it's better than that. It remembers patterns. If you highlight both of these cells, click in the bottom right-hand corner of this box and drag it out until you get to 12. That's a little bit faster. The same thing down here. Click the pattern. Drag it out until you get to 12. You're done. You can just delete these. Oops. Sorry. I'm going to show you why I did that in just a second. Let me clear it for a second. Remember now we have created the model up here, but in order for the data table to work, the reference cell needs to be in the upper left-hand corner. So we're going to type equals, and then we are going to click on this cell to tell Excel, hey, this is what we want in the cell. Remember, if you click on this, even though this number appears, that's not what's up there. Sorry, what's up there is the reference B33, which is referencing this formula, which is divided by B31, which is A divided by B. Remember, to create the data table, we highlight the entire range that we'd like, including the upper left-hand cell. We find in our Excel or our open office where we access the data table, and this box should appear. Then we need to tell Excel, okay, Excel's asking you, you would like me to perform the calculation specified in this box. But I need to know where this row of numbers are located that you want me to plug in to this formula. And the row is located here under A, so it's asking you for the row input, that's A. And the column input, these numbers here are B. That's there. If we screwed this up, if we told Excel the wrong thing, it would give us incorrect results. What it would do, if you think about it for a moment, if we switch them, instead of giving us A divided by B, it would give us B divided by A. We finish that up, we push OK, we check our analysis. I guess before we check it, we can make it look a little bit nicer. You could click on the home tab, go back to formatting, and we could adjust the number of decimal places. We don't need that many decimal places. Actually, I must have just expanded that viewable area. There we go. Oh, sorry about that. Keep doing the same thing. Ah! I want fewer decimal places. Then you can't see. Oh, no, you can see. Okay. Sorry for that little hiccup. So now we have equal number of decimals. And we can check if it's correct. Do we in fact have A divided by B? Well, let's take a peek. Right here, for example, and the diagonal, we should have all ones because A divided by B is going to be one in that case. Over here, four divided by two, that works. That's also equal to one. Actually, it would be a little bit prettier if we adjusted these to have no decimals. And these... There we go. That's a little bit prettier. The other thing that you can do to make this table pretty is this is kind of weird up here, this random number. You cannot delete it because you can't change part of a data table. It needs to be there. But you can hide it. And the trick to hiding it is by making it white. The same color as the background. Go to your text. Do it with us up here to change your text color. Change it to white. And you can't see it. It's still there. If you click on it, it's still there on B33. But you cannot see it. The other way to make the table pretty is you would maybe rather have this A centered across the table. The quick and dirty way to do that is to cut it, to paste it in the middle. That might not be right in the middle, depending if you have a large number of columns. One little trick I'd like to show you is... If you click on that cell, highlight the area that you'd like to have it centered over. Now, this isn't that critical, but it's just something I thought I'd show you. And if you go to... If you right click on that and you go to format the cells, and then you click on... Oh, here we go. Center across selection. Push OK. That will move... That will keep the value in cell B35, but move it over to cell A. And the benefit of that is you don't have to merge all the cells together. If you don't know what merging cells is, don't worry about it. That's just a nice trick I'm going to show you. Okay, I think that I'm going to put the rest of the instructions in another file, since that file was slightly large. Sorry for that, but I think that's it.