In this screencast, I'm going to be teaching you about one-way data tables in Excel. I'm going to start this discussion by working through where we left off on the previous screencast, this is on a file called loanpayments. In the previous screencast, we set up this column of monthly payments as a function of the amount that we could pay. So this is how most people perform a case study, they put a formula into a cell and they drag it down. And this is fine, but I want to show you another option. So I'm going to change this worksheet around a little bit, I'm going to move the principal up here. I'm also going to put the number of payments up here and I'm going to drag all of this down one row just to make a little more space. And for now, let's just delete column B. For one-way data tables, you set up your spreadsheet, you set up an area of your spreadsheet where you calculate one scenario, a single scenario. And actually, we still need an area here for payment. So let's just put in A and that's going to be our dollar per month. And let's just put in right now $350 and let's go ahead and make that currency. All right, we're going to set this up for a single scenario. We have our interest rate, our principal P, and I'm going to go ahead and name this A for our payment. And now the number of payments just based on this single scenario, and actually I'm going to drag this down a little bit more to give us more space. Let's just calculate using the NPER function the number of payments based upon this scenario that we have up here. So I'm going to put in i divided by 12, that's our rate. Our payment is going to be negative A, payments are negative values in Excel formulas, comma the next argument then is our principal, that's P. We don't have a future value, the future value is 0, because we want to pay off this loan. I can press Enter, and so it's calculating that based upon a monthly payment of $350, it'll take us 24 payment periods or just over two years to pay this loan off. Now we want to perform a case study, we want to do a what-if analysis. What if we pay a 150, 200, and so on, and how will that affect the number of payments? And we're going to do this using a one-way data table. I'm just going to put in another label up here. I'm going to copy the number of payments and we're just going to replicate that here. You notice I've skipped one row, this cell here, and I'm going to highlight this yellow. This is a very special cell, so this cell in a one-way data table is a very special cell. This is where we link to where on our spreadsheet have we calculated whatever we want to put in this column? We want this column here to be the number of payments, where on the spreadsheet am I calculating the number of payments? I'm calculating that in cell B7. So I'm just going to click =B7, this is known as a Likert formula or a pointer formula. So I'm just replicating the number of payments and that's really important. This here is known as a column input vector, it's really important that one row up and one column to the right you put the number of payments. Now that we have this set up, I can go ahead and highlight, I'm going to highlight this entire region A10 down to B20. Again, you notice that I have that top row and I'm going to go up here to the Data Tab > What-If Analysis, and there's a Data Table option here. So let's click on Data Table, I can bring this up. And we're doing a one-way data table, for a one-way data table and the ones I'm going to show in this course are, they're typically done column-wise like this. We don't have something known as a row input cell, but we do have a column input cell. Our monthly payments over here that we're varying, this is known as a column input vector. The column input cell is what variable on the worksheet corresponds to your column input vector, that's the column input cell. Our column input vector is our monthly payment, in our single scenario up here that we set up which cell corresponds to the column input vector? Well, that's going to be cell B5, so B5 represents our monthly payment. And again, that's our column input vector, and so that's our column input cell. So I can go ahead and click OK, and when I click OK the Data Table tool is going to take every one of our items here that we've highlighted the left column. It's going to place that into the column input cell that's cell B5 one at a time. And whatever remains up here in the upper right most cell, the Linker cell, it's going to put right next to that value of the payment. So it's going to put 150 into column B5, whatever results there it's going to put right next to it. Then it's going to go 200, is going to put it up into cell B5. Whatever results in this upper right cell, it's going to put right to the right of it. It's going to do that, it's going to perform a case study for all of these different values of the monthly payment and it's going to fill this out, so I can click Ok. And it does that very quickly, and I've got it set up over here on this plot, and that's another way to perform a case study. To be honest, it's a little easier in this situation to just do the formula approach that I showed at the very beginning of this screencast and I showed in the intro to case study screencast. But in the next example, I'm going to show you why you might want to set it up to use a one-way data table tool. It's typically preferred when you have a lot of complicated calculations going on between your inputs and your output. This is a very straightforward calculation, we can just put in those parameters into our NPER function to get an output. But in the next example, we're going to perform a couple of conversions and calculations. And so if that's the case, it's oftentimes preferable to use a one-way data table.
