Welcome, this is the review session for week three of operations in analytics course. As before, this review session is completely optional, if you feel that the simulation concepts are clear to you, you can skip this review and move on to the practice problems. After that if you feel ready, tackle week three's homework. However, if before trying the practice problems, you would like to have an additional look at how a simulation can be set up and run, then keep watching. In this review session, we will go over another simulation example. Here's the business context we're going to model. We're looking at the ESI, a company that produces chemical solvent. The company has a contract that requires it to deliver 35 tons of solvent at the price of 10,000 Euros per ton every day. While the expected value of the solvent produced each day is exactly 35 tons, the actual production amount is hard to control exactly, due to the nature of the production process. And the actual amount of solvent produced can be different from the expected value. The analysis of the past production output shows that the actual daily production output can be modeled as a normal random variable with mean of 35 tons and the standard deviation of 7 tons. The cost to ESI to produce one ton of solvent is 9,500 Euros. If the actual production exceeds 35 tons, the extra production above 35 tons must be salvaged at a price of 9,000 Euros. If the production falls short of 35 tons, ESI is penalized at the amount of 2,000 Euros for each ton below 35. So here are the questions we would like to answer in this problem. First, we will calculate the profit ESI earns on a day when its production turns out to be 31 tons. Then, we'll repeat this calculation for a day when the production is 37 tons. Answering these first two questions will prepare us to write down an algebraic formula for the daily profit V. As a function of the production output p. Finally, we will set up and run a simulation for the daily profit value using 100 simulation runs and the seed 123 to estimate the expected value and the standard deviation of daily profits. Okay, let's start by calculating ESI's profit on a day when its production happens to be 31 tons. Since the production on that day is below 35 tons, the company will earn revenue by selling all of its production at 10,000 Euros per ton. And will also incur production costs of 9,500 Euros per ton. In addition, it will also incur a cost of 2,000 Euros per ton below 35 tons. So on the revenue side, ESI will earn 310,000 Eeuros. On the cost side, it will incur the production cost of 294,500 Euros. It will also incur a penalty cost of 8000 Euros on that day, so the profit will be 310000- 294500- 8000, 7500 Euros. Now let's do the same calculation for a day when ESI's production is 37 tons. Since production on that day is above 35 tons the company will earn revenue in two ways. First it will sell 35 tons at 10,000 Euros per ton. Second, it will sell the remaining 2 tons at a discount price of 9,000 euros per ton. It will incur a production cost of 9500 Euros per ton. So let's count. The regular revenue on that day will be 350,000, the discount revenue on that day will be 180,00 Euros, the production cost will amount to 351,500 Euros. So the total profit on that day will be 16,500 Euros. Now let's put together both cases, with production being below versus at or above 35 tons, using algebra. If P<35, three components contribute to the profit. Now let's put both cases together, with production being below versus at or above 35 tons, using algebra. If P<35, three components contribute to the profit. First the firm incurs a production cost of 9500*P. Then there's a revenue of 10,000*P. Finally there's a penalty cost of 2000*35-P. So putting these three components together we get, 10000*P-9500*P-2000*(35-P) and the final answer is 2500*P-70000. In the second case, when the production P is at or above 35, we also have three profit components. First we have a production cost of 9500*P. On the revenue side we have regular revenue of 35*10000 plus the discount revenue of 9000*(P-35). So the first 35 tons ESI sells at 10000 Euros per ton. And everything above 35 it sells at the discount price of 9000 Euros per ton. So putting these three components together we get, 100000*35 + 9000*P- 35- 9500*P. And the final answer is -500*P + 35,000. So we can combine both cases using IF function in Excel. If P is less than 35, then the first formula applies and if P is at or above 35, then the second formula applies. The last question in this problem, asks us to set up and run a simulation of the daily profit value using 100 simulation runs and the seed = 123. We have prepared an Excel template Europa Solvent_0 that we will use to simulate the distribution of the daily profit. Let's open this file and set up and run our simulation. Let us set up a simulation of the daily production and daily profit values for the ESI company using 100 simulation runs and the seed 123. We will start with the file Europa Solvent_0. This file contains the problem data and is ready for the simulation set up. For convenience, we have also copied here the formula from the PowerPoint slides that calculates the daily profit value V for any daily production value P. Okay, in order to prepare our simulation, let's first add headers to the sales D1, E1, and F1. In D1 we enter Simulation Run. This header will indicate that below, in the column D, we will count the instances of our random variables. In E1, we will enter Daily Production. In tons. So in the column E we will be generating random instances of the ESI's daily production values. Finally, in the cell F1 we will enter profit in Euros. Okay. This column will contain the daily profit calculated using the random instances of the data production from column E. This simulation will have 100 simulations runs, in other words we will generate 100 random instances of daily production and for each which of those we will calculate a corresponding daily profit value. First let's number our simulation runs in column D. Let's put 1 in the cell D2. And let's keep adding ones until we get 100 simulation runs. Okay, let's double check, yes we have 100 simulation runs Now it is time to generate 100 random daily production values. These values will be stored in the cells E2 through E101. In order to generate random production values we will use the Analysis ToolPak. We go to Data > Data Analysis, select Random Number Generation, click OK. In the Random Number Generation dialog, we put 1 in the Number of Variables box. We put a 100 in the Number of Random Numbers. So, we instruct Excel to generate 100 instances of a single random variable. Then in the distribution we select normal. And then for the parameters of this normal we must select 35 for the mean and 7 for standard deviation. This will instruct Excel to generate 100 random instances of daily production values using a normal distribution with these parameters. Now let's use 123 for the random seed and finally in the output options we will select an output range, and we put cell E2 here. This will instruct Excel to put the random instances of the daily production values into the cells E2 through E101. Now let's click OK. Excel has now filled the cells, E2 through E101 with random instances of the normal random variable with mean 35 and standard deviation seven, as we requested. Let's hide some rows to make our presentation more compact. Select some rows, for example 12 through 100, and hide. Here we go. And let's color the E column values And make them bold to indicate that we're dealing with simulation inputs here. Okay, now it is time to turn to the output of our simulation. The daily profit values. Let's go into the cell F2 and put in the formula that will calculate the daily profit value P for any given value of the production in the cell E2. As the formula we copied from the slides indicates, we must enter in the cell F2 the full in expression. = If the production, which is E2, is less than 35, then the value of the profit is given by 2,500 times the value of the production minus 70,000. Otherwise, it's equal to minus 500 times the value of production plus 35,000. As the result for this particular case indicates, Europa will lose more than 21,000 Euros on a such day. Now we copy and paste the profit formula from the cell F2 to the cells F3 through F101. We also color the cells F2 through F101 to indicate that we're dealing with the output of the simulation. Okay, the final step is to calculate the sample mean and the standard deviation of the simulated values of the daily profit. Let's put the corresponding headers into the cells E103 and E104. Sample Mean. Simple Standard Deviation. And let's calculate the values themselves. Average of all the profit values and standard deviation, STDEV of all the profit values. We can make them blue and bold to indicate that those are output values. As the results indicate, the sample mean of the daily profit value is around 8,752 Euros. And the simple standard deviation is around 9,415 Euros. Okay, to reiterate, here's a snapshot of the complete simulation setup with formulas. We saved it in the Excel file called Europa Solvent. As the results of the simulation indicate, the estimate for the expected daily profit is about 8,752 Euros, and the estimate for the standard deviation of the daily profit is about 9,415 Euros.
