capital-budgeting decisions using “crystal ball”

pherent in capital-budgeting decisions

ng a "Crystal Ball,"

T e chief problem with making capital-budget decisions is that those decisions, such as making a long- range investment, are based on forecasts that carry considerable uncertainty. As a result, researchers have developed techniques to model some of that uncertainty. Monte Carlo simulation, for instance, can model some capital-budgeting uncertainty, although little new work has been done in this area of late. (Monte Carlo simulation is a modeling technique that uses a range of values to describe all of the uncertain inputs in a model and predicts system output through replication. Each replication of the model can

Stanley Atkinson, Ph.D., is a professor of finance at the University of Central Florida, where Charles Kelliher, Ph.D., is a professor of accounting and Stephen LeBruto, Ph.D., is a professor of hospitality management.

© 1997, Cornell University

be thought of as a separate "what if" analysis whereby the simulation changes the value of all the variables simultaneously. Monte Carlo simulation, therefore, shows the entire range of possible outcomes, not just what is probable or most likely based on one's best guesses.) Since 1990, all but one research study we examined used Monte Carlo simulation to estimate the capital- budgeting process; the remaining one used a different simulation to estimate the semivariance of the project returns.

The mathematics involved in these forecasts can be daunting to the layman. Chen, Maghsoodloo, and Park, for example, presented a set of linear-regression models to approximate the semivariance of a sum of independently distributed project returns.

This approximation was bounded by a 1.5-percent relative error for single-project returns (random variables) and 2.5-percent relative error for project portfolios (linear combi- nations). In addition to the bounds in error in estimating the semivariance of the project returns, the regression model provided an easier and more rapid alternative to the computer-simulation method. Lob- man and Baksh subsequently concluded that the use of Monte Carlo computer simulation did not provide definitive proof of the performance of capital-budgeting-decision procedures for dealing with risk)

The researchers wanted to gain insights under fairly complicated and realistic scenarios that would enrich our understanding of the

1C.L. Chen, S. Maghsoodloo, and C.S. Park, "A Method for Approximating Semivariance in Project Portfolio Analysis," Engineering Economist, Vol. 37, Fall 1991, pp. 33-59.

2J.R. Lohman and S.N. Baksh,"The IRR, NPV, and Payback Period and Their Relative Performance in Common Capital Budgeting Decision Procedures for Dealing with Risk (Net Present Value, Internal Rate of Return);' Engi- neering Economist, Vol. 39, Fall 1993, pp. 17-47.

relative performance of these techniques, Meinban, Morris, and Govett then applied a probabilistic model and Monte Carlo simulation to the capital-budgeting process involving the purchase of a wood- fired cogeneration plant. They concluded that the probabilistic model provided a better analysis than other approaches of investments for projects subject to variable returns. Fi- nally, Smith stated that Monte Carlo simulation can help assess risks in capital budgeting. He concluded that the result could be produced quicHy and in a format that allowed decision making without specialized knowledge of statistics)

Little recent work has been done on capital budgeting because it was fairly well explored in the early 1980s; it was perceived to be limited to research (versus being useful for practical applications); and running a Monte Carlo simulation required mainframe computers, knowledge of sophisticated programming lan- guages, and lengthy processing time. Recent technological developments, however, now allow complex simu- lations to be run on a personal computer within a commercial spreadsheet program in a fraction of the time that was required just a few years ago. Not only can Monte Carlo simulation be run on a personal computer, but an add-on application known as "Crystal Ball" can provide better answers to capital-budgeting questions than can other applications a l o n e . 4

In this article we provide a step-by-step example of how this research tool can be used by hospitality and financial managers to ex- amine the capital-budgeting process.

3 D.J. Smith, "Incorporating Risk into Capital- Budgeting Decisions using Simulation (Monte Carlo Simulation)," Management Decision, Vol. 32, No. 9 (1994),pp. 20-26.

4 Crystal Ball (Version 4.0) is a registered trademark of Decisioneering, Inc., Aurora, Colorado.

Grappling with Uncertainty The difficulty in capital budgeting stems from the uncertainties of estimating the amount and timing of future cash flows. Unlike most traditional approaches that ignore uncertainty and rely instead on single, best-guess point estimates, we suggest the need to explore ways to grapple with the probabilistic nature of capital-budgeting calculations.

