Financial Management Excel

Introducing Uncertainty

------~----~----~- ---~ ----~------------

Introducing Uncertainty

If we lived in a world of perfect certainty, the catfish fillet project would be accepted without question. After all, it appears that it will increase shareholder wealth by $91,272.55. Unfortunately, the world is not certain. Even in this simplified example, it should be clear that many sources of uncertainty may arise. For example, the marketing department doesn't really know that the finn will sell 200,000 pounds of catfish fillets in the first year. Likewise, it doesn't know that it will be able to get the assumed $2.50 per pound or that demand will grow at an annual rate of 8% per year. Consumer demand may be far less than expected. This could lead to a double whammy: Not only would unit demand be less than expected, but the wholesale price would likely be less than $2.50 per pound. Poor first year acceptance could also mean lower subsequent growth rates. These and many other uncertainties naturally result in uncertainty surrounding our expected annual cash flows that, in tum, results in uncertainty surrounding the estimated NPV

In such an uncertain world, it is helpful to develop models that allow us to determine how much uncertainty surrounds our estimate of the NPY. For example, we might like to make an educated guess as to the probability that the NPY will actually tum out to be less than zero. The following sections will lead us to an answer to this question.

Sensitivity Analysis

As noted above, many uncertain variables exist in our catfish fillet example. In fact, we could say that virtually all of the variables are uncertain, as are many others that we have not explicitly considered. However, some of these variables have more of an impact on the NPY than others. Since it would take a lot of time and effort to generate precise forecasts of every variable, it is helpful to concentrate on only the most important variables. Sensitivity analysis is the tool that helps us to identify the variables that deserve the most attention.

The idea is to make small changes in variables, one at a time, and observe the effect on the NPY (or any other decision criteria). For example, we might change the selling price from $2.50 per pound to $2.25 (a change of -I 0%) and then calculate that the NPY would decline to $34,291.68. Record this fact and reset the selling price to its original value. Now, reduce the terminal value of, the land to $315,000 (a change of -10%) and note that the NPY declines to $77,146.59. Reducing the selling price by 10% leads to a much bigger decline in the NPY than does a similar reduction in the terminal value of the land. Therefore, we should devote more resources to accurately determining the selling price, and not spend much time estimating the value of the land.

There are two problems with the procedure outlined above. First, by making only a single small change to each variable, we may miss nonlinear relationships. Second, to carry out this procedure for each uncertain variable would be cumbersome. We would have to change a

,

365

CHAPTER 12: Risk and Capital Budgeting

variable, write down the resulting NPV, reset the variable to its original value, change another variable, write down the resulting NPV, and so on. To solve the first problem, we can simply make several changes in each variable, both up and down. For example, we could change the selling price per pound by -30% to +30% in, say, 10% increments. This, however, exacerbates the second problem by making the analysis even more onerous. Fortunately, Excel provides a solution.

Using Data Tables

A data table is an Excel tool that automatically performs the process described previously. To see how they work, let's set up a simple example. Suppose that we wish to see what happens to the expected NPV as the sell ing price varies from $1.50 to $3.50 per pound. To start, enter $1 . 50 in G5 and $ 2.00 in H5.4 Now use AutoFill to create the rest of the price series. The next step is to enter a formula into F6. In this case, we are interested in the NPV so we need to enter: =NPV (B14, C2 8: G2 8) +B2 8.

FIGURE 12-3 THE DATA TABLE DIALOG Box

DataTable

~ow ",put cell: [$~i?~~~__ [ .~ !;oIumn ",put cell: r ... . _l~]

