American Journal of Business Education – Third Quarter ...

American Journal of Business Education ? Third Quarter 2017

Volume 10, Number 3

NPV Sensitivity Analysis:

A Dynamic Excel Approach

George A. Mangiero, Iona College, USA Michael Kraten, Providence College, USA

ABSTRACT

Financial analysts generally create static formulas for the computation of NPV. When they do so, however, it is not readily apparent how sensitive the value of NPV is to changes in multiple interdependent and interrelated variables.

It is the aim of this paper to analyze this variability by employing a dynamic, visually graphic presentation using Excel. Our approach illustrates how these variables, when increased or decreased to reflect the potential range of values in a business case, change the value of NPV, and hence affect the decision about whether to proceed with the project or to reject it.

Furthermore, since sales revenue is one of the least certain elements in the business case, the presentation includes a probability estimate of whether NPV will be positive or negative, assuming that sales revenue is normally distributed with a known mean and standard deviation.

The business case we have chosen for illustrative purposes is a global energy project. Nevertheless, financial analysts in any industry should be able to apply our dynamic spreadsheet approach to their projects as well.

Keywords: Dynamic Forecasting; Financial Modeling; Net Present Value; Sensitivity Analysis; Pedagogical Application

I. INTRODUCTION

A

t the heart of Corporate Finance is the decision about whether to invest in capital assets, either to expand the corporation's capacity to produce its existing products or to replace obsolete or worn equipment. This decision, referred to as the "Capital Budgeting" decision, entails the use

quantitative techniques which, if valid and used properly, should result in "yes" or "no" decisions that increase the

value of the firm, and hence shareholder wealth, or prevent the value of the firm from decreasing.

In a typical Corporate Finance course, several such quantitative techniques are presented, discussed and compared. These include: Payback, Discounted Payback, Internal Rate of Return (IRR), Profitability Index (PI), and Net Present Value (NPV). This last method, NPV, is considered by most to be the premier method. "NPV is king!" is a quote by the author of a widely used textbook.

NPV, like IRR and PI, is a discounted cash flow method. If a project's NPV is computed and results in a positive value (NPV > $0), the project is then considered to be one that will, if pursued, result in an increase in shareholder wealth. If the computation of NPV results in a negative value (NPV < $0), the project should be rejected.

Several steps are involved in the computation of NPV, including a determination of all of the risky and "riskless" incremental cash flows that result from initiation to completion of the project, determining an appropriate rate to use in the discounting process, and then applying the NPV formula to determine its value. Essentially, the method compares the present value of the project's incremental benefits to the present value of its incremental costs. Many variables are involved in this process, including incremental revenues, incremental variable and fixed costs, incremental depreciation expense, the firm's tax rate, and the relative uncertainty of the project's cash flows, the market and book values of existing equipment to be replaced and new equipment to be purchased at the beginning

Copyright by author(s); CC-BY

113

The Clute Institute

American Journal of Business Education ? Third Quarter 2017

Volume 10, Number 3

and end of the project, to name several of the more important. The relative uncertainty of the project's cash flows can be used to determine a risk-adjusted discount rate or to calculate certainty equivalent cash flows.

Each of the factors that influence the NPV calculation represents an estimate. Some estimates can be established with a significant degree of precision, but other estimates can only be established within a relatively broad range. When the level of uncertainty is relatively high, the range of the estimate is comparatively broad, and NPV can only be quantified with a limited degree of certainty.

By simply creating a static formula for the computation of NPV, it is not readily apparent how sensitive the value of NPV is to changes in these variables. Thus, it is the aim of this paper to analyze this sensitivity by using a dynamic (as opposed to a static*), visually graphic presentation using Excel. The resulting presentation illustrates how the variables discussed above, when increased or decreased, change the value of NPV, and hence affect the decision about whether to proceed with the project or to reject it.

Furthermore, since sales revenue is one of the least certain elements in the calculation, the presentation includes a probability estimate of whether NPV will be positive or negative, assuming that sales revenue is normally distributed with a known mean and standard deviation.

* We have seen static approaches, used by accounting firms, which, though they are robust, are not as pedagogically illustrative as our dynamic approach. Our method emphasizes a vivid visual depiction of the impact of uncertainty produced by variations in projection variables on NPV.

