An orchard has a acre of specialty fruit trees and is preparing for summer harvest. Historically, this acre has yielded and average of 1,800 lbs of fruit with a standard deviation of 800 lbs. A gourmet fruit vendor has surveyed their customers, and 2,000 of them expressed great interest in buying a pound of specialty fruit. The fruit vendor is planning on purchasing the full yield of the crop at a cost of $5.00 per pound (lb) and she will sell it to her customers for $7.00 per pound (lb). While she thinks this will be a profitable endeavor, she also knows it will be a large expense for her small business. She is looking for a way to minimize her risk while investing in the crop. She plans on asking her customers to order in advance for this specialty fruit. She is trying to determine whether she will require them to put down a deposit. She is considering 3 options- no deposit (0%), 50% deposit, or full deposit (100%). While charging a deposit will off-set her upfront expense, she knows that asking customers to pay upfront for the fruit will drive down demand. She expects that if she allows pre-orders with no deposit (0%), approximately 90% of the customers who expressed interest in the survey will place a pre-order. If she requires 50% deposit, she expects that only 75% of the customer that expressed interest in the survey will place a pre-order. If she requires the full amount 100% deposit, she expects that only 60% of the customers will place a pre-order. (Let us track this variable as pre-orders placed.). Of course the problem with pre-orders is that not everyone will come to collect their order when the fruit arrives. (Let us call this pre-order pick ups). For customers that put down 0% deposit, she expects that 60% will come in to complete the order. For customers who put down 50% deposit, she expects 90% of them will come in to complete their order. And for the ones who put a 100% deposit down, she expects 99% of those customers to come in to complete their order. When those orders are picked up, she will collect the remaining balance for their order if there is enough fruit to fill their order.
Since the pre-orders must be taken well in advance, the vendor cannot be certain of the crop (Let us track this variable as actual yield) will yield enough fruit to fill all orders. If the crop is not enough to fill the pre-order pick ups, then a refund of the deposit amount must be issued to the customers who came to pick up their orders but did not get their fruit. (Obviously the remaining balance will not be collected from these customers. If the actual yield is more than the pre-ordered pick ups, any left over fruit will be sold for $4.00 a pound (lb). Given all of these uncertainties, build a Monte Carlo simulation, that will allow the fruit 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, min and max of profit for each deposit level.
(1) Use a random variable for crop yield using the parameters provided
(2) Use a random variable for pre-orders placed. This random variable should follow a binomial distribution (of the 2,000 customers that were interested, how many will place an order) using the probability of success dictated by the deposit level.
(3) Use a random variable to pre-order picked up. This random variable should follow a binomial distribution (of the customers who placed an order, how many of them will come to complete their order) using the probability of success dictated by the deposit level.
Must be performed in Excel
Crop Yield per Deposit Amounts 0% 50% 100%
acre (lbs)
Average 1,800 P-place an order 90% 75% 60%
StDev 800 p-pick up a pre-order 60% 90% 99%
Cost per
pound $5.00 Pre order price per lb $7.00
Salvage price $4.00

Customers
Interested 2,000 (assume each customer is equal
to 1lb of demand)