Task: Monte Carlo Analysis Advanced Project
Question: A grocery store is trying to determine the optimal amount of fresh produce to have on hand each week. They have (based on historical figures) determined that their demand for produce is governed by the following discrete random variable.
| Winter |
|
|
Summer |
|
| Demand |
Probability |
|
Demand |
Probability |
| 1500 |
0.1 |
|
2000 |
0.15 |
| 2000 |
0.2 |
|
2500 |
0.2 |
| 2500 |
0.3 |
|
3000 |
0.35 |
| 3000 |
0.25 |
|
4000 |
0.25 |
| 3500 |
0.15 |
|
4500 |
0.05 |
The cost of the produce is $1 per pound. They can sell it, when fresh, for $3 per pound. Once It is no longer fresh, the produce is worth only $.10
a) How much should they order each summer? Each winter?
To do this, simulate each possible order quantity (1500, 2000) many many times.
Then determine which quantity yields the maximum average profit over the 1000 iterations.
b) Whats your confidence interval for the mean profit?
In other words, whats the 95% confidence interval for the true mean profit.
Find upper and lower bounds.
c) What is the impact of risk on your decision? Calculate risk as the standard deviation of the profit.
d) Enter a trial order quantity (ordered). Then create a random number cell using =RAND()
Simulate demand
The number of pounds sold is the smaller of our order quantity and demand.
You will need to create a data table with 1000 lines for the 1000 iterations.