II. NPV CALCULATION

A. The static calculation of NPV is as follows:

N CF0 + CF1/ (1+k) 1 + CF2/ (1+k) 2 +...+ CFN/ (1+k) N i = 0

Each of these cash flows, except for CF0, occurs in the future and, hence, subject to uncertainty in terms of both timing and value.

B. The benefits of a Dynamic Approach

? The analyst must be able to manipulate independent variables (singly or together) to quickly perform sensitivity assessments. In the real world, such variables are often interrelated and independent, thereby making a static approach insufficient.

? The analyst must be concerned about the projects potential for breakeven at the same time as its potential for surpassing minimum profitability requirements. Thus the analyst must utilize a spreadsheet tool that enables the simultaneous determination of several breakeven values. ( TR is used to illustrate this point in our case)

? This approach saves time in a pedagogical setting and makes the presentation more visually more interesting to students.

III. A VISUALLY DYNAMIC ANALYTICAL APPROACH FOR QUANTIFYING UNCERTAINTY

The purpose of this section is to show visually how several features in Excel can be used to dynamically demonstrate to students how various independent variables affect NPV, or to put it another way, how sensitive NPV is to the various variables in the NPV equation. This is by no means an exhaustive inventory of the features or variables available to the instructor for manipulation. This presentation is meant simply to show, with a few examples, what can be done and to suggest expansions in other directions.

Copyright by author(s); CC-BY

114

The Clute Institute

American Journal of Business Education ? Third Quarter 2017

Volume 10, Number 3

We have chosen four key variables to manipulate using "spinners" available in Excel: Total Revenue (TR), Variable Cost Ratio (VCR), Terminal Market Value (MV), and cost of capital (k). The initial investment, TFC, DEP, and the tax rate, T, are kept constant throughout (and thus have not been programmed with spinners) at $5,000,000, $6,000,000, $1,000,000, and 40% respectively.

Our baseline variable values (See Figure 1), for this presentation were arbitrarily selected to be:

TR = $6,500,000 VCR = 0.15 MV = $5,000,000 k = 5% For these values, NPV -$2,151,531.68 and IRR is -6.88%.

The spinner attached to TR, changes TR in increments of $100,000. Keeping VCR, MV, and k at their baseline levels, we now manipulate TR.

In Figure 2, using the spinner, TR is reduced $5,800,000. Consequently NPV declines to -$3,697,154.84 and IRR falls to -15.80%.

In Figure 3, using the spinner, TR is increased $7,200,000. NPV increases to -$605,908.51 and IRR rises to -1.71%.

Notice that the TRBE is $7,474,410.97. Increasing TR to a value above this amount produces a positive NPV.

Returning to the baseline values listed above, we now manipulate VCR. The spinner attached to VCR, changes VCR in increments of 0.01, or 1 percent.

In Figure 4, using the spinner, VCR is increased to 0.25. NPV declines to -$3,840,027.59 and IRR falls to -16.64%. Notice that TRBE is now $8,470,999.10, a value required to compensate for the higher VCR.

In Figure 5, using the spinner, VCR is increased even further to 0.35. NPV declines to -$5,528,523.49 and IRR falls to -26.90%. TRBE now rises to $9,774,229.73.

In Figure 6, using the spinner, VCR is decreased to 0.05. NPV increases to -$463,035.78, IRR rises to 2.49%, and TRBE falls to $6,687,630.87.

Returning again to the baseline values listed above, we now manipulate MV. The spinner attached to MV, changes MV in increments of $250,000.

In Figure 7, using the spinner, MV is increased to $10,000,000. As expected, NPV increases to $199,046.82 and IRR rises to 5.86%. TRBE now falls to $6,409,853.34, as expected, in comparison to the baseline TRBE.

Returning one last time to the baseline values listed above, we now manipulate k. The spinner attached to k, changes k in increments of 1 percent.

In Figure 8, using the spinner, k is decreased to 1.00%. In comparison to the baseline NPV, NPV increases to -$1,587,468.34 and IRR remains at -6.88%, since IRR is not affected by changes in k.

TRBE falls to $7,141,332.55 in comparison to the baseline TRBE, as a result of the lower k value.

Copyright by author(s); CC-BY

115

The Clute Institute