Recent hardware and software advancements have made it possible to model the uncertainty related to cash flows, for instance, within a spreadsheet. Everyone in the hospitality industry involved with cost planning and control--accountants, managers, controllers, and financial analysts--must expand their understanding of risk and uncertainty to manage better the scarce resources of the company.

Many studies have been pub- lished on capital-budgeting techniques employed by Fortune-500 and -1,000 U.S. corporations. 5 However, only a few studies have examined the capital-expenditure and capital-acquisition policies of firms in the hospitality industry. Two of the major studies in this area were conducted in 1981 and 1990.

Eyster and Geller in 1981 examined the development of capital- budgeting techniques employed by hospitality firms between 1975 and 1980. 6

s For example: L.J. Gitman andJ.R. Forrester, "A Survey of Capital Budgeting Techniques Used by Major U.S. Firms," Financial Management, Fail 1977, pp. 66-71; L.J. Gitman and V.A. Mercurio,"Cost of Capital Techniques Used by Major U.S. Firms: Survey and Analysis of Fortune's 1,000," Financial Management, Winter 1982, pp. 21-29; E. Brigham, "Hurdle Rates for Screening Capital-Expenditure Proposals," Financial Management, Fall 1975, pp. 17-28; and J.M. Fremgen,"Capital-Budgeting Practices: A Survey,"Management Accounting, May 1973, pp. 19-25.

6j.j. Eyster and A.N. Geller, "The Capital- Investment Decision: Techniques Used in the Hospitality Industry," Cornell Hotel and Restaurant Administration Quarterly, Vol. 22, No, 1 (May 1981), pp. 69-73.

October 1997 • 21

Exhibit 1 Deterministic solution to capital-budget problem

Cost of Machine A $16,000 Trade-in value-A $2,000 Useful life-A 5

Period 1

Expenses Training/installation costs Maintenance contract

Supplies Depreciation

Savings less expenses Less: Income (taxes) benefit Net income

2,500 525

2 3 4 5 $5,885

525

$6,297

525

$6,738 $7,209

525 525

500 515 530 546 563 2,800 (825)

281

2,800

(545)

2,800 2,800 2,800 2,045 2,192 2,866 3,322 (695) (745) (975) (1,129) 1,350 1,446 1,892 2,192

Add: Depreciation 2,800 2,800 2,800 2,800 2,800

2,256 4,150 4,246 4,692 6,992 Net cash flow Discount factor 0.909091 0.826446 0.751315 0.683013 0.620921

Present value 2,050 3,430 3,190 3,205 4,342 Total present value-A 16,217 Less: Initial investment-A (16,000) Net present value-Machine A 217

Cost of Machine B $14,000 Trade-in value-B $1,000

2 3 4 5 $5,304 $ 5 , 5 1 6 $ 5 , 7 3 7 $5,966

425

530

425 425 425

546 2,600 2,165

563 2,600 2,379

1,500

Useful life-B

Period

Expenses Training/installation costs Maintenance contract

Supplies Depreciation

425

500 2,600

75

515

Savings less expenses Income taxes (benefit) Net income

2,600 2,600 1,764 1,711

(26) (600) (582) (736) (809) 50 1,164 1,129 1,429 1,570

Add: Depreciation ,, 2 2,600 2,600 ' 2,600 >~,~ ;~ o, ,, ~::~ ;,~' ~600 2,600

Net cash flow 2,650 3,764 3,729 4,029 5,170 Discount factor 0.909091 i 0.826446 0.751315 0.683013 0.620921

Present value 2,409 3,111 2,802 2,752 3,210 Total present value-B 14,283 Less: Initial investment-B (14,000)

283 Net present value-Machine B I I

Eyster and Geller concluded that even though the lodging and food-service companies used more sophisticated methods in 1980 than they did in 1975, the capital- budgeting techniques used in the hospitality industry were misleading and naive as compared to other industries. The 1990 study by Schmidgall and Damitio, limited to large lodging chains, concluded that more hospitality-industry firms used discounted cash flow measures in their decision making in 1990 than did so in 1980. 7 However, Schmid- gall and Damitio noted further that many hotel chains still did not use formal risk analysis in their decision-making processes.

