Page 49 -

USING EXCEL FOR BREAKEVEN ANALYSIS 29

Figure 1.5 Solution Using a Production Volume of 800 Units for the Nowlin Plastics
Production Example

Excel’s Goal Seek tool allows the user to determine the value for an input cell that will
cause the value of a related output cell to equal some specified value (called the goal).
In the case of breakeven analysis, the ‘goal’ is to set Total Profit to zero by ‘seeking’
an appropriate value for Production Volume. Goal Seek will allow us to find the value
of production volume that will set Nowlin Plastics’ total profit to zero. The following
steps describe how to use Goal Seek to find the breakeven point for Nowlin Plastics:
Step 1. Select the Tools menu
Step 2. Choose the Goal Seek option
Step 3. When the Goal Seek dialog box appears:
Enter B18 in the Set cell box
Enter 0 in the To value box
Enter B12 in the By changing cell box
Click OK
The completed Goal Seek dialogue box is shown in Figure 1.6, and the worksheet
obtained after selecting OK is shown in Figure 1.7. The Total Profit in cell B18 is
zero, and the Production Volume in cell B12 has been set to the breakeven point
of 1000.

Figure 1.6 Goal Seek Dialogue Box for the Nowlin Plastics Production Example

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.