Sanders Manufacturing wants to analyze the potential profit for a new product. Profit depends on the selling price per unit, fixed advertising costs, direct labor cost per unit, materials cost per unit, and demand. The selling price will be $130 and advertising costs will be $800,000. Materials cost per unit is estimated to vary anywhere between $70 and $95 and demand is estimated to be normally distributed with a mean of 14,500 and standard deviation of 1000 (and should be a whole number). However, the company can only produce a maximum of 15,000 products with the current setup. Direct labor costs per unit can vary from $43 to $49 with the following probabilities: Labor cost Probability $43 0.1 $44 0.2 $45 0.4 $49 0.3 The clients would rather ignore the uncertainty of demand, materials and direct labor costs and only use the average values to determine profit. Your goal is to convince them of their flaw of using the average values for the uncertain inputs and solve the problem with a simulation model in Excel.

Description and Information: Describe the flaw of averages to the client and how simulation can overcome this flaw. Describe the Monte Carlo simulation process. Be sure to incorporate and describe risk and probability distributions in nontechnical language.

Modeling and Results: Develop a Monte Carlo simulation model in Excel and use a data table with 300 trials to simulate the profit. Explain the following results to the client. o Clearly define and summarize the results from your simulation for the minimum, maximum, average, and standard deviation of profit of the simulation trials. The client wants to focus on the average of the simulation trials. Explain to the client the importance of the histogram from the simulation trials in contrast to the average. Create a histogram over the trials (use 9 to 12 bins). Explain what a histogram is and interpret the results. Also give and explain the meaning of the 80th percentile. The client is also interested in the probability of making a profit of less than $200,000. Calculate this and give them an estimation based on the simulation trials. Run the simulation model with many more trials (1000 or more). What should happen to the standard deviation? Can you guarantee the shape of the histogram will be narrower with more trials?