Student Group Names

Group Assignment $2-$ Simulation

Case Problem: Nuts Galore Product Mix Simulation

Nuts Galore Inc. is analyzing each mix given some uncertain variables. They have researched the uncertainties in the marketplace and found that variations could occur in product costs of nuts due to supply chain issues with suppliers, labor costs due to labor disruptions, and demand could vary from customers too.

To simulate the behavior of uncertain variables, they draw $500$ random samples from a probability distribution for each of those variables. Product variable cost $lb$ of nuts are subject to a uniform distribution ranging between upper and lower limits as given: Regular $$1\mathrm{.}03245-$$$1\mathrm{.}4875$; Deluxe $$1\mathrm{.}07-$$$1\mathrm{.}6$; Holiday $$1\mathrm{.}1-$$$1\mathrm{.}46.$ The adoption of some modern robotics equipment has increased equipment costs by $$\frac{1}{l}b$ on each nut mix but reduced labor costs by $$\frac{3}{l}b$ on each of the three mixes. Labor variable cost $lb$ of nuts are now subject to an exponential distribution ranging between upper and lower limits as given: Regular $$0-$$$18\mathrm{.}00$; Deluxe $$0-$$$18\mathrm{.}50$; Holiday $$0-$$$19\mathrm{.}00.$ Demand changes based on a normal distribution with a given mean and standard deviation of each mix: Regular $10,000$ mean, st dev $4000$ ; Deluxe $3,000$ mean, $300$ st dev, Holiday $5,000$ mean, $2000$ st dev. Setup costs are provided for producing each type of mix: Regular $$18,000$; Deluxe $$16,500$; Holiday $$19,200.$ See Tables $1-3$ below showing these numbers.

The simulation makes it easy for management to experiment with the selling price and other variables if they choose. $1)$ Calculate profits, min, max, mean, st dev, also mean demand and mean profit and probability of loss on each mix. $2)$ Which mix seems to be the most profitable? $3)$ If the nut producer is able to avoid labor disruptions by eliminating the uncertainty of this variable, does this improve its profit projections? Explain. $4)$ If the nut producer is able to control its suppliers and avoid supply chain disruptions, thereby remove the variability of the product cost, does this improve its profits? Explain. $5)$ What other insights do you get from this simulation that you would highlight to the nut producer?

Prepare a PowerPoint presentation for Nuts Galore Inc.$\text{'}$s President to explain your findings using appropriate excerpts and analyses from the simulation.

$\backslash$table$[[\backslash$table$[[\backslash$table$[[$Regular$],[$Selling Price$]]]],$Product,Demand$],[$Variable Cost$/$lb$,],[\backslash$table$[[$$$,10\mathrm{.}90.$ Can you do it using RAND function on excel?