# Using Monte Carlo simulation for a capital budgeting project.

Monte Carlo simulation is a powerful spreadsheet-based tool that
allows managers to better understand and visualize risk and uncertainty
in discounted cash flow (DCF) analysis. The primary output, a histogram
of net present values (NPV), maps the entire distribution of possible
outcomes as a bell-shaped curve and therefore estimates the probability
of success for the project (e.g., NPV > zero). Although we use
fictional names, we illustrate a real capital budgeting problem using
Monte Carlo simulation to demonstrate how employing this tool can result
in more-informed decision making.

Finance theory states that expected (mean) cash flows should be discounted at the opportunity cost of capital using a decision rule to accept or reject all positive or negative NPV projects. A central issue for managers, however, is how to deal with uncertainty--i.e., the fact that expected cash flows are only a point estimate of a large number of possible realizations. Traditional finance textbooks suggest two tools for this--sensitivity analysis and scenario analysis. Sensitivity analysis tweaks one variable at a time and evaluates the effect on the project's net present value, and scenario analysis examines a limited number of combinations of variables: worst-case (WC), most-likely-case (MLC), and best-case (BC) estimates of financial variables that determine future cash flows (e.g., sales, costs, growth rates, investment in working capital, etc.). The output is three project NPVs where all variables simultaneously take on one of the three hypothetical realizations. Neither tool produces probabilities of success or failure for the project.

Monte Carlo simulation, however, overcomes the limitations of sensitivity and scenario analyses by examining the effects of all possible combinations of variables and their realizations. Although the inputs are no different from scenario analysis, Monte Carlo simulation treats the estimates as a triangular distribution with the probability of WC and BC realizations being close to zero and increasing linearly up to the MLC. The simulation package then draws randomly from this distribution (100,000 times in our examples) for all variables that are specified in the DCF model and calculates an NPV for each draw. The result is a bell-shaped distribution function of NPVs (i.e., the area under the curve is equal to one) that provides estimates of the probability of success and failure. Sensitivity analysis applied to the Monte Carlo results allows us to address issues such as:

1. Which variables are the most important--i.e., have a large impact on the NPV calculation and/or have a high level of uncertainty? This helps focus management attention on relevant issues and actions that might reduce uncertainty.

2. Suppose the project-evaluation team sees that there is an optimistic bias in the forecast of unit sales. It is a simple matter to change the WC and/or the BC estimate and determine the resulting decrease in the probability of a successful project.

3. Suppose management believes that prices and volume are negatively correlated--i.e., on average, higher prices result in lower sales volume. Alternatively, many costs move up or down together, implying a positive correlation. Simulation software permits the financial modeler to specify such correlations and quantify their effects on the probability of success or failure.

An interesting byproduct of simulation is that it can clearly highlight implausible assumptions. Consider a project submitted to the capital expenditure committee with a 99% probability of success and an internal rate of return (IRR) greater than the cost of capital. On the surface, it appears to be the perfect project--high returns with little chance of failure. Projects with high returns, however, by definition must be risky projects because, as we know, there is no free lunch. Therefore, the probability of a positive NPV project cannot be close to 100%--even projects that earn their cost of capital are risky. Monte Carlo simulation can be a useful tool for detecting the inherent optimistic bias of project originators.

The two main commercial simulation software packages are Crystal Ball and @Risk. We will use Crystal Ball to analyze a capital expenditure project involving the purchase, installation, and commercial use of an MRI scanner a group of physicians affiliated with a large state university purchased. One of us has used Crystal Ball in MBA classroom and executive education programs, so we know it has sufficient uncertainty about future outcomes of volume, product mix, prices, and costs to provide sophisticated users of financial information with a rich forum for discussion and analysis.

THE MRI SCANNER PROJECT

CRSA, a physicians group affiliated with a university medical center, is considering the purchase of a new state-of-the-art MRI scanner. Dr. Margaret Reed, CEO of CRSA, believes a market for this service exists (more than 2,500 scans per year at a separate location) and that the group can make a significant amount of money on the investment, which would provide much-needed funding to meet the group's research and teaching missions.

General Electric Medical Systems offers the first Open Bore MRI scanner with a total imaging matrix, 16 channels, twin-speed, dual-gradient, and high-definition format. The base cost is $1.4 million, but the customer must also purchase a number of accessories, such as injectors, workstation, and computers.

The clinical operations of CRSA departments (cardiology, neurology, orthopedics, etc.) are distinct legal and economic entities with some centralized management functions (legal, accounting, planning, etc.). Instead of mandating the project, Dr. Reed has decided to finance the venture by selling shares of stock to individual departments. In this new era for the group, CRSA analyzes capital investment projects using DCF techniques with uncertainty explicitly incorporated into the analysis. All department chairs have completed executive education programs in business and are sophisticated users of financial information. Therefore, investors will expect a fully integrated set of pro forma financial statements (balance sheet, income statement, and cash flow statement) that calculate free cash flows to investors. Of particular importance to Dr. Reed is the transparency of the assumptions about the future and resulting NPV/IRR.

VOCABULARY

Readers will need to understand two terms from the healthcare industry.

CPT[R] Codes: A registered trademark of the American Medical Association, CPT stands for current procedural terminology. The codes provide a comprehensive description of medical, surgical, and diagnostic services and are designed to communicate uniform information about medical procedures among physicians, coders, patients, accreditation organizations, and payers for administrative, financial, and analytical purposes (adapted from Wikipedia).