Spreadsheets for Capital Budgeting Electronic spreadsheets are ideally suited for building models to analyze capital-budgeting choices. Fur- thermore, by changing inputs such as the discount rate we can see the model automatically recalculate a new present value of the future cash flows. However powerful, this ad hoc approach involves a great deal of manual effort, since the key inputs need to be changed after each run of the model. Furthermore, each what-if analysis has its own unique solution that makes interpretation of the results difficult. While a deterministic model is mathematically accurate, no one would suggest that it is more than a rough way to analyze capital-budgeting alternatives. A poor estimate for any of the input variables could change the results. A more realistic approach would rec- ognize that the numbers chosen have a particular probability of occurring, as do the many other numbers not chosen. For example, even if it was likely that the discount rate were 10 percent, should we not

7 tL.S. Schmidgall and J. Damitio, "Current Capital-Budgeting Practices of Maj or Lodging Chains," Real Estate Revie~ Fall 1990, pp. 40-45.

22 C t EIt HOTELAND RESTAURANTADM,NISTRAT,ON QUARTERLY

consider the chance that the rate could be 8 or 12 percent? To approach the best estimate of the answers, we seek a similar assessment of almost all future-oriented specifications in the following capital- budgeting example.

A Deterministic Example The Model Hotel is considering buying a new POS system for its F&B department. For the deterministic example, all of the input variables are known with 100-percent certainty (single-point estimates). The company has narrowed the choice to one of two POS systems. In addition to having different initial costs, the two systems have different annual cost savings and future expenses.

The Model Hotel's managers believe that the annual savings from either POS system will increase each year as they learn to use more of the system's capabilities. Addi- tional savings could come from better inventory control, more effi- cient purchasing decisions, menu engineering and analysis, server improvement and productivity, and management's increased use of ex- ception reports.

The cost of POS system A, which was just introduced and comes with the most current technological innovations, is $16,000 with an expected $2,000 scrap value. The system is expected to generate savings of $5,500 during year one, and the savings are expected to grow by 7 percent per year. Installation and training costs for POS system A total $2,500. A maintenance contract on the system is available for $525 per year.

The simpler, older-model POS system B would cost $14,000 with a $1,000 scrap value. This system, however, is expected to generate savings of only $5,100 during year one, and the annual savings are expected to increase by only 4 percent

per year. The installation and training costs associated with POS system B total $1,500, and its maintenance contract would cost $425 per year.

The Model Hotel would depre- ciate both POS systems by the straight-line method over an estimated useful life of five years. Both systems would require an overhaul at the end of year three that is estimated to cost $250.

The cost of supplies for both machines in year one would be $500. Based on past experience, managers expect the cost of supplies to increase by 3 percent per year.

The company's expected tan rate is 34 percent. The Model Hotel uses a 10-percent discount rate to evaluate all of its projects.

The traditional deterministic solution to this capital-budgeting decision is shown in Exhibit 1. This is a spreadsheet model that computes the net present value for each of the two POS systems to determine whether the Model Hotel should invest in system A or system B.

Since the net present value of system B ($283) is greater than the net present value of system A ($217), a deterministic solution of this kind would suggest that the Model Hotel should buy system B. Unfortunately, the deterministic solution does not indicate how sen- sitive this result is to poor estimates of costs and savings (i.e., changes in the inputs). While a deterministic solution works well with inputs that are all normally distributed, moreover, more-sophisticated procedures, such as Monte Carlo simulation, are needed to analyze uncertain data and input variables that may not be normally distributed.

Using a Monte Carlo Simulation A probabilistic simulation model replaces the deterministic problem's single-point estimates with

probability-density distributions that may take on a range of many different values. The chance of a single value's occurring is dictated by its location in the distribution and the distribution's shape. Models of the complexity involved in capital- budgeting decisions require more elaborate procedures than the auto- matic recalculation of interdepen- dent values within spreadsheets. Monte Carlo simulation uses a probabilistic mathematical model to represent these relationships and to predict system output through replication. Unlike other modeling at- tempts, Monte Carlo simulation does not require rigorous certainty assumptions about the values that variables will assume. The input variables do not have to be represented by single-point estimates, but rather are indicated by a range of values with any type of distribution. Five of the commonly used distributions in a such a simulation are:

