Varying Your Data to Get a Desired Result Using Goal Seek

When you run a business, you must know how every department and product is performing, both in absolute terms and in relation to other departments or products in the company. 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, Jenny Lysaker of Consolidated Messenger might have the goal of reducing the firm's labor cost by 20 percent over 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, Jenny might want to decrease labor costs so they represent no more than 20 percent of the company's total outlays.

As an example, consider the following worksheet, which 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 this worksheet, the values in the Share row are displayed as percentages, but the underlying values are decimals. For example, Excel 2007 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 2007: Goal Seek. To use Goal Seek, you display the Data tab and then, in the Data Tools group, click What-If Analysis. From the menu that appears, click Goal Seek to open the Goal Seek dialog box.

In the dialog box, you identify the cell with the target value; in this case, it is cell C4, which has the percentage of costs accounted for by the Labor category. The box has the target value (.2, which is equivalent to 20%), and the box identifies the cell with the value Excel 2007 should change to generate the target value of 20% in cell C4. In this example, the cell to be changed is C3.

Clicking OK tells Excel 2007 to find a solution for the goal you set. When Excel 2007 finishes its work, the new values appear in the designated cells, and the Goal Seek Status dialog box appears.

Tip

Goal Seek finds the closest solution it can without exceeding the target value. In this case, the closest percentage it could find was 19.97%.

In this exercise, you'll use Goal Seek to determine how much you need to decrease transportation costs so those costs comprise no more than 40 percent of Consolidated Messenger's operating costs.

USE the Target Values workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Alternatives folder.

OPEN the Target Values workbook.

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Goal Seek. The Goal Seek dialog box appears.
2.	In the Set cell field, type D4.
3.	In the To value field, type .4.
4.	In the By changing cell field, type D3.
5.	Click OK. Excel 2007 displays the solution in both the worksheet and the Goal Seek Status dialog box.
6.	Click Cancel. Excel 2007 closes the Goal Seek Status dialog box without saving the new worksheet values.

CLOSE the Target Values workbook.