Goal Seek - a Simple, Powerful Excel Tool

Goal Seek can be used to find a desired value by changing a dependent variable.  For example:  In the following income statement, 100 units have been sold at $1,000 a piece resulting in $100,000 in total sales.  Cost of Goods sold runs 60% of sales or $60,000 in this case, variable costs comes in at 15% of sales and fixed costs come in at a static $10,000.  The resultant EBITDA is $15,000 and after taxes of 30% we are left with a Net Income of $10,500.  It is important to note that each formula is wired into the spreadsheet; if the number of units change, the impact on the various line items will be reflected in the statement.

Income Statement
# of Units Sold	100
Selling Price Per Unit	$            1,000
Total Sales	$        100,000
COGS	$          60,000
Variable Costs	$          15,000
Fixed Costs	$          10,000
EBITDA	$          15,000
Taxes	$            4,500
Net Income	$          10,500

Table 1.1

Goal Seek

If I wanted to find how many units need to sell in order to reach a net income of $30,000, I could hunt and peck, typing in unit numbers until I reach the desired $30,000 net income.  Or I could use goal seek.

I start by selecting the cell containing the net income number of $10,500 and then under the data tab, “What if Analysis” drop-down, I select “Goal Seek.”  The dialog box that opens asks for values for “Set cell”, “To value” and “By changing cell”.  As you can see, by initially highlighting the net income cell, “Set cell” is already pointed to cell C20, the net income cell in my case.

Figure 1.1

“To value” is simply the desired net income I am seeking or $30,000.  “By changing cell” is the number of units sold cell; I want to determine how many units I must sell in order to reach a net income of $30,000.  I do this by setting the net income cell to $30,000 by changing the number of units sold cell. 

Figure 1.2

Remember also, it is imperative to have all of the formulas plugged into the worksheet in order to get an accurate result.  Total Sales in this case is number of units sold multiplied by the selling price per unit.  COGS is 60% of total sales, so on and so forth, with each formula entered in the appropriate cell. 

Hit okay and goal seek will work to find a solution.  In this example it determines that 211 units must be sold in order to reach a net income of $30,000.

Figure 1.3

Income Statement
# of Units Sold	211
Selling Price Per Unit	$            1,000
Total Sales	$        211,429
COGS	$        126,857
Variable Costs	$          31,714
Fixed Costs	$          10,000
EBITDA	$          42,857
Taxes	$          12,857
Net Income	$          30,000

Table 1.2

In Conclusion

Goal Seek is a data analysis tool found in Excel under the Data Tab - What If Analysis.  It can be used to determine a resultant value by changing a variable it depends on.  In our example we sought to find how many units must be sold at $1,000 a piece in order to reach a net income of $30,000.  Given our cost structure composed of Cost of Goods Sold, Variable Costs, Fixed Costs and Taxes, goal seek found that 211 units must be sold at $1,000 a piece in order to reach a net income of $30,000.