The Case Study

The following is a simple case study that I often use when I teach seminars on quantitative business analysis. It’s simple enough to convey a big-picture idea, yet challenging enough to teach important concepts related to structuring the thoughts and communications about complex business interactions, representing uncertainty, and managing risk. There is no doubt the business case could contain many more important details than are presented here. The idea, however, is not to develop an exhaustive template to use in every case; rather, the key idea is to demonstrate the use of the R language in just enough detail to build out your own unique cases as needed.

We will think through the case study in two sections. The first section, “Deterministic Base Case,” describes the basic problem for the business analysis with single value assumptions. These assumptions are used to set up the skeletal framework of the model. Once we set up and validate this deterministic framework, we then expand the analysis to include the consideration of uncertainties presented in the second section, “The Risk Layer,” that might expose our business concept to undesirable outcomes.

Abstract the Case Study with an Influence Diagram

When I ask people what the goal of business analysis should be, they usually respond with something like logical consistency or correspondence to the real world. Of course, I don’t disagree that those descriptors are required features of good business analysis; however, no one ever commissions business case analysis to satisfy intellectual curiosity alone. Few business decision makers ever marvel over the intricate nature of code and mathematical logic. They do, however, marvel at the ability to produce clarity while everyone else faces ambiguity and clouded thinking.

I assert that the goal of business case analysis is to produce and effectively communicate clear insights to support decision making in a business context. Clear communication about the context of the problem at hand and how insights are analytically derived is as important, if not more so, than logical consistency and correspondence. Although it is certainly true that clear communication is not possible without logical consistency and correspondence, logical consistency and correspondence are almost useless unless their implications are clearly communicated.

I think the reason many people forget this issue of clear communication is that, as analysts who love to do analysis, we tend to assume lazily that our analysis speaks for itself in the language we are accustomed to using among each other. We tend to forget that the output of our thinking is a product to be used by others at a layer that does not include the underlying technical mechanisms. Imagine being given an iPhone that requires the R&D laboratory to operate. An extremely small number of users would ever be able to employ such a device. Instead, Apple configures the final product to present a much simpler user interface to the target consumers than design engineers employ, yet they still have access to the power that the underlying complexity supports. Likewise, good business case analysis–that which supports the clear communication of insights to support decision making in a business context–should follow principles of good product design.

Before we write the first line of R code (or any code in any construct, even a spreadsheet) I recommend that we translate the context of the problem we have been asked to analyze to a type of flowchart that communicates the essence of the problem. This flowchart is called an influence diagram.

Influence diagrams are not procedural flowcharts. They do not tell someone the order of operations that would be implemented in code. They do, however, graphically reveal how information (both fixed and uncertain) and decisions coordinate through intermediate effects to affect the value of some target key figures of merit on which informed decisions are based. In most business case analyses, the target key figures of merit (or objective functions) tend to be a cash flow profile and its corresponding NPV.

The influence diagram shown in Figure 2-1 captures the essence of the deterministic base case. It reveals how starting assumptions work through interdependent calculations to the objective function, NPV. The structure of the influence diagram is built by connecting the assumptions to other nodes with solid arcs to indicate the flow of causality or conditionality. Intermediate nodes are then connected to other dependent nodes in sequence until some set of nodes converge on the objective or value node.

Notice that assumptions are represented both as light blue ellipses and purple trapezoids. The light blue ellipses represent assumptions that are not necessarily innately fixed in value. They can change in the real world due to effects beyond our control. For example, notice that the Phase 1 construction duration is represented as an ellipse . Although it is true that this outcome in the real world might be managed with project management efforts , at the beginning of the project, no one can declare that the outcome will conform to an exactly known value. Many events, such as weather, supply chain failures, mechanical failures, and so on, could work against the desired duration. Likewise, some events could influence a favorable duration. For this reason, Phase 1 construction duration and the other light blue ellipses will eventually be treated as uncertainties. For now, though, as we set up the problem, we treat their underlying values as tentatively fixed. The other assumptions displayed as trapezoids represent facts that are fixed by constraints of the situation, by definition (i.e., the tax rate or depreciation schedule of capital expense), or are known physical constants or unit conversion constants.

Intermediate values are displayed as double-lined yellow ellipses. These nodes represent some operation performed on assumptions and other intermediate values along the value chain to the objective function. The objective function (or value function), usually a red hexagon or diamond, is the ultimate target of all the preceding operations.

The dark blue parallelogram in the influence diagram labeled Year represents an index. In this case, it is an index for a time axis defined by the initial assumption of the life span duration of the analysis but that functions as a basis for all the other values. Because this node can affect many or all of the nodes, we don’t connect it to the other dependent nodes to reduce the visual complexity of the diagram. We need to explain this condition to the consumers of our analysis.

Historically, we observe the convention that canonical influence diagrams do not contain feedback loops or cyclic dependencies. As much as possible, the diagram should represent a snapshot of the flow of causality or conditionality. However, if the clarity of the abstraction of the problem is enhanced by showing a feedback loop in a time domain, we can use a dotted line from the intermediate node back to an appropriate predecessor node. Keep in mind, though, that in the actual construction of your code, some variable in the loop will have to be dependent on another variable with a lagged subscript of a form that conceptually looks like the function

where t is the temporal index, and n is the lag constant.

Again, the influence diagram is supposed to capture the essence of the problem in an abstract form that does not reveal every detail of the procedural line code you will employ. It simply gives the big picture. Constructed carefully, though, the diagram will provide you as the analyst an inventory of the classes of key variables required in your code. You should also make a habit of defining these variables in a table that includes their units and subject matter experts (SMEs) or other resources consulted that inform the definitions or values used.

The influence diagram displayed in Figure 2-1 represents the base case deterministic model; that is, the model that does not include the risk considerations. Once we develop the base case code structure and produce our preliminary analysis, we will modify the influence diagram to include those elements that capture our hypotheses about the sources of risk in the problem.

Set Up the File Structure

Again, we should think of good business case analysis as a product for some kind of customer. As much as possible, all aspects of the development of our analysis should seek to satisfy the customer’s needs in regard to the analysis. Usually, the customers we have in mind are the decision makers who will use our analysis to make commitments or allocate resources. Decision makers might not be the only consumers of our analysis, though. There are a number of reasons why other analysts might use our analysis as well, but if we are not immediately or no longer available to clarify ambiguous code, their job becomes that much more difficult. There is a social cost that accompanies ambiguity, and my personal ethics guide me to minimize those costs as much as possible. With that in mind, I recommend setting up a file structure for your analysis code that will provide guidance about the content of various files.

To avoid the problem of resetting the working directory of your projects, I also recommend that you build your file structure in the R application working directory. You can do this manually the way you would create any other file directory or you can use the dir.create() function of R. Consult the R Help function for the details of dir.create(), but to quickly and safely set up your project file directory, use the following commands in the R console:

Of course, you should create any other subdirectories that are needed to partition categories of files for good housekeeping .

Style Guide

Keeping with the theme of clear communication out of consideration for those who consume your code directly, I also recommend that you follow a style guide for syntax, file, variable, and function names within your R project. Google’s R Style Guide¹ provides an excellent basis for naming conventions. Following this guide provides one more set of clues to the nature of the code others might need to comprehend.

Of course, always provide clear comments within your code to describe routines for which operation might not be immediately obvious (and never assume they are immediately obvious) and to tie the flow of the code back to the influence diagram. The complete Google R Style Guide presents more complete details and examples as well as other recommendations for file structuring.

I learned the value of these guidelines the hard way several years ago while working on a very complex financial analysis for a new petroleum production field. The work was fast paced, and my client was making frequent change requests. In an effort to save time (and not possessing the maturity to regulate change orders effectively with a client), I quit following a convention for clear communication, namely adding thorough comments to my code. As I was certainly tempting the Fates, I got hit by the bus of gall bladder disease and exited the remainder of the project for surgery and extended recuperation. Although my files were turned over to a very capable colleague, he could make little sense of many important sections of my code. Consequently, our deliverables fell behind schedule, costing us both respect and some money. I really hated losing the respect, even more than the money.

Write the Deterministic Financial Model

We need to write our code in the order of events implied by our influence diagram. Studying the diagram, we can see that the nodes directly related to the annual capital expense (CAPEX) are involved in driving practically everything else. This makes sense because no production occurs until the end of the first phase of construction. No revenues or operational expenses occur, either, until production occurs. Therefore, the first full code block we address will be the CAPEX block . Just before we get to that, though, we need to set up a data file to hold the key assumptions that will be used in the model.

Data File

To begin the coding, we create a data file named, say, global_assumptions.R and place it in the ∼ /BizSimWithR/data subdirectory of our coding project. The contents of the data file look like this:

# Global Model Assumptions

kHorizon <- 20 # years, length of time the model covers.

year <- 1:kHorizon # an index for temporal calculations.

kSampsize <- 1000 # the number of iterations in the Monte Carlo simulation.

run <- 1:kSampsize # the iteration index.

kTaxRate <- 38 # %

kDiscountRate <- 12 # %/year, used for discounted cash flow calculations.

kDeprPer <- 7 # years, the depreciation schedule for the capital.

The values in this file are those that we will treat as constants, constraints, or assumptions that are used throughout the model. Notice that next to each assumption we comment on the units employed and add a brief description. Also, we choose variable identifiers that are descriptive and will permit easy interpretation by another user who might need to pick up the analysis after becoming familiar with the context of the problem.

Next, we import this file into our main R text file ( Determ_Model.R ) using the source() command.

source("∼/BizSimWithR/data/global_assumptions.R")

If you are familiar with the concept of the server-side include in HTML programming, you will easily understand the source() command. It essentially reads the contents as written in the file into the R session for processing.

For the variables that we will perform sensitivity analysis around or ultimately treat as uncertainties, we create a CSV data file named risk_assumptions.csv that contains the values of the operational variables used in the deterministic and risk model (Figure 2-2).

We create columns in this file not only to capture the names of the variables and their values, but their units and explanatory notes, as well.

We read the contents of the CSV file using the read.csv() command and assign it to a variable.

d.data <- read.csv("∼/BizSimWithR/data/risk_assumptions.csv")

The read.csv() function reads the data in the table structure of the file, then creates a data frame from it such that the header row names the columns, and the rows are created from the case values in the table. Anytime we need to refer to the contents of the assumption file while in the R console, we can do so quickly by calling d.data.

The data frame looks pretty much the same as the CSV file structure as it appears in Microsoft Excel; however, we will need to use the p50 values only for the initial deterministic analysis, so we slice out the p50 values from d.data and place them in their own vector. For the deterministic analysis, we will use only values of the first 13 variables. We will use the last five variables in the risk layer of the model.

# Slice the p50 values from data frame d.data.

d.vals <- d.data$p50[1:13]

The contents of the p50 vector look like this:

[1] 4.0e+07 2.0e+00 2.0e+07 2.0e+00 2.0e+06 3.0e+06 3.0e+00 3.5e+00 5.0e+00 2.0e+01 5.0e+00 5.0e+00 6.0e+00

The final step in the process of setting up the data for our deterministic analysis is to assign the p50 values in d.vals to variable names in the R file.

 # Assign p50 values to variables.

p1.capex <- d.vals[1]

p1.dur <- d.vals[2]

p2.capex <- d.vals[3]

p2.dur <- d.vals[4]

maint.capex <- d.vals[5]

fixed.prod.cost <- d.vals[6]

prod.cost.escal <- d.vals[7]

var.prod.cost <- d.vals[8]

var.cost.redux <- d.vals[9]

gsa.rate <- d.vals[10]

time.to.peak.sales <- d.vals[11]

mkt.demand <- d.vals[12]

price <- d.vals[13]

At this point, you might be wondering what the p10, p50, p90 values represent, as the deterministic base case description of the model only included single point values. Remember, though, that in the description of the influence diagram, I described the ellipses as representatives of uncertain variables . For the deterministic base case, we treat these values in a fixed manner. Ultimately, however, for the risk layer of the business case to be thoroughly considered, ranges for each of the variables will be assessed by SMEs as their 80th percentile prediction interval. The three characteristic points of those prediction intervals are the 10th, 50th, and 90th percentiles. We use the median values, the p50s, for the deterministic analysis. The p50s were the numbers used in the business case descriptions.

CAPEX Block

The first thing we need to establish in the actual calculation of the model is when the capital gets expended by phase. The phase calculation accomplishes this by telling R when the construction and operating phases occur. We can use 1 and 2 for the construction phases, and 3 for the maintenance phase.

# CAPEX Module

phase <- (year <= p1.dur) * 1 +

    (year > p1.dur & year <= (p1.dur + p2.dur)) * 2 +

    (year > (p1.dur + p2.dur)) * 3

The result is a vector across the year index.

[1] 1 1 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3

From our influence diagram , we know that capex is a vector conditionally related to phase, captured by the following expression.

capex <- (phase == 1) * p1.capex / p1.dur +

    (phase == 2) * p2.capex / p2.dur +

    (phase == 3) * maint.capex

It produces the following vector .

[1] 2e+07 2e+07 1e+07 1e+07 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06 2e+06

Next, we need to handle the depreciation that will be subtracted from the gross profit to find our taxable income. The depreciation is based on the capital emplaced at the time of construction or when it is incurred (i.e., maintenance); however, it is not taken into account until it can be applied against taxable profit. This means that the capital incurred in Phase 1 won’t be amortized until Phase 1 is over and the plant begins generating revenue. Capital incurred in the Phase 2 construction and maintenance phases can be amortized starting in the year following each year of expenditure.

One way to handle this operation would be to use a for loop . The following code performs this process as already described.

We start with

depr.matrix <- matrix(rep(year, kHorizon), nrow = kHorizon,

      ncol = kHorizon, byrow = TRUE)

to initialize the following matrix.

Then, we run a loop to set up a depreciation schedule programmatically (instead of typing each one manually):

for (y in year) {

    if (y <= p1.dur) {

      depr.matrix[y, ] <- 0

    } else if (y == (p1.dur+1)) {

      depr.matrix[y, ] <- (depr.matrix[y, ] >= (1 + p1.dur)) *

        (depr.matrix[y, ] < (y+ kDeprPer)) * p1.capex / kDeprPer

    } else {

      depr.matrix[y, ] <- (depr.matrix[y, ] >= y) *

        (depr.matrix[y, ] < (y + kDeprPer)) * capex[y - 1] / kDeprPer

    }

}

This loop incorporates the logic that, in general, for a straight-line depreciation schedule of n years, capital C incurred in year Y is spread over years Y + 1, Y + 2, …, Y + n - 1, as C/n in each year. So, if the project incurs $10 million of construction capital in Year 3, a seven-year depreciation schedule would spread that out evenly over seven years ($10 million/7 years) starting in Year 4 and running through Year 10.

More specifically to our earlier depreciation code snippet, R looks in the initialization table and replaces the numbers in rows for the Phase 1 duration years with 0s. Starting in the row representing the first year after the end of Phase 1 when sales are produced and a taxable profit can be made, our code replaces any value less than or equal to the Phase 1 duration with a 0. Then it looks for values in the row that are greater than the Phase 1 duration and less than or equal to the Phase 1 duration plus the depreciation period, and it replaces those values with the appropriate capital value divided by the depreciation period . Finally, the remainder of values in the row are set to 0. This process is repeated for each phase. The result of our code block for depreciation produces the matrix shown in Figure 2-3.

Now, if we sum the columns across the years of depr.matrix , we get the total annual depreciation.

# Sum the columns of the depreciation matrix to find the annual

# depreciation.

depr <- colSums(depr.matrix)

Because R is a vectorized language, though, many people frown on the use of for loops . Instead we might consider replacing the loop with a variant of the apply() function combined with the ifelse() functions . Here, we use the sapply() function . The sapply() function is a type of functional for loop that steps across the elements of an index, applying the value of the step at the appropriate place in the expression defined after the function() statement.

A general for loop does this:

for (i in index) {

    expr[..., i, ...]

}

The sapply() function does this instead:

sapply(index, function(i) expr[..., i, ...]).

It’s a great way to reduce the visual complexity of the expressions we write as well as accelerate the speed of the code. We use this function more in the sections to come. In the meantime, to learn more about sapply(), simply call the help function in the R console using ?sapply or help(sapply).

Now, we replace the depreciation code block that employs the for loop with this sapply() function and nested ifelse() functions to accommodate the conditional requirements in the loop. Note that we transpose the results of sapply() with t() because the sapply() function produces a matrix result that is transposed from the for loop approach.

# Depreciation Module

depr.matrix <-

  t(sapply(year, function(y)

    ifelse(

      y <= p1.dur & year > 0,

      0,

      ifelse(

        y == (p1.dur + 1) & year < y + kDeprPer & year >= y,

        p1.capex / kDeprPer,

        ifelse((year >= y) & (year < (y + kDeprPer)),

               capex[y - 1] / kDeprPer, 0)

      )

    )

  )

)

depr <- colSums(depr.matrix)

We can now see what the depreciation calculation looks like graphically (Figure 2-4) with the plot() function using

plot(year,

       depr / 1e6,

       xlab = "Year",

       ylab = "Depreciation [$000,000]",

       type = "b")

Note that we scaled the depr values by 1 million to make the plot a little more readable, using the "b" parameter to plot both lines and points.

Sales and Revenue Block

Sales begin in the year following the end of construction in Phase 1. Here we model the market adoption as a straight line over the duration from the start of Phase 2 to the time of peak sales.²

mkt.adoption <- pmin(cumsum(phase > 1) / time.to.peak.sales, 1).

(phase > 1) produces a vector of TRUEs starting from the position in the phase vector where Phase 2 starts, and FALSEs prior to that.

The cumsum(phase > 1) phrase coerces the logical states to integers (1 for TRUE, and 0 for FALSE), then cumulates the vector to produce a result that looks like this:

[1] 0 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18.

By dividing this vector by time.to.peak.sales , we get a vector that normalizes this cumulative sum to the duration of time required to reach peak sales.

[1] 0.0 0.0 0.2 0.4 0.6 0.8 1.0 1.2 1.4 1.6 1.8 2.0 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.6

The pmin() function performs a pairwise comparison between each number in this vector and the maximum adoption rate of 100%.

[1] 0.0 0.0 0.2 0.4 0.6 0.8 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0

Multiplying this adoption curve by the maximum market demand gives us the sales in each year.

sales <- mkt.adoption * mkt.demand * 1000 * 2000

Because the price of the product is given in “$/lb,” we convert the sales to pounds by multiplying it by the conversion factors 1,000 tons/kiloton and 2,000 lbs/ton.

By plotting revenue with

plot(year, sales / 1000,

       xlab = "Year",

       ylab = "Sales [000 lbs]",

       type = "b")

we observe the revenue profile (Figure 2-5) and confirm that it is behaving as we expect.

Finally, revenue is given by

revenue <- sales * price.

By plotting revenue with

plot(year,

       revenue / 1000,

       xlab = "Year",

       ylab = "Revenue [$000]",

       type = "b")

we also observe the revenue profile (Figure 2-6) and confirm that it is behaving properly.

Pro Forma Block

The pro forma calculations are rather straightforward. According to generally acceptable accounting principles (GAAP ) –more or less–we have

• Gross profit = revenue - GS&A

• OPEX = fixed cost + variable cost

• Operating profit before tax = gross profit - OPEX - depreciation

• Operating profit after tax = operating profit before tax - tax

• Cash flow = operating profit after tax + depreciation - CAPEX

Implemented as R code , we have the following:

gross.profit <- revenue - gsa

op.profit.before.tax <- gross.profit - opex - depr

tax <- op.profit.before.tax * kTaxRate/100

op.profit.after.tax <- op.profit.before.tax - tax

cash.flow <- op.profit.after.tax + depr - capex

cum.cash.flow <- cumsum(cash.flow)

We can see the cash flow (Figure 2-7) in thousands of dollars.

plot(year,

       cash.flow / 1000,

       xlab = "Year",

       ylab = "Cash Flow [$000]",

       type = "b")

We can also show the cumulative cash flow (Figure 2-8) in thousands of dollars.

plot(year,

       cum.cash.flow / 1000,

       xlab = "Year",

       ylab = "Cash Flow [$000]",

       type = "b")

Net Present Value

To obtain the NPV of the cash flow, we simply need to find the sum of the discounted cash flows over the year index. Following the convention that we count payments as occurring at the end of a time period, we first create a vector of discount factors to be applied to the cash flow.

discount.factors <- 1/(1 + kDiscountRate / 100) ^ year

which produces

[1] 0.8928571 0.7971939 0.7117802 0.6355181 0.5674269 0.5066311 0.4523492 0.4038832 0.3606100 0.3219732 0.2874761 0.2566751 0.2291742 0.2046198 0.1826963 0.1631217 0.1456443 0.1300396 0.1161068 0.1036668.

Figure 2-9 shows us the plot of the discount.factors.

plot(year,

       discount.factors,

       xlab = "Year",

       ylab = "Discount Factors",

       type = "b")

The discounted cash flow is then given by

discounted.cash.flow <- cash.flow * discount.factors

which produces

[1] -17857143 -15943878 -5748744 -3523004 3058044 4183361 5186660 5006359 4788540 3846642 3507746 3187060 2985618 2783313 2583740 2389570 2202730 2024555 1855908 1697283.

Figure 2-10 shows us the plot of the discounted.cash.flow .

plot(year,

       discounted.cash.flow,

       xlab = "Year",

       ylab = "Discounted Cash Flow [$000]",

       type = "b")

Finally, we sum the values in this vector

npv <- sum(discounted.cash.flow)

to obtain NPV = $8,214,363.

It is very important to remember at this point that the NPV reported here is based on the deterministic median assumptions. It does not include any of the effects from the underlying uncertainty in our assumptions or that competition might impose. This NPV is unburdened by risks.

The NPV calculation is so important to financial analysis and so repeatably used that we should make a function out of it and keep it in a functions file that we would import every time we start a new analysis, just like we did at the beginning of this analysis with the data file.

CalcNPV = function(series, time, dr, eotp = TRUE) {

# series = the cash flow series

# time = index over which series is allocated

# dr = discount rate

# eotp = end of time period calculation (default is TRUE)

# or beginning of time period calculation (FALSE)

this.npv <- sum(series / (1 + dr) ^ (time - (1- eotp)))

}

Using this function, we would write the following to R with our currently defined assumptions .

npv <- CalcNPV(cash.flow, year, kDiscountRate / 100)

Remember that this business case deals with a project spanning two decades. The likelihood that the company will use the same discount rate over the entire horizon of concern here is small, as the company might take on or release debt, face changing interest rates on debt, experience changing volatility for the price of similar equity, and so on. More realistically, then, we might consider using an evolving discount rate for projects with long horizons. That would require stochastic simulation, which is more of a topic for Chapter 3. If a question about the importance of the discount rate arises for whether a project is worth pursuing, we can always test our sensitivity to rejecting or accepting a project on the sensitivity of the NPV to the potential evolution of the discount rate .

As a bonus , we can also find the internal rate of return (IRR) of the cash flow. The IRR is the discount rate that gives an NPV of $0. However, there are no known methods of finding the IRR in a closed form manner, so we generally use a while routine to find that value.

CalcIRR = function (series, time, irr0 = 0.1, tolerance = 0.00001) {

#Calculates the discount rate that produces a 0 NPV

# of a series of end-of-year cash flows.

# series = a vector of cash flows

# time = index over which series is allocated

# irr0 = the initial guess

# tolerance = the error around 0 at which the goal seek stops

my.irr <- c(irr0)

my.npv <- CalcNPV(series, time, my.irr[1])

i <- 1

while ( abs(my.npv[i]) > tolerance ) {

    if (i ==1) {

      if ( my.npv[i] > 0 ) {

        (my.irr[i + 1] = my.irr[i] * 2)

      } else {

        (my.irr[i + 1] <- my.irr[i]/2)

        }

      } else {

  # uses Newton-Raphson method of convergence

        slope.my.npv <- (my.npv[i] - my.npv[i - 1]) /

        (my.irr[i] - my.irr[i - 1])

        my.irr[i + 1] <- my.irr[i] - my.npv[i] / slope.my.npv

      }

    my.npv[i + 1] <- CalcNPV(series, time, my.irr[i + 1])

    i <- i + 1

    }

  my.irr <- my.irr[i]

}

Note that the CalcIRR() function calls the CalcNPV(). Therefore, we need to make sure to include the IRR() function in our functions file somewhere after the CalcNPV() function.

Be aware that the IRR most likely won’t have a unique solution if the cash flow switches over the $0 line multiple times. Regardless of the several cautions that many people offer for using IRR, it is a good value to understand if the conditions for finding it are satisfactory. Namely, by comparison to the discount rate, it tells us the incremental rate by which value is created (i.e., IRR > 0) or destroyed (i.e., IRR < 0).

Using the CalcIRR function now in our model, we write

irr <- CalcIRR(cash.flow, year)

and get back 0.1416301.

The IRR at 14.2% is better than our discount rate, 2.2% more, which should not surprise us because we have a positive NPV.

Write a Pro Forma Table

To create a pro forma table , we’d like to have all of the elements from the GAAP flow described earlier with the names of the GAAP elements in a column on the left, and the values for each element in a column by year.

The first thing we do is create an array composed of the pro forma elements, remembering to place a negative sign in front of the cost values.

The 14 in the dim parameter refers to how many pro forma elements we put in the array.

We then assign that array to a data frame from the existing calculations.

This gives the table shown in Figure 2-11.

Notice that R has oriented our pro forma with the result values in column form, and it uses a sequentially numbered X.n for the pro forma elements. Obviously, this is a little unsightly and uninformative, so we might want to replace the default column headers with header names we like better. Furthermore, we might prefer to transpose the table so that pro forma elements are on the left as opposed to across the top.

To accomplish this we first define a vector with names we like for columns.

Next, we coerce the default headers to be reassigned to the preferred names.

Then, using the transpose function t(), we reassign the pro forma to a transposed form with the column headers now appearing as row headers.

A few columns of the finished form look like the table in Figure 2-12.

Conduct Deterministic Sensitivity Analysis

Deterministic sensitivity represents the degree of leverage a variable exerts on one of our objective functions, like the NPV . By moving each of our variables across a range of variation, we can observe the relative strength each variable exerts on the objective. However, there is an extreme danger of relying on this simplistic type of sensitivity analysis, as it can lead one to think the deterministic sensitivity is even close to the likely sensitivity. It most likely will not be if it uses ±x% type variation that is too common in business case analysis.

Understand that the primary reason for conducting the deterministic analysis at this point is mainly to confirm the validity of the logic of the model’s code, not necessarily to draw deep conclusions from it just yet. Our primary goal is to ensure that the the objective function operates directionally as anticipated.³ For example, if price goes up, and we don’t use conditional logic that makes revenue do anything other than go up, too, then we should see the NPV go up. If the construction duration lasts longer, we should see the NPV go down due to the time value of money. We really should not draw deep conclusions from our analysis until we consider all the deeper effects of uncertainty and risk, which we’ll discuss in Chapter 3.

The best way to conduct deterministic sensitivity analysis would be to use the low and high values associated with the range obtained from SME elicitations for each variable that will eventually be treated as an uncertainty. Although the uncertain sensitivity analysis is a little more involved than the deterministic type, it does build on the basis of the deterministic analysis. In Chapter 3, not only will we use the full range of potential variation supplied by SMEs for all the variables (including those that explore the effects of competition), but each variable will run through its full range of variation as we test each sensitivity point.

In pseudocode , this might look like this:

The way we implement this in real R code is to duplicate the original R file. Then we wrap the for loops around the original code. Before we do this, though, we set up parameters that control the looping after the variable values are assigned to the d.vals vector and before the individual assignment of value to each variable.

The for loop code blocks surround our original code that found the NPV.

The resultant npv.sens matrix looks like this.

To make this table more understandable, we can convert it to a data.frame with column names equal to the sensitivity points and the row names equal to the variable identifiers that are driving the NPV using the following coercions .

The result is a table for npv.sens that looks like this.

The way we interpret this table is that each value is the NPV for our business case when each variable is independently varied through their p10, p50, p90 values independently of the others. For example, if the price were to go to its low (or p10) value, the NPV would shift to -$25.3 million. On the other hand, if the price were to go to its high value, the NPV would shift to $75.2 million. Accordingly, the price, market demand, and variable production costs are the factors that could cause the NPV to go negative or financially undesirable. If we delude ourselves into thinking there will be no competition, the other variables don’t seem to cause enough change to worry about. We’ll leave the analysis to the risk layer of the model that accounts for uncertainties associated with the competitive effects.

The following code produces the sensitivity chart using the barplot() function .

The result of the code graphically reveals the information in our sensitivity table. The inherent pattern in the numbers, which remains difficult to discern in its tabular form, becomes immediately obvious in Figure 2-13. We typically refer to this kind of chart as a tornado chart .

Finally, the par(mai = c(1, 1.75, 0.5, 0.5)) statement, which we placed before the barplot function , sets the width of the graphics rendering window to accommodate the length of the names of the variables. You might want to adjust the second parameter of this term, here set to 1.75, to account for different variable names if you choose to expand the complexity of this model.