Using the Goal Seek Function in Excel to Save Time and Effort
Sometimes Excel spreadsheet users may find that they would like to change an analysis result to a desired value by changing one of the variables used in the analysis. This is most useful when the analysis is non-linear, i.e., you can’t just set the input variable with a simple formula to create the desired outcome. In effect, the result tells the user what the selected input needs to be to produce a desired outcome.
There are many applications where a desired result is needed, but it is not clear what one or more of the inputs need to be to produce that result. To illustrate the technique, let us review a relatively simple example.
Figure 1 shows a projected order for several widgets needed for a manufacturing process. We refer to the items as A, B, C etc., showing the required quantity and cost of each. We also calculate the total weight of the order to compute shipping cost.
Figure 1.
The total order comes to about $8,200 and a total shipping weight of 826.1 units (since this is a fictitious example, the weight units can be pounds, ounces or whatever you like).
The supplier offers certain discounts on shipping cost as an incentive for the ordering organization to order more products. In our example the shipping cost per unit weight is $1.80 for orders up to 1,000 weight units and $1.50 per unit weight for 1,000 units or more. In addition, the supplier discounts the shipping cost by 30% if the dollar amount of the order is $9,000 or greater.
We find that neither the weight nor the dollar amount of the order meets the supplier minimums for a shipping cost discount. But we know that we could order more of item F if need be to reduce shipping cost since we expect to use more item F soon. Due to our setup, we need 1.5 item D’s for each item F we order (item D quantity is set by formula to 1.5 x item F quantity).
Now the question is, how much more of item F would we have to order to meet the shipping discount minimums? Here is where the Excel Goal Seek function comes in handy. It appears that if we buy $9,000 worth of product to get the 30% discount in shipping, we will also exceed the 1,000-unit weight reduced cost limit.
We check it out by clicking on cell D13 that we will call the target cell. We then click on the Goal Seek function in the Tools menu. This gives us a small user form with the target cell set to D13. In the “to value” box we enter 9000 (the desired amount of the order needed to meet the discount minimum). In the “by changing cell” box we enter B10 or simply click in the open box and click on cell B10 (the quantity we wish to change to meet the minimum order amount). Then we click OK.
Immediately cell B10 changes to 25.15 and the order amount changes to $9,000 as requested. We can either accept the result by clicking OK or cancel it, in which case the values in the two cells return to their original amounts. We accept the value, but since we can’t order fractional units, we round the quantity in cell B10 up to the next whole number (26). We could have rounded down to 25, but then the order quantity would have dropped below the minimum $9,000.
The resulting spreadsheet is shown in Figure 2. The item F quantity is now changed to 26; this causes item D quantity to go up to 39 (1.5 x item F quantity), and the order amount changes to $9,031.65. We note that the weight total is indeed over the 1,000 limit. With the lower rate for weight over 1000 and the 30% discount on shipping, the shipping cost changes to $1,182.69. This is a $304.24 saving on the shipping cost from our initial projected order.
Figure 2.
The Goal Seek function is quick and easy. Sometimes, however, you may find that there is no good solution for the values you choose, or it may come up with negative values for the other side of the curve rather than the desired positive value. So take care to make your choices judiciously and check the result to be sure it is what you wanted. Also, be sure the cell you intend to change contains an initial value, not a formula.
Download This Article (PDF) with Sample Spreadsheet
For cases where multiple input variables are involved, you may find it better to use the Add-in package for Excel called “Solver”. You can find more on this topic at http://office.microsoft.com/en-us/excel-help/introduction-to-optimization-with-the-excel-solver-tool-HA001124595.aspx
Another approach is to use macros to solve these types of analyses, especially if they are more complex.
If you would like a custom macro or a complete solution in Excel or Access, please contact Jerry Erwin at Business Solutions Associates, 972-233-9442.
Subscribe to our RSS feed via email. You’ll receive an email automatically every time a new post is added! Subscribe to Best Business Tips & Software by Email
2 Responses to “ Using the Goal Seek Function in Excel to Save Time and Effort ”
Trackbacks
Leave a Reply
RSS Subscribe
Thanks Jerry. Thisis very good and very profesional. Thans for thinking of me.
Donna
Way to go Jerry! Jerry has done things for me in excel that others could not! I appreciate you Jerry. The project that Jerry Erwin worked on was my website buildityourselfsolutions.com.