Reverse What-If Analysis in Excel

In our this series of articles: What-If Analysis in Excel, we have discussed what-if analysis— the process of changing values of input cells to observe the results on other dependent cells.

In our this chapter, we shall look at the process from the opposite perspective: we shall find the value of one or more input cells that produces the desired result in a dependent formula cell.

Let’s start with a What-If question: “What is the total profit if company sales increase by 20%?” Say, for example, cell B12 contains a formula (say the formula is: =B1*B2+B10) and the result of this formula displays the total profit of your company.

It means that the value of B12 depends on the values of B1, B2, and B10. In what-if analysis, we can change any of the values of B1, B2, or B10 to see the impact in the cell B12.

In reverse What-If analysis, we’re going to find out the values of B1, or B2, or B10 when given B12 value. In reverse order, the above-mentioned question may take this form: “How much do sales need to increase to produce a profit of \$1.2 million?”

Note: In above example, cell B12 is dependent variable and B1, B2, and B10 are independent variables. Cell B12’s value is dependent of B1, B2 and B10’s values.

Excel provides two tools that are relevant to reverse What-If analysis:

• Goal Seek: We’ll find out a single cell value that produces a result. Say in our above example, we shall find out only B1’s value, where B12, B2, and B10’s values are given. A complete tutorial on Goal Seek features Excel.
• Solver: In this process, we’ll find out multiple cell values that produce a result value. In our above example, we’ll find out the values of B1 and B2’s values, where B10 and B12’s values are given. With this process, you can specify certain constraints to the problem. So you gain significant problem-solving ability.

Happy Excelling 🙂