Financial Management-Simulationa Analysis

Simulation Analysis

Introduction

Simulation is a statistics-based behavioral approach that applies predetermined probability distributions and random numbers to estimate risky outcomes. By tying the various cash flow components together in a mathematical model and repeating the process numerous times, the financial manager can develop a probability distribution of project returns.

The process of generating random numbers and using the probability distributions for cash inflows and cash out-flows enables the financial manager to determine values for each of these vari-ables. Substituting these values into the mathematical model results in an NPV. By repeating this process perhaps a thousand times, one can create a probability distribution of net present values.

The capital budgeting decisions that a financial manager makes require analyzing each project’s;

• Future cash flows
• Uncertainty of future cash flows
• Value of these future cash flows

Comparing available investment opportunities, the key is to maximize the value of the company’s wealth for owners.  In deciding on the best project we weigh its benefits and costs. The costs are;

• The cash flow necessary to make the investment i.e. Investment outlay and,
• The opportunity costs of using the cash flows.

Then, we incorporate the risks in the following ways;

• Discount future cash flows using a higher discount rate, the greater the cash flow’s risks OR,
• Compute a higher annual return on a project, the greater the cash flow’s risks.

Some of the risks associated with cash flow include the following;

§  Economic risks i.e. will consumers be spending or saving etc

§  Market conditions

§  Taxation

§  Interest rates changes

§  International conditions

Having appreciated the fact that all investments are prone to risks and that risk are degrees of uncertainty, and then one of the tools of testing projects viability is simulation analysis defined earlier.

It involves analysis of cash flows and returns on investments when more than one uncertain element is considered therefore allowing more than one probability outcome to be considered. It is more realistic than sensitivity analysis because it introduces uncertainty for many variables in the analysis.

Sensitivity analysis examines possible cash flows and investment returns when one uncertain element is altered. Sensitivity analysis is also known as scenario analysis.

Advantage of Simulation analysis;

• It examines more than one variable or risk elements at the same time.

Disadvantages of Simulation analysis;

• Is a complex approach since variable may be interpedently related to each other in a given year.
• It looks at a project in isolation and ignores diversification effects of a project instead focusing on its total risk.
• Ignores the effects of diversification for the owner’s personal portfolios and then the owner’s will focus on how their portfolios are affected than the project’s total risks.

Example;

The first step in risk analysis is to define the relevant variables and how each variable affects the outcome of the actual situation being analyzed and how the variables interact. The second step of the process is to determine the different outcome values for each scenario and for each set of simulation parameters and to conduct sensitivity analysis. The third step is to make decisions based on the results for step two.

To implement this paradigm, we use program functions within Excel perform financial modeling. We show how to analyze the risk profile of a capital budgeting project within Excel.

Table 1 contains the deterministic financial model used – a capital budgeting decision. Gross profit margin [GPM] is the difference between revenue [sales volume ´ sales price] and cost of goods sold [sales volume ´ unit variable cost]. Net income before taxes is GPM minus operating expenses and depreciation. Taxes are calculated with an assumed tax rate of 34%. Taxes owed are adjusted for any tax carry-forward of previous losses. Net income is net income before taxes minus taxes owed. Net cash flow is net income after taxes with the addition of depreciation, which is a non-cash expense. The final panel computes the net present value (NPV) and internal rate of return (IRR) for the project.

Table 1

NPV and IRR Simulation Input Variables
Assumptions	Probability Distribution	Mean/Mode	Standard Deviation	Low	High	Growth Rate
Sales Volume	Normal	100,000	2,000			6%
Variable cost per unit	Triangular	Sh 6		Sh 5	Sh 7	10%
Sales price per unit	Empirical
	Unit Price (SH)	Probability	Cumulative probability
	11	0.20	0.00			8%
	12	0.60	0.20
	13	0.20	0.80
Tax Rate	34%
Cost	Sh  2,000,000
Cost of Capital	12.50%
Gen & Admin	Sh 25,000

Example 2; Capital budgeting;

Widget Corporation is considering a new manufacturing project. This will be treated as a stand-alone, new venture analysis. The cost of building and equipping the manufacturing plant is

$2,000,000 and will be depreciated over the five year life of the project. WidCo uses straight line depreciation. Using Modified Accelerated Cost Recovery System (MACRS) would be straightforward with a spreadsheet. For example, see Moyer, McGuigan, and Kretlow (2001), pp. 332-335. WidCo believes that the riskiness of this project requires a 12.5% required rate of return.

Sales volume in the first year will be normally distributed with an expected value of 100,000 units and a standard deviation of 2000 units and demand will rise by 6% each year. The initial price of a unit will have three possible outcomes of $11, $12 or $13 and will rise by 8% each year. Variable cost per unit will follow a triangular begin at minimum value of $5, a most likely outcome of $6, and a maximum outcome of $7 and rise by 10% per year. The marginal tax rate is assumed to be 34%. To do the capital budgeting analysis, we first construct a table of cash flows for WidCo after which we compute the net present value and internal rate of return for WidCo. These probability distributions are chosen to show the variety of distributions available.

Table 2 provides a solution to the capital budgeting example for a deterministic scenario.

For the deterministic scenario, all of the input variables are assumed to be deterministic, that is all of the input variables are assumed to be known with certainty. The first three rows show the value of the three input variables: sales volume, sales price, and variable cost per unit. The level of sales volume begins at 100,000 units in year 2000 and grows by six percent each year to end at 126,248 units. The beginning sale price is $12 and grows by eight percent each year to end at $16.33 per unit. Variable cost per unit begins at $6 and grows at ten percent each year to end at $8.78 per unit. The IRR for this scenario is 15.84 percent and the NPV for this scenario is $178,546. Table 2 shows the results for the NPV and IRR analysis using a deterministic model. The NPV is $178,546 and the IRR is 15.84%.

Table 2

NPV and IRR Simulation Output Variables
NPV/IRR Computation
Year		1		2		3		4	5
Sales Volume		100000		106,000		112360		119102	126248
Sales Price/Unit		12.00		12.96		14.00		15.12	16.33
VC/Unit		6.00		6.60		7.26		7.99	8.78
Revenue			1200000		1373760		1572680	1800405	2061103
Variable Costs			600000		699600		815734	951145	1109036
Depreciation			400000		400000		40000	400000	400000
Gen & Admin			25000		25000		25000	25000	25000
EBT			175000		249160		331947	424259	527068
Depreciation			400000		400000		400000	400000	400000
Net Cash Flow	(2,000,000)		515500		564446		619085	680011	747865
PV			2178546
Cost			2,000,000
NPV			178546
IRR			15.84%