How to use Goal Seek to find an input value & produce a desired result
Goal seek is part of the What-If Analysis set of data analysis tools, in Excel. A user can evaluate the input value needed, to produce the desired output value using Goal Seek.
What do I mean by this? Well, let’s get started with a simple example.
How to use Goal Seek?
“What’s in a name? That which we call a rose by any other name would smell as sweet.” William Shakespeare
“I feel as if I had opened a book and found roses of yesterday sweet and fragrant, between its leaves.” – L.M. Montgomery, Anne of the Island
“But I send you a cream-white rosebud,
With a flush on its petal tips;” – John Boyle O’Reilly
Roses are one of those enchanting, enduring flowers that have fascinated many novelists, gardeners and poets alike. It is advised, when growing roses to water them frequently, use mulch to encourage consistent growth, as well as make sure that they receive a minimum of six hours sunlight a day. It is highly recommended to watch out for insects such as aphids, slugs, and snails. These pests can quickly decimate a rose garden.
There are many homemade gardening tips, tricks, and remedies that some rose lovers credit for the award-winning roses, growing in their gardens. Some rose lovers advise chopping two to three banana skins per rose plant, in order to provide the roses with the adequate phosphorous intake. Others use beer in order to rid the roses of slugs and snails. While, still others advise mixtures of baking soda and cooking oil, in order to deal with powdery mildew and rusts. Homemade compost comprised of citrus peels, coffee beans, pine bark and sawdust is also popular.
A hypothetical gardener who owns a large garden filled with award winning roses has gone totally organic, in terms of homemade remedies for growing his roses, but he is still on a budget. He has found the craft beer he uses, to be very effective against slugs and snails which are particularly problematic in his area. However, it can be slightly expensive. He has set a weekly budget of $ 165, for his four organic remedies that he is using and would like to find out, what the maximum amount is, that he can spend on the craft beer while still remaining in budget.
We are going to use Goal Seek to deliver the weekly amount he can allocate to craft beer, given the three other items in the budget, while still meeting his weekly target of $ 165 for the overall budget.
A picture of the source data is shown below:
1) Cell C17 contains the formula
=SUM (C13: C16)
which denotes the overall homemade remedy budget and is made up of the sum of the cost of the four homemade remedies.
2) With Cell C17 selected, since this cell contains the formula and is the desired result.
3) We go to the Data tab and click on the drop down arrow next to What-If Analysis, in the Data Tools group.
4) Select Goal Seek from the drop down menu.
5) The Goal Seek dialog box should appear, with the C17, already selected and detected in the Set Cell option. This is the cell containing both the formula and the budget that we would like to set.
6) We are going to set cell C17, to $ 165, since this is the desired weekly budget that we would like for the homemade remedies.
7) We select cell $C$15 in the By changing cell option. This is the amount of the craft beer budget allocation that we want Goal Seek to determine, in order to meet the target overall budget of $ 165. $C$15 is our input value in other words. It is unknown at this stage.
8) We click Ok.
9) A dialog box appears in order to let us know that Goal Seek found a solution for the value, cell C15 has to be, in order to produce the desired result of $ 165 in cell C17. Click Ok.
10) The required input, in cell C15 for the weekly craft beer budget allocation, has to be $ 48, in order for us to meet the overall homemade remedy budget target of $ 165 in cell C17.
Goal Seek is a useful tool for simple analysis. When using Goal Seek, one starts off with the desired result and then the required input value is determined by the tool, to obtain that result.
Goal Seek works in situations, where one needs to find one input value or variable. For more advanced analysis, it is recommended that you use Scenarios and Data Tables.
Please feel free to comment and tell us about any homemade rose growing tips and tricks you use as well as your thoughts about Goal Seek.