Windows XP

 Windows 7

# Microsoft Excel 2010 : Varying Your Data to Get a Desired Result by Using Goal Seek

10/22/2012 3:50:42 PM
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.

### Important

If you save a workbook with the results of a Goal Seek calculation in place, you will overwrite the values in your workbook.

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

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

### 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 percent.

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

### Set Up

1. On the Data tab, in the Data Tools group, click What-If Analysis and then, in the list, click Goal Seek.

The Goal Seek dialog box opens.

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 displays the solution in both the worksheet and the Goal Seek Status dialog box.

6. Click Cancel.

Excel closes the Goal Seek Status dialog box without saving the new worksheet values.

### Clean Up

Save the TargetValues workbook, and then close it.

 Other -----------------

 REVIEW

 VIDEO TUTORIAL

Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
 Top 10