Financial Modeling Techniques

While uncertainty is inherent in planning any strategic undertaking, it is possible to apply analytical techniques that acknowledge the presence of uncertainty and help us to understand its potential economic impact on the overall business strategy. Four proven techniques that are commonly used for this purpose are:

1. Break-even analysis

2. Sensitivity analysis

3. Scenario analysis

4. Simulation analysis

We will now provide an overview of each method and talk about the strengths and weaknesses of each.

Break-Even Analysis

Break-even analysis occurs when the benefits of an SA equal the cost of its operation. Break-even analysis is important because it identifies the value needed to create a self-sustaining initiative. It can be calculated in various ways, examples of which include:

• Number of units

• Dollar volume processed

• Revenues generated

• Number of users

• Number of FTEs (full-time employees) redeployed

Run Like Crazy, Inc., a retailer of running shoes, is considering selling online. The average pair of running shoes sells for eighty dollars. The fully loaded cost (which includes all costs of sale such as labor, shelf space, etc.) of a pair of shoes (based on an ABM study) is forty-eight dollars per pair. The profit per pair of shoes is thirty-two dollars:

Cost of Manual Process	$80
Cost of Automated Process	−$48
Savings per Transaction	$32

Assume that the cost to operate the online sales system is $8 million. What is the break-even sales volume necessary to justify the system?

Annual Operating Costs	$8,000,000
Savings per Transaction	$32
Break-Even Transaction Volume	$250,000

Conducting this simple analysis will help us understand the inherent risk in this project. If you compare current sales volume levels with the calculated break-even point for this system, you can get an idea of the likelihood of reaching break-even transaction levels. If our company had a current sales volume that was less than $250,000, the firm would probably be taking a big risk in implementing the system. The beauty of break-even analysis is that it is simple to understand and relatively easy to perform. On the other hand, it does not provide a range of estimates and assumes that no value is being generated.

Sensitivity Analysis

The purpose of a sensitivity analysis (for the purposes of our work) is to understand how variances in such things as benefits, costs, and other components of value affect intrinsic value. More specifically, a sensitivity analysis will show the change in net present value of an SA, where key inputs vary over a range of values. The benefit of conducting this analysis is that it helps to reveal the variables that have greatest influence over the potential success of the SA.

Drawing upon our earlier example that demonstrated discounted cash flow analysis, we will apply a sensitivity analysis to Wide Awake's acquisition of Caffeine City. The sensitivity analysis can be used to show us what would happen to the NPV of the Strategic Alternative if there were a change in the estimated growth rate of the cash flows generated by the acquisition. Before, we assumed these cash flows would grow at 10 percent annually. However, there is a possibility that market demand for coffee will decline, perhaps due to the effects of a lagging economy on discretionary spending. In this case, it may be reasonable to assume cash flows from the acquisition would grow at a slower rate, perhaps at only 5 percent annually (Exhibit 11-5). Under this condition, our cash flows would be similar to those in Exhibit 11-1.

Exhibit 11-5: Projected net cash flows for Caffeine City acquisition, version B.

Now, if we apply the same discount rate—15 percent—to the change in projected cash flows, we arrive at the following NPV for the acquistion (Exhibit 11-6).

Exhibit 11-6: Projected net present value for Caffeine City acquistion.

Caffeine City Acquistion: Net Present Value @ 5% Annual Growth
Year	Revised Cash Flow[*]	Discount Factor @ (15%)[**]	Present Value of Cash Flow[*]
0	($50,000)	1.0000	($50,000)
1	$16,000	0.8696	$13,914
2	$16,800	0.7561	$12,703
3	$17,640	0.6575	$11,598
4	$18,522	0.5718	$10,591
5	$75,000	0.4972	$37,290
NPV			$36,096
[*]Values in thousands of dollars. [**]Discount rate factors can be found in corporate finance books when discount rate and number of time periods is known.

Exhibit 11-6 shows the net present value of the project, assuming cash flows grow at 5 percent annually, instead of at 10 percent annually. At 5 percent annual growth, the net present value of the SA is $36.1 million, whereas at 10 percent annual growth, the net present value of the project is $39.4 million. The sensitivity analysis shows us that a 5 percent reduction in the growth rate of cash flows generated from the acquisition will result in a decrease of $3.3 million in the estimated value of the project. If the sensitivity is performed for a range of growth rates between 5 percent and 10 percent, the outcome would be a sensitivity analysis, as depicted in Exhibit 11-7.

Exhibit 11-7: Caffeine City acquistion growth rate.

NPV	Growth Rate rate	5%	6%	7%	8%	9%	10%
2		$12,703	$12,823	$12,944	$13,065	$13,186	$13,307
3		$11,598	$11,820	$12,044	$12,271	$12,499	$12,729
4		$10,591	$10,896	$11,208	$11,525	$11,848	$12,177
NPV		$36,095	$36,744	$37,400	$38,064	$38,737	$39,417

The sensitivity shows the impact of a change in growth rate on the discounted cash flow for years two to four and the NPV overall. (Recall that the years one and five do not change. The discounted values in year one is $13,914, and year five is fixed at $37,290.) What the analysis shows is the incremental difference in the net present value based on a downward pressure in growth. The decision maker can get a quick understanding of the decline in value throughout the range. So if you feel that a 5 percent drop is too steep, you can see what the impact would be of a more reasonable decline, such as a 3 percent reduction in growth.

The power of sensitivity analysis is that it shows the progression of a result against different levels of selected variables. It is a positive exercise because it forces managers to understand key variables driving the success or failure of a project and identifies areas where additional information may be beneficial. This tool also has its limits. One of the drawbacks is that the results can often be considered somewhat ambiguous. When asking different forecasters to provide optimistic and pessimistic projections, it is important to understand that each forecaster may define optimistic and pessimistic differently based on subjective interpretations. Another weakness of sensitivity analysis is that it neglects the fact that variables are often interrelated. For example, an increase in market size is likely associated with higher marketing costs. Given these inherent weaknesses, sensitivity analysis should not be viewed as a complete solution for finding the estimated future value of a Strategic Alternative.

Scenario Analysis

Scenario analysis uses the same fundamental techniques as sensitivity analysis. However, the method attempts to address one of the primary limitations of sensitivity analysis by taking into consideration how certain variables may be interrelated. Instead of altering a single variable independently and observing the change in net present value, you can consider a scenario under which a number of related variables may change, resulting in a collective change in net present value. Using the Wide Awake acquisition of Caffeine City as an illustration, it is logical to assume that the same lagging economy that limits growth in market demand for coffee may also produce lower yields on other investments in the capital markets. This may cause discount rates to decline so the scenario we are analyzing takes into account the potential for slower growth rate in cash flows between 5 percent and 10 percent, and a discount rate of 10 percent instead of 15 percent. Exhibit 11-8 shows the NPV of the acquisition of Caffeine City under these scenarios.

Exhibit 11-8: Caffeine City acquisition scenario analysis.

Year	Growth Rate rate	5%	6%	7%	8%	9%	10%
Discount Rate
10%		$50,903	$51,654	$52,414	$53,183	$53,962	$54,751
11%		$47,658	$48,386	$49,124	$49,871	$50,627	$51,392
12%		$44,562	$45,270	$45,986	$46,711	$47,445	$48,187
13%		$41,608	$42,295	$42,991	$43,695	$44,407	$45,128
14%		$38,788	$39,455	$40,131	$40,814	$41,506	$42,207
15%		$36,095	$36,744	$37,400	$38,064	$38,737	$39,417

Our scenario analysis shows that if the economy weakened to the point where growth for coffee was slower than predicted, the estimated value of the acquisition could range from $39.4 million to $36.1 million, or a variance of $3.3 million. At the same time, if the SA team thought that this transaction was lower-risk, the value would increase to $54.8 million, based on a 10 percent growth rate.

Despite the fact that scenario analysis attempts to compensate for a key limitation of sensitivity analysis—lack of consideration for the interrelationships between variables—it would be unwise to consider it the end-all solution to estimating future value creation for a Strategic Alternative. The other weakness inherent in sensitivity analysis, the presence of subjectivity, still presents a barrier to more accurate predictions. Additionally, even though a relationship between variables may be obvious, it is impossible to know the exact degree to which each variable relates to the other. However, more sophisticated methods exist that attempt to make these predictions more scientific in nature using probabilistic and statistical methods.

Monte Carlo Simulation

While sensitivity analysis allows you to consider the effect of changing one variable at a time, and scenario analysis evaluates a change in multiple variables, Monte Carlo simulation is an analytical tool used when considering all possible combinations of variables (see Exhibit 11-9). In short, Monte Carlo simulation allows you to analyze multiple variables simultaneously as well as account for the range of possible values within each of those variables. These scenarios are tested through random sampling techniques. To do this, the analysis takes into account the probability associated with all possible outcomes of each variable. Ultimately, the simulation arrives at cash flow estimates that illustrate the full spectrum of possibilities, assuming that original estimations for variable ranges and probabilities are accurate. Using the Caffeine City example further, the steps in the Monte Carlo simulation process are as follows:

1. Model the project. The first step in the process is to create a model of the SA that illustrates the relationships between the project's variables mathematically. Taking market size as one example of a variable, it is important to understand that an offtarget estimation for one year requires an adjustment in the estimation for the following year. This can be linked to Caffeine City's cash flows.

2. Specify probabilities. The next step in the process is to specify the probability of error in forecasting each of the variables in the model. Using the example of market size again, an analyst may expect the total market size to be 1 million consumers while acknowledging that it may be as low as 500,000 or as high as 1.5 million, depending on macroeconomic factors, for instance. Assuming this range is established with absolute certainty, the remaining step is to assign probabilities to each distinct value within that range.

3. Simulate cash flows. The final step is to calculate the estimated cash flows based on the model and probabilities defined previously. In the real world, this would be done with software designed specifically for this purpose.

Exhibit 11-9: Monte Carlo analysis, Caffeine City acquisition.

A Monte Carlo analysis yields a range of values. If we look at an illustration that uses this analysis on the Caffeine City acquisition (Exhibit 11-9) we might find that there is a:

• 10 percent chance that the value will be between $36 million and $40 million

• 40 percent chance that the value will be between $41 million and $44 million

• 40 percent chance that the value will be between $45 million and $48 million

• 10 percent chance that the value will be between $49 million and $54 million

The strength of this analysis is that it provides detail around the variability of the valuation. Just getting one number using a traditional discounted cash flow does not provide you with a level of confidence regarding the answer. The weakness is that Monte Carlo is a highly sophisticated tool that, if misused, will distort rather than refine the view of the valuation.