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
Goal Seek interface
Excel Goal seek tool has three components to fill in.
-
Set cell → This is the cell where your results will reside
-
To value → This is where you need to enter your target value. For example, $1m is our target value.
-
Changing cell → The cell where this tool can make changes to arrive at the target.
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.
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
And the result is as per below.
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..!!
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.
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.
Have you had a chance to use goal seek..? if so did you find it useful, please comment below.