Question: Problem set/requirements should be self explanatory. can use excel to finalize and show answers.
1. Open "E-Commerce Service.xlsx" and answer the questions in the file and submit your simulation file. To better understand the problem and the simulation process, you should watch the accompanying video in the following link: https://www.palisade.com/examples/en/risk/ECommerce/
2. In order to generate a discrete random variable, you can use the "RANDDISCRETE" function from SimVoi. Study the RandDiscrete function explained on pp. 127-128 of the document "Monte Carlo Simulation Using SimVoi."
Now consider the number "X" of patients waiting for flu vaccine at Karle clinic that has the following probability distribution:
|
X
|
Probability
|
|
1
|
0.2
|
|
2
|
0.3
|
|
3
|
0.4
|
|
4
|
0.1
|
Using RANDDISCRETE function, write a simulation model, i.e., an Excel or SimVoi formula that generates this random value with the above probability distribution. Submit your Excel file that contains your simulation model. (You don't have to perform the Monte Carlo simulation).
Note:
I just realized that it is not easy to see the third question in the E-Commerce Excel file. (Question (1) and (2) are visible, but question (3) is visible only if you click on the text box.) My apologies for any inconvenience. Let me reproduce all the questions from the E-Commerce case below.
E-Commerce Service
Suppose that your company offers an E-Commerce service. You want to figure out the optimal "reserved" capacity of E-Commerce transactions per month, i.e., up to how many transactions are allowed per day. Each unit of reserved transaction costs $0.80, but each actual transaction brings a revenue of $1. The daily demand for transactions is a normal random variable with mean = 2 million and standard deviation = 500,000. In the file below, the profit per day is simulated for a test value of capacity (C10) so that you could choose the one with the "best" profit.
(1) Set the transaction capacity (C10) at 2 million, and perform Monte Carlo simulation of the profit (C15) with 1000 trials. What is your estimate and 95% confidence interval for the "expected value of the profit"?
(2) Even though the transaction demand is normally distributed, the simulated profit data (from the SimVoi Histogram from question (1)) does not seem to have a normal distribution. Why do these two quantities have different probability distributions?
(3) Repeat (1) for transaction capacity of 1.5 million. Repeat it for transaction capacity of 2.5 million. Of the three capacity values, which one is the best choice?
Attachment:- E-commerce-service.xlsx