RVU: RVU (relative value unit) represents the level of effort, expertise, and resources required for medical procedures. Medicare and HMO reimbursement rates are often a constant dollar amount times the RVU for a procedure, so higher numbers represent more-difficult and, therefore, expensive procedures.

FORECASTING ASSUMPTIONS

Any capital expenditure project requires forecasts of key financial and operating variables that determine cash flows in order to perform an NPV analysis. In this analysis, the key variables are product mix, reimbursement rates, volume (number of scans), collection period, and operating costs (see Table 1).

(1) Product Mix: The actual MRI scanner project used historical data from another installation on the type of scan (CPT code), its RVU, and the number of scans to predict the weighted average (WA) RVU of 19.13 per scan (see Table 2). Our examination of the data showed a logical grouping to summarize product mix into low RVU (48% of total) and high RVU (52% of total) scans. Therefore, the key product-mix variable that must be predicted is the fraction of low RVU scans (the fraction of high RVU scans will be its complement).

(2) Reimbursement: An analysis of recent reimbursement rates per RVU revealed an average of $34.50 across different providers. Multiplied by the WA RVU, this figure is the expected reimbursement per scan for year one. The key variable is whether the change in reimbursement rate will go up or down in the future, and this will determine the projected revenue per scan over the five-year life of the project.

(3) Volume: The average cycle time for an MRI scan is about one hour, so, based on a 12-hour day, the maximum capacity of the facility is about 12 scans per day. The key variables we need to predict are the number of scans in the first year and the subsequent increase. These estimates, in combination with the number of facility days per year (five days per week times 48 weeks per year), is the basis for predicting the annual number of scans.

(4) Capital Expenditures: Base cost for the GE Medical Systems scanner is $1.4 million, but that figure does not include a number of accessories, such as injectors, workstation, and computers, so the total investment is $1.468 million. These numbers are known with certainty because of the GE bid on the proposal. The estimated residual value is $0.2 million.

Working Capital: Working capital requirements in our model include accounts receivable, supplies inventory, and minimum operating cash balance. WC, MLC, and BC estimates are required for the simulation.

(5) Financing: Total capitalization for the project was conservatively estimated at $2.2 million. The cost of equity capital (COEC) for the departments is 11%, and the income tax rate is 40%. We calculate NPV and IRR for an all-equity-financed project and avoid weighted average cost of capital (WACC) and capital structure issues.

(6) Operating Costs: These include direct supplies (primarily film), personnel, and other operating costs. In each case we assume that estimates for the first year are fairly accurate, but the annual increase in costs is the variable to forecast.

Variable Costs: Film for the scanner is estimated to be $65 per scan. The first-year cost is known with considerable accuracy, but we must also forecast increases for the simulation.

Personnel Costs: Two MRI technicians need to be on duty at all times. Running 1.5 shifts per day (12 hours) requires three FTEs. In addition, one registration clerk needs to be on duty, requiring 1.5 FTEs. Estimates for year one are based on current salary levels for MRI technicians and clerks. Increases in personnel costs are subject to uncertainty.

Other Operating Costs: Table 1 shows estimates for year one that GE Medical Systems provided; increases are subject to uncertainty.

(7) This table summarizes income statement items through the operating expenses.

NPV ANALYSIS

Table 3 presents the pro forma income statements, balance sheets, and cash flow statements. Income statement numbers are derived from the summary table in Table 1. We determined all numbers on the balance sheet (except retained earnings) by formula through the forecasting assumptions. Therefore, the plug number to complete the balance sheet is ending retained earnings each year. With this number, implied dividends are computed from the clean surplus relation:

Ret [Earn.sub.0] + [NI.sub.1] - [DIV.sub.1] = Ret [Earn.sub.1]

The dividends represent free cash flow (FCF) to investors, who, in this case, are CRSA departments that decided to purchase shares in the MRI scanner project. We use FCF to investors to determine the project's NPV and IRR. Note that the cash flow statement articulates with the balance sheet; i.e., net cash flow each period is equal to successive changes in cash balances. It is also interesting to note that cumulative cash flows do not turn positive until the project's fifth year.

The project NPV equals $87,134, and the IRR equals 12.5%. MLC estimates of all forecasted variables gave us these point estimates. One would conclude that the investors should accept the project because it has a positive NPV and an IRR greater than the cost of equity capital (11.0%), but the picture changes dramatically when we introduce uncertainty.

UNCERTAINTY AND RISK

Monte Carlo simulation requires the user to estimate a probability distribution to reflect the uncertainty for each random variable. A common technique in financial forecasting assumes a triangular distribution using the WC, MLC, and BC estimates. The triangular distribution assigns a near-zero probability to the WC and BC outcomes and the highest probability to the MLC outcome.

[FIGURE 1 OMITTED]

Table 4 shows the assumptions for WC, MLC, and BC realizations of all random variables. Figure 1 illustrates the probability distribution for the number of scans in year one. The shape of the distribution for other random variables is similar; i.e., for now, the distributions are symmetric for the MLC scenario, which means that the MLC and expected value for each assumption are the same.

[FIGURE 2 OMITTED]

