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.

	Crop Yield per acre (lbs)		Deposit Amounts	0%	50%	100%
Average	2,100		P-place an order	90%	70%	60%
StDev	950		p-pick up a pre-order	60%	85%	99%
Cost per pound	$ 6.00		Pre order price per lb	$ 8.00
			Salvage price	$ 4.00
Customers Interested	2,500	(assume each customer is equal to 1lb of demand)

What is the out of pocket expense with these settings?

Which of the following is the decision variable in this model?

Group of answer choices

· Yield of fruit

· % of customers placing pre-orders

· Amount of Deposit to require

· Salvage price

There are three uncertain (random) variables in this problem. Select the variables that should represent uncertainty in this model.

Group of answer choices

· Yield

· Initial Research and Development Cost

· Pre-Orders Picked up

· What type of fruit to grow

· Pre-Orders Placed

· Salvage Price

What is the output of your model?

Group of answer choices

· Costs

· Revenue

· Profit

· Pre-order Price

Adjust your model with the following:

Deposit Required = .5

Yield = 2100

Pre-Orders Placed = 2000

Pre-Orders Picked up = 1900

What is the profit under these conditions?

Adjust your model with the following:

Deposit Required = .5

Yield = 1800

Pre-Orders Placed = 1700

Pre-Orders Picked up = 1600

What is the Profit under these conditions?

This is all the information that was given. Have to use Excel and Monte Carlo simulation

Please do you best to answer it and make appropriate assumptions

Answer rating: 100% (QA)

To build the Monte Carlo simulation and address the vendors questions follow these steps using Excel Steps to Build the Monte Carlo Simulation Set Up Variables and Parameters Yield lbs Mean 2100 Std D... View the full answer