Greetings, everyone. Before I start this case study, let me first talk about the prerequisites of this case study. One of the things you must know about before you can delve deep into this case study of making a certain dashboard, you must be able to work out with data table, you should know the basics of conditional formatting. In one of our previous videos, we also showed how to bring the scroll bars using the developer tab, you must know about that procedure, you must also in fact know about this formula indirect and the concept of range naming and towards then we might also use drop down data validation list. So these are the ingredients for our recipe of this wonderful, fascinating case study that you will see in some moments. All we have discussed the landscape of this case study in one of our previous videos, but for viewers who have not seen that video, I'll quickly walk you through.

On the left hand side lies a table which only shows a basic revenue and total cost calculation thus leading to net Loss of net profit. On the right hand side, there is a what if analysis of different prices quantity, and as I click on the buttons, the answers change. And as I click on the orange cell and change this to revenue, all the output variables turn out to be revenue. Now we'll build this from scratch, we go to sheet one. Step one, let's first populate the data table with at least two variables. So I'm going to place price vertically, and quantity horizontally, you can place the other way round to no harm.

So I'm going to put a formula which says equal to 10 plus one. And thereafter I only choose this tale and not both only the sell and stretch it down so that we have a gamut of numbers in terms of prices. On the other hand, I adopt a similar strategy for quantity. So plus 300, and stretching it a bit to the right hand side. So there you go up to 3600 quantity. So this was step one, step two, I had purposely kept one of these cells Blank the one marked in yellow, because this is the one which is going to lead us the way we will point to the cell which contains the calculation for profit.

And that is how the entire calculation and logic will be disseminated to all the numbers underneath this table. Pressing ENTER, that was step two, then choosing any cell value from the table and pressing Ctrl S such that the entire table is chosen. And that's when we start with the next step of data data tab. What if analysis data table as per our VC HR approach from our earlier discussion, we go to the column input cell which has been marked with three and here we first find out what are the vertically placed data, those are prices. So since it says column input cell I cannot take a range of values it has to be one value and that two from a neutral venue the main model, so that's price. On the other hand, using the logic of HR He's already placed information quantity that is that must be paired up with row input cell.

And now I'm going to choose from the neutral venue, that's the cell gets selected that contains the quantity. What we have been able to achieve is a variety of answers that are showing profit levels based on different prices, which I'm going to make as bold and different quantities, which are also converted into bold. Now, this is just one strategy, which shows you numbers but I want to add some extra jazz to it. So I go to developer, I go to Insert, and I pick up the third button from the second row which is talking about scroll bar. And I'm going to create the scroll bar quickly as if you are drawing a shape in a PowerPoint. Now to be able to configure this particular scroll bar, let me right click go to Format control E and thereafter, let me consider this control.

So minimum value, let that be 500 maximum when he tells us And the incremental value, let that be 50, which means if I click on the edge of the button, the value which I'm going to link right now, that is the one which shows us 1500, it will increase by 50 1515 1600 1650. And so on cell link, I choose the cell. The first one, because that's the leader of the pack. If I change this cell, the other cell value will also change. As I press Ok, I see the change in number, the minimum value shows as 500 because that was the minimum value. Now let me click outside the button to activate it.

And let me see whether it works. You notice, as I click on it, it's changing by 50. And based on that value, the subsequent values also change. Similar thing. I'm going to do that for prices, I go to developer insert, second row, third button. Note that I'm taking these buttons from form controls only and not ActiveX control.

If you want to know more about these toolbars please watch one of our previous videos. Which exactly explains what tools are. So scroll bar, I click on this and draw it as if it were a shape, right click Format control. minimum value, let me give a value of five, maximum value hundred, although nobody stops me from selling at 1000. But of course, there'll be no customers. So 100 incremental changes one, I cannot put a decimal value cell link.

Let me again choose the leader of the price pack, which is that value 10. I press OK. Just to test whether it's working. Oh, yes, it is. I notice if the numbers change the values all change. Now what we've been able to populate is a semi complete dashboard, which has data table which has full bar, but here comes the next big trick. I would want that when the user finds this exercise before him, he should be able to also see the effect on revenue.

And for that to happen, he has to link this yellow And link with what the cell which contains a calculation of revenue. But not every user will do that. So what we do is make his life easier. First, we go to a particular cell, I'm going to color this just for our own reference purposes, or in cell. In that I'm going to go to data, data validation and allow a list so that it has two values sales. It's not case sensitive, you can write sales cap comma, then profit.

As of now I'm just putting two variables. So I will have sales or profit. Now I want to connect the orange and the yellow cells such that orange cell dictates the yellow cell and the yellow cell intoned it takes the other answers. Now question is how do I do so? simply putting equal to and pointing to the orange cell will not benefit me? I need a lead detection.

What is redirection? If you want to know more about redirection, please feel free to watch videos on index formula. So here it goes. I want a mechanism whereby the yellow cell goes to the orange cell for taking the address and it gets redirected to the appropriate address. To make that happen. I'm going to choose the cell containing revenue, the one which has been underlined, I'm going to give this a name, simple name, sales, enter, make sure you press Enter after you have given that name.

It then shows you the next time whenever you are choosing this as sales next time, if I go and choose the particular cell, which contains a profit, give this a name, simple name, profit. Enter. So as I double click on the tab, and thereafter if I find that Oh yes, I have profit which is pointing to the cell containing profit. If I choose sales, it takes me to the cell value sales. Now the final nail on the coffin equal to indirect and pointing to the orange sale which contains the exact word profit. Notice what is going to happen.

If I press enter at this point in time, it will go to profit and profit will then redirect to the cell containing profit. And the best part, if you change this to sales, it turns out to be sales. If you change this to profit, all these numbers are profit numbers. And the next thing that I would want to add is conditional formatting such that all negative numbers are red, and all positive numbers are green. I understand that this is a long video, but this requires one single session to understand end to end. So I choose the entire gamut of numbers which are the output variables.

If I want to apply color, conditional based color, I must go to Home tab, conditional formatting. And I go to new rule, second option, and here I tell Excel, look, if the cell value is less than zero, go to Format. Pick up a font color and make dark Red with board. Okay, that's how the output is going to look like at this moment there is no negative number and hence the effect is not shown to you. Let me also add one more rule, new rule. And this time let me add the logic of greater than or equal to zero.

If that is so, then please set the format to a font bold and font color should be green, okay. Okay. As I reduce the prices, notice how numbers get represented in red and green. So friends, how many tricks have used in this one single case study, we have used data table we have used indirect and naming we have used how to populate the scroll bar. It also added conditional formatting. So practice this and trust me This will require at least two rounds of practice, but the day you should stick to anybody in your industry, people sitting beside you in office, they will ask you to Definitely How did you do it?

And that's what I want people asking you how did you do it? see in the next video