Question

The Mountain Red Vineyard (MRV) is planning to launch a luxury wine brand, MRV Shiraz to be sold at the fixed price ? = ? per barrel. The MRV operations research analyst conducted the

survey of MRV customers and obtained the discrete probability distribution of annual demand for the new luxury wine brand as shown in the table below.

(?)

Further the analyst developed the risk analysis simulation scenario assuming the MRV Shiraz sold quantity is a random variable with discrete probability distribution shown in the table below.

0

Demand for MRV Shiraz (barrels)	Probability
50	0.5
60	0.3
70	0.2

3

(?)

The risk analysis simulation scenario also included the fixed cost ? = $300 per barrel of the MRV Shiraz. This cost is associated with introduction and operation of the new production line.

The MRV is committed to have enough supply to meet the demand. The profit function is ? = ? × ? − ? × ?.

Name the Excel sheet ‘Problem 3’.

MRV Shiraz sold quantity (barrels)	Probability
30	0.6
45	0.3
60	0.1

enter ? = ? of your choice into the cell $B$4;0

Set the input parameter values:

figure below.

0

enter ? = $300 into the cell $B$5.
Your implementation of the simulation model should also include the information shown in the

Let N be the sample size.

1) Could we use a single standard uniform random variable (e.g. to be generated in column B) for simulation of ‘Demand’ and ‘Sold’ quantities instead of two standard uniform random variables? Provide your reasoning.

2) Let ? = 50. Report the average profit and standard deviation of the profit. Construct a 95% - confidence interval for the expected profit using your simulation results.

3) Let ? = 500. Report the average profit and standard deviation of the profit. Construct a 95% - confidence interval for the expected profit using your simulation results.

4) Comment on the tendency of the 95% - confidence intervals obtained in 2) and 3). Explain your answer.

4

5) Give a ‘break-even’ price estimate, you would recommend to the MRV operations research analyst, using your simulation results. Give your reasoning.

Answer 1

1) We couldn't use single standard uniform random variable to simulate both the demand and quantity sold.

In case, if we use single random variable to simulate both demand and quantity sold, both the values will be same. Thus demand and sold quantities will be same if we use single random variable, which is practically impossible.

2)

N	R1	Demand	R2	quantity sold	profit
1	0.655714	60	0.027387	30	6000
2	0.970118	70	0.41531	30	3000
3	0.921793	70	0.53968	30	3000
4	0.590635	60	0.889401	45	18000
5	0.078029	50	0.323128	30	9000
6	0.100446	50	0.351825	30	9000
7	0.632934	60	0.265828	30	6000
8	0.875227	70	0.058577	30	3000
9	0.958657	70	0.181805	30	3000
10	0.455358	50	0.98313	60	33000
11	0.961557	70	0.855887	45	15000
12	0.943801	70	0.068075	30	3000
13	0.614561	60	0.889275	45	18000
14	0.904897	70	0.263497	30	3000
15	0.673472	60	0.70413	45	18000
16	0.989673	70	0.685905	45	15000
17	0.170093	50	0.643637	45	21000
18	0.74892	60	0.199264	30	6000
19	0.56692	60	0.54034	30	6000
20	0.984213	70	0.941335	60	27000
21	0.202006	50	0.210846	30	9000
22	0.310648	50	0.607257	45	21000
23	0.980473	70	0.563357	30	3000
24	0.341244	50	0.800271	45	21000
25	0.725391	60	0.008781	30	6000
26	0.36596	50	0.788662	45	21000
27	0.425224	50	0.09359	30	9000
28	0.111948	50	0.209221	30	9000
29	0.049116	50	0.789733	45	21000
30	0.519724	60	0.454702	30	6000
31	0.019536	50	0.768746	45	21000
32	0.417032	50	0.908839	60	33000
33	0.402904	50	0.743702	45	21000
34	0.514135	60	0.389825	30	6000
35	0.030984	50	0.291021	30	9000
36	0.012894	50	0.158923	30	9000
37	0.098327	50	0.617667	45	21000
38	0.487265	50	0.470854	30	9000
39	0.231842	50	0.661549	45	21000
40	0.155766	50	0.147215	30	9000
41	0.067375	50	0.415555	30	9000
42	0.47076	50	0.679598	45	21000
43	0.591083	60	0.123545	30	6000
44	0.462077	50	0.379724	30	9000
45	0.948773	70	0.547871	30	3000
46	0.635066	60	0.434623	30	6000
47	0.395644	50	0.472693	30	9000
48	0.026484	50	0.437178	30	9000
49	0.096709	50	0.150411	30	9000
50	0.444539	50	0.015408	30	9000

For 50 trails, using standard uniform random variable R1 , demand is simulated and another uniform random variable R2, quantity sold is simulated. And the price is chosen as 800$. And the profit is calculated from the given formula Profit function P = Q*p - C*D.

For the simulated data, average and standard deviation of profit is calculated.

Average = 12000

Standard deviation = 8017.837, sample size n = 50

95% confidence interval for the mean of profit =. Here since = 0.05, = 1.96

95% confidence interval for the mean of profit = (9777.61,14222.39)

3)In a similar way , For 500 trails , demand and quantity sold is simulated using two standard uniform random variables. And the price is chosen as 800$. And the profit is calculated from the given formula Profit function P = Q*p - C*D.

For the simulated data, average and standard deviation of profit for n =500 is calculated.

Average   = 12582

Standard deviation = 8559.545, sample size n = 500

95% confidence interval for the mean of profit =. Here since = 0.05, = 1.96

=(11831.74,13332.26)

4) 95% Confidence interval is the range of values that we can be 95% sure contains the mean of the population.

By comparing the confidence interval obtained in 2) and 3),it can be seen that the confidence interval for sample of size 500 is narrower than the confidence interval for sample of size 50. Thus, as sample size increases, accuracy increases.