Question

Develop an Excel worksheet simulation for the following problem. The management of Paragon Household Products is considering the introduction of a new product. The fixed cost to begin the production of the product is $25,388. The variable cost for the product is uniformly distributed between $15 and $20 per unit. The product will sell for $42 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1200 units and a standard deviation of 300 units. Develop a spreadsheet simulation that uses 500 simulation trials:A. What is the mean profit for the simulation?B. What is the probability that the project will result in a loss?C. What is your recommendation concerning the introduction of the product?

Answer 1

Disclamer:

As it ask to run simulations the values calculates will difer even if you follow the same step as I did.

Answer:

Mean Profit:  $ 4,295  

Probability of loss:  29.80%

As the product has a mean profit it will on average generate gains

but:  

as the standard deviation of the simulation was $ 7,778.40

we should not invest on the product as it is to variable

Explanation:

We are going to use the =RAND() function of excel

which, generates a random number between 0 and 1

This will be done 1,000 times 500 for the variable cost

and 500 for the demand.

Then we copy and paste this numbers to get them fixed.

Then, we convert them into actual cost and demand in units considering their distribution

using excel dist.norm.inv

Now, with this values we solve for profit on each one.

FOr the complexity I attached the excel file as the plataform interface cannot handle large tables.

Download xlsx