Excel-Goal-Seek-Tutorial_v4

How to Solve Your Real Problems With Excel Goal Seek

We want to generate $1m Revenue in 2018 said by CEO.

Well, that’s ambitious goal…

Considering our current year Revenue is at $0.7m but how do we achieve that..? let’s see if we can use Excel Goal Seek to put some foundations.

By the way, we are into Chocolate business.

I’ll walk you through step by step on building various models to achieve 1m revenue target by using Goal Seek in excel.

Keep reading.


What is Excel Goal Seek


Goal Seek is an excel built-in function used for solving day to day problems quickly.

Trust me this is one of the most undermined utilities in excel but very powerful if you know how to use it.

 


Where can I find this


You can access this built-in function from Data → What If Analysis → Under the drop-down → Goal Seek

Where to find excel goal seek

Goal Seek interface


Excel Goal seek tool has three components to fill in.

#1 Set cell → This is the cell where your results will reside

#2 To value → This is where you need to enter your target value. For example, $1m is our target value.

#3 Changing cell → The cell where this tool can make changes to arrive at the target.

goal seek interface

Tom Chocolate Company problem


Tom Chocolate Company has been based out of United States of America. During FY17, the company had generated $0.7m Revenue through sales operations.

The company CEO has set up an ambitious target of achieving $1m Revenue during FY18.

You have been asked to come up with feasible solutions to achieve that target.

goal seek example-1 to find number of units_v1

Scenario #1:

How many units do we need to sell in order to generate $1m Revenue?  [your sale price should be unchanged i.e. $2 per unit].

Step 1:

Head over to data tab and launch Goal Seek tool

Step 2:

On the set cell field → select the resulting cell, in this example E7 i.e. Revenue is our result cell

Step 3:

Enter $10,00,000 in “to value” field – this is our revenue target

Step 4:

Provide a range where goal seek tool can make changes to arrive at our target – for this example, we are ok to change number of units so select E5

Step 5:

Click ok and wait for the computation to complete – once we have the solution click ok again

changing sales units in goas seek

And the result is as per below.

result for change units

Bingo – we have the result, but wait for a second…

.. it’s very easy to say if we can sell 500,000 units we can generate 1M revenue, but the question is can we produce 500k units..?

Well, maybe not…

..because our production capacity is limited to 400k units only

Let’s find a solution by changing the price.

 

Scenario #2:

Based on the first scenario we only can produce 400k units hence we need to restrict units to 400k and let’s see what price we need to sell.

For this scenario, you will have to change ‘changing cell’ field to ‘E6’ i.e. price and rest all remain same.

After all the parameters hit ok to run the computation.

Wow, you have it..!!

changing price per unit

So, we need to sell each chocolate at $2.5 based on the above solution.

Well, the solution looks feasible, but not very sure our CEO would agree to increase the price per unit by 25%.

Let’s build another scenario by increasing number of units to 450k assuming what will happen if we upgrade our machinery.

Also read: 10 most common reasons for excel formulas not working 

 

Scenario #3:

Ok, let’s change Sales (in units) to 4,50,000 and see what price we can sell.

All the fields would remain same except units

Here is the result.

price & units change using goal seek

Awesome, with 450k units we can sell $2.2 per unit which is +10% increase compared to previous year.

Based on the above scenarios, the $1m Revenue target looks achievable so the advice for CEO is to execute the plan.

 

Conclusion

Excel goal seek tool is a hidden gem for quick solutions.

It comes handy to prepare various scenarios to make quick decisions.

On the downside, this tool will not support multiple conditions. For that, we should use ‘solver’ tool – which is very powerful.

You may refer following tutorial about excel Solver.

Last … have you had a chance to use goal seek..? if so did you find it useful, please comment below.