• Normal distribution--Values are symmetrical about the mean, and are more likely to fall near the mean than far away;

• Uniform dis tr ibut ion-- the values fall equally between the minimum and maximum values;

• Log, normal dis tr ibut ion-- the values are positively skewed, represented by a long tail to the right. The most likely values fall near the minimum value or lower end of the range (usually bound by zero);

• Triang,ular dis tr ibut ion-- the most likely value falls between the minimum and maximum value, and values near the minimum and maximum are not as likely to occur; and

• Custom dis tr ibut ion--describes any unusual distribution. The first step in running a Monte

Carlo simulation is to build the spreadsheet model of the kind we just described in Exhibit 1, with single-point estimates that describe

October 1997 • 23

Exhibit 2 Defining a probabfility-density function

Step 1: Select the distribution that characterizes the input.

Microsoft Excel- FIG_I&2.XLS File Edit View Insert Format Tools --Data Window Cell Run Help

_Weibull

-=1 Cell Big: Distribution Gallery

Unifo[m

Beta

Exponential

H�pe_r geometric

...,,,dllllllh,, ....

_Binomial

6eometmic

III1,,,,,,,,,,, ........... Gu~_ t om

I - ¢

#

1 4 IF I I [ I~ I L f ~ l f l l i l ~ , r l r l ~ L ~ l l ~ l [ l U [ I ~;O:5

15 Maintenance contract 16 Special overhaul 17 Supplies 18 Depreciation 19 Savings less expenses 20 Income taxes (benefit) 21 Net income 22 Add: Depreciation 23 Add: Scrap value 24 Net cash flow

deterministic 3robabilistic

_More

L~OUU

525 525 525 525 250

500 515 530 546 2,800 2,800 2,800 2,800 (825) 2,045 2,192 2,866 281 (696) (745) (975)

(546) 1,350 1,446 1,892 2,800 2,800 2,800 2,800

2,256 4,160 4,246 4,692

m

F G t Type of distribution (parameters)

luniform (9% to 11%) custom (30% or 34%)

triangular (min 5%; most likely 7%; max triangular (min 1%; most likely 3%; max

5 $ 7,209 Iognormal ($5,500 mean, std dev 10%)

525 custom ($250-$400-$500)

563 2,800 3,322

(I ,129) 2,192 2,800 2,000 uniform ($1,000 to $2,000) 6,992 7

the mathematical relationships between inputs. Then one must add the following additional information to build a probabilistic solution for the same problem.

A Probabilistic Example The Model Hotel is still considering buying a new F&B POS system and is still looking at the same two systems (A & B) as described earlier. In this analysis, however, all of the input variables are represented by probability-density functions.

POS system A (cost = $16,000) is expected to generate cost savings

that follow a lognormal distribution with a mean of $5,500 (standard deviation $550). The annual cost savings are most likely to increase by 7 percent per year. However, the cost savings may increase by as little as 5 percent or by as much as 10 percent annually (a triangular distribution). Installation, training, and maintenance-contract costs for POS system A remain the same as above.

The uncertainty assumptions governing the analysis of system B (cost = $14,000) are the same as those for system A. The cost savings associated with POS system B also

follow a lognormal distribution, although the mean is $5,100 and the standard deviation is $510. The most likely growth in system B's cost savings is 4 percent per year, although the savings may be flat over time or rise by as much as 6 percent (a triangular distribution). The installation, training, and maintenance costs associated with POS system B are as described above.

The scrap value of each system is also subject to probabilistic estimates. Due to excessive wear and tear and technological obsolescence, the actual scrap value may be only

24 101{NELL HOTEL AND RESTAURANT ADMIN]STRATION QUARTERLY

F I N A N C E

Step 2: Enter the numerical specifications in the dialog box (minimum, maximum, most likely value).

~ F ~ Microsoft Excel- FIG_I&2.XLS _Edit View _Insert Format Tools Data_Window _Cell Run Help

=1 F

Cell BS: Triangular Distribution

Assumption Name: IAnnual savings increase-A

4"

5.80% 6.25% 7.50%

I. 15.00~4

Min [5.00~ Ukeliest (7.00¾

46 Special overhaul 17 Supplies 18 Depreciation 19 Savings less expenses 20 Income taxes ( b e n e f i t ) 21 Net income 22 Add: Depreciation 23 Add: Scrap value 24 Net cash flow

deterministic \ #robabilistic

8.75% 1000%

i 110.00~

Max { 10.00~

[Correlate.., [ ~ - ~

G Type of distribution (parameters uniform (9% to 11%) custom (30% or 34%)

triangular (rnin 5°/"o, most likely 7 triangular (rain 1%; most likely 3

Iognormal ($5,500 mean, stddev

custom ($250-$400-$500)

uniform ($1,000 to $2,000}

one-half the expected salvage value at the end of five years (a uniform distribution between the minimum and expected scrap value). Likewise, the year-three overhaul for each system is subject to probabilistic estimates. There is a 45-percent chance that the overhaul will cost $250. However, the overhaul could cost $400 (35 percent) or be as high as $500 (20 percent). Too, the expected increase in the cost of supplies, still $500 in the first year, fol- lows a triangular distribution, with a 1-percent minimum and a 4-percent maximum increase.

The Model Hotel's current tax rate is 34 percent. However, management estimates a 35-percent chance that new tax legislation being debated in Congress, if enacted, would lower the company's tax rate to 30 percent. Finally, the Model Hotel estimates that the discount rate to evaluate its capital projects will follow a uniform distribution between 9 percent and l 1 percent (+ 1 percent of the most likely value of 10 percent).

A Monte Carlo simulation model within a spreadsheet (using Excel and Crystal Ball) can be used to

compute the net present value of the two POS systems to determine whether the Model Hotel should invest in system A or system B, as explained below. 8

Once the deterministic model is complete, Crystal Ball or @RISK (another spreadsheet insert) is used to replace the single-point estimate in each cell with the appropriate probability-density function. 9

SExceI (Version 5.0) is a registered trademark of the Microsoft Corporation, Redmond, Washington.

9@tLISK is offered by Palisade Corporation, Newfield, New York. Other programs also exist.

October 1997 • 25

Exhibit 3 Graphic representation of assumptions

gel~ Bt2, Le As

N~

4,057,30

0.00

Mean = 5,500

4,888.45 5,719.59 6,559.73 7,381,8"/

Infinity

Std. dev. = 550

Cell F23, Ur~iferm ~istrib~tie~ Ass~mptie~ ~ame: Scrap ~saI~e-,Na~:t~i~e A

1,000

Min = 1,000

1,250 1,500 1,750 2,000

Max = 2,000

The highlighted cells in Exhibit 1 (shaded) show the inputs (assumptions) that need to be changed to transform the deterministic model into a probabilistic model. While the deterministic solution reported earlier used the mean or most likely value for each input variable and assumed the input variables were all normally distributed, the probabilistic model may be better with uncertain data that are not normally distributed, with data that may be skewed, or with data that can only be estimated by a range of possible values.

He lp for the user. The probability distribution defines the range of values that the cell may take on during the simulation, while the shape of the distribution determines the number of times that individual values will occur in the simulation. Each add-in program contains a menu of the most common distributions needed to characterize most variables encountered in practice. The user selects the appropriate distribution from the software's menu gallery and then supplies the specifications that define the distribution. For most input variables this

requires the user to supply the mean, minimum, maximum, standard deviation, and possibly any correlation between input variables. An option for the user to define a custom-made distribution also ex- ists. Exhibit 2 shows the steps for constructing the probability-density function for the annual cost savings increase for POS system A that has been estimated to follow a triangular distribution.

The solution to this problem requires the construction of a probability-density function for each input variable. Exhibit 3 shows graphs of the distribution and distribution specifications for two input variables (savings and scrap value).

After all of the input variables in the model have been replaced with probability-density functions, the user selects the cells to be forecast during the simulation. In our example, the cells to be selected are the net present value of the future cash flows for each POS system (forecast ceils are displayed in color in Exhibit 1). Finally, the user speci- fies the number of iterations of the model to run during the simulation. Each iteration of the model could be thought of as a different "what- if" analysis, where the inputs to the model are drawn at random from each probability distribution.

After 10,000 iterations of the model, which can be run in under three minutes on a Pentium (166 mhz) IBM-compatible computer, the simulation will have produced a wealth of forecast output. The output includes both common statisti- cal and graphical representations of the data. Some descriptive statistics for the forecast (i.e., the net present value of the future cash flows) include: mean, median, mode, minimum, maximum, skewness, kurtosis, percentiles, and sensitivity of the forecast to each input variable. The results of the simulation show the entire range of possible outcomes,

26 IO~tILHOTEL AND RESTAURANT ADMINISTRATION QUARTERLY

the most likely outcomes, and the probability of their occurrence.

Graphic displays include both frequency charts and cumulative distributions. The user can specify whether the graph will show the entire output range or a subset of the data. Finally, the output allows the user to set a certainty range that would show the probability that the forecast will exceed some minimum value (in this example the probability that the net present value of the future cash flows would be greater than 0). Some of the forecast output is shown in Exhibit 4.

In contrast to the deterministic example, the results of the Monte Carlo simulation show that the net present value of system A (mean 67; median 3) is greater than system B (mean 5; median -105). Also, both results are much less optimistic than the best-case solution given by the deterministic model. This result is confirmed both by the descriptive statistics (mean and median) and by looking at graphs. Clearly, system A is more likely to have a higher net present value than system B. In fact, the median forecast for system B is negative, which indicates that a project based on that system should be rejected. Often, the median is considered to be the best descriptive measure when the data are not normally distributed, as it is in this probabilistic model.

Crystal Ball can also show the certainty that the net present value of the future cash flows will be posi- t ive - tha t is, the likelihood that the project will be successful. In our example there is a 50-percent probability that machine A will yield a positive net present value, while there is only a 47-percent chance of a positive result for machine B. Overall, the results from this Monte Carlo simulation provide more ro- bust information than would any analysis that considers only the single most-likely outcome.

Exhibit 4 Forecasts of net present value of future cash flows

Forecast: Net present value-Machine A

10,000 Trials Frequency Chart

.026

.019

" ~ .013

a . , .006

.000

i ,

. . . . . . . . . . . . . . . . 1 m , ~ . . . . . . . . . . . . . . . .

• : i "i :

"l "~ ~'" • I , . 1~ "1 '1 ~ ' ' I "~ ~"

, , - I IH;: : :: I l h l i - . i . -5 ,000 -2 ,500 0 2 ,600 5 ,000

Certainty is 5 0 , 0 7 % from 01o +Infinity

37 Outliers

259

194.2 i I- I

129.5

64.75

10,000 Trials

.025

.018

. i , i

.012 C~l

0 a . . .008

.000

Forecast: Net present value-Machine B

Frequency Chart

i t

:l' ;; . : , , . ; i

~'I - , i ¢

...... lell*' l, ; ' • " ~.

-4,000 -2,000

t lllllllli,lilirli,iiiiiill I

:., ,~;

: ::,,: :::,':

, . . ~ .

: -" , , I

2,000 4,000

Certainty is 4 7 , 3 1 % from 0 to +Infinity

StatisticsIMachine A Value Statistics--Machine B Value

Trials 10,000 Trials 10,000 Mean 67 Mean 5

Median 3 Median -105 Minimum -5,438 Minimum -4,563 Maximum 7,360 Maximum 6,630

78 Outliers

245

183,7 "1'1

122.5

, i i

61.25

Budgeting Help In the past, Monte Carlo simulation was considered to be an unwieldy analytical tool for all but the largest tasks. The former prohibitive condi- tions involving mainframe computers have been greatly alleviated by software and hardware improve- ments. Monte Carlo simulation is now possible on personal computers, primarily because processing time has been reduced to a fraction of its former duration• Most impor- tant, hospitality managers have at their disposal a far superior financial

tool to assist in making capita]- budgeting decisions than relying on a deterministic model• Our example shows how the use of Monte Carlo simulation produced a different recommendation than did a deterministic model for which POS system to purchase. A probabilistic model can better analyze uncertain data represented by a range of possible values and data that is not normally distributed. Moreover, the outcome can readily be interpreted by a manager who must make a capital-budget decision• OQ :

October 1997 ° 27

capital-budgeting decisions using “crystal ball”

Documents