Cost Management: A Strategic Emphasis 5th Edition by David Stout, Edward Blocher, Gary Cokins

Exercise 45

Contribution Income Statements Using Excel; Sensitivity Analysis; Goal Seek

Required

1. Using the data in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI, Inc. Use Exhibits 9.2 and 9.6 as a guide.

---

2. Use the Goal Seek tool within Excel to determine which sales price would allow HFI to earn $100,000 operating profit, assuming that all the other cost information is the same as in Exhibit 9.1. Use Exhibit 9.5 as a guide.

Step 1 of 3

Sensitivity analysis is the name for an assortment of ways that scrutinize how a quantity deviates if factors concerned in predicting that quantity deviates. For example, how the operating profit will change if the factor used to determine the profit like sales price per unit changes. The sensitivity analysis deals with the operating risk. This technique benefits the manager to examine the sensitivity of profit to all possible changes in selling price, fixed cost, variable value, the price at which per unit can be sold and the output level. When there is a major variation in these items than the company’s strategy should also change. There can be a situation in which the company’s sales could reach a level below expectations and the cost can be above the level of expectation.