Wednesday, August 15, 2012

The Efficacy of Goal Seek

After our stroll through one of the cool new features of Excel 2013 last week, I thought it would be good to take a look at a Classic Management Tool that has been available in Excel for several years.

"What If” scenarios are essential instruments of analysis for nearly any business. Excel provides a great tool called Goal Seek which allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. As with so many little-used tools in Excel, it can save you a ton of work, and make you look good in the process.

Now we will utilize a very Simple Example to explore how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the executive branch. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:
Assumption: The Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)

1) Put the number of Expected Calls into Cell B2
2) Insert the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

Obviously, this tool can be used to greater advantage with more complex scenarios, but you can see by our the example that Goal Seek can be a handy tool for the boardroom or any planning venue. Give it a try sometime!

No comments: