Question

Katharine Rally is the vice president of operations for the ZUSH Company. She oversees

operations at a plant that manufactures components for hydraulic systems. Katharine is

concerned about the plant’s present production capability. She has reduced the decision

situation to three alternatives. The first alternative, which is fully automation, would

result in significant changes in present operations. The second alternative, which is semi-

automation, involves fewer changes in present operations. The third alternative is to make

no changes (do nothing).

As a manager of the plant management team, you have been assigned the task of

analyzing the alternatives and recommending a course of action.

a. Based on the past data, Katharine is further convinced that the capital investment,

annual revenue, useful lives, and salvage values can be considered random variables

with the following specified probability distributions. She also asks you to develop a

simulation of 50 sample points of AW values at a MARR 0f 20%/year. Interpret your

results and indicate which alternative should be selected.

Hint: Use the Random Number Generation (RNG) Data Analysis Tool package of

Microsoft Excel. The online help function explains how to initiate and use the RNG

to generate random numbers from a variety of probability distributions: normal,

uniform (continuous variable), binomial, Poisson, and discrete.

b. How do you trust the result of your simulation study? Increase the sample points to

100, 500, 1000, and 5000 and conclude that one the alternatives would be better than

the other one.

c. Statically show that one of the alternatives is more appropriate than the other one.

Hint: Apply hypothesis testing method to one of the sample point data, say the 100

sample point, data.

Alternative

--------------------------------------------------------------------------------------------

Parameter

A

B

--------------------------------------------------------------------------------------------

Capital

Normal

Normal

Investment

Mean: $300,000

Mean: $85,000

Std. dev.: $50,000

Std. dev.: $500

Annual

Normal

Normal

Revenue

Mean: $150,000

Mean: $85,000

Std. dev.: $10,000

Std. dev.: $500

Useful live

Discrete uniform

Discrete uniform

3 to 8 years with

3 to 7 years with

equal probability

equal probability

Salvage Value

Uniform

Uniform

30,000 to $60,000

$10,000 to $20000

Answer 1

We can simulate the distribution in Excel using excel functions and excel data table feature.

After simulation, the following values are obtained:

Average NPV of Alternative A (for any one iteration of 50 sample points) = 186,201

Note the above value is obtained in one random simulation of 50 points.

Average NPV of Alternative B = 170,633 (obtained from one random simulation of 50 points)

Comparing the two alternatives, the Average NPV is consistently greater for Alternative A. The calculations are attached below and assumptions are also given at the end. The excel formula used are also depicted in another attachment at the end.

Hence, the Answer is Alternative A which should be chosen. Given below are the simulation tables.

(Note: It is however also possible to calculate the confidence level for each average NPV)

The excel image is produced below:

Following assumptions and calculations are used in calculating the distribution:

1. The number of periods of useful live has been given as discrete uniform distribution.Since exel does not provide functions for discrete uniform distribution, random number between given values are generated using the formula:

Random Useful life = RAND() * (b-a) +a where a and b represent the range of values

2. The above assumption is also used for selecting the salvage value for each simulation

3. For investment, free cash flow (i.e. revenue), the normal distrubtion is assumed with the given standard deviation and mean. Following formula were used:

Random free cash flow = NORMINV (RAND(),Mean, Standard Deviation)

4. The above method is used for investment and free cashflow simulation

5. Excel data table function is used to simulate 50 sample points.

6. The average NPV of sample points were computed.

7. Simulation: While using the data table feature, in order to incorporate random value in the formula, the column set value chosen in the data table option can be any blank cell.

8. The Excel formulas are given below - to understand the formula, note that Marr of 20% at column 2 refers to cell B4 in excel. Using this as basis, you can understand rest of the formula. For example, cell C5 used in formula for investment is given in third column at C5 cell as 300,000. Rest of the cells in formulas can be understood accordingly.