Question

Manager of a company is considering investing in a new $40,000 machine. Use of the new machine is expected to generate a cash flow of about $8,500 per year for each of the next five years. However, the cash flow is uncertain, and the manager estimates that the actual cash flow will be normally distributed with a mean of $8,500 and a standard deviation of $600. The discount rate is set at 4% and assumed to remain constant over the next five years. The company evaluates capital investments using net present value. How risky is the investment? Develop and run a simulation model to answer this question using 100 trials.

How to solve on excel and steps. (do not know how generate 100 trials, as well as plugging in this problem on excel).

Answer 1

For the purpose of this roblem one has to generate revenues for the next 5 years so as to ascertain whether the give investment is worth it or not.

For revenue generation one would have to obtain revenue values from normal distribution with mean 8500 and sigma 600. We will assume that the revenue is generated at the end of the year and the machine is purchased in the beginning of the year.

Using the discount rate of 4% p.a. we get that out of the 100 trials only three outcomes result in a profit while the rest of the 97 trials result in a negative value or loss.

Thus, it is sufficient to say that the investment is highly risky to take up wherein 97% of the trials result in a loss.

The first 10 trials and their values are as follows:

R1	R2	R3	R4	R5	D1	D2	D3	D4	D5	Profit
8885.57	8321.41	8364.91	8026.37	8265.86	8543.82	7693.61	7436.38	6860.97	6793.94	-2671.28
9212.37	8116.30	7266.45	8627.67	8486.50	8858.04	7503.97	6459.85	7374.97	6975.28	-2827.89
8028.21	8484.14	9009.84	8520.93	8580.84	7719.43	7844.07	8009.72	7283.73	7052.83	-2090.23
8039.27	9178.71	7891.29	7696.64	8484.32	7730.06	8486.23	7015.33	6579.12	6973.49	-3215.76
8879.73	8409.94	9574.71	8655.01	8876.19	8538.20	7775.46	8511.88	7398.34	7295.58	-480.54
8183.97	9259.21	8113.83	8162.87	7334.16	7869.20	8560.66	7213.16	6977.66	6028.15	-3351.17
9231.32	8640.19	8227.81	8055.88	8927.66	8876.27	7988.35	7314.50	6886.20	7337.89	-1596.80
8250.83	9092.10	8543.25	8277.17	8158.09	7933.49	8406.16	7594.91	7075.36	6705.36	-2284.72
9905.30	8692.88	8962.46	8988.62	8519.37	9524.33	8037.06	7967.60	7683.51	7002.30	214.79
9290.16	7520.83	9325.75	8700.38	8837.64	8932.84	6953.43	8290.56	7437.12	7263.89	-1122.16

Here, R's are the revenues generated for years 1 to 5. D's are the revenues discounted to the present year So we have:

D1 = R1/1.04 , D2 = R2/1.04^2 and so on

The profit calculated is the sum of all the discounted values minus the initial investment of $40000