Question

Katharine Rally is the vice president of operations for the XYZ 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. 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.

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.

Statically show that one of the alternatives is more appropriate than the other one using hypothesis testing?

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.