A farmer has an acre of specialty vegetables and is preparing for the summer harvest. Historically, this acre has yielded an average of 2,100 lbs of product with a standard deviation of 950 lbs.

A gourmet vendor has surveyed her customers, and 2,500 of them expressed great interest in buying a pound of this specialty product. The vendor is planning to purchase the full yield of the crop at a cost of $6 per pound (lb) and she will sell it to her customers for $8 per pound. While she thinks this will be a profitable endeavor, she knows this will be a large expense for her small business. She is looking for a way to minimize her risk while investing in this crop.

She plans to ask her customers to order in advance for this specialty product. She is trying to decide whether to require them to put down a deposit. She is considering 3 options: no deposit

(0%), 50% deposit, or 100% deposit.

While charging a deposit will offset her up-front expenses, she knows that asking customers to pay in advance for the product will drive down the demand. She expects that if she allows pre-orders with 0% deposit, 90% of customers who expressed an interest in the survey will place a pre-order. If she requires 50% then 70% of customers will express an interest and pre-order. If she requires 100% deposit then only 60% will place a pre-order. Track this variable as Number of Pre-Orders. It is also likely that not everyone will pick up their pre-order. Track this variable as Pre-Orders Picked Up. For customers who put down 0% deposit, she expects only 60% of customers to pick up their order. For 50% deposit she expects 85% to pick-up and for 100% deposits she expects 99% to pick-up their order. When those orders are picked up, she will collect the remaining balance for their order if there is enough product to fill their order.

Since the pre-orders must be taken well in advance, the vendor cannot be certain that crop will yield enough product. Track this variable as Actua,I Yield. If the crop yield is not enough to fulfill the Pre-Orders Picked Up, then a refund must be issued and the remaining balance will not be collected. If the Actual Yield is more than the Pre-Orders Picked Up, any leftover product will be sold at $4 per pound.

Given these uncertainties, build a Monte Carlo simulation that will allow the vendor to see the range of profits she might expect from each deposit level. Run 1,000 iterations of the model and compile descriptive statistics to show the average, standard deviation, and the min/max of profit for each deposit level.

Random Variable Considerations:

Use a random variable for the crop yield using the parameters provided.

Use a random variable for Number of Pre-Orders. This random variable should follow a binomial distribution: of the 2500 customers who are interested, how many will place an order. Use the probability of success dictated by the deposit level.

Use a random variable for Pre-Orders Picked Up. this random variable should follow a binomial distribution: of customers who place an order, how many of them will complete their order. Use the probability of success dictated by the deposit level.

SOLVE in EXCEL