Crystal Ball draws 100,000 random samples for each variable and uses the pro forma financial statements in Table 3 to calculate the project's NPV for each draw. The primary output of Crystal Ball is the distribution function of NPVs with the probability of a positive NPV project at 72.3%, which is highlighted in blue in Figure 2 (see the "certainty" box at the bottom of the figure). Note that the probability of success is not 100%--it is, after all, a risky project. Because the MLC and expected value are the same for each assumption, it is also true that the mean of the NPV distribution ($87,626) will be very close to the NPV calculated earlier using all of the MLC values (see Table 1). The difference is because of the randomness of simulation technology.

Crystal Ball also produces a contribution-to-variance chart as shown in Figure 3. This chart quantifies how much each assumption affects the project NPV. For example, the number of scans in year one is the primary driver of the project NPV, accounting for 72% of its variation. The growth in the number of scans (20.1%), the growth in reimbursement rates (6%), and the product mix (1.5%) also contribute visibly to the variation in the project NPV. In contrast, residual value, increases in costs, and the collection period have a negligible effect on NPV. In the following analyses, therefore, we will drop these assumptions from the simulation and use the MLC as a point estimate for each one.

OPTIMISTIC ASSUMPTIONS

It is generally acknowledged that the originators or champions of projects may be optimistically biased in their assumptions about the financial and operational variables affecting cash flows, and this can have a significant effect on the estimated probability of the project's success. To demonstrate the effect of this bias, we will assume that only the increase in scans (the second-most-important assumption, accounting for 20% of the variation in NPV) is subject to this distortion and that the WC estimate should really be 5% per year instead of 15% per year (see Table 4). The new probability distribution for growth in scans appears in Figure 4 and helps us visualize uncertainty. Note that the expected value (mean) is now 16.7%, whereas the MLC remains at 20% per year. We would expect this to also shift the mean NPV downward, but we do not know by how much. Here Monte Carlo simulation again provides a probability distribution that helps us visualize risk.

Figure 5 shows the distribution function of NPVs. The only change from the previous example is the different assumption about the WC realization of growth in scans. Note that the mean NPV is now negative (-$8,097) and that the probability of a positive NPV project has dropped from 72.3% to 47.8%. Decision makers can now visualize the effect of optimistic bias.

We now examine the relative importance of each assumption about NPV with a revised contribution-to variance chart, as Figure 6 shows. The number of first-year scans is now less important to the project NPV, although it still accounts for 42% of the variation in NPV, and the growth in scans is now the most important assumption, contributing 53.9% to the variability of the project NPV.

[FIGURE 4 OMITTED]

[FIGURE 5 OMITTED]

Management now has the information to perhaps make some adjustments to the project's design and implementation. For example, the number of scans in year one is still very important to the project's success. Does that justify a significant pre-opening marketing campaign? The growth in scans is even more important. Does that suggest additional marketing expenditures during the life of the project? NPV analysis and Monte Carlo simulation can answer these questions.

[FIGURE 7 OMITTED]

CORRELATED VARIABLES

Now we examine the effects of correlated variables on our MRI scanner project. Suppose the number of scans in year one is near the high end of the range. Could that encourage the entry of a competitor? Would it suggest that subsequent growth in the number of scans is low? On the other hand, if the first-year scans are low and competitor entry is thus discouraged, then subsequent growth might be high. In such a scenario, the number of scans in year one and the subsequent growth in scans would be negatively correlated.

Alternatively, assume that the market is such that another MRI scanner is highly unlikely to be installed in the next five years. If the number of first-year scans is high, that might indicate robust demand for scans, so subsequent growth would also be high and vice versa. In this case, the first-year scans and subsequent growth would be positively correlated.

Figure 7 presents the distribution of NPVs for the case in which first-year scans and subsequent growth have a correlation coefficient of -0.70. The probability of success is slightly lower now (43% versus 47.8%), which suggests that negatively correlated variables do not change the project characteristics dramatically. A closer examination, however, reveals that the probability of extreme outcomes--a huge success (e.g., NPV > +$300,000) or a catastrophic failure (e.g., NPV < -$300,000)--has been reduced significantly compared to the distribution of NPVs in Figure 5 without correlated variables; i.e., the tails of the distribution are thinner.

The new contribution-to-variance chart is even more interesting (see Figure 8). It demonstrates that, with negatively correlated variables, the growth in reimbursement rates has become a much more important assumption, now accounting for 27% of the project NPV variance, compared to 3.3% in Figure 6. This suggests that management may want to scrutinize the assumptions about reimbursement rates more closely than was previously warranted.

The opposite happens if first-year scans and growth in scans are positively correlated. The probability of a positive NPV project increases slightly to 49.7% (figure not shown), but the importance of reimbursement rates becomes almost negligible. Understanding and including correlations among financial and operational variables is, therefore, an important part of capital budgeting analysis.

SOME IMPLICATIONS

We can draw the following implications from our analysis:

* Financial modeling using Excel and Crystal Ball is a useful tool for visualizing and quantifying the effects of uncertainty and risk on capital budgeting decisions.

* A triangular distribution for cash flow relevant variables dovetails nicely with the way managers typically view uncertainty--i.e., estimates of WC, MLC, and BC outcomes for financial and operating variables.

* The contribution-to-variance charts embedded in Crystal Ball help focus management's attention on the variables that are important to the decision, including actions to mitigate the effects of particular uncertainties (e.g., evaluating the desirability of marketing campaigns in our example).

