Welcome to session two of the fourth week of modeling risk and reality scores. In this session, we will go over converting an algebraic formulation of a simulation model into a spreadsheet form. In particular, we will look into the details of setting up and running a simulation in Excel. We're starting session two of week four of our course. In the first session, we have talked about the differences between two approaches to modeling uncertainty, scenario based distributions and continuous distributions. But I've also introduced an example of a company that needs to decide how to design a new apartment building in the face of uncertain demand, and discussed the notions of random inputs and random outputs in a simulation. In this session, we will continue to work with this example and see how we can set up and run a simulation in Excel. Okay, let's start by recalling the example we focus on. We're describing a decision facing a company that plans to construct a new apartment building. The company will have two types of apartments in this building, regular and luxury. The decision is effectively how many floors to allocate to regular apartments, and how many to luxury apartments since floors cannot mix the apartments, and each regular floor will have exactly eight apartments and each luxury floor, exactly four. Effectively, there will be two sales phases for new apartments. We can call initial phase and salvage phase. During the initial phase, the company plans to charge prices that will generate a profit of $500,000 for each regular apartment sold, and of $900,000 for each luxury apartment sold. In the salvage phase, all of the apartments unsold in the initial phase, if any, will be disposed at lower profit values, $100,000 for a regular apartment and $150,000 for a luxury apartment. The key uncertainty factors in this settings are the numbers of buyers for each apartment type the company will get in the initial sales phase. The company projects that the number of regular buyers can be modeled using a normally distributed random variable with mean of 90 and a standard deviation of 25. Also, the number of luxury buyers will be modeled as a normally distributed random variable with mean of ten and a standard deviation of three. Normal distributions are convenient, in particular, they only use two parameters, mean and standard deviation, but they can produce fractional values and negative values. We need to make sure that we account for this when we generate integer positive demand values using those distributions. As we discussed in the last sessions, we will be using simulation to answer question regarding the distribution of the profit that the company will earn. This is an algebraic formula that connects random inputs that the company cannot fully control, demand for regular apartments, D-R, and demand for luxury apartments, D-L, to a random output that the company is interested in, the profit. Simulation will proceed to generate instances of random inputs and to calculate the corresponding values for the random output. So this is how we're going to generate random demand values. Let's look at the demand for regular apartments, D R. First. We're going to generate a random value VR from the normal distribution with mean 90 and a standard deviation of 25. But we'll see later in this session how to do it in Excel. Now, a normal distribution can in theory produce instances that fall between negative infinity and positive infinity and that can take fractional values. So we can get a value of the R that is fractional, like 93.7. We can even get a value that's negative like -3.4. We need to have a plan of how to use the values coming from the normal distribution to model the non-negative integer demand values like 90, 78, or 110. Here's what we're going to do. We'll calculate the demand value we're going to use in our simulation using the E formula. Let's see how it works. The E formula has the following structure. If condition, choice 1, choice 2. If condition is true, the value that the formula uses is equal to choice 1. If condition is false, the formula is equal to choice 2. For example, the formula for use has VR less than zero as a condition. So if the value that the Excel generates for us from the normal distribution is negative, DR will be set to zero. Otherwise, DR will be set to equal to the integer part of VR. The integer part of VR is the largest integer that does not exceed VR. So we generate the random value from the normal distribution, VR. We convert this value to a DR we can use as a realization of non negative integer demand, and we plug it in into the formula for the profit. We can do the same for the random value of the demand for luxury apartments. In particular we first generate a random value from the normal distribution with mean ten and standard deviation of three. Convert it using a formula to the value that we can use as demand realization, and plug it into the profit formula. So this is how we're going to use simulation to generate the values of the random inputs, the demand values and then, we're going to use this formula to calculate the corresponding value of profit. The sequence of steps which is described is usually called a simulation run. The simulation run generates a pair of demand values, one for regular apartments and one for luxury apartments, and the corresponding value of the profit. We can repeat the simulation runs as many times as we like, and get a sample distribution of profit. We call it sample distribution because it is based on a finite number of samples from demand values. In order to obtain the true distribution of profit, we will theoretically need to take infinite number of realizations of demand values DR and DL to make sure that the entire distribution of DR and DL participates in the simulation. But generating infinite number of realizations will take infinite time, so we'll always have to settle for a finite sample. The larger is the number of simulation runs, the closer is the sample of the demand values to the continuous distribution. And the closer is the sample distribution of profit to its true distribution. So in practice, running long simulations, that is, simulations with large number of simulation runs, is essential for getting reliable estimates for reward and risk measures. We'll return to the subject in session three. Once we have a sample of profit values, we can use it to estimate the expected profit, the standard deviation of profit, the probability of the profit to fall below a threshold, and any other reward and risk measures. Excel can help us run this simulation as well as analyze its results. We're turning now to a spreadsheet star growth underscore zero to see how we're set up and run the simulation of profit values for the star growth company. Okay. This is the cell filed star growth underscore zero that we will use for running a simulation for the profit of the star growth company. We have separated this file into three areas. The area in the top left corner, A4 through C15, contains the problem parameters. He will have the profit values obtained by selling a regular or a luxury apartment during the initial sales phase and the salvage phase the cells B5 through C6. The profit threshold of $45 million the company considers to be acceptable. Cell B8. The numbers of apartments that describe the decision that Stargrove wants to evaluate. Cells B11 and C11. And the parameters of the normal distribution that describe demand for regular and luxury apartments. Cells B14 through C15. The second area in the file is where the simulation is set up and run. Cells A17 Through I28. Let's have a closer look at this area. We're going to run a simulation with ten simulation runs. Which means that we will generate ten pairs of values for the demand for regular and luxury apartments. As we discussed earlier, the Excel will generate ten pairs of values VR and VL from the normal distribution with mean of 90 and a standard deviation of 25 for VR, and the normal distribution with mean 10 and the standard deviation of three for VL. Those values will be stored in cells B19, Through. C28, colored in green to make sure that the random inputs into the simulation stand out visually. For example. So for B19 and C19 we'll store the values for the R and the L in the first simulation run. Currently we have put the expected values for the R and the L in all of those cells. 90 for the R values and ten for VL values. And we will later replace them by random instances, taking from the respective normal distributions. Now, cells D19 through E28, calculate the corresponding values of DR and DL. The values in those cells use E function to create non-negative and integer random demand values. For example, cell D19 converts the value of VR from the cell B19. Here's the formula. We will use DR and DL values from cells D19 through E28. To calculate the profit values for each simulation run. We calculate the profit for each simulation run in three steps. In the first step, we use the regular demand values DR from the cells D19 through D28. To calculate the profit contribution from the sales of the regular apartments. Those profit contributions are calculated in cells F19 through F28, for example cell F19 calculates the profit from sales of regular apartments if the demand for regular apartments is given in cell D19. The formula in the Cell F19. It's profit contributions from initial and salvage sale phases, following the corresponding formula in the slides. In the second step, we use the luxury demand values DL from the cells E19 through E28, to calculate the profit contribution from the sales of luxury apartments. Those profit contributions are calculated in the cells G19 through G28. Using the formulas similar to the ones in the cells F19 through F28. Finally in the third step the profit contributions from sales of regular and luxury apartments are added in cells H19 through H28. Cells H19 through H28 contain simulation output values that is values of the profit generated in ten simulation runs we're considering. We color them in blue to make them visually distinguishable. Cells i-19 through i-28 contain the indicators of whether the profit value obtained in a particular simulation run falls below the threshold of $45 million dollars. For example, the formula in the cell i-19, if H19 less than B8, then it's one, otherwise it's zero, puts the value of one into the cell if the profit value in the cell H19 is below the threshold of 45 million. If the profit value in the cell H19 is at or above the threshold of 45 million the formula in the cell i19 evaluates to zero. But we'll use the values in the cells i19 through i28 to estimate the probability of the profit being below the threshold. Finally, the last area of this file, comprised of cells F4 through G5, Contains the estimates for the reward and risk measures we're interested in. Cell G4 contains the average of the simulated profit values from the cells H19 through H28. This is our estimate of the expected profit value. Cell G5, Contains the average of the simulated indicator values From the cells I19 through I28. Because it is the average of zeros and ones the number of the cell G5 as we will see registers the frequency with which ones are placed in the cells I19 through I28. This will become clear once we're on the actual simulation. Now it is time to run our simulation. Next, we'll ask Excel to replace the numbers currently in cells b19 through b28, by random values, VR. Generated from the normal distribution with mean 90 and a standard deviation of 25. We'll also ask Excel to replace the numbers currently in cells C19 through C28 by random values, VL, generated from the normal distribution with mean ten and a standard deviation of three. That we do to accomplish this task is to use Excel's capability to generate random variables from several popular distributions. For this, we'll need to use Excel's data analysis tool pack. We'll go to the data tab and click on data analysis button. In the appearing menu we select random number generation option and click OK. In the appearing dialog, we select one as the number of variables and ten as the number of random numbers. This instructs excel to generate ten instances of a single, random variable. Now we need to specify the type of the distribution we want, and its parameters. We select normal, and put ninety as its mean. And 25 as its standard deviation. Next, in order to start a simulation, we need to put in a value of random seed. Let's put some combination of numbers, say 123. Into the random seed box. The role of random seed is as follows. Excel does not generate truly random numbers, it generates numbers usually labeled as pseudo random. But those numbers roughly speaking are random enough to help us generate statistically valid predictions. Random seed instructs Excel to generate ten pseudo random numbers from a distribution we specified in a particular way. We do not really need to know much about how the pseudo random variables are generated by Excel to run an interpret simulation. Keep in mind however that if you are running Excel 2013 on Windows, and you set up your model in the same way I do. And use the same number of simulation runs in the same seed. You will generate exactly the same demand values as I do. So if you want to compare your simulation results to mine, you should be setting up your simulation model in the same way as I do. Use the same number of simulation numbers, like ten, and the same seed, like one to three. Note that if you are using Excel on Mac or using some other spreadsheet program like Google Sheets, you will get different simulated values even if you are getting the same number of simulation runs and the same seed as I do. This is normal as the algorithm used for generating pseudorandom numbers maybe different on different systems. In practice, it does not met at much with pseudo select as long as you run a fair little long simulation. In other words, as long as the value in the number of random numbers box is high. So for a simulation that has only ten simulation runs, the result of the simulation you will get in terms of risk and reward estimates can change quite a bit if you choose a different seed value. But if you run a simulation with say 10,000 simulation runs, it will not really matter in the end what seed you select. Finally, in the output options, select the cell B19 as the output range. This way, excel will put ten random values of generates into the cells b19, through b28. When you click OK, Excel will generate ten random numbers into the cells B19 through B28 using the distribution and the parameters we specified. The numbers in cells B19 through B28 are all instances of a normal random variable with mean of 90 and the standard deviation of 25. Now let's generate random values for the demand for luxury apartments. Again, we're going to the data analysis tool pack. We click on random number generation. We specify one in ten instances. Choose normal distribution with mean ten and standard deviation three. Choose a random seed of say one, two, three, four. And select C19 as the output range. After we're on the simulation, we'll get ten random instances of normal random variable with mean ten as striation of three, placed into the cells C19 through C28. The excel changed the color of our random input cells. Let's make them green again. [SOUND] So we have run our simulation with ten simulation runs. For each run, we have generated random realizations of the demand values for regular and luxury apartments and calculated the correspondent and profit values. We see that out of ten scenarios we simulated, only in two scenarios, scenario one and scenario six the simulated profit value was below that threshold. Observe that the formula in cell G5 basically counts the number of times one was observed in the cells I19 through I28, and then it divides this number by ten. This is what the average of zeros and ones in the cells I19 through I28 calculates. We will save the results of this simulation in the file Stargrove and analyze it in the next session. Now we have designed and run our first simulation model. In the next session, we will look at the interpretation of simulation results.
