Buford has generously offered to assist the Fireside Girls troop 46231 in their annual cheese sale. Last year, there was a significant amount of cheese left over due to patrons not picking up (and paying for) their orders. Buford feels that it's hard not to place an order when the Fireside girls come knocking on your door, but then there is less motivation to actually follow through with the purchase absent the sales pressure. Requiring a deposit at the time of order may help with this issue. The town of Danville has about 3000 potentially interested customers. The probability that a customer will place an order will decline as the required deposit increases, however, as the deposit amount increases, there is a higher probability that a customer who placed an order will actually pick up the cheese and pay the balance. The table in the spreadsheet shows that relationship. Note that these probabilities apply independently to all customers, and are not a blanket percentage of all customers. You will need to create a random variable for customers placing an order and a random variable for customers picking up an order.

It is also not certain how much cheese will actually be available. Buford's friend has run some calculations and believes that the amount of cheese that they can aquire for sale will be normally distributed with a mean of 1500 orders and a standard deviation of 200 orders. If there is insufficient cheese to fill an order when a customer arrives at pickup, then the amount of the deposit must be refunded on the spot. If a customer placed a pre-order but doesn't show up to pickup the cheese, then they don't know you ran out and there is no reason to refund their deposit. Round the number of orders available down to the nearest whole number since no one wants part of an order. Assume that the amount of cheese orderd by the troop should take into account the supply available and the number of orders placed by the customers.

Any unsold cheese can be sold to a local restaurant at a discounted price of $6.00 per order. The restaurant will order as much cheese as you have available at that price.

It will cost the Troop $7.50 per order of cheese.

Build a Monte Carlo simulation with 1000 iterations to determine what deposit percentage (0,25,50,100) will maximize the expected profit for Troop 46231. Indicate the deposit to require in cell B13.
Transcribed Image Text:Average StDev Cost per order Customers Interested Deposit to Require: Cheese Orders Available 1,500 200 $ 7.50 (cost to acquire 1 order of cheese) 3,000 (assume each customer will place at most 1 order) Deposit Amounts 0% 25% 50% 100% % that place an order % that pick up an order 45% 95% 80% 70% 50% Revenue per Completed Order Salvage Revenue per Order $ $15.00 6.00 65% 90% 99% Buford has generously offered to assist the Fireside Girls troop 46231 in their annual cheese sale. Last year, there was a significant amount of cheese left over due to patrons not picking up (and paying for) their orders. Buford feels that it's hard not to place an order when the Fireside girls come knocking on your door, but then there is less motivation to actually follow through with the purchase absent the sales pressure. Requiring a deposit at the time of order may help with this issue. The town of Danville has about 3000 potentially interested customers. The probability that a customer will place an order will decline as the required deposit increases, however, as the deposit amount increases, there is a higher probability that a customer who placed an order will actually pick up the cheese and pay the balance. The table in the spreadsheet shows that relationship. Note that these probabilities apply independently to all customers, and are not a blanket percentage of all customers. You will need to create a random variable for customers placing an order and a random variable for customers picking up an order. It is also not certain how much cheese will actually be available. Buford's friend has run some calculations and believes that the amount of cheese that they can aquire for sale will be normally distributed with a mean of 1500 orders and a standard deviation of 200 orders. If there is insufficient cheese to fill an order when a customer arrives at pickup, then the amount of the deposit must be refunded on the spot. If a customer placed a pre-order but doesn't show up to pickup the cheese, then they don't know you ran out and there is no reason to refund their deposit. Round the number of orders available down to the nearest whole number since no one wants part of an order. Assume that the amount of cheese orderd by the troop should take into account the supply available and the number of orders placed by the customers. Any unsold cheese can be sold to a local restaurant at a discounted price of $6.00 per order. The restaurant will order as much cheese as you have available at that price. It will cost the Troop $7.50 per order of cheese. Build a Monte Carlo simulation with 1000 iterations to determine what deposit percentage (0,25,50,100) will maximize the expected profit for Troop 46231. Indicate the deposit to require in cell B13.

Question