* Monte Carlo simulation allows decision makers to quantify and visualize the effects of optimistic bias quickly and effectively. The same is true for correlated financial and operational variables.

Again, Monte Carlo simulation helps management accountants make better decisions because it examines the effects of all possible combinations of variables and lets managers better understand and visualize risk and uncertainty. Then they can estimate the probability of a project's success.

By Virginia Clark, CPA; Margaret Reed, Ph.D., CPA; and Jens Stephan, Ph.D.

Virginia Clark, CPA, is an adjunct associate professor at the University of Cincinnati, in Cincinnati, Ohio. You can reach her at virginia.clark@uc.edu.

Margaret Reed, Ph.D., CPA, is a field service associate professor at the University of Cincinnati. You can reach her at margaret.reed@uc.edu.

Jens Stephan, Ph.D., is a professor at Eastern Michigan University in Ypsilanti, Mich. You can reach him at jstepha3@emich.edu.

Finance theory states that expected (mean) cash flows should be discounted at the opportunity cost of capital using a decision rule to accept or reject all positive or negative NPV projects. A central issue for managers, however, is how to deal with uncertainty--i.e., the fact that expected cash flows are only a point estimate of a large number of possible realizations. Traditional finance textbooks suggest two tools for this--sensitivity analysis and scenario analysis. Sensitivity analysis tweaks one variable at a time and evaluates the effect on the project's net present value, and scenario analysis examines a limited number of combinations of variables: worst-case (WC), most-likely-case (MLC), and best-case (BC) estimates of financial variables that determine future cash flows (e.g., sales, costs, growth rates, investment in working capital, etc.). The output is three project NPVs where all variables simultaneously take on one of the three hypothetical realizations. Neither tool produces probabilities of success or failure for the project.

Monte Carlo simulation, however, overcomes the limitations of sensitivity and scenario analyses by examining the effects of all possible combinations of variables and their realizations. Although the inputs are no different from scenario analysis, Monte Carlo simulation treats the estimates as a triangular distribution with the probability of WC and BC realizations being close to zero and increasing linearly up to the MLC. The simulation package then draws randomly from this distribution (100,000 times in our examples) for all variables that are specified in the DCF model and calculates an NPV for each draw. The result is a bell-shaped distribution function of NPVs (i.e., the area under the curve is equal to one) that provides estimates of the probability of success and failure. Sensitivity analysis applied to the Monte Carlo results allows us to address issues such as:

1. Which variables are the most important--i.e., have a large impact on the NPV calculation and/or have a high level of uncertainty? This helps focus management attention on relevant issues and actions that might reduce uncertainty.

2. Suppose the project-evaluation team sees that there is an optimistic bias in the forecast of unit sales. It is a simple matter to change the WC and/or the BC estimate and determine the resulting decrease in the probability of a successful project.

3. Suppose management believes that prices and volume are negatively correlated--i.e., on average, higher prices result in lower sales volume. Alternatively, many costs move up or down together, implying a positive correlation. Simulation software permits the financial modeler to specify such correlations and quantify their effects on the probability of success or failure.

An interesting byproduct of simulation is that it can clearly highlight implausible assumptions. Consider a project submitted to the capital expenditure committee with a 99% probability of success and an internal rate of return (IRR) greater than the cost of capital. On the surface, it appears to be the perfect project--high returns with little chance of failure. Projects with high returns, however, by definition must be risky projects because, as we know, there is no free lunch. Therefore, the probability of a positive NPV project cannot be close to 100%--even projects that earn their cost of capital are risky. Monte Carlo simulation can be a useful tool for detecting the inherent optimistic bias of project originators.

The two main commercial simulation software packages are Crystal Ball and @Risk. We will use Crystal Ball to analyze a capital expenditure project involving the purchase, installation, and commercial use of an MRI scanner a group of physicians affiliated with a large state university purchased. One of us has used Crystal Ball in MBA classroom and executive education programs, so we know it has sufficient uncertainty about future outcomes of volume, product mix, prices, and costs to provide sophisticated users of financial information with a rich forum for discussion and analysis.

THE MRI SCANNER PROJECT

CRSA, a physicians group affiliated with a university medical center, is considering the purchase of a new state-of-the-art MRI scanner. Dr. Margaret Reed, CEO of CRSA, believes a market for this service exists (more than 2,500 scans per year at a separate location) and that the group can make a significant amount of money on the investment, which would provide much-needed funding to meet the group's research and teaching missions.

General Electric Medical Systems offers the first Open Bore MRI scanner with a total imaging matrix, 16 channels, twin-speed, dual-gradient, and high-definition format. The base cost is $1.4 million, but the customer must also purchase a number of accessories, such as injectors, workstation, and computers.

The clinical operations of CRSA departments (cardiology, neurology, orthopedics, etc.) are distinct legal and economic entities with some centralized management functions (legal, accounting, planning, etc.). Instead of mandating the project, Dr. Reed has decided to finance the venture by selling shares of stock to individual departments. In this new era for the group, CRSA analyzes capital investment projects using DCF techniques with uncertainty explicitly incorporated into the analysis. All department chairs have completed executive education programs in business and are sophisticated users of financial information. Therefore, investors will expect a fully integrated set of pro forma financial statements (balance sheet, income statement, and cash flow statement) that calculate free cash flows to investors. Of particular importance to Dr. Reed is the transparency of the assumptions about the future and resulting NPV/IRR.

