There is three type of what-if Analysis. Scenario Manager, Goal Seek and Data Table; let’s try to cover all these one by one:
In that case, we will use PMT formula to calculate EMI. PMT formula requires the following input:Rate: the interest of the loan. It is required. Nper: the total number of payment for the loan. It is required Pv: Principal amount. It is also required. Fv: optional. The future value. If it is omitted then it is assumed to be zero. Type: Option. You can put only 0 and 1 here which indicates when payment is due. Now go back to our example. I want to take Rs. 500000 and the interest rate is 13.25% and within 5 years you want to repay the loan. And I want to give my EMI at the end of the month. As soon as you will press enter you will get the EMI value. I have divided my interest with 12 for the monthly rate of interest and nper is multiplied with 12 for 60 months.
Now you can see your monthly EMI is Rs. 11,441. Now if you think it can pay Rs. 20000/month for next 5 years then what loan amount you can get from your bank. Then you need to apply Goal seek formula. To apply goal seek you need to go to Data – What-if Analysis – Goal Seek.
As soon as you click on Goal Seek you will get a tool box. You need to put the value there as shown in the below picture⇓.
Look at the Goal Seek tool box. First, it is asking for Set Cell. Which cell you to set and the next option is to value. Remember here you cannot choose a cell. You need to write it manually what value you want to set. I have given -20000 as it will be in a loop. And finally which cell value you want to change. I want to check how many loans I can get so it is my loan amount. Then Press ok.
This time, your loan amount is changed and you can get loan Rs. 874,077 from your bank.
Get Advanced Excel Training from Charter Accountant (Corporate Trainer)
Data Table in What-If analysis:
You need to first create a table like below:You need to put two variables vertically and horizontally and in the process please ensure that one cell is blank (the first cell of the table). This is the first step.
Now you have a table in the sheet. Table 1 and table 2.Your table 1 knows how to calculate but table 2 does know which we have created right now. So you need to establish a connection between tables no 1 and 2 such that this logic of how to calculate revenue gets known to the table no 2. You need to create the connection in the empty cell of your table no 2 which is the brain of the table. That was step no 2.
After pressing the enter you will create the connection between 2 tables. The connection will be established only in the one dedicated cell. Then choose the entire table no 2 as shown in the below picture (Picture 1.3). Please do not choose less or more rows and columns. Just chose the entire table only. As soon as you select your table you need to go to data- what-if Analysis – Data Table. As you press on data table a small toolbox will appear. It was looking for row input cell and column input cell. You can write only one cell reference like A1, B1 etc. Now, what will be the value in Row input cell and what will be the value in column input cell? Remember the two terms VC and HR. VC means vertical column and HR means horizontal row. So whatever data is placed vertically will go to the column input cell. And whatever data is placed horizontally will go to row input cell. So in our example price must be paired up with column input cell and quantity must be paired up with row input cell from your main model. After your choosing row and column input cell please press ok to get the desired result in your data table. You will get the impact on your revenue. And your result will look like below. Now your data table is ready. You can notice that if your product price is 19 and if you sell 2000 quantity then revenue will be 38,000. More quantity sold means more revenue comes into your pocket. Just remember the VC (Vertical column) and HR (Horizontal row) rule when you are applying data table.
3. Excel Dashboard using What-If analysis:Now you will learn how to create a perfect dashboard for your analysis. You need to know how to create a named range, data table, indirect function and conditional formatting and how to create scroll bar using your developer tab and a drop down list.
First look what dashboard we are going to prepare:You can use the vertical and horizontal scroll bar to get the data in a table. You can also choose what you want to from your data table. The changes in drop drown should reflect in your data table. To prepare this kind of dashboard please follow the below steps.
Step A: – I hope that your model table is ready so that you can prepare your data table:
Now prepare your data table. In my example, I am placing price vertically and quantity horizontally.
Your table should look like below:The brain of the table is marked as yellow colour so that it can easily be noticeable. You need to first set up the connection between your two tables because this is the one which leads us the way. In my case, I have connected it with profit value of my model table which is 5000. In my case, I have created the first price value hard coded which is 10 in the F5 cell. And in the next cell (F6) I have used a very simple formula = F5+1 (F5=10). Then I have simply dragged it. Similar case for the quantity also. The first one (cell G4) is manually written and then in the H4 cell simply add 500 with earlier using the formula (=G4+500). Then please choose your entire table (here data table) and go to data – what-if analysis and then click on the data table and then apply VC and HR (discussed above) to get the output in your data table. Your result will look like below.
So far so good. What we have achieved is that variety of answers that are showing profit level based on our price and different quantities. Now if you change the value price value 10 to 11 all the value in the price field will be changed and the value in the data table also be changed. But we trying to do it in a more advanced way.
Step B: – Take the scroll bar from your developer tab:The next step is that we are going to create a scroll bar from our developer tab. Remember we will choose the scroll bar from Control section not form ActiveX Controls. Click on the scroll bar icon and create the scroll bar in excel. To configure the configure the scrollbar you need to right click and then click on format control. Then you will get another box where you need to configure it. Set the value as per your requirement. I have set the values like below Minimum value: 500 Maximum value: 10000 (Limit is 30000) Incremental changes: 200 Cell Link: Linked with an N4 cell. Leader of the quantity After the setting please press ok. Your scrollbar configuration is done. Then click outside of the scrollbar. Now you can see if you click on the edge of the scroll the quantity will be changed and as the quantity is changing the value in the data table will also be changed. In a similar way, you can create the vertical scroll bar. Do the configuration in the same as you have done it for the quantity.
Step C: – Now come to the next trick:Till now records of my data table shows my profit. But now I want to see the revenue also. Which means I need to connect the brain of the table with the revenue of the model table. Now the import thing is that how do I connect revenue and profit at the same time in a single cell? For that, we need to create the validation which should be linked with the brain of a table. In my E2 cell, I am creating a data validation which will be like below. Then you need to press ok to work it properly. Now your data validation is created and you need to link the brain of your data table with E2 cell where you have created your validation list. Now if you simply connect the brain of the table (yellow cell ) with your E2 cell it will not work. You need a re-direction. That means you need to apply indirect function.
Step D: – Name Range:Now you need to create name range so that you can re-direct it. For revenue give name revenue and for-profit, sale gives name Profit from the name box. After setting your name ranges go back to your brain of the data table.
Step E: – Indirect Function:Now you need to use an indirect function to re-direct the value. In the brain of the data, table writes the formula =indirect (then choose the value from your drop down).
As soon as you press enter what will happen is your yellow cell will go to profit and profit will profit will redirect to the value of your model table. Now you can choose revenue from the drop down and the data table will show you revenue value.
Step F: – Conditional formatting:The next thing I want to add in my data table is conditional formatting. Whenever the numbers are negative it will show as red font colour and when the numbers are positive it will show as green font colour. To apply that I will first choose the record set of my data table (not the entire table). Then Go to home tab and then check on conditional formatting and then click on new rules and from there choose the second one (format only cell that contains ) and set the below setting. After setting this please click on format and set the font colour as red and font style as bold. Then press ok and again press ok. In the same way, just apply for the positive numbers and this time, you need to set cell value greater than or equal to as 0 and then format the font colour as green and font style as bold. Your data table will look like below table. You may test it how it will look like by clicking on the edge of your scroll bar. You may also choose the value from your validation list which will directly reflect in your data table. I am quite sure if you do this stuff in your office people will love it and they must want to know how you did that.
Join our free Online Excel Course to learn more such tricks and be an Office hero