t:2.Cj[ ~ I

When we execute the data table command, Excel will automatically substitute the values from G5:K5 into our model (in cell B9) one at a time and record the resulting NPVs in the table. Select F5:K6 (this is the entire area of the table, including the NPV formula) and then choose Data !able... from the What-If Analysis button on the Data tab. In the resulting dialog box, type B 9 into the !!cow input cell edit box as shown in Figure 12-3. After clicking the OK button, this section of your worksheet should look like the one in Exhibit 12-3.

EXHIBIT 12-3 THE DATA TABLE FOR DIFFERENT PRICES

$1.50 $2.00 $2.50 $3.00 $3.50

(136.650 91) (22,689.18) 91,272.55 205,234.27 319,196.00

-----_._--_._--­

4. The data table can be created anywhere on this worksheet, but it cannot be in another worksheet. You can get around this limitation by carefully constructing your formulas.

366

Introducing Uncertainty

The values in G6:K6 are the NPYs. For example, if the price per pound was $1.50 the NPY would be -$136,650.91. Similarly, if the price was $3.50 the NPY would be $319,196. If necessary, you can change any or all of the prices in row 5 and the table will automatically update.

Note that the original NPY formula in F6 is not a part of the table per se, and it might confuse some people. It is only there so that Excel knows what formula to use when calculating the table. We can easily hide this value by simply selecting F6 and changing the font color to white. This will make the table easier to read.

Excel allows for other types of data tables than we have demonstrated here. The data table in Exhibit 12-3 is called a row-oriented one-variable table because our prices are in a row. If the prices were in a column instead, we could create a column-oriented one-variable table. To create a column-oriented table, the only difference is that you would enter the changing cell (89) into the ~olumn input cell edit box (see Figure 12-3). The result would be exactly the same, except for the orientation table. We can also create two-variable data tables that allow for two changing variables. The procedure is similar, but you should check the online help for the details.

Since we have more than one uncertain variable in our catfish fillet problem, we will need several data tables. It will also be helpful, for comparison purposes, to deviate a bit from the methodology described above. Specifically, we can set up several data tables based on percentage changes in our uncertain variables. This will make it easier to compare the result from a change in unit sales to the result from a change in the growth rate.

EXHIBIT 12-4 THE INPUT AREA SET UP FOR SENSITIVITY ANALYSIS

Frozen Catfish Fillet Project Inputs

Cost of Land

Cost of Buildings & Equipment

MACRS Class

Life of Project (Years)

Terminal Value of Land

Terminal Value of Buildings & Equipment

First Year Catfish Sales (lbs)

Price per Pound

Unit Sales Growth Rate

Variable Costs as % of Sales

Fixed Costs

Tax Rate

WACC

250,000

400,000

20

5

350,000

200,000

200,000

2.50

8%

60% 80,000

35%

10%

Sensitivity % 0% 0% 0% 0% 0% 0%

367

CHAPTER 12: Risk and Capital Budgeting

Let's start by changing the input area of the worksheet so that it can accommodate this type of sensitivity analysis more easily. In 05 enter: Sensitivity %, and then in 06:011 enter: 0 % in each cell. Change 86 so that it has a formula rather than a number: = 3 5 0 0 0 0 * (1 +0 6) . Now if we put 10% into 06, for example, the terminal value of the land will change from $350,000 to $385,000. Make similar changes in cells 87:8 II so that those values change as we change the corresponding percentages. Your input area should now look like the one in Exhibit 12-4. Note that we will be doing the sensitivity analysis on only six of the variables.

At this point, we can proceed in a similar manner as we did above'. Let's first create a percentage-based data table for the terminal value of the land. Go to A38 and enter: Terminal Val ue of Land. In 838:H38 enter a series of numbers from -30% to +30% in 10% increments (-30%,-20%, -10%, etc.). In A38, enter the Nrv function: =NPV(B14,C28:G28)+B28. We have now set up the table and all that remains is to select it and execute the Data Table ... command. In this case the row input cell is 06, which is the percentage that corresponds to the terminal value of the land. The data table will plug -30% into 06, which will change the terminal land value in 86 resulting in a different NPY. Next, it will plug in -20% and so on.

Using the same procedure, create data tables for each of the uncertain variables, each time changing the row input cell (07, 08, etc.). You should end up with six data tables as shown in Exhibit 12-5. Note that, as mentioned above, we have hidden the original NPY formula so that the table is easier to read.

EXHIBIT 12-5 DATA TABLES FOR THE UNCERTAIN VARIABLES

A I B J c I D I E I II I G J H 37

lerminal Value of Land ·30%

48.895

Sensitivity Tables

-~OO;() ·10%

63.021 77.147

0%

91.273

10~i;l

105.399

20%

119.524

30%

133,650

38 '3; '40 'it nrValue of Buildings & l.quipmcnt -3()\~·u

67.057

-20% ·!O%

75.129 83.201

0(1'(,

91,273

10%)

99.345

20110

107AI7

30%

115A88 f-;jt '44 First Year Catfish Sales (Ibs) -30/% -20% ·10% O~·1u IO(}';) 20% 30% '45 (N.670) (22.689) 34.292 91.273 148.253 205.234 262.215 '46 f-'-=­

~ Price per Pound ·30% ·20% ·10')', 0% 101}'0 20lJo 30%

48 (79.670) (22/,89) 34,292 91.273 148.253 205.234 262,215 ~ ~ 'itc..;:..;;..

Unit Sales Growth Rate -30~·~)

66.954

