Sensitivity Analysis: An Example

To illustrate sensitivity analysis, let’s go back to Snowboard Company, a company that produces one snowboard model. The assumptions for Snowboard were as follows:

Recall from earlier calculations that the break-even point is 500 units, and Snowboard must sell 800 units to achieve a target profit of $30,000. Management believes a goal of 800 units is overly optimistic and settles on a best guess of 700 units in monthly sales. This is called the “base case.” The base case is summarized as follows in contribution margin income statement format:

Question: Although management believes the base case is reasonably accurate, it is concerned about what will happen if certain variables change. As a result, you are asked to address the following questions from management (you are now performing sensitivity analysis!). Each scenario is independent of the others. Unless told otherwise, assume that the variables used in the base case remain the same. How do you answer the following questions for management?

1. How will profit change if the sales price increases by $25 per unit (10 percent)?
2. How will profit change if sales volume decreases by 70 units (10 percent)?
3. How will profit change if fixed costs decrease by $15,000 (30 percent) and variable cost increases $15 per unit (10 percent)?

Answer: The CVP model shown in Figure 6.6 "Sensitivity Analysis for Snowboard Company" answers these questions. Each column represents a different scenario, with the first column showing the base case and the remaining columns providing answers to the three questions posed by management. The top part of Figure 6.6 "Sensitivity Analysis for Snowboard Company" shows the value of each variable based on the scenarios presented previously, and the bottom part presents the results in contribution margin income statement format.

Figure 6.6 Sensitivity Analysis for Snowboard Company

^a $17,500 = $37,500 − $20,000.

^b 87.5 percent = $17,500 ÷ $20,000.

Carefully review Figure 6.6 "Sensitivity Analysis for Snowboard Company". The column labeled Scenario 1 shows that increasing the price by 10 percent will increase profit 87.5 percent ($17,500). Thus profit is highly sensitive to changes in sales price. Another way to look at this is that for every one percent increase in sales price, profit will increase by 8.75 percent, or for every one percent decrease in sales price, profit will decrease by 8.75 percent.

The column labeled Scenario 2 shows that decreasing sales volume 10 percent will decrease profit 35 percent ($7,000). Thus profit is also highly sensitive to changes in sales volume. Stated another way, every one percent decrease in sales volume will decrease profit by 3.5 percent; or every one percent increase in sales volume will increase profit by 3.5 percent.

When comparing Scenario 1 with Scenario 2, we see that Snowboard Company’s profit is more sensitive to changes in sales price than to changes in sales volume, although changes in either will significantly affect profit.

The column labeled Scenario 3 shows that decreasing fixed costs by 30 percent and increasing variable cost by 10 percent will increase profit 22.5 percent ($4,500). (Perhaps Snowboard Company is considering moving toward less automation and more direct labor!)

Computer Application

Using Excel to Perform Sensitivity Analysis

The accountants at Snowboard Company would likely use a spreadsheet program, such as Excel, to develop a CVP model for the sensitivity analysis shown in Figure 6.6 "Sensitivity Analysis for Snowboard Company". An example of how to use Excel to prepare the CVP model shown in Figure 6.6 "Sensitivity Analysis for Snowboard Company" is presented as follows. Notice that the basic data are entered at the top of the spreadsheet (data entry section), and the rest of the information is driven by formulas. This allows for quick sensitivity analysis of different scenarios.

Using the base case as an example, sales of $175,000 (cell D14) are calculated by multiplying the $250 sales price per unit (cell D5) by 700 units (cell D8). Variable costs of $105,000 (cell D15) are calculated by multiplying the $150 variable cost per unit (cell D6) by 700 units (cell D8). Fixed costs of $50,000 come from the top section (cell D7). The contribution margin of $70,000 is calculated by subtracting variable costs from sales, and profit of $20,000 is calculated by subtracting fixed costs from the contribution margin.

Expanding the Use of Sensitivity Analysis

Question: Although the focus of sensitivity analysis is typically on how changes in variables will affect profit (as shown in Figure 6.6 "Sensitivity Analysis for Snowboard Company"), accountants also use sensitivity analysis to determine the impact of changes in variables on the break-even point and target profit. How is sensitivity analysis used to evaluate the impact changes in variables will have on break-even and target profit points?

Answer: Let’s look at an example for Snowboard Company. Assume the company is able to charge $275 per unit, instead of $250 per unit. How many units must Snowboard Company sell to break even? The following calculation is based on the shortcut formula presented earlier in the chapter:

Thus if the sales price per unit increases from $250 to $275, the break-even point decreases from 500 units (calculated earlier) to 400 units, which is a decrease of 100 units.

How would this same increase in sales price change the required number of units sold to achieve a profit of $30,000? We apply the same shortcut formula:

Thus if the sales price per unit increases from $250 to $275, the number of units sold to achieve a profit of $30,000 decreases from 800 units (calculated earlier) to 640 units, which is a decrease of 160 units.