VOCABULARY

Readers will need to understand two terms from the healthcare industry.

CPT[R] Codes: A registered trademark of the American Medical Association, CPT stands for current procedural terminology. The codes provide a comprehensive description of medical, surgical, and diagnostic services and are designed to communicate uniform information about medical procedures among physicians, coders, patients, accreditation organizations, and payers for administrative, financial, and analytical purposes (adapted from Wikipedia).

RVU: RVU (relative value unit) represents the level of effort, expertise, and resources required for medical procedures. Medicare and HMO reimbursement rates are often a constant dollar amount times the RVU for a procedure, so higher numbers represent more-difficult and, therefore, expensive procedures.

FORECASTING ASSUMPTIONS

Any capital expenditure project requires forecasts of key financial and operating variables that determine cash flows in order to perform an NPV analysis. In this analysis, the key variables are product mix, reimbursement rates, volume (number of scans), collection period, and operating costs (see Table 1).

(1) Product Mix: The actual MRI scanner project used historical data from another installation on the type of scan (CPT code), its RVU, and the number of scans to predict the weighted average (WA) RVU of 19.13 per scan (see Table 2). Our examination of the data showed a logical grouping to summarize product mix into low RVU (48% of total) and high RVU (52% of total) scans. Therefore, the key product-mix variable that must be predicted is the fraction of low RVU scans (the fraction of high RVU scans will be its complement).

(2) Reimbursement: An analysis of recent reimbursement rates per RVU revealed an average of $34.50 across different providers. Multiplied by the WA RVU, this figure is the expected reimbursement per scan for year one. The key variable is whether the change in reimbursement rate will go up or down in the future, and this will determine the projected revenue per scan over the five-year life of the project.

(3) Volume: The average cycle time for an MRI scan is about one hour, so, based on a 12-hour day, the maximum capacity of the facility is about 12 scans per day. The key variables we need to predict are the number of scans in the first year and the subsequent increase. These estimates, in combination with the number of facility days per year (five days per week times 48 weeks per year), is the basis for predicting the annual number of scans.

(4) Capital Expenditures: Base cost for the GE Medical Systems scanner is $1.4 million, but that figure does not include a number of accessories, such as injectors, workstation, and computers, so the total investment is $1.468 million. These numbers are known with certainty because of the GE bid on the proposal. The estimated residual value is $0.2 million.

Working Capital: Working capital requirements in our model include accounts receivable, supplies inventory, and minimum operating cash balance. WC, MLC, and BC estimates are required for the simulation.

(5) Financing: Total capitalization for the project was conservatively estimated at $2.2 million. The cost of equity capital (COEC) for the departments is 11%, and the income tax rate is 40%. We calculate NPV and IRR for an all-equity-financed project and avoid weighted average cost of capital (WACC) and capital structure issues.

(6) Operating Costs: These include direct supplies (primarily film), personnel, and other operating costs. In each case we assume that estimates for the first year are fairly accurate, but the annual increase in costs is the variable to forecast.

Variable Costs: Film for the scanner is estimated to be $65 per scan. The first-year cost is known with considerable accuracy, but we must also forecast increases for the simulation.

Personnel Costs: Two MRI technicians need to be on duty at all times. Running 1.5 shifts per day (12 hours) requires three FTEs. In addition, one registration clerk needs to be on duty, requiring 1.5 FTEs. Estimates for year one are based on current salary levels for MRI technicians and clerks. Increases in personnel costs are subject to uncertainty.

Other Operating Costs: Table 1 shows estimates for year one that GE Medical Systems provided; increases are subject to uncertainty.

(7) This table summarizes income statement items through the operating expenses.

NPV ANALYSIS

Table 3 presents the pro forma income statements, balance sheets, and cash flow statements. Income statement numbers are derived from the summary table in Table 1. We determined all numbers on the balance sheet (except retained earnings) by formula through the forecasting assumptions. Therefore, the plug number to complete the balance sheet is ending retained earnings each year. With this number, implied dividends are computed from the clean surplus relation:

Ret [Earn.sub.0] + [NI.sub.1] - [DIV.sub.1] = Ret [Earn.sub.1]

The dividends represent free cash flow (FCF) to investors, who, in this case, are CRSA departments that decided to purchase shares in the MRI scanner project. We use FCF to investors to determine the project's NPV and IRR. Note that the cash flow statement articulates with the balance sheet; i.e., net cash flow each period is equal to successive changes in cash balances. It is also interesting to note that cumulative cash flows do not turn positive until the project's fifth year.

The project NPV equals $87,134, and the IRR equals 12.5%. MLC estimates of all forecasted variables gave us these point estimates. One would conclude that the investors should accept the project because it has a positive NPV and an IRR greater than the cost of equity capital (11.0%), but the picture changes dramatically when we introduce uncertainty.

UNCERTAINTY AND RISK

Monte Carlo simulation requires the user to estimate a probability distribution to reflect the uncertainty for each random variable. A common technique in financial forecasting assumes a triangular distribution using the WC, MLC, and BC estimates. The triangular distribution assigns a near-zero probability to the WC and BC outcomes and the highest probability to the MLC outcome.

[FIGURE 1 OMITTED]

Table 4 shows the assumptions for WC, MLC, and BC realizations of all random variables. Figure 1 illustrates the probability distribution for the number of scans in year one. The shape of the distribution for other random variables is similar; i.e., for now, the distributions are symmetric for the MLC scenario, which means that the MLC and expected value for each assumption are the same.

