What-if Analysis is a pretty common analysis done by decision makers. Often, they would just create simple excel tables and adjust their variables manually until they get an answer that works. But instead of doing it manually there are features available in excel that will make your life much easier and analysis much more accurate. So, the goal of this blog post is to introduce you to the Goal Seek and Solver feature to help you do what-if analysis in Excel.

#1. Goal Seek:

Let’s say you are a CEO of an e-commerce startup and wondering about what factors you need to focus on to increase revenue. Here’s what the data (*assume per month) looks like when you start out:

And you want to increase the Revenue to $150K from $125K. The three levers you can pull are website visitors, conversion and revenue per customer.

Now you could manually tweak the values for this variables till you get to $150K but as I promised earlier, there’s a better way!

Let’s start with Goal Seek.

You need to set two variables for Goal Seek.

a. Your goal — which in this case is 150K

b. The variable that needs to be changed to achieve that goal — note that you can specify just one variable to do so. So you need to choose out of the three above what you would like to focus on. Let’s say you want to focus on conversion rate.

So once you have these two things — from the Data Tab in Excel, Go To What-if Analysis, Goal Seek:

Now, specify the values. For this example, we want to figure out what should be the new conversion rate so that our revenue will be $150K. So here’s an example of how that would look on Goal-seek:

After entering the values, you will see the status — you can click “OK” to keep the solution and cancel to go back to what you had:

Perfect! So you need to increase the conversion rate from 1.25% to 1.5% to get to the goal that you had set!

#2: Solver add-in

So, you worked on improving the conversion rate for next month or two and you & your team found out that it’s getting really hard to increase it above 1.35% — And also you found that with the less effort you can move the needle on other variables (website visitors & revenue per customer). Now Goal Seek allows you just set one variable so if you more variables than it doesn’t serve the purpose that well! That is where Solver add-in helps.

Think of Solver as advanced Goal seek where you can set more than one cell that can change. You can also set constraints on what the values could be for all the variables that can change.

Now, for our scenario, the conversion rate is at 1.35% but you want to see the possible changes that you can make for website visitors and revenue per customer to reach $150K.

You also know that you can’t above 1,100,000 Website visitors per month and also need to have less than $11 as revenue per customer.

You will need to enable the Solver add-in in Excel and once you do that you will see that in the Data Tab.

Once you have it, open it and fill up the information needed in the dialog box:

a,. Set objective to Total Revenue with value of 150000

b. By changing cells to: Website Visitors and Revenue per Customer

c. Constraints. Website Visitors <= 1,100,000 and Revenue Per Customer < $11

After that click on Solve.

if it found a solution, it would show you that on Excel and also give you additional options to whether you want to keep the solver solution or restore it to original values:

For our scenario, it suggesting that with website visitors to 1,010,101 and revenue per customer to $11, we should hit our goal.

Click on OK when you’re done.

Conclusion:

In this post, we saw how you can use Goal Seek and Solver add-in using an e-commerce scenario but you these techniques can be applied to wide variety of data analysis problems that can be solved using “what-if” techniques.

Hope this was helpful and I would love to hear from you about how will you use this in your work? Or if you use it already then what do you use it for?