Microsoft Office Tutorials and References
In Depth Information
Varying Your Data to Get a Desired Result
by Using Goal Seek
When you run an organization, you must track how every element performs, both in
absolute terms and in relation to other parts of the organization. Just as you might want
to reward your employees for maintaining a perfect safety record and keeping down
your insurance rates, you might also want to stop carrying products you cannot sell.
When you plan how you want to grow your business, you should have specific goals in
mind for each department or product category. For example, Lori Penor of Consolidated
Messenger might have the goal of reducing the firm’s labor costs by 20 percent as
compared to the previous year. Finding the labor amount that represents a 20-percent
decrease is simple, but expressing goals in other ways can make finding the solution
more challenging. Instead of decreasing labor costs 20 percent over the previous year,
Lori might want to decrease labor costs so they represent no more than 20 percent of
the company’s total outlay.
As an example, consider a worksheet that holds cost figures for Consolidated Messenger’s
operations and uses those figures to calculate both total costs and the share each category
has of that total.
Important In the worksheet, the values in the Share row are displayed as percentages, but the
underlying values are decimals. For example, Excel represents 0.3064 as 30.64% .
Although it would certainly be possible to figure the target number that would make labor
costs represent 20 percent of the total, there is an easier way to do it in Excel: Goal Seek.
To use Goal Seek, you display the Data tab and then, in the Data Tools group, click What-If
Analysis. On the menu that is displayed, click Goal Seek to open the Goal Seek dialog box.