Using Goal Seek in Microsoft Excel: Solving for a Target Value.
Browse articles:
Auto Beauty Business Culture Dieting DIY Events Fashion Finance Food Freelancing Gardening Health Hobbies Home Internet Jobs Law Local Media Men's Health Mobile Nutrition Parenting Pets Pregnancy Products Psychology Real Estate Relationships Science Seniors Sports Technology Travel Wellness Women's Health
Browse companies:
Automotive Crafts & Gifts Department Stores Electronics Fashion Food & Drink Health & Beauty Home & Garden Online Services Sports & Outdoors Subscription Boxes Toys, Kids & Baby Travel & Events

Using Goal Seek in Microsoft Excel: Solving for a Target Value.

The reader will learn how to use Goal Seek in simple spreadsheet applications.

A common need in finance and other business problem areas is the need to find a value for a variable resulting in a specific result. This end result may be the result of margin requirements, or the need to match a competitor's rate. Microsoft Excel is one of the more useful tools in the analyst's arsenal, and can be used as a very fast and effective means to finding the desired value.

The Excel feature that is used to accomplish this task is Goal Seek. It is a built-in feature that does not require the use of an add-in to use. No knowledge of Visual Basic is required, although a neat application of Goal Seek is to embed it within a Visual Basic driven button to enable a dynamic refresh of a model based on changes in assumptions.

As a simple example, suppose that one has a desired margin target and needs to determine the discount off of list price needed to hit a desired margin. The setup for such a problem is given here, for both variables and values.

Goal Seek Formulas

Goal Seek Values

To use the Goal Seek function properly, one must distill the problem to a single key variable of interest and a single formula representing the outcome. In a more complex example, formulas may be chained together to introduce multi-variant assumptions. The variable of interest in this simple example is the discount off of list price. The solution result is the gross margin percentage. The target used in this example is a 25% gross margin.

Once the variables have been identified, the solution may be found in a matter of seconds. The Goal Seek function is accessed from the Tools menu. An example of the values entered is given below:

Goal Seek

After clicking OK, the gross margin percentage shown is 25%, and one will note that the discount has been changed by Goal Seek to 27%. Thus, a 27% discount off of list price is needed to hit a 25% gross margin, given the cost structure provided.

Similar applications would be finding interest rates needed to hit desired returns (using the PMT function) or finding a discount or markup needed to precisely target a competitor's rate.

For more advanced users, the Excel Solver add-in provides the capability of using a goal seek on multiple variables, with constraints thrown in for good measure. A similar tutorial on the Solver Add-in will be written in the future, and will be of use to those with more complex models than the simple example given here.

Need an answer?
Get insightful answers from community-recommended
in Computer Programming & Languages on Knoji.
Would you recommend this author as an expert in Computer Programming & Languages?
You have 0 recommendations remaining to grant today.
Comments (0)