American Journal of Business Education ? Third Quarter 2017

Volume 10, Number 3

Note again that in these last five figures, TRBE adjusts accordingly to account for the change in VCR, MV, and k ? a valuable pedagogical point to make in a classroom setting. In other words, it is important to simultaneously consider a myriad of concepts, factors, and variables (i.e., the breakeven concept, revenue elasticity concept, and accounting estimates of various expenses) in a dynamic fashion by addressing all of these considerations in a single TRBE metric.

Statistical analysis can be added to the discussion by superimposing a distribution of one (any) of the variable on the plot. We chose TR and assumed a normal distribution.

Returning to the baseline values listed above, the distribution ON/OFF "switch" is set to the ON position and a normal distribution plot of TR appears on the display. Spinners attached to ?(TR) and (TR) are used to change the mean and standard deviation of the distribution. In Figures 9, 10, 11, an 12, the baseline values are kept as in figure 1 and four different combinations of ?( TR) and (TR) are illustrated. For example, in Figure 9, if TR were normally distributed with a mean ?(TR) = $7,500,000 and a standard deviation (TR) = $400,000, then the probability of a negative NPV would be 47.45%, as shown. This is exactly equivalent to the probability that TR will be less than TRBE = $7,474,410.97, which is the area under the curve to the left of the TRBE = $7,474,410.97. This same discussion applies to Figures 10-12.

IV. CASE APPLICATION: SAVE THE BLUE FROG

Our Excel application has been adopted for use in the online case Save The Blue Frog (see ). This learning activity is an integrated accounting case involving valuation, sustainability, controls and risk, and ethics.

Save The Blue Frog was developed as an integrated application of social presence theory, cognitive complexity theory, and gaming theory. Accordingly, it earned the Best Research Paper Award at the Strategic and Emerging Technologies Workshop of the 2014 Annual Meeting of the American Accounting Association in Atlanta, Georgia.

The case is designed to inculcate the principles of the scientific method and critical thinking by requiring the student to proceed through a series of four modules. The first module establishes a traditional baseline valuation for a global energy project, and each of the subsequent three modules forces a reconsideration of the baseline projections because of future uncertainties involving an environmental threat to an endangered species, a litigation risk involving illicit payments, and a concern involving professional ethics.

There are four features of the case that collectively require a visually dynamic approach to project analysis:

1. The primary variables that impact the earnings and cash flow financial projections also impact each other. Furthermore, the internal controls and other prospective solutions that are designed to address these variables likewise impact each other.

2. Some of the variables are quantitative in nature, whereas others are qualitative in nature. Some wield short term effects on earnings and cash flows, whereas others wield long term effects. Some are predictable, whereas others are unpredictable, and some are controllable, whereas others are uncontrollable.

3. The baseline valuation illustrates a project that is cash flow solvent throughout its twenty year forecast, and yet falls just shy of an organization's target valuation metrics. It is thus necessary to consider various modest-to-moderate modifications of the firm's operating plans and expectations in order to assess the probability that the valuation targets may yet be achieved.

4. The case is designed as a role playing activity that concludes with the development of a written report and corresponding presentation. The goal of these tasks is to persuade the senior partner of a global accounting firm to accept the student's recommendations.

These four features all emphasize the need for an analytical approach that can: (1) simultaneously assess the impact of different metrics in an extremely diverse collection of variables on the valuation of a project, and (2) present the

Copyright by author(s); CC-BY

116

The Clute Institute

American Journal of Business Education ? Third Quarter 2017

Volume 10, Number 3

outcomes in a visually persuasive manner that is suitable for written reports and presentations. We have designed our Excel application to serve these needs.

V. IMPLICATIONS

The applications of our spreadsheet based analytical tool extend far beyond the development of visually dynamic presentations of Net Present Value and other valuation metrics. The tool can also be utilized to assist managers in making critical decisions about a project's continuing operations.

Whereas Figures 1 through 8 illustrate how the tool's forecasting function can be utilized to approve or reject initial project investments, Figures 9 through 14 address the backcasting function for projects that are in the operations stage. As year after year of actual operating TR data are accumulated, the mean and standard deviation of the actual data will inevitably vary from the initial annual TR forecast.

Thus, the spreadsheet tool's P (NPV ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download