Buford has generously offered to assist the Fireside Girls troop 46231 in their annual cheese sale. Last year, there was a significant amount of cheese left over due to patrons not picking up (and paying for) their orders. Buford feels that it's hard not to place an order when the Fireside girls come knocking on your door, but then there is less motivation to actually follow through with the purchase absent the sales pressure. Requiring a deposit at the time of order may help with this issue. The town of Danville has about 3000 potentially interested customers. The probability that a customer will place an order will decline as the required deposit increases, however, as the deposit amount increases, there is a higher probability that a customer who placed an order will actually pick up the cheese and pay the balance. The table in the spreadsheet shows that relationship. Note that these probabilities apply independently to all customers, and are not a blanket percentage of all customers. You will need to create a random variable for customers placing an order and a random variable for customers picking up an order.

It is also not certain how much cheese will actually be available. Buford's friend has run some calculations and believes that the amount of cheese that they can aquire for sale will be normally distributed with a mean of 1500 orders and a standard deviation of 200 orders. If there is insufficient cheese to fill an order when a customer arrives at pickup, then the amount of the deposit must be refunded on the spot. If a customer placed a pre-order but doesn't show up to pickup the cheese, then they don't know you ran out and there is no reason to refund their deposit. Round the number of orders available down to the nearest whole number since no one wants part of an order. Assume that the amount of cheese orderd by the troop should take into account the supply available and the number of orders placed by the customers.

Any unsold cheese can be sold to a local restaurant at a discounted price of $6.00 per order. The restaurant will order as much cheese as you have available at that price.

It will cost the Troop $7.50 per order of cheese.

Build a Monte Carlo simulation with 1000 iterations to determine what deposit percentage (0,25,50,100) will maximize the expected profit for Troop 46231. Indicate the deposit to require in cell B13.

Average
StDev
Cost per order
Customers Interested
Deposit to Require:
Cheese Orders Available
1,500
200
$
7.50 (cost to acquire 1 order of cheese)
3,000 (assume each customer will place at most 1 order)
Deposit Amounts
0%
25%
50%
100%
% that place an order % that pick up an order
45%
95%
80%
70%
50%
Revenue per Completed Order
Salvage Revenue per Order $
$15.00
6.00
65%
90%
99%
Buford has generously offered to assist the Fireside Girls troop 46231 in their annual cheese sale. Last year, there was a significant amount of cheese left over due to
patrons not picking up (and paying for) their orders. Buford feels that it's hard not to place an order when the Fireside girls come knocking on your door, but then there is
less motivation to actually follow through with the purchase absent the sales pressure. Requiring a deposit at the time of order may help with this issue. The town of
Danville has about 3000 potentially interested customers. The probability that a customer will place an order will decline as the required deposit increases, however, as
the deposit amount increases, there is a higher probability that a customer who placed an order will actually pick up the cheese and pay the balance. The table in the
spreadsheet shows that relationship. Note that these probabilities apply independently to all customers, and are not a blanket percentage of all customers. You will need
to create a random variable for customers placing an order and a random variable for customers picking up an order.
It is also not certain how much cheese will actually be available. Buford's friend has run some calculations and believes that the amount of cheese that they can aquire for
sale will be normally distributed with a mean of 1500 orders and a standard deviation of 200 orders. If there is insufficient cheese to fill an order when a customer arrives at
pickup, then the amount of the deposit must be refunded on the spot. If a customer placed a pre-order but doesn't show up to pickup the cheese, then they don't know
you ran out and there is no reason to refund their deposit. Round the number of orders available down to the nearest whole number since no one wants part of an order.
Assume that the amount of cheese orderd by the troop should take into account the supply available and the number of orders placed by the customers.
Any unsold cheese can be sold to a local restaurant at a discounted price of $6.00 per order. The restaurant will order as much cheese as you have available at that price.
It will cost the Troop $7.50 per order of cheese.
Build a Monte Carlo simulation with 1000 iterations to determine what deposit percentage (0,25,50,100) will maximize the expected profit for Troop 46231. Indicate the
deposit to require in cell B13.

Accepted Answer

To build a Monte Carlo simulation for determining the deposit percentage that will maximize the…