Welcome, everybody. In this video we talk about data tables, one of the most underutilized features of Excel. And it's quite important in case you are in the field of finance feasibility analysis, cost determination, and profitability analysis, we will see how this can be used to our benefit. I'm going to start with a very simple revenue model. And I have two input values, which is price and quantity. And as you can see, the formula suggests that I'm multiplying price into quantity and that gives me 30,000.
Towards the other end, I am deducting 10% discount to fetch a net revenue of 30,000 less 10% 27,000. Now, imagine at a very basic level, if you want to find out that at various levels of prices and at various levels of quantity that can be sold in the market, what is the impact on revenue. Now, if you do not know the right technique, the approach is going to be something like this. You write, then you find the answer you write? Well, you find the answer. And for each such combination, you feed in the numbers and see what the answer is going to look like.
Instead, what I'll be giving you is something like this on various levels of prices and quantity, you have the impact on revenue. Not only that, in case you decide to choose the price to go a little on the higher side, let's say 20. The range of prices also increased 2122 23. And all the impact on revenue also changes and we'll see how two inputs can be put into our Excel and the third variable can be derived. So we start from scratch. First step is you decide which two input variables you are going to feed in your data table or the what if analysis.
In the first example we are going to choose price and demand. In the other example we are going to choose the discount percentage and the demand quantity. Now, it is not necessary that you need to fetch in Numbers vertically or horizontally, any one of them can be placed vertically. So I've fed in 15. And below that are, I'm putting a simple formula that says 15 plus one, which will fetch me a series of values with an increment of one each. On the other hand, I'm going to put a formula which says previous quantity, plus 500.
And that will also give me further such numbers such as 2000 2500, and so on, so forth. So this was my step one, we need to put two variables vertically and horizontally. And in the process, ensure that you have kept this one cell blank. This is step one, step two, the table on the left knows how to calculate revenue. But table on the right which we have created right now it doesn't know that. So I need to establish a connection such that this logic of how to calculate revenue gets known to the table on the right.
So I use this empty blank So equal to an endpoint to the cell which contains the formula, how to calculate revenue. It's as simple as that. And that connection is established only in this one dedicated cell. I press enter, that was step number two. Step number three, I choose the entire block as it is not more, not less. And after step number three, I go to Step four, which prompts me that I must go to data.
Mm hmm. But then data, let me go to what if analysis section, and by the terminology, data table sounds the least jazz? Yes, but believe me, this is one of the most wonderful of the three. So I go to data table. Oh, I'm getting a very small prompt box. As compared to the complexity of the problem.
This looks absolutely insignificant, but trust me, this is what is going to lead us to the answer. It says row input cell, mind you It says cell which means what you can write only one cell reference something like a one B one or C 11. Whatever that could be. So, input cell column input cell. Now, I have seen professionals once they stopped using this data table for a while they forget the logic. So, let me give you an mnemonic, which will help you recall this technique even after months and months of not using this particular technique.
So, VC remember these two terms VC and HR, VC means whatever is vertically placed in your table, which is price must be paired up with C column input cell we see. So, now it says column input cell I'm tempted to choose the entire series of numbers, but wait it says cells I cannot choose a group of cells nor can I choose the cell from within the table. It has to be from a neutral venue which in this case is the price sell from the mean model. So, vertical please data is price that must be be paired with column input cell. And that column input cell has to be the cell from the main model. By that logic, if I follow the HR rule, horizontally placed data is demand or quantity that must be paired with row input cell.
And that comes from the main model itself. As I press OK, well, I get the impact on revenue so 15 to 1500, the revenue is going to be 2500. We now see and populate a table for the next table where the two inputs are discount and quantity. Now, you can put quantity in the vertical you can put the discount on the horizontal, it doesn't matter. We will stick to our original case study which is 10% 1500. And let me populate a series of values and percent plus 1%.
I copy paste this down. Now I've seen people they select both these two cells and copy paste this down. No, that is not correct. I would ask you to only copy paste this cell one cell which contains the formula, although you have the power to adjust the formatting changes such that it looks like two decimal accuracy. On the other hand, I'm going to put plus 500. And that will help you create a series of quantity numbers.
That was step one, step two, you point the cell, the blank cell to the net output some pointing to the cell, which contains the calculation for net revenue. Now, note that you would not get any meaningful result if you point to the cell containing 30,000. Why because you have not reached to the point of discount and this variable is discount. So right now since we want to incorporate the discount factor, we would choose a variable which has already done so. So 27,000 days and done. This is step two, step three is choosing the entire table.
Now mind you, do not choose extra values. Less values. In fact, the best result would be achieved if you choose the entire table as it is go to data. What if analysis Next, click on Data Table if you followed the VC HR rule by that account vertically placed the data as discount, which must be paired up with column input cell. So, I choose 10% vertically please data discount should be paired up with C and hence column input cell is discount from a neutral venue by that logic I must step inside row input cell do not entertain them because this might hold true or give you results correct results in this particular example because there's not so complex but in a complex scenario, you would not get a correct result unless you follow the VH rule. So horizontally placed the data that is quantity must be paired up with row input cell and we do not take any values from the table we always take it from neutral venue which is the main model itself, I press OK. And there you go.
If you want to test the accuracy you'll notice at 10% discount and 1500 quantity, we're down to that I'm supposed to get here same onsen should be getting here. So there you go. This is a two input variable and one output, which we got to data table