Description of the video:
Let's gauge here is measuring pressure in this tank. That's the output. You can imagine a lot of things that affect that pressure, what's inside the tank, temperature of the tank, and who knows what else? I'm not a chemist. But in this video, we're going to explore the relationship between inputs and outputs and specifically look at how we can use data tables to quickly assess how an output changes when our input. One of the more flexible tools in analyzing business problems in Excel is called a data table. Let's video. Let's look at how to build a data table and how to apply a data table. Why data tables. Data table provide a very quick and efficient way to measure how different inputs in a model affect an output. I'm building a spreadsheet with a bunch of calculations, I don't want to have to manually go in and change numbers and somehow manually record the output changing. I want an automated way to do this, and data tables provide that automated way to do it. It can evaluate multiple inputs at once, and it's much quicker and more systematic than manual approaches. It can also provide a steady state output for randomness. So if my inputs are changing randomly, I can use a data table to say, what on average would I expect the output to be? And it allows a complex analysis to be completed with just a few random variables. We're going to get to the point where we're doing simulations in this course, and data tables provide a simple way to execute those simulations without having to create loads and loads of randomness on its own.
What do I need for a data table? Well, I need one or two inputs. Fortunately, I can't do more than two based on how data tables are structured. We'll see that in a minute. But I take one or two inputs and I need to identify an output that I wish to measure. I need a spreadsheet model then that's going to connect the input to the output. If the input is not connected to the output, then when I change the inputs to the model, the output isn't going to move, Data table is not going to tell me anything. Let's look at a quick example here. So I've got a model here. I've copied a spreadsheet screenshot, and this is a loan example right here. So in this case, I'm borrowing $10,000 on a 6.2% interest rate over the course of five years. And to make this problem simpler, I'm just going to assume we're making annual payments instead of monthly payments. I also am going to put $1,000 down. So that makes my loan amount $9,000. You can see here it's just the purchase price minus the down payment, and then I'm going to calculate a yearly payment using the payment function in Excel, and that's shown here as well. So let's think about identifying our inputs and our outputs. I want to try to figure out how does the length of the loan, the term here, currently five, affect my payment? And how does my down payment affect my payment? Now, conceptually, we already know that the longer you make the loan, the smaller my payments going to be. And the more money I put down, the smaller the loan amount is and therefore, the smaller my payments are. But because we know what to expect, it makes a great example for introducing the data tables. Okay? So I need to make sure that my output changes when my inputs change. So let's look at the equations in this table that I've pasted here. I've got the payment function for the yearly payment. And that payment function refers to my interest rate, the terms, and the loan amount. Now, one of the inputs I said was the length of the loan. That's the term in this equation. So that's directly inside my output function. The other input was the down payment. Now, I don't have down payment directly in this function, but I do have the loan amount in this function. And the loan amount, if we go up one row on that table, refers directly to the down payment. Right? So as I change the down payment, that makes a loan amount change, which then makes the monthly payment change or the yearly payment change, right? So, they don't have to be directly connected, but there has to be a link through my spreadsheet that eventually connects my inputs to my output. I'm going to create a table here that you see in the middle of the screen that contains inputs as rows and or columns. In this case, I have two inputs, so I have one in the row and one in the column. Across the top row, I have different links of the loan, a one year loan, a two year loan, a three year loan, a four year loan, a five year loan, et cetera. On the way down, I've got down payments from $1,000 down to $5,000. So different loan terms, I think it's three to seven years, and different down payments, $1,000 to $5,000. In the top left corner, I have another cell reference in red there, and that is a cell reference to the box there that is the yearly payment. So that number is not typed in. Every other number on there is typed in. That number is not typed in. That number is equal to, and then I've referenced whatever cell I calculated the payment inside. So what this table is going to do, I am going to tell it to put those various values for length of loan up into the terms box, where the five is right now. I'm going to tell it to sit those down payments up into the down payment box, where the $1,000 is right now. And when it does that, for each combination of down payment and term, it's going to go back to the cell if reference in the top left corner, read the new value for the payments, and insert it into that table in the appropriate box. Okay? The way I do that is under my data ribbon, I use the table option for what if analysis. I'm going to what if analysis. You'll see a little question mark when we get into Excel, you'll see it. Under what if analysis is a data table option. If I click on that, I get this lower blue box. The blue box asks for these inputs. What it's asking me is for the row input cell, it's looking at my table that I highlighted before clicking the what if analysis, and it's saying, Okay, that top row, those numbers, three through seven, what are those? I need to tell it those are terms of the loan because it doesn't know. It might try to put those in as the interest rate or the purchase price, unless I tell where it goes. I'm going to take those numbers. I'm going to cell reference in that first box for row input cell, the cell that contains a term of the loan, which is that third cell down in the very top table. And then for column input cell, it wants to know where do these down payment values go. I'm going to reference the cell that contains the down payment in my model, the fourth box down in that top table. Once I reference those two things, it knows where to put those values to calculate new payment amounts. When I do that, the output of this model looks something like this. Again, that number $2,148, that payment amount, that comes from a cell reference, the very bottom element in my spreadsheet model. I've told it, take the length of the loans, put it up in the terms, Take the down payment amount, put it up where the down payment goes, and it has auto filled the meat of that table that's red, yellow, and green. Now, why is it red, yellow, and green? Because I put some conditional formatting down there to make it easier to read. I'll show you that in Excel a little bit as well. But what we can see here is exactly what we expected. If we assume a lower payment is better for most of it it is, we can see that either increase in the terms of the loan or increase in the down payment or some combination of the two results in the most favorable payment, the lowest payment. Now we're going to pay more interest if we have a longer loan, but that's not what I'm measuring here. I'm measuring what the payment amount is. So with this data table, you can see I didn't have to go and manually change the values for term and down payment and manually record the outputs. This very quickly goes through in a millisecond, evaluates all the terms, all the down payments, and records for me all the outputs that I need to look at. So let's go back into Excel now. I'm going to jump into the same example, work through it mechanically, show you how I did it, and then break it down a little bit further.
So, I showed you the output of the data table, Let's go ahead and look how I built the thing in the first place. We're going to vary the length of the loan 3 and 8 years and vary the down payment here $1,000 and $5,000. Again, we want to put our data table are the yearly payments associated with each of those combinations of different terms and different down payments. Let's start by building a row that represents the different lengths of the loans in years. Down a column, how much of a down payment we want to have.
And, inside this table, I want to report the yearly payments. In the upper left corner, I'm going to reference this cell right here. What this data table is going to do for me is one at a time, stick these lengths of the loan into this cell where it belongs and take these values and put them in this cell. If it sticks a three over here, and 100 here, it should record $33,79.45 into this cell. If it sticks a seven up here for the length of the loan, and a $4,000 down payment, it should stick $1,082.46 into the cell right here.
Now obviously, I can do this manually, but the data table is going to do it a lot more quickly. Once I have the data table constructed, I'm going to select the entire table. Under my Data ribbon, I can go over to the what if analysis with the question mark. From there, I will select the data table option. The table here wants to know two values, a row input cell and a column input cell. What do these mean? The row input cell is asking me what to do with the values on the top row of my selected data table. In other words, in what cell, do these values belong when calculating payment? These values belong in this model in cell B7. That's where the term is. The column input cell wants to know, where do these numbers belong? Well, these represent down payment amounts. I'm going to place them in B5. I've got my row input for terms in B4. I've got my column input for down payment in B5. This tells Excel to one by one put these numbers in B4, these numbers in B5, and for every combination, because I've referenced B7 here, record the new value of B7 in the appropriate square in my table. And here you can see that's done that. Just to check it. If I have six years on my loan and make a $2,000 down payment, my yearly payment is $1,637.14. That six years and $1,637.14. So there you have it, regardless of whether you're worried about pressure inside this tank or the car payment for your car loan, data tables are a way to quickly assess how change in input can affect an output.