[FIGURE 2 OMITTED]

Crystal Ball draws 100,000 random samples for each variable and uses the pro forma financial statements in Table 3 to calculate the project's NPV for each draw. The primary output of Crystal Ball is the distribution function of NPVs with the probability of a positive NPV project at 72.3%, which is highlighted in blue in Figure 2 (see the "certainty" box at the bottom of the figure). Note that the probability of success is not 100%--it is, after all, a risky project. Because the MLC and expected value are the same for each assumption, it is also true that the mean of the NPV distribution ($87,626) will be very close to the NPV calculated earlier using all of the MLC values (see Table 1). The difference is because of the randomness of simulation technology.

Crystal Ball also produces a contribution-to-variance chart as shown in Figure 3. This chart quantifies how much each assumption affects the project NPV. For example, the number of scans in year one is the primary driver of the project NPV, accounting for 72% of its variation. The growth in the number of scans (20.1%), the growth in reimbursement rates (6%), and the product mix (1.5%) also contribute visibly to the variation in the project NPV. In contrast, residual value, increases in costs, and the collection period have a negligible effect on NPV. In the following analyses, therefore, we will drop these assumptions from the simulation and use the MLC as a point estimate for each one.

OPTIMISTIC ASSUMPTIONS

It is generally acknowledged that the originators or champions of projects may be optimistically biased in their assumptions about the financial and operational variables affecting cash flows, and this can have a significant effect on the estimated probability of the project's success. To demonstrate the effect of this bias, we will assume that only the increase in scans (the second-most-important assumption, accounting for 20% of the variation in NPV) is subject to this distortion and that the WC estimate should really be 5% per year instead of 15% per year (see Table 4). The new probability distribution for growth in scans appears in Figure 4 and helps us visualize uncertainty. Note that the expected value (mean) is now 16.7%, whereas the MLC remains at 20% per year. We would expect this to also shift the mean NPV downward, but we do not know by how much. Here Monte Carlo simulation again provides a probability distribution that helps us visualize risk.

Figure 5 shows the distribution function of NPVs. The only change from the previous example is the different assumption about the WC realization of growth in scans. Note that the mean NPV is now negative (-$8,097) and that the probability of a positive NPV project has dropped from 72.3% to 47.8%. Decision makers can now visualize the effect of optimistic bias.

We now examine the relative importance of each assumption about NPV with a revised contribution-to variance chart, as Figure 6 shows. The number of first-year scans is now less important to the project NPV, although it still accounts for 42% of the variation in NPV, and the growth in scans is now the most important assumption, contributing 53.9% to the variability of the project NPV.

[FIGURE 4 OMITTED]

[FIGURE 5 OMITTED]

Management now has the information to perhaps make some adjustments to the project's design and implementation. For example, the number of scans in year one is still very important to the project's success. Does that justify a significant pre-opening marketing campaign? The growth in scans is even more important. Does that suggest additional marketing expenditures during the life of the project? NPV analysis and Monte Carlo simulation can answer these questions.

[FIGURE 7 OMITTED]

CORRELATED VARIABLES

Now we examine the effects of correlated variables on our MRI scanner project. Suppose the number of scans in year one is near the high end of the range. Could that encourage the entry of a competitor? Would it suggest that subsequent growth in the number of scans is low? On the other hand, if the first-year scans are low and competitor entry is thus discouraged, then subsequent growth might be high. In such a scenario, the number of scans in year one and the subsequent growth in scans would be negatively correlated.

Alternatively, assume that the market is such that another MRI scanner is highly unlikely to be installed in the next five years. If the number of first-year scans is high, that might indicate robust demand for scans, so subsequent growth would also be high and vice versa. In this case, the first-year scans and subsequent growth would be positively correlated.

Figure 7 presents the distribution of NPVs for the case in which first-year scans and subsequent growth have a correlation coefficient of -0.70. The probability of success is slightly lower now (43% versus 47.8%), which suggests that negatively correlated variables do not change the project characteristics dramatically. A closer examination, however, reveals that the probability of extreme outcomes--a huge success (e.g., NPV > +$300,000) or a catastrophic failure (e.g., NPV < -$300,000)--has been reduced significantly compared to the distribution of NPVs in Figure 5 without correlated variables; i.e., the tails of the distribution are thinner.

The new contribution-to-variance chart is even more interesting (see Figure 8). It demonstrates that, with negatively correlated variables, the growth in reimbursement rates has become a much more important assumption, now accounting for 27% of the project NPV variance, compared to 3.3% in Figure 6. This suggests that management may want to scrutinize the assumptions about reimbursement rates more closely than was previously warranted.

The opposite happens if first-year scans and growth in scans are positively correlated. The probability of a positive NPV project increases slightly to 49.7% (figure not shown), but the importance of reimbursement rates becomes almost negligible. Understanding and including correlations among financial and operational variables is, therefore, an important part of capital budgeting analysis.

SOME IMPLICATIONS

We can draw the following implications from our analysis:

* Financial modeling using Excel and Crystal Ball is a useful tool for visualizing and quantifying the effects of uncertainty and risk on capital budgeting decisions.

* A triangular distribution for cash flow relevant variables dovetails nicely with the way managers typically view uncertainty--i.e., estimates of WC, MLC, and BC outcomes for financial and operating variables.

