by Frank Kuhn

Application:

Microsoft Excel 2000/2002/2003/2004/2007/2010/2013

Operating Systems:

Microsoft Windows, Macintosh

Download: http://download.elijournals.com/excel/201601/goalseek.zip

Scenario: At the last-minute, your supervisor has asked you to present a “what if” plan for reaching a specific company goal. You know where you need to get, but you have no idea what data you’ll need to reach the desired result. For these kinds of situations, Excel’s Goal Seek feature hits the spot.

To find the input value necessary to reach a given output using Goal Seek, we’ll:

- Set up a sample break-even analysis problem and demonstrate how difficult it is to define what data values will guarantee that you reach the break-even goal.
- Explain how to use Goal Seek to get around this dilemma by testing for different scenarios and optimizations.
- Point out some of the limitations of the Goal Seek feature.

Excel spreadsheets and data tables are ideal for finding a formula result. But they aren’t necessarily as powerful in helping you work backwards and answer how much of input x you would require to get resultant y. In such cases, also known as what-if analysis, you’re really asking, “If I want to get a specific result, what would my inputs have to be?” One way to answer this is by using Goal Seek, just one of the many powerful, built-in what-if analysis tools Excel provides.

The advantage of using Goal Seek to test various what-if scenarios becomes most obvious if you try testing without it first. We’ll use a routine break-even analysis problem as our illustration, as shown in Figure A.

Download: You can download and extract the file breakeven.xls from the URL given at the beginning of this article.

In this example, we’ve found the break-even point in units and sales. We’ve also filled in our current sales level.

A:

A break-even analysis worksheet provides us with chances for using what-if analysis.

Now, let’s suppose that your investors are pushing you to raise the pre-tax income to $200,000. Let’s examine several scenarios for how you can do that.

In this scenario, let’s suppose that your marketing department assures you that it’s a sellers’ market and that you can raise the sales price. How much would you have to raise your sales price (all other things remaining constant) until income reached $200,000?

In this scenario, let’s assume that the market and your competitors don’t allow you to raise your sales price. Instead, you’ll put pressure on your sales team to sell more. How many units would you have to sell to reach the magic $200,000?

Now let’s say that you can’t reach the lofty sales goal, but purchasing and procurement assures you that they can definitely buy materials for a lower price. Allowing only direct materials to change, how low would you have to go to reach the $200,000 goal?

Using trial and error, spend a few minutes trying to solve for these inputs. When you’re done, use Figure A to set the inputs back to their original values. See how time-consuming it is to solve for spreadsheet input values? Now imagine solving for all three in less than ten seconds. You can do so using Goal Seek, and we’ll show you how.

We’ll use Goal Seek to solve for each of our three scenarios as follows:

- Select cell C18, which contains the pre-tax income value.
- Select Tools | Goal Seek from the menu bar. (In Excel 2007 and higher, click on Data and [...]

Return to the ExcelSkillsSociety's homepage