·20%) .] O~··(l

74.941 83.046

01% 9l.173

10(%

99,620

20%

108,091

30%

116.686

~ 53r-;r Variable COSlS as 0;(1 ofSales ·30% 347,686

-20~"o ·10%

262,215 176,744

O~··;I

91,273

lorvo

5,801

20°;;)

(79,670)

30%

( 165,141)

---._---- ----- ----~

368

Introducing Uncertainty

Sensitivity Diagrams

Some people can look at thc data tables and see at a glance that the most important variables are the unit sales, price per pound, and the variable cost as a percentage of sales. Others, however, find it helpfu I to create charts of the data. The most appropriate type of chart for this analysis is a Scatter chart. We can either create a separate chart for each variable or put all of the variables in one chart.

To create one chart that shows all of the variables, we must first start with a chart of one of the variables. Select B38:H38, and then Ctrl+c1ick each of the NPY series. Create a Scatter chart and place it somewhere convenient in the worksheet. For this example problem, it turns out that some of the lines overlap, so it is impossible to tell them apart on the chart. This is not generally the case. However, even when we don't have this problem, it can be much easier to see which variables are most important if they are all in separate charts. This is particularly true when we have a lot of variables.

FIGURE 12-4 SENSITIVITY DIAGRAMS FOR EACH VARIABLE

Sensltn il~ Diaj!nlln: Land Valli"

..;> ~_lll.(l(I(1 Z

\'<\.I\i)\. 0

~ ,(HI(j11

17[11;110 =t==117oll()fl '" .10"0 -111"0 1(1""

')erc{'lIhtgi.> CIHlRj:!,C

Sen<dli,-il) Dlanram: Prtcepcr Pound

,.'11.(11)11 ;> e, ~ ,\\ til1l'

1 WOOII 0

lit 1100 " ] 170 (I{lO)

117(1 (100)

,'II",. ·111",. 10"" 1(\""

'"

Percentage Change

Z

*

Sensili\'it) Diagram: Buildin\!.sand Equipment

,111101I

11111(1 ~

1111111>1) 0 "

,(I(j(1I1

~ , IIOIIr

;>

=t=I ~II (1111 I '" ru-,

I'erecntage ("IHinge

"iemiti"it) llillgram: Growth Rate

' ~ II 1)1Ii I > c, ~ 'II mu, Z

rmnoo " <IIIHII!

(7(1ei!".

~

0 ~11'1111011' '11"" (0"" \H"" 'II""

Pereenrage Cnaugc

Scnsith it~ Diagram: t nu "illle'i (Ih ..)

..;> ~ '!I 11(111 I _~ II 11111 ' "

;11.11111)

<i! r-'looOJ II'''"

Percentage Change

Z

* Sensith it) Diagram:

var Cos to; 3\ 'Yo of Sale'"

~ _~". (II)I! Z

" I '''Itl)(t c '11'1011

(~Ii (10(11

~ ~I i ~II 'lilO1 Perccutage Change

Creating a separate chart for each variable is more time consuming, and you need to make sure that the scaling of the axes is the same in each chart. The advantage to this approach is that it is much easier to identify the individual data series. As can be seen in Figure 12-4, the lines with the steepest slopes are the same as those previously identified as the most important variables. In order to make this comparison, it is vital that the axis scaling is identical in each chart. To make creating all of these charts easier, you can copy and paste the first one and then simply change the data ranges. To change the data series in a chart,

369

--------

~ .~,.._- -,,-_..

CHAPTER 12: Risk and Capital Budgeting

right-click in the chart and choose Select Data ... In the dialog box, select a series and then click the ~dit button to change the data ranges.

An alternative to the visual approach is to LIse the SLOPE function to determine the slope of each line. This function calculates a regression equation and returns the slope. It is defined as:

SLOPE(KNOWN_Y'S, KNOWN_X'S)

where KNOWN_Y'S and KNOU'N_X'S are the Y and X data ranges, respectively. For our purpose, the Y variables are the NPVs, and the X variables are the percentage changes. For example, we can use =SLOPE (B3 9: H3 9, B3 8: H3 8) to find that the slope of the line for the terminal land value is 141,259.60. This can then be numerically compared to the slopes of the other lines. The larger the slope, the more important the variable. Note that if any of the slopes might be negative, then it is helpful to LIse the ABS function to return the absolute value of the slope. Adding this function to the formula will change it to: =ABS (SLOPE ( B39 : H39, B38 : H3 8) ) . This allows us to compare magnitudes without regard to signs.

Clearly, the most important variables are the unit sales, price per pound, and the variable cost as a percentage of sales. These are the variables that we will use in our scenario analysis in the next section.

Scenario Analysis

The sensitivity analysis has identified the three most important variables, but we've only seen their impact on the NPV in isolation. A scenario analysis will allow us to see the combined effects of changing all of these variables simultaneously. Suppose that after seeing the sensitivity analysis report, a meeting was held to determine three possible scenarios. The best and worst cases are shown in Table 12-3 along with the base case, which represents the original expectations. It also shows the probability that each scenario will actually occur.

TABLE 12-3 THREE SCENARIOS

.. Variable Worst Case

20% Base Case

6()% Best Caw

20% ......••.. Unit Sales 125,000 200,000 275,000

Price per Pound $2.25 $2.50 $2.65

Variable Cost % 65% 60% 55%

Note that the worst case scenario is one in which all of the variables are at their worst possible values. Similarly, the best case assumes that all of the variables take on their best possible values simultaneously. While such outcomes are unlikely, they are useful for determining the extreme boundaries around the expected NPY.

370