Question

A salesperson must buy a number of products from a supplier each Monday, to sell them through the week, which ends on Friday. The salesperson wants to maximize profits, but the number of products sold each week is a random variable. However, the analysis of past year’s data shows the distribution of weekly demand shown in the table below.

Demand per week	Probability
200	15%
250	25%
300	20%
350	35%
400	05%

One product cost the salesperson $4.00, which is sold for $8.00. At the end of the week, any unsold product (it’s perishable) are returned to the supplier for a credit of $1.00.

1. Using Excel, run twenty-five simulations of the demand (show your results, don’t send the worksheet.)
2. Based on your twenty simulations, determine the average weekly profit of the salesperson.

Answer 1

You have to specify the order quantity (Q) for computing the profit.

This is not given in the question, so, I take it equal to Q = 300.

Result:

Now, by varying the value of Q from 200 to 400 in D3, we can note the value of the average profit in J28.

Q	Avg. profit
200	800
250	890
300	948
350	938
400	788

This will vary too much since the number of replications is too low, only 25!