Microsoft Office Tutorials and References
In Depth Information
Break-Even Point (BEP) Chart
FIGURE 17.12 Projections Statement Model
I prepared two tables: one to investigate the impact of changes in the number of
units sold on the Revenue, Expenses, and Income and a second table to examine the
effect of changes of the selling price on the same output.
In the first table, I transferred the Revenue, Expenses, and Income to the top of
the table using the following procedure: in cell F3, I typed
and selected C5, which
is named Revenue. I repeated the process for cells G3 and H3, resulting in the
information shown in Figure 17.13.
Now you can select the data table range E3:H13 and use the Data Table menu as
shown in Figure 17.14. Remember that you are trying to analyze the impact of
varying the number of units, therefore it is necessary to use the original number
of units as the Column Input cell. The results are the values in the figure. Check
where the break even point is. In Chapter 16, we determined, using Goal Seek, that it
was at 59,333 units; the sensitivity analysis returns the same result as the Income goes
from negative to positive between the 50,000 and 60,000 units mark.
BREAK-EVEN POINT (BEP) CHART
To generate an easy-to-read visual aid, create a BEP chart. After selecting the table
E3:H13, use the Chart Wizard with the Scatter XY Chart to create the BEP chart
(Scatter with data points connected by lines; Excel calls it
Scatter with Straight Lines
). See Figure 17.15. You can use the Insert ribbon for the chart.
The Break-Even Point chart is shown in Figure 17.16. Depending on your
audience, the visual impact might be stronger with a graph than with numbers only.
The break-even point is easily found on the chart.