Reverse What-If Analysis in Excel

In 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.

This article is part of my series: Excel Solver – A Step by Step Complete How-to-use Guide.

In this chapter, we shall look at the process from the opposite perspective: we shall find the value of one or more input cells that produce 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 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 🙂

Read More…

How to Assign Work Using Evolutionary Engine?

Performing manual what-if analysis in Excel

Types of What-If analysis in Excel



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply