The first step in building our simulation model was to confirm whether or not a normal distribution was in fact reasonable for the unknown demand of Egress ski jackets in the coming year. To do this we input Egress’ estimated demands into Best Fit. After analyzing the output (Exhibit 1 and 2) we see that the p-value for Chi-Squared is 0.5637, this indicates that the assumption of a normal distribution of demand is indeed reasonable.
Based on this output we determined that the mean of demand was 12,000 and the standard deviation was 3,497 (rounded up from 3496). Our next step was to build a simulation model based on the data provided to us by Egress using @RISK. Based upon our findings in Best Fit we used @RISK’s normal distribution function to generate random inputs of demand and the RiskSimtable func
...tion to run the simulation multiple times using different production quantities. For each simulation run, we did 1,000 iterations and used the @RISK output function to analyze profit. At Egress’ request, we ran our first simulation for a production quantity of 7,800 and production quantity at the mean demand of 12,000.
From the output (Exhibit 3 and 4) we see that expected profit at Q=7800 is $42,249 and the standard deviation is $52,026. At Q=12,000 the expected profit is $42,296 with a standard deviation of $143,181. While the mean demand quantity of 12,000 yields a slightly better-expected profit, it is a much riskier option. First, the standard deviation of Q=12,000 is more than three times larger than that of Q=7,800. Second, we can see in Exhibit 6 that there is a 16% chance that at Q=12,000 Egres
will lose $100,000 or more. In comparison when Q = 7,800 there is only a 3.3% chance that Egress will lose more than $100,000 (Exhibit 5). Finally, while at Q=12,000 the company’s maximum profit is considerable larger than it is at Q=7,800, the company stands to lose much more at Q=12,000 as is reflected in the minimum profit of $702,019. Based on the results from our first simulation, we were confident that the optimal Q for Egress was somewhere between 7,800 and 12,000. So for our next simulation, we decided to test production quantities of 8,000; 9,000; 10,000; 11,000; and 12,000.
The output (Exhibit 7) indicated that a production quantity of 10,000 yielded the highest mean profit at $56,748. In an effort to narrow our results down a bit more, we ran another simulation with production quantities of 9,800; 9,900; 10,000; 10,100; and 10,200. The output of this report (Exhibit 8) confirmed our findings in the previous simulation run, a production quantity of 10,000 will maximize their profit. In conclusion, as this is a small company that has only been in business for a few years, we believe that going with a production quantity equal to the mean demand of 12,000 is probably too risky. Egress would be better off going in a more conservative direction.
Based on our simulation model a production quantity of 10,000 will be optimal for Egress, maximizing their expected profit at $56,748. Additionally, with a production quantity of 10,000, there is only 8.2% (Exhibit 9) chance that Egress will lose more than $100,000, and a 78. 1% chance that Egress’ will make more than $50,000 (Exhibit 9), their break-even point.
