The purpose of this paper is to demonstrate the use of simulation to increase understanding of the capital budgeting process. We use a capital budgeting case to demonstrate the use of scenario analysis and Monte Carlo simulation for capital budgeting using several types of probability distributions to represent different inputs. We use internal operations of Excel and do not use an independent software package or add-in. All of the operations are Excel functions.
INTRODUCTION
The use of sophisticated capital budgeting techniques has increased dramatically for large firms. Scott and Petty (1984) report that the percent of firms using discounted cash flow techniques rose from 30% in 1960 to 86% in 1978. Farragher, Kleiman, and Sahu (1999) report an increase in the use of discounted cash flow techniques from 57% in 1969 to 86% in 1986. Bierman (1993) reports that 99% of the 1991 Fortune 100 firms use the internal rate of return technique as a primary (88%) or secondary (11%) capital budgeting technique. Bierman reports that 73 of 74 responding firms use IRR, 85% use NPV as the primary or secondary capital budgeting technique. However, Graham and Harvey (2001) report that large firms use NPV and IRR significantly more than small firms, which are more likely to use payback period. One might argue that large firms can afford to be more risk oriented in assuming projects since they may rely on the law of large numbers. Ford would need a large number of adverse results projects in a row to suffer financial catastrophe while a small firm may only need one adverse result. Size may be an issue even in the Fortune 500. The largest firm for 2000 has annual revenue of $210 billion and the smallest firm has annual revenue of $3.2 billion. The largest firm is 66 times the size of the smallest firm. The Fortune 100 represents 51.5% of the total revenue of the Fortune 500.
Pinches (1982) identifies four stages of the capital budgeting process: identification, development, selection, and control. Farragher, et al. (1999) expand the four step capital budgeting process discussed in Pinches (1982) to an eight step process. Stage one is strategic analysis - determining areas in which the firm has competitive advantage. Stage two involves determining investment goals - minimum required rates of return and maximum levels of risk for investments. Stage three is the process of finding investment opportunities. Stage four is forecasting future cash flows from projects. Stage five is determining the value of the project under consideration. Stage six is the determination of which projects are acceptable. Stage seven is the implementation of accepted projects. Stage eight is the post-audit of accepted project outcomes.
Farragher, et al. (1999) find that 90%, or more, survey respondents indicate that they consider strategic issues in the capital budgeting process, establish strategic goals, and view capital budgeting as an ongoing process. Although 93% of respondents forecast cash flows fore capital budgeting projects, only 60% of respondents require a formal link between project cash flows and corporate strategy. Fifty-five percent of respondents require quantitative risk analysis, of which 95% use sensitivity analysis, 79% used scenario analysis, but only10% use Monte Carlo simulation or beta analysis (11%). Most respondents use internal rate of return (80%) or net present value (78%), but 52% use payback period. Eighty-eight percent of respondents assign specific responsibility for projects and the same percent conduct post-audits of accepted projects.
Graham and Harvey (2001) report survey results for 392 CFO's about capital budgeting, cost of capital, and capital structure decisions. Although 75% of respondents use NPV/IRR, larger firms are more likely to use NPV/IRR and small firms are more likely to use payback period. Only 14% of respondents use simulation for risk analysis. Ho and Pike (1991) find that only 11% of respondents use simulation analysis. The use of sophisticated risk analysis techniques has increased, but not the use of Monte carlo simulation. Klammer (1972) reports that 13% of respondents use simulation and Klammer, Boch, and Wilner (1991) report that 12% of respondent use simulation.
Farragher, Kleiman, and Sahu (2001) report that 70% of firms use risk-adjusted cash flow in their capital budgeting analysis. Farrahger, et al. (2001) find no "clear" relationship between the level of sophistication of capital budgeting and firm performance.Reichert, Moore, and Byler (1988) report that 91% if survey respondents encourage decision makers to use computers and 91% believe that the use of computers improves the capital budgeting process. Ho and Pike (1991) report limited use of Monte Carlo simulation with 2.8% of respondents using the technique for capital budgeting purposes all off the time. Pike (1988) posits that the increase in the sophistication of the capital budgeting process over the period from 1980 to 1986 results from the increased availability of computers and software packages.
THE CAPITAL BUDGETING PROCESS
Capital budgeting is the process of making long-term investment decisions in a corporation. Capital budgeting involves making tactical decisions as a final stage of strategic planning. Strategic planning has three components: defining goals, developing strategies for implementing these goals, and making tactical decisions to implement these strategies.
Following the Farragher, et al. (1999) model, the capital budgeting process has eight stages:
strategic analysis
determining investment goals
finding investment opportunities,
forecasting cash flows for the proposals,
evaluating cash flows for the proposals,
selecting proposals, and
implementing selected proposals
post-auditing proposal performance.
Steps 1 to 3 involve the process of determining which projects to evaluate. Steps 4 to 6 involve determining which projects to implement. Steps 7 and 8 involve the implementation and monitoring of the projects that are accepted. This paper deals primarily with Step Five, the evaluation of proposals.
There are five categories of capital budgeting projects. For alternative classifications of capital budgeting projects, see Brigham and Ehrhardt (2002, page 504) or Moyer, McGuigan and Kretlow (2001, page 300). These are listed from least risky to most risky.
replacing capacity for existing products,
expanding capacity for existing products,
expanding capacity for new products,
research and development, and
mandated projects.
Replacing capacity for existing products is the least risky because this decision does not affect market share and, consequently, does not lead to reaction from competitors. Expanding capacity for existing products carries a higher level of risk because expansion may require increasing markets share which may lead to competitors reacting by, for example, lowering prices. Expanding capacity for new products has higher risk yet because the firm has less information about the demand for a new product. Research and development has more risk because of the uncertainty of the final outcome. Mandated projects, such as environmental projects or health and safety projects, must be implemented to remain in business. The decision is to implement the project or cease operating.
Each capital budget project must be evaluated on the basis of incremental cash flows for the project. Incremental cash outflows for the project are composed of increases in revenue and decreases in costs. Incremental cash outflows for the capital budgeting project are increases in materials cost, labor cost, selling and administrative expenses, depreciation and amortization, and taxes.
There a number of acceptable techniques to evaluate capital budgeting projects. The technique most widely used in the United States is the internal rate of return (IRR) for the project. The IRR, ( r ), is the discount rate that equates the present value of incremental cash inflows, (CIt), with the present value of the incremental cash outflows, (COt).
The left-hand side of the equation is the discounted present value of the cash outflows, and the right-hand side of the equation is the discounted present value of the cash inflows. The net present value (NPV) is the difference between the discounted present value of the cash inflows and the discounted present value of the cash outflows. The discount rate used is the required rate of return for the project.
FINANCIAL MODELING
Financial modeling is the process of using a system of equations to represent an actual situation. Although a model may not be an exact representation of the situation, the model should provide a method to analyze the actual situation. In a situation with only one set of input variables, only one outcome is possible. With actual situations, multiple outcomes are not only possible but likely. Scenario analysis and simulation analysis can both be used to evaluate the likelihood of a particular outcome value and how much effect each variable has on the outcome.
The first step in risk analysis is to define the relevant variables and how each variable affects the outcome of the actual situation being analyzed and how the variables interact. The second step of the process is to determine the different outcome values for each scenario and for each set of simulation parameters and to conduct sensitivity analysis. The third step is to make decisions based on the results for step two.
To implement this paradigm, we use program functions within Excel perform financial modeling. We show how to analyze the risk profile of a capital budgeting project within Excel. Table 1 contains the deterministic financial model used in this paper - a capital budgeting decision. Gross profit margin [GPM] is the difference between revenue [sales volume times sales price] and cost of goods sold [sales volume times unit variable cost]. Net income before taxes is GPM minus operating expenses and depreciation. Taxes are calculated with an assumed tax rate of 34%. Taxes owed are adjusted for any tax carry-forward of previous losses. Net income is net income before taxes minus taxes owed. Net cash flow is net income after taxes with the addition of depreciation, which is a non-cash expense. The final panel computes the net present value (NPV) and internal rate of return (IRR) for the project.
Table 1
NPV and IRR Simulation
Input Variables
Assumptions:
Probability Distribution
Mean/ Mode
Standard Deviation
Low
High
Growth Rate
Sales volume
Normal
100,000
2,000
6%
Variable cost per unit
Triangular
$6.00
$5.00
$7.00
10%
Sale price per unit
Empirical
Unit Price
Probability
Cumulative probability
$11.00
0.20
0.00
8%
$12.00
0.60
0.20
$13.00
0.20
0.80
Tax rate
34%
Cost
$2,000,000
Cost of capital
12.50%
Gen & Admin
$25,000
Risk analysis requires an information set, knowledge about the number and value of possible input variables. When a project decision involves a single value or outcome for each input variable, risk analysis is not possible. More information about a decision allows for more risk analysis. A probability distribution with three points [high, low, best guess] provides more information for risk analysis than a point estimate. A triangular distribution provides even more information.
A normal distribution provides the highest level information set. The normal distribution is useful because of the ability to conduct very strong hypothesis testing. A normal distribution is such that 68% of the sample outcomes fall within one standard deviation of the mean.
Discrete probability distributions represent four possible information sets:
equal probability for each outcome,
symmetric probabilities with a large probability of no change,
non-symmetric probabilities with positive effects,
non-symmetric probabilities with negative effects.
Each of these distributions can represent an actual situation. In the first case, information about changes is available and each outcome is weighted equally. In the second case, it is not known in which direction the variable changes and there is a large probability of only a small change. In the last two cases, the estimated variable change indicates a specific direction of change.
WIDGET CORPORATION: A CAPITAL BUDGETING EXAMPLE
Widget Corporation is considering a new manufacturing project. This will be treated as a stand-alone, new venture analysis. The cost of building and equipping the manufacturing plant is $2,000,000 and will be depreciated over the five year life of the project. WidCo uses straight line depreciation. Using Modified Accelerated Cost Recovery System (MACRS) would be straightforward with a spreadsheet. For example, see Moyer, McGuigan, and Kretlow (2001), pp. 332-335. WidCo believes that the riskiness of this project requires a 12.5% required rate of return.
Sales volume in the first year will be normally distributed with an expected value of 100,000 units and a standard deviation of 2000 units and demand will rise by 6% each year. The initial price of a unit will have three possible outcomes of $11, $12 or $13 and will rise by 8% each year. Variable cost per unit will follow a triangular begin at minimum value of $5, a most likely outcome of $6, and a maximum outcome of $7 and rise by 10% per year. The marginal tax rate is assumed to be 34%. To do the capital budgeting analysis, we first construct a table of cash flows for WidCo after which we compute the net present value and internal rate of return for WidCo. These probability distributions are chosen to show the variety of distributions available.
Table 2 provides a solution to the capital budgeting example for a deterministic scenario. For the deterministic scenario, all of the input variables are assumed to be deterministic, that is all of the input variables are assumed to be known with certainty. The first three rows show the value of the three input variables: sales volume, sales price, and variable cost per unit. The level of sales volume begins at 100,000 units in year 2000 and grows by six percent each year to end at 126,248 units. The beginning sale price is $12 and grows by eight percent each year to end at $16.33 per unit. Variable cost per unit begins at $6 and grows at ten percent each year to end at $8.78 per unit. The IRR for this scenario is 15.84 percent and the NPV for this scenario is $178,546. Table 2 shows the results for the NPV and IRR analysis using a deterministic model. The NPV is $178,546 and the IRR is 15.84%.
Figures 1 to 3 show the probability distributions assumed for each of the input variables. Sale volume is assumed to follow a normal distribution with a mean value of 100,000 units and a standard deviation of 2000 units. Sales price is assumed to be a histogram distribution with values of $11, $12, and $13. The bottom and top one-third each have a probability of 20% and the middle one-third has a probability of 60. Unit cost is assumed to be a triangular probability distribution with a minimum outcome of $5, a most likely outcome of $6, and a maximum outcome of $7. Figures 4 and 5 show the probability distributions for the NPV and the IRR calculated in the simulation.
Table 2
NPV and IRR Simulation
Output Variables
NPV/IRR Computation
Year
1
2
3
4
5
Sales volume
100000
106000
112360
119102
126248
Sale price per unit
12.00
12.96
14.00
15.12
16.33
Variable cost per unit
6.00
6.60
7.26
7.99
8.78
Revenue
1200000
1373760
1572680
1800405
2061103
Variable costs
600000
699600
815734
951145
1109036
Depreciation
400000
400000
400000
400000
400000
Gen and Admin
25000
25000
25000
25000
25000
EBT
175000
249160
331947
424259
527068
Taxes (34%)
59500
84714
112862
144248
179203
EAT
115500
164446
219085
280011
347865
Depreciation
400000
400000
400000
400000
400000
Net cash flow
-$2,000,000
515500
564446
619085
680011
747865
PV ($)
2178546
Cost ($)
2000000
NPV
178546
IRR
15.84%
Figure 1
Probability Distribution of Unit Sales
Figure 2
Probability Distribution of Unit Sales Price
Figure 3
Probability Distribution of Unit Cost
ONE-VARIABLE DATA TABLE
To run the simulation models multiple times and collect the output simultaneously we use a one-variable data table. A one-variable data table has input values entered in either a column (column-oriented) or row (row-oriented). Formulas must refer to column (row) input cell for column-oriented (row-oriented) one-variable data table. We used the column-oriented table in our paper. To run our simulation model 1,000 times, we did the following:
Name the worksheet containing the simulation model "Model".
Open a new worksheet and entered NPV, =Model!C37, IRR, and =Model!C38 in cells B1, B2, C1 and C2, respectively.
Enter 1 and 2 in cells A3 and A4, and use the fill handle to extend the series up to 1000, i.e., we enter 1 through 1000 in the range A3:A1002. This step is optional.
Highlight the table range A2:C1002. Column A must be included even if nothing has been entered into it. Please note that Row 1 is not included. The first row of the data table must be the row containing the formulae.
On the Data tab, click What-If Analysis in the Data Tools group, and then click Data Table.
Enter A1 (or any other cell outside the range of the table) in the Column Input Cell box and click the OK button.
Step 3 is optional because the two formulae in Row 2 do not refer to A1, i.e., the input values in Column A are not used at all. All we do is to trick the data table tool to run our simulation model 1,000 times.
To obtain the descriptive statistics for both NPV and IRR, (1) click Data Analysis on the Data tab, (2) select Descriptive Statistics and click OK, (3) enter B3:C1002 in the Input Range textbox, select an output option and click OK. The descriptive statistics show that NPV ranges from -461,810 to 784,812 and IRR, from 0.03 to 0.27. To generate a histogram, a bin range is required. The bin range for NPV was entered in cells E3 through E18 based on the NPV range stated above. To generate the frequency-distribution column chart for NPV, (1) click Data Analysis on the Data tab, (2) select Descriptive Statistics and click OK, (3) enter B3:C1002 in the Input Range textbox, enter E3:E18 in the Bin Range textbox, select an output option, check Chart output and click OK.
Table 3 shows the output statistics generated by the simulation. The expected value for the NPV is $178,768 with a standard deviation of $233,418 and a range from -$461,810 to $784,812. The IRR has an expected value of 15.77% with a standard deviation of 4.21% and a range from -3.30% to 26.98%. The NPV has a probability of 80% of being positive. The IRR has a probability of 87% of being greater or equal to the cost of capital. Thus, for this project, the risk of actually having a positive NPV is very high. The NPV and IRR histograms are depicted in Figure 4 and Figure 5, respectively.
Table 3
Summary Statistics for Capital Budgeting Simulation
NPV
IRR
Mean
178472
Mean
0.157676
Standard Error
7381
Standard Error
0.001333
Median
189817
Median
0.156621
Mode
#N/A
Mode
#N/A
Standard Deviation
233418
Standard Deviation
0.042145
Sample Variance
54484062000
Sample Variance
0.001776
Kurtosis
-0.162523
Kurtosis
-0.197723
Skewness
-0.063624
Skewness
-0.050389
Range
1246622
Range
0.236771
Minimum
-461810
Minimum
0.032982
Maximum
784812
Maximum
0.269753
Sum
178472067
Sum
158
Count
1000
Count
1000
Largest(1)
784812
Largest(1)
0.269753
Smallest(1)
-461810
Smallest(1)
0.032982
Confidence Level(95.0%)
14485
Confidence Level(95.0%)
0.002615
Alternative solutions to the same capital budgeting example but with Sales volume 95,000 or 105,000 units, sales price of $10 or $14 per unit, and unit costs of $5 or $7 per unit are computed. With increased (decreased) sales volume, the IRR increases (decreases) to 14.19% (10.87%) and the NPV increases (decreases) to $89,951 (-$85,355). With increased (decreased) sales price, the IRR increases (decreases) to 23.35% (0.02%) and the NPV increases (decreases) to $609,982 (-$605,386). With decreased (increased) unit costs, the IRR increases (decreases) to 18.31% (6.3%) and the NPV increases (decreases) to $317,806 (-$313,209).
Scenario analysis allows the financial decision maker to change variable inputs to determine the sensitivity of NPV/IRR to changes in each input variable. Scenario analysis allows the decision maker to determine which input variable have the most significant impact of NPV/IRR. The capital budgeting project can be restructured to mitigate the effect of those input variables where only a small adverse change in the input variable changes the NPV/IRR decision.
A significant scenario level for each input variable is the level at which the NPV is zero or the IRR is equal to the required rate of return, 12.5%. For sales volume, this level is 99,869 units. For sales price, this level is $11.99 per unit. For unit variable cost, this level is $6.01 per unit. This project is very close to the break-even point in the original scenario.
SUMMARY AND CONCLUSIONS
In this paper, we discuss the evolution of the theory and practice of capital budgeting. We note that, although the use of sophisticated capital budgeting techniques is almost universal among large firms, small firms use less sophisticated capital budgeting techniques. We further note that the use of simulation analysis in capital budgeting is at a very low level and has not increased significantly over time.
This paper discusses the capital budgeting process and the types of capital budgeting proposals firms evaluate. We discuss financial modeling and give an example of a capital budgeting problem. We discuss scenario analysis and the use of simulation.
This paper is intended to demonstrate the use of simulation in the capital budgeting process. By using simulation analysis, the financial decision maker is able to determine the overall probability that a project will not meet the firms intended goals and objectives even when the expected value of the outcome is above the stated minimum (the project has a positive net present value). The financial decision maker is further able to determine the sensitivity of the project to certain of the variables. This sensitivity analysis allows the financial decision maker to focus on the variables that most affect the outcome of the decision accept or reject the project.
