Barbara's Commercial Bakery (BCB) has been in business for quite a few years and has a sterling reputation for their sourdough bread. They sell the bread in lots of 12 to both wholesalers and retailers. Fearing they may run into some shortages of key ingredients due to supply chain issues, they wanted to be able to get a handle on their "expected monthly demand." This would allow them to get a better handle on ingredient purchases. BCB has always kept copious records so they were able to produce the last five years of demand. DEMAND FREQUENCY PROBABILITY 3/60 = 0.05 9/60 0.15 500 3 520 9 540 15 560 18 580 9 600 6 60 MONTHS 1.00 15/60-0.25 18/60-0.30 9/60-0,15 6/60-0.10 Based on these numbers, Barbara was able to manually calculate an "expected monthly demand" of dozen loaves. But Barbara, who only recently completed her undergraduate degree, knew that these probability values reflect only the "long term behavior. In the "short term" the occurrence of demand could be quite different from these probability vales. So she needed a concept that in the "short term" generated values that do not exhibit any certain pattern but in the "long term" conform exactly to the required probability distribution. She knew that these issue could be addressed with the use of RANDOM NUMBERS. Barbara did not have to buy expensive software as she remembered Excel could handle this. She started by establishing a "cumulative probability" that would be associated with a demand quantity and a random number range. When she got a random number in a certain range that would designate the demand for the month. She then remembered another Excel function that could tie this process together, LOOKUP FUNCTION. She dug out some class notes and was able to get to the textbook companion website and bring up file 10-1. She simply deleted what was in there and put her own numbers in. She now had her "simulated monthly demand." Having gone this far Barbara now realized that it would not be much of an additional step to use the simulated monthly demand to see what her profit would be. She remembered it being called "Expected Profit Simulation." To her five year demand history, a couple of other inputs were necessary to proceed. Based on competitive pricing and other market issues, Barbara estimated the average selling price of a dozen loaves to be between $40 and $60 as the cost of ingredients varies. Knowing this she estimates her profit margin to be between 20% and 30% per dozen loaves. Her fixed cost of stocking and selling the bread is $3000 per month. She now feels she has enough to "simulate" her average profit per month. 1. What is BCB's "expected monthly demand?" 2. What is the "simulated monthly demand" and what is the "random number" that generated it? (send the file) 3. On your single replication, what is your demand, selling price and profit margin? (send the file) 4. If you did 200 replications, what would be your average monthly profit? (would be on same file as number three)