* The contribution-to-variance charts embedded in Crystal Ball help focus management's attention on the variables that are important to the decision, including actions to mitigate the effects of particular uncertainties (e.g., evaluating the desirability of marketing campaigns in our example).

* Monte Carlo simulation allows decision makers to quantify and visualize the effects of optimistic bias quickly and effectively. The same is true for correlated financial and operational variables.

Again, Monte Carlo simulation helps management accountants make better decisions because it examines the effects of all possible combinations of variables and lets managers better understand and visualize risk and uncertainty. Then they can estimate the probability of a project's success.

By Virginia Clark, CPA; Margaret Reed, Ph.D., CPA; and Jens Stephan, Ph.D.

Virginia Clark, CPA, is an adjunct associate professor at the University of Cincinnati, in Cincinnati, Ohio. You can reach her at virginia.clark@uc.edu.

Margaret Reed, Ph.D., CPA, is a field service associate professor at the University of Cincinnati. You can reach her at margaret.reed@uc.edu.

Jens Stephan, Ph.D., is a professor at Eastern Michigan University in Ypsilanti, Mich. You can reach him at jstepha3@emich.edu.

Table 1: Forecasting Assumptions NPV = $87,134 IRR = 12.5% (1) Product Mix # CPT Volume Average Codes # Scans Percent RVU WARVU CPT Codes: Group 1 19 285 48.0% 11.98 19.13 CPT Codes: Group 2 11 309 52.0% 25.73 WA = weighted average (2) Reimbursement per RVU $34.50 Year 1 2 3 4 5 WA reimbursement $659.99 $646.79 $633.85 $621.17 $608.75 per scan (3) Vol ume Year 1 2 3 4 5 # Facility days 240 240 240 240 240 per year # Scans per day 4.3 5.2 6.2 7.4 8.9 # Scans per year 1,032 1,238 1,486 1,783 2,140 (4) Capital Expenditures MRI Scanner $1,400,000 Injector $35,000 Workstation $17,000 Computers $6,000 Furniture & Fixtures $10,000 Total $1,468,000 Estimated residual value $200,000 Depreciation expense $253,600 Working Capital # Days' receivables 75 Supplies inventory (# days) 15 Min cash: sales 15.0% (5) Financing Equity $2,200,000 Cost of equity capital 11.0% Income tax rate 40.0% (6) Operating Costs Direct supplies (variable with # scans) $65.00 Personnel Costs Annual Annual Cost Salary # FTEs MRI technician $90,000 $30,000 3.0 Registration clerks $37,500 $25,000 1.5 Total $127,500 Other Operating Costs (all fixed) Rent $44,000 Office supplies $3,900 Telephone $1,400 Utilities $1,000 Maintenance $43,000 Other $11,000 Physicist service contract $2,800 Licensure of equipment $2,500 Insurance $19,000 Total Other Operating Costs $128,600 (7) Summary Year 1 2 3 Variable costs $681,105 $800,720 $941,900 Personnel costs $67,080 $84,494 $101,420 Other operating costs $127,500 $133,875 $140,569 Revenues $128,600 $135,030 $141,782 Year 4 5 Variable costs $1,107,551 $1,302,723 Personnel costs $121,690 $146,055 Other operating costs $147,597 $154,977 Revenues $148,871 $156,314 Table 2: CPT Codes Product Mix Low RVU Group CPT Technical CODE Volume RVU RVU x Vol. 73218 3 11 .49 34.47 73221 28 11.49 321.72 73718 7 11 .49 80.43 73721 56 11.49 643.44 71550 1 11 .55 11.55 72195 5 11 .55 57.75 74181 3 11.55 34.65 70544 33 11.70 386.10 70547 3 11 .70 35.10 70548 1 11 .70 11.70 70551 39 11.70 456.30 72141 23 11.70 269.10 73725 1 11 .70 11.70 74185 18 11.70 210.60 72146 4 12.97 51.88 72148 42 12.97 544.74 74182 1 13.85 13.85 70552 16 14.03 224.48 72149 1 14.03 14.03 Sum 285 3,413.59 High RVU Group CPT Technical CODE Volume RVU RVU x Vol. 70546 4 22.81 91.24 70549 16 22.81 364.96 70543 8 25.52 204.16 73720 1 25.52 25.52 73723 1 25.52 25.52 72197 8 25.59 204.72 74183 23 25.59 588.57 70553 192 25.99 4,990.08 72156 21 25.99 545.79 72157 12 25.99 311.88 72158 23 25.99 597.77 Sum 309 7,950.21 Group Product Mix WA RVU Low RVU 48% 11.98 High RVU 52% 25.73 All 100% 19.13 Table 3: NPV Analysis Income Statement 1 Revenue $681,105 Operating Expenses Supplies $67,080 Personnel $127,500 Operating $128,600 Depreciation $253,600 Pretax Income $104,325 Income Taxes $41,730 Net Income $62,595 Balance Sheet 0 1 Assets Cash $589,290 $102,166 Accounts Receivable $139,953 $139,953 Inventories $2,757 $2,757 Equipment (net) $1,468,000 $1,214,400 Total Assets $2,200,000 $1,459,275 Contributed Capital $2,200,000 $2,200,000 Retained Earnings $0 40,725) Total Stockholders' Equity $2,200,000 $1,459,275 Cash Flow Statement 1 CF from Operating Activities Net Income $62,595 Depreciation $253,600 [DELTA] Accounts Receivable $0 [DELTA] Inventories $0 CF from Operating Activities $316,195 CF from Investing Activities $0 CF from Financing Activities Dividends ($803,319) Net Cash Flow ($487,125) Year 0 1 Cash Flows to Investors ($2,200,000) $803,319 Cumulative Cash Flows to ($2,200,000) ($1,396,681) Investors Income Statement 2 3 Revenue $800,720 $941,900 Operating Expenses Supplies $84,494 $101,420 Personnel $133,875 $140,569 Operating $135,030 $141,782 Depreciation $253,600 $253,600 Pretax Income $193,722 $304,531 Income Taxes $77,489 $121,812 Net Income 6,233 $182,718 Balance Sheet 2 3 Assets Cash $120,108 $141,285 Accounts Receivable $164,532 $193,541 Inventories $3,307 $3,969 Equipment (net) $960,800 $707,200 Total Assets $1,248,747 $1,045,996 Contributed Capital $2,200,000 $2,200,000 Retained Earnings ($951,253 ($1,154,004) Total Stockholders' Equity $1,248,747 $1,045,996 Cash Flow Statement 2 3 CF from Operating Activities Net Income 6,233 $182,718 Depreciation $253,600 $253,600 [DELTA] Accounts Receivable ($24,579) ($29,010) [DELTA] Inventories ($550) ($662) CF from Operating Activities $344,704 $406,646 CF from Investing Activities $0 $0 CF from Financing Activities Dividends ($326,762) ($385,469) Net Cash Flow $17,942 $21,177 Year 2 3 Cash Flows to Investors $326,762 $385,469 Cumulative Cash Flows to ($1,069,919) ($684,450) Investors Income Statement 4 5 Revenue $1,107,551 $1,302,723 Operating Expenses Supplies $121,690 $146,055 Personnel $147,597 $154,977 Operating $148,871 $156,314 Depreciation $253,600 $253,600 Pretax Income $435,793 $591,777 Income Taxes $174,317 $236,711 Net Income $261,476 $355$166 Balance Sheet 4 5 Assets Cash $166,133 $0 Accounts Receivable $227,579 $0 Inventories $4,763 $0 Equipment (net) $453,600 $0 Total Assets $852,074 $0 Contributed Capital $2,200,000 $2,200,000 Retained Earnings ($1,347,926) ($2,200,000) Total Stockholders' Equity $852,074 $0 Cash Flow Statement 4 5 CF from Operating Activities Net Income $261,476 $355,066 Depreciation $253,600 $253,600 [DELTA] Accounts Receivable ($34,038) $227,579 [DELTA] Inventories ($793) $4,763 CF from Operating Activities $480,245 $841,008 CF from Investing Activities $0 $200,000 CF from Financing Activities Dividends ($455,397) ($1,207,141) Net Cash Flow $24,848 ($166,133) Year 4 5 Cash Flows to Investors $455,397 $1,207,141 Cumulative Cash Flows to ($229,052) $978,088 Investors NPV= $87,134 IRR= 12.5% Table 4: WC, MLC, and BC Assumptions WC MLC BC Product Mix (Group 1 %) 51.0% 48.0% 45.0% Growth in Reimbursement -4.0% -2.0% 0.0% # Scans per Day,Year 1 3.6 4.3 5.0 Growth in # Scans per Day 15.0% 20.0% 25.0% Estimated Residual Value $150,000 $200,000 $250,000 # Days' Receivables 80 75 70 Increase in Costs of Direct Supplies 7.0% 5.0% 3.0% Increase in Personnel Costs 7.0% 5.0% 3.0% Increase in Other Operating Costs 7.0% 5.0% 3.0% Figure 3: Contribution to VarianceVariance of Project NPV 100,000 Trials Contribution to Variance View Sensitivity: NPV= # Scans per Day Year 1 72.0% Growth in # Scans per Day 20.1% Growth in reimbursement 6.0% Product mix (Group 1 %) -1.5% Estimated residual value 0.2% Increase in other operating ... -0.1% Increase in personnel costs -0.1% # Days receivables 0.0% Increase in costs of direct 0.0% Note: Table made from bar graph. Figure 6: Revised Contribution to Variance of Project NPV 100,000 Trials Contribution to Variance View Sensitivity: NPV= Growth in # Scans per Day 53.9% # Scans per Day Year 1 42.0% Growth in reimbursement 3.3% Product mix (Group 1 %) -0.9% Note: Table made from bar graph. Figure 8: New Contribution to Variance of Project NPV 100,000 Trials Contribution to Variance View Sensitivity: NPV= * Growth in # Scans per Day 55.0% Growth in reimbursement 27.0% *# Scans per Day Year 1 11.2% Product mix (Group 1 %) -6.8% * - Correlated assumption (sensitivity data may be misleading) Note: Table made from bar graph.

Printer friendly Cite/link Email Feedback | |

Author: | Clark, Virginia; Reed, Margaret; Stephan, Jens |
---|---|

Publication: | Management Accounting Quarterly |

Geographic Code: | 1USA |

Date: | Sep 22, 2010 |

Words: | 4709 |

Previous Article: | Is a balanced scorecard useful in a competitive retail environment? |

Next Article: | The missing piece in the CPE pie. |

Topics: |