In this topic, I will try to cover the features of what-if Analysis. It is a tool that allows you to try to different formulas. If you apply what-if analysis you will notice changing the values in cells and how those changes will affect the outcome of the formulas in your spreadsheet.
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:
First, we will discuss the PMT formula which is often used to find the easy monthly installments for any loan you have taken or you wish to take in the future. You want to take a home loan and how will you calculate this thing in Excel and the next one we will cover the Goal Seek, the back-calculation of certain desired number in Excel.
For example, you want to take Rs. 50000 and the interest rate is 13.25% and within 5 years you want to repay the loan. What will your monthly EMI?
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.
Now you can see your monthly EMI is Rs. 11,441. Now if you think if 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 much loan 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
Now we will discuss the features of the data table. This option is not used too much. It is important if you are in the field of finance, Analysis, cost determination and profitability analysis. (Refer this link for use of Excel in Financial Modelling) We will see how this can be used to our benefits.
Let’s say you are working in a product company and assuming your product price is Rs. 15.00 and products sold monthly are 2000. So it is clear that your monthly revenue is Rs. 30,000. Let’s assume I am giving 10% discount so my net profit is Rs. 27000/-.
Now if you have various level of prices and various level of quantities then what will be the impact of revenue?
Now I assume you have the various prices like 12, 13,14,15,16,17,18,19 and 20. You also have the various quantity like 1500, 2000, 2500, 3000 and 3500. Now if you don’t the right technique then every time you change the number and check the numbers. If you know the usage of data table it won’t have to find it manually. Here is the trick.
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.
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.
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
Then 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 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.
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