Question

Suzanne owns "The Citris Store" in the Indianapolis International airport and sells bundles of fruit to passengers. Suzanne buys her fruit from a wholesaler. The cost of the fruit varies each day. She estimates that the unit cost of a bundle is normally distributed with a mean of $2.00 and a standard deviation of $0.25.

Based on the stores records for the past three years, she has found that demand displays the probabilities outlined in the worksheet. Suzanne sells the fruit for $3.00.

When she ends the day with more fruit than customers, she can sell the leftovers the next day.   Demand on the 2nd day for fruit is normally distributed at an average of 50 and a standard deviation of 10 and she sells them for $1.50 on the 2nd day.

After the end of the 2nd day, leftover fruit is thrown away. Conversely, when she has more customers than fruit, Suzanne gives the customer a $2.00 coupon for a popcorn store, that she also owns. This coupon is given only if customers would like 1st day fruit and the cost to Suzanne is incurred when she provides the coupon to the customer.

Build a simulation where Suzanne orders fruit in bundles of 200 starting at 2900 and ending at 4100. Run 1000 iterations of each ordering quantity. In your analysis calculate the average and sample standard deviations (you may create additional statistics if you wish). Also calculate the percentage of the time that Suzanne's profits are at or below $1,500. Indicate the ordering quantity that you think is best for Suzanne based on the statistics you calculate by entering the order quantity in cell B3.

Day 1 Demand
Cum. Prob.	Prob	Demand
0%	10%	2,900
10%	25%	3,200
35%	40%	3,400
75%	20%	3,600
95%	5%	3,800

Answer 1

First let us see how to simulate various random numbers

First is the day 1 demand.

We use the following cumulative probability and the intervals

Day 1 Demand
Cum. Prob.	Prob	Demand	Interval of random numbers
10%	10%	2,900	0 to less than 0.1
35%	25%	3,200	0.1 to less than 0.35
75%	40%	3,400	0.35 to less than 0.75
95%	20%	3,600	0.75 to less than 0.95
100%	5%	3,800	0.95 to less than 1

To simulate day 1 demand

• draw from uniform distribution (0,1) using =RAND()
• Assign the demadn corresponding to the interval in which random number falls
  • for example if the random number drawn is o,56 then the day 1 demand is 3400

Next we need to generate the unit cost of the bundle. We use inverse CDF method to simulate from normal distribution with mean 2 and standard deviation 0.25. We use the excel function =NORM.INV(RAND(),2,0.25)

Lastly the day 2 demand is normally distributed with mean 50 and standard deviation 10. Just like the unit cost before we use =INT(NORM.INV(RAND(),50,10)), where INT is used to convert the value to an integrer as demand is an integer

Let D1 be the day 1 demand C be the unit cost, D2 be the demand for Day 2 fruits and Q be the order quantity

The Day 1 revenue from selling

When D1>=Q - day 1 demand is greater than order quantity

3*D1

When D1<Q - day 1 demand is less than order quantity

3*Q

or we can write this as

Day1 revenue =3*min(D1,Q)

Day 2 revenue when Q>D1 --->

We know the demadn for Day 2 fruits is D2

The quantity of excess fruits from Day 1 can be written as

Q - min(D1,Q) ---> This will be zero if Q<=D1

Next using the earlier logic we can get the revenue from Day 2 fruits as

1.5*min(D2,[Q - min(D1,Q)])

Any fruits left after this are discarded and hence no revenue

So the total revenue is

3*min(D1,Q) +1.5*min(D2,[Q - min(D1,Q)])

Now the cost

Cost of buying Q fruits is

C*Q

the excess demand on day 1 when D1 > Q is

(D1-min(Q,D1)) ---> this will be zero when Q>=D1

the cost of coupons when D1 > Q on day 1

2* (D1-min(Q,D1))

The total cost is

C*Q +2* (D1-min(Q,D1))

The profit is (revenue) - (cost)

[3*min(D1,Q) +1.5*min(D2,[Q - min(D1,Q)])] - [C*Q +2* (D1-min(Q,D1))]

We implement this in the spread sheet as below

and this

We replicate this 1000 times and get the following (remember to paste all the random numbers as values)

Now we  calculate the average and sample standard deviations (you may create additional statistics if you wish). Also calculate the percentage of the time that Suzanne's profits are at or below $1,500.

and

get the following

We can see that for order quantity 3500, the average profit is the maximum at $2,969.53

Hence Suzanne should order 3500 to maximize the expected profit

Other possible choices.

If she wants minimum loss then she should go for 3100

If she wants to go for a choice which will give her a shot at maximum profit then she should order 3700

Finally, the least probability of making a loss is when she orders 3,300. this is also the quantity when the percentage of the time that Suzanne's profits are at or below $1,500 is the least.