[MUSIC] In today's exercise, we're going to cover how to use Excel to simulate multiple outcomes for a given objective, such as profit. We'll demonstrate Excel's ability to create random inputs that are based on a theoretical probability distribution. After viewing this exercise, you will understand how to create a range of outcomes based on random inputs to deliver a more realistic scenario. Your operations team has prepared a brief summary of their outlook on demand over the coming year. They estimate that we'll have as few as 135 passengers 10% of the time, 150 passengers 40% of the time, 175 passengers 30% of the time, and 185 passengers 20% of the time. In addition to random demand, we'll now have random cost per mile depending on fuel price, season, and weather conditions, to name a few factors. The operations team estimates that the cost per mile will be between $30 and $50 with each value in the range equally likely. We've added a few more important inputs, such as the number of scheduled flights, miles per flight and revenue per passenger in cells B173 to H174. Now, we want to take the probability information above along with the inputs in cells in B174 to H174 to simulate annual profit for the proposed Chicago to Atlanta route. Using Excel's random functionality and the summary information directly below, let's run 90 simulations and compute an average annual profit under the uncertain conditions. To assist in our calculations later in the exercise, we've created several aimed ranges. First we have Total_Miles, located in cell E174, which refers to the total number of annual miles for the Chicago to Atlanta route. Next we have Min_Cost_per_Mile and Max_Cost_per_Mile in cells F174 and G174, respectively. Revenue_per_Passenger is in cell H174. The final named range is the likelihood bins table in cells B181 to D185, named likelihood bins. We've identified four possible outcomes for the number of passengers we can expect per flight. However, we must consider the likelihood of each outcome using the operation team's probability estimates. For example, we need to provide Excel with information that will choose 150 passengers roughly 40% of the time as an input for our 90 simulation runs. Likewise, 175 should be an input roughly 30% of the time, and so on. The reason we say roughly is because we're going to use Excel's RAND function to randomly generate a value which will help us determine which value of passengers to choose for each simulation run. The first step in this process is setting up likelihood bins. The bins are going to be used in conjunction with VLOOKUP and the RAND function. So let's turn our attention to the likelihood bins we've started for you in cells B182 to B185. We have bin set up, but how did we assign each bin a passenger per flight value? You may notice that we have 0%, rather than 40%, for 150 passengers. Notice that 41% is in cell B183. What this means is that when VLOOKUP is provided, say, 0.39, it will choose 150. However, for anything greater than or equal to 0.04, but less than 0.71, VLOOKUP will choose 175 passengers. In a similar manner, 185 will be chosen for RAND values greater than or equal to 0.71, but less than 0.91. For anything greater than 0.91 up to 1, VLOOKUP will choose 135. This helps explain the mechanics of the VLOOKUP, but where does the 71% come from? It wasn't a likelihood value provided by the operations team. Recall anything greater than or equal to 0, but less than 41%, will result in 150 for our VLOOKUP. That represents our 40% estimated probability, which is basically 40% minus 0%, which equals 40%. Now, returning to the 71%. Recall that 175 passengers are expected around 30% of the time. 71% minus 41% equals 30% in this case. The reason this works is because if we compute 100 values using the RAND function, we'd expect approximately 30 values between 0.31 and 0.71. Again, this is approximate because RAND uses a random number generator. So each sample of 100 values will be a little different. Likewise, we expect to see 180 approximately 20% of the time. So we have 91% minus 71%, which equals 20%. Finally, since RAND returns values between 0 and 1, anything greater than or equal to 0.91, up to 1, should occur roughly 10% of the time, which corresponds to our estimate for 135. To summarize, we're going to generate 90 random values using RAND. We expect around 40% of those values to be less than or equal to 0.4, 30% to be between 0.41 and 0.71, 20% between 0.71 and 0.91, and 10% between 0.91 and 1. We're taking advantage of this property of RAND to generate a probability distribution of passengers per flight. In column D of the likelihood bins table, we have calculated the annual revenue associated with each level of passengers per flight in column C. This will be the final output of the VLOOKUP calculation. Beginning in cell G181, we've designated a range of cells to calculate profit, which is in column J. For each of the 90 profit calculations in column J, we'll have two random inputs, Cost per Mile in column H and Passenger Revenue in column I. Cost per Mile can be anywhere between $30 and $50, where each value in the range is equally likely. This is different than the range of passengers to expect, where we were provided with a probability distribution. Similar to passengers per flight, we're going to use the RAND function to calculate a random value for Cost per Mile, but this calculation does not require a VLOOKUP. So, let's begin by thinking about this problem. How do we get a value between two numbers using RAND? Let's begin by typing in the formula in cell H182. Then we can explain why it works. First, begin by typing equal, then the name of the named range that refers to max cost per mile. Max_Cost_per_Mile. This will serve as our upper bound. Now, type -rand(). This is our random component. We want to multiply this by the difference between our upper bound and lower bound. So, type *( then the named range that represents the upper bound, Max_Cost_per_Mile minus our lower bound, Min_Cost_per_Mile). Hit enter and we should have a value between 30 and 50. So, why does this work? Remember than RAND returns a value between 0 and 1. When RAND is close to 0, we have our upper bound being subtracted by a number very close to zero, since a number multiplied by close to zero will itself be close to zero. In this case, we'll be close to 50. On the other hand, if RAND is close to one, we have our upper bound 50 being subtracted by a number very close to the difference between 50 and 30, which is 20. 50 minus 20 equals 30, which is close to our lower bound. Finally, if RAND is close to 0.5, we'll be in the middle of our range, around 40. Turning to column I, let's use VLOOKUP, RAND and our likelihood bins to get a random revenue figure. We explained this calculation when we introduced the likelihood bins. So let's begin by typing =VLOOKUP And hit the tab key. The first argument is going to be a value returned by the RAND function. So type RAND, and open and close parenthesis then a comma. The table array argument is going to be the likelihood bin, named range, so type likelihood underscore bin then a comma. Once VLOOKUP finds the correct bin, we want it to choose values from the third column. Type three, then a comma. Finally, we have the range_lookup argument. In this scenario, we want this argument to be set to true, so type true. We need the true option because we're looking for ranges of values to match against, not exact matches. To finalize the formula, close parentheses and hit Enter. We've calculated annual revenue, so now we need to subtract annual cost using the value in cell H182 and the total miles named range. In cell J182 type equal I182, which is the annual revenue figure. Now, subtract annual costs by typing minus open parenthesis Total_Miles multiplied by H182. Close parenthesis and hit Enter. We've calculated one simulated value of profit. But we want 90, so we need to copy our formulas down the rest of the table. To copy the formulas down, highlight cells H182 to J182. Hover over the bottom right corner of J182 until you see the cross-hair. Once the cross-hair is visible, double click and the formulas will be copied down. Now we have 90 different values of profit, each accounting for the random nature of our inputs. Instead of one value, we now have a range of values. So how do we use this range to make a decision? Let's turn to the summary information. We've provided summary information that is calculated from the results in cells J182 to J271, our 90 simulation runs. In cell C190, we have calculated the average profit from the 90 simulation runs. Since the calculation is random, the value will change with each iteration. If you want to recalculate the formulas using new random inputs for Passenger Revenue and Cost per Mile, hit the F9 key. Notice how the average fluctuates each time we press F9. The good news is the average profit is positive. But the average can be a tricky statistic if the data is skewed. Another way to view our simulation runs is to count the proportion of runs that produce a profit greater than zero. In cell C191, we've provided this calculation along with a conditionally formatted cell, C192. The conditional formatting rules are based on the assumption that anything greater than or equal to 80% in cell C191 corresponds to a profitable scenario. So it will get a green traffic light icon. Less than 80% but greater than or equal to 75% means the scenario could be pursued, but with caution. So that range gets a yellow light. Scenarios with less than 75% profitable simulation runs should not be pursued and will get a red traffic light icon. Please note these threshold values are hypothetical. In reality, you'd need to work with your team to determine when a scenario is worth pursuing given scenario specific objectives and concerns. We've demonstrated how to use Excel to simulate a random process. Now it's your turn to practice what you've learned using the student workbook. [MUSIC]