Simulation

Instructions for Exam 3B Version B The SuperStar Tennis Ball Company is in negotiations with a Sporting Goods Distribution Retail Sales Company. They have introduced a brand new tennis ball that has excellent performance and is widely enjoyed by amateur and professional tennis players in the United States. The contract demand is estimated to be between 100,000 and 125,000 tennis balls. They have determined that in order to push this contract forward that they are expecting $250,000 in marketing and administrative costs. The dilemma that SuperStar faces is that they have numerous unknowns that they want to run through a simulation to get a profit evaluation. They have told you that they would like to have at least 1,000 simulations to perform on this study. Here are some of the parameters that they would like simulated and the type of random evaluation to take place. 1) They would like to evaluate a randomly simulated selling price (per unit) for each can of tennis balls sold. They have estimated the mean of their sales to be $60, with a standard deviation of 10% of the mean. Use these values to randomly dellver a normal distribution using mean/std deviation. 2) They deal with numerous suppliers that give them procurement costs. It is estimated that 25% of their procurement costs are $14,50% of their procurement costs are $15, and 25% of their costs are $16. Use these values to randomly deliver a normal distribution using probability. 3) To package, label, and conduct quality assurance inspections on the balls, they hire employees to provide labor. The labor costs (per unit) are estimated to be from $22 to $26, in $1 increments. However, 10% of the labor gets $22, 25% of the labor gets $23,30% of the labor gets $24,25% of the labor gets $25, and 10% of the labor gets $26. Use these values to randomly deliver a normal distribution using probability. 4) The postage is estimated to be between $2 and $4 per unit. Use the randbetween function to randomly generate postage costs. 5) The demand is uniformly random, using the demand values above. Key Requirements: 1) You need to develop a simulation model from the Excel spreadsheet given. 2) You need to run a minimum of 1,000 simulations and create a data table for simulated mean outputs. The simulation table requirements are Pre-labeled for you to simulate. 3) Generate a histogram using a minimum of 20 bins where the low and highs may be seen in the graph. 4) Generate profit statistics where the following are determined: Mean Profit Standard Deviation Minimum Profit Maximum Profit P(Profitco) IMPORTANTI Keep in mind.. I will be looking at your formulas...throughout this problem. I will be looking at your generated data table. AT LEAST 1,000 simulations. I will be looking at your histogram to see if it is "normal". 50 You cannot "hard code" answers...this simulation is randomly generating values. 51 I will be looking at least 4 comments on the page. 52 SuperStar Tennis Simulation Parameters \begin{tabular}{|r|r|} \hline Simulated Selling Price: & \\ \hline Simulated Procurement Cost: & \\ \hline Simulated Labor Cost: & \\ \hline Simulated Shipping/Postage Cost: & \\ \hline Administrative Cost & \\ \hline Simluated Demand & \\ \hline \end{tabular} Profit Summary Statistics Mean Standard Deviation \begin{tabular}{|r|r|} \hline \multicolumn{1}{|c|}{ Selling Price (Per Unit) (Normal Distribution) } \\ \hline Simulated Seling Price: & \\ \hline 3 & Mean: \\ \hline 4 \\ \hline Standard Deviation: & \\ \hline \end{tabular} Minimuan Profit Maximem Prefit P( Profit