Microsoft Office Tutorials and References
In Depth Information
FIGURE 7.13 The Break-Even Point Example
12,500 15,000 17,500 20,000
FIGURE 7.14 The Data Table and the Break-Even Point Chart
Here the BEP is calculated by dividing the fixed cost by the Unit price minus (
the cost per unit. In Figure 7.13 the last row reads:
Break-Even Point 6,250
Chapters 16 and 17 show how to find the BEP using the Goal Seek. These
chapters also demonstrate how to create sensitivity analysis tables called Data Tables
in Excel to calculate the Revenue, Expenses, and Profit as a function on the number
of Units Sold. Using the Data Table, we can create a chart. See Figure 7.14.
In order to animate the chart, it is best to fix the axis so it doesn
t change while the
chart parts aremoving. If the axis ranges change youmay lose some of the visual effect of
the animations. Tomake sure the axis does not change follow the following steps: Right-
click on the axis and choose Format Axis. On the Format Axis menu, make sure that at
least the first two, Minimum and Maximum, are selected as Fixed. See Figure 7.15.
If you want to change values that will shift and move the lines on the chart, you
have to use a scroll bar. Creating a scroll bar is described in detail in Chapter 18.
What we want to do on this chart, the BEP chart of Figure 7.14, is vary the two
inputs, Cost and Price to visualize in real time their impact on the break even.
The Break-Even chart was copied/moved to a new sheet on the workbook
Animated Chart. There are two Scroll bars on the left side of the Chart. They were
inserted using the Developer Ribbon
s Insert Tool Box as described in Chapter 18.
The first Scroll Bar will control the Price and the second scroll bar will control the
Cost per Unit. See Figure 7.16.
To configure the first scroll bar: Right-click on the bar. Choose Format Control. On
the Format Control menu enter the information shown on Figure 7.17. The Current