Example Sensitivity Analysis

Boston Metal Company (BMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to be the supplier of transmission housings for Gulf Electric. Gulf Electric has its own in-house manufacturing facility to produce transmission housings, but it has almost reached its maximum production capacity. Therefore, Gulf is looking for an outside supplier. To compete, the firm must design a new fixture for the production process and purchase a new forge. The new forge would cost $125,000, including tooling costs for the transmission housings. If BMC gets the order, it may be able to sell as many as 2000 units per year to Gulf Electric for $50 each, and the variable production costs,* such as direct labor and direct material costs, will be $15 per unit. The increase in fixed costs** other than depreciation will amount to $10,000 per year. The firm expects that the proposed transmission-housings project would have about a 5-year product life. The firm also estimates that the amount ordered by Gulf Electric for the first year will be ordered in each of the subsequent 4 years. (Due to the nature of contracted production, the annual demand and unit price would remain the same over the project after the contract is signed.) The initial investment can be depreciated on a MACRS basis over the 7-year period, and the marginal income tax rate is expected to remain at 40%. At the end of 5 years, the forge machine is expected to retain a market value of about 35% of the original investment. Based on this information, the engineering and marketing staffs have prepared the cash flow forecasts shown in Table 17.5.1. Since NPW is positive ($40,169) at the 15% opportunity cost of capital (MARR), the project appears to be worth undertaking.

However, BMC's managers are uneasy about this project because there are too many uncertain elements that have not been considered in the analysis. If decided, BMC must make the investment in the forging machine to provide some samples with Gulf Electric as a part of the bidding process. If Gulf Electric does not like BMC's sample, BMC stands to lose the entire investment in the forging machine. On the other hand, if Gulf likes BMC's sample but it is overpriced, BMC would be under pressure to bring the price in line with competing firms. There is even a possibility that BMC would get a smaller order as Gulf may utilize their overtime capacity to produce some extra units. They also are not certain about the variable and fixed cost figures. Recognizing these uncertainties, the managers want to assess the various potential future outcomes before making a final decision. (a) Perform a sensitivity analysis to each variable and (b) develop a sensitivity graph.

* Expenses that change in direct proportion to the change in volume of sales or production.

** Expenses that do not vary as the volume of sales or production changes. For example, property taxes, insurance, depreciation, and rent are usually fixed expenses.

TABLE 17.5.1 A Typical Excel Worksheet Design to Perform Sensitivity Analyses (Example 17.5.1)

A | B

I C I

D

I E I

F

G I

H

1 2

Input Data (Base):

Sensitivity Analysis: *..*.

3

Unit Price ($)

50.00

4

Demand

2000

Category

% Change

5

Var. cost ($/unit)

15.00

Unit price

0%

6

Fixed cost ($)

10000

Demand

0%

7

Salvage ($)

40000

Var. cost (unit)

0%

8

Tax rate (%)

40%

Fixed cost

0%

9

MARR (%)

15%

Salvage

0%

10

11

Output (NPW)

$40,169

12

I3

; 0

liSlili

.:•:;:• 2 :

3 • .••••:.:

.• :•' 4

¡lililí:

14

Income Statement

15

16

Revenues:

17

Unit Price

$

50.00

$ 50.00

$

50.00

$

50.00

$

50.00

18

Demand (units)

2000

2000

2000

2000

2000

19

Sales Revenue

$

100,000

$ 100,000

$

100,000

$

100,000

$ 100,000

20

Expenses:

21

Unit Variable Cost

$

15

$ 15

$

15

$

15

$

15

22

Variable Cost

30,000

30,000

30,000

30,000

30,000

23

Fixed Cost

10,000

10,000

10,000

10,000

10,000

24

Depreciation

17,863

30,613

21,863

15,613

5,581

26

Taxable Income

T

42 137

$ 29,387

T

38,137

T

44,387

T

54,419

27

Income Taxes (40%)

16,855

11,755

15,255

17,755

21,768

29

Net Income

IT

25,282

$ 17,632

¥

22,882

T

26,632

T

32,651

30

31

Cash Row Statement

32

33

Operating Activities:

34

Net Income

25,282

17,632

22,882

26,632

32,651

35

Depreciation

17,863

30,613

21,863

15,613

5,581

36

Investment Activities:

37

Investment

(125,000)

38

Salvage

40,000

39

Gains Tax

(2,613)

41

Net Cash Flow

$ (125,000) $

43,145

$ 48,245

$

44,745

$

42,245

T

75,619

Discussion. Table 17.5.1 shows BMC's expected cash flows — but there is no guarantee that they will indeed materialize. BMC is not particularly confident in is revenue forecasts. The managers think that if competing firms enter the market, BMC will lose a substantial portion of the projected revenues. Before undertaking the project described, the company wants to identify the key variables that determine whether the project will succeed or fail. The marketing department has estimated revenue as follows:

annual revenue = (product demand)(unitprice) = (2000)($50) = $100,000

The engineering department has estimated variable costs such as labor and material per unit at $15. Since the projected sales volume is 2000 units per years, the total variable cost is $30,000.

Having defined the unit sales, unit price, unit variable cost, and fixed cost, we conduct a sensitivity analysis with respect to these key input variables. This is done by varying each of the estimates by a given percentage and determining what effect the variation in that item has on the final results. If the effect is large, the result is sensitive to that item. Our objective is to locate the most sensitive item(s).

Solution.

(a) Sensitivity analysis: We begin the sensitivity analysis with a "base-case" situation, which reflects the best estimate (expected value) for each input variable. In developing Table 17.5.2, we changed a given variable by 20% in 5% increments, above and below the base-case value, and calculated new NPWs, holding other variables constant. The values for both sales and operating costs were the expected, or base-case, values, and the resulting $40,169 is called the base-case NPW. Now we ask a series of "what-if" questions: What if sales are 20% below the expected level? What if operating costs rise? What if the unit price drops from $50 to $45? Table 17.5.2 summarizes the results of varying the values of the key input variables.

(b) Sensitivity graph: Figure 17.5.1 shows the transmission project's sensitivity graphs for six of the key input variables. The base-case NPW is plotted on the ordinate of the graph at the value 1.0 on the abscissa. Next, the value of product demand is reduced to 0.95 of its base-case value, and the NPW is recomputed with all other variables held at their base-case value. We repeat the process by either decreasing or increasing the relative deviation from the base case. The lines for the variable unit price, variable unit cost, fixed costs, and salvage value are obtained in the same manner. In Figure 17.5.1, we see that the project's NPW is very sensitive to changes in product demand and unit price, is fairly sensitive to changes in the variable costs, and is relatively insensitive to changes in the fixed cost and the salvage value.

Graphic displays such as those in Figure 17.5.1 provide a useful means to communicate the relative sensitivities of the different variables on the corresponding NPW value. However, the sensitivity graph does not explain any variable interactions among the variables or the likelihood of realizing any specific deviation from the base case. Certainly, it is conceivable that an answer might not be very sensitive to changes in either of the two items, but very sensitive to combined changes in them.

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Post a comment