Pave a concrete pathway to your financial goal with help from Goal Seek
by Frank Kuhn
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
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.
Working without Goal Seek
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.
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.
Scenario 1: Raise the sales price per unit
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 remaini
