Select Page

What-If is a function built in to Microsoft Excel to compute answers to “what if” questions, exactly as the name suggests! A business would be faced many such “what if” questions – “What if we need to earn a profit of Rs. 10000, given that the profit per unit is Rs. x, and the fixed costs are Rs. y, and the variable cost per unit is Rs. z?” In other words, the What-If set of tools help you answer hypothetical questions relating to scenarios involving unknown quantities.

Take a look at the Data tab on the ribbon, and spot the What-If Analysis button. If you click the the What-If Analysis icon, you would be presented with three options:

Let us examine each of the three functions individually, and understand how to use them:

  • Scenario Manager is a tool that helps you plan for different situations, or scenarios with different calculation values. You can quickly move between various scenarios, and reach different results for each. You can even gather different scenarios from different users on different sheets, and merge them into a single Scenario Report.
  • Goal Seek tool does exactly what it says on the tin – it helps you seek a goal. Assume a value in a cell is calculated based on the value in another cell, what should the value be in that other cell, so that you can reach a target value in the first cell? This can be calculated using Goal Seek. Imagine you have a data series with ten numbers. You don’t know all the numbers, you only know nine of them. However, you also know the average of them. Goal Seek will let you calculate the missing number in such a case.
  • Data Table would let you generate a table showing the results for different values of a particular formula. Assume we have a formula that calculates EMI for a certain loan amount at a certain interest rate for 12 months. Now, if we wish to know what the EMI values would be for other tenures, we don’t have to enter the formula each time. We can simply use the Data Table function to compute the EMI values.