0:00

In session one, we compared decision making processes in business settings with

Â and without significant uncertainty.

Â We have used the wireless data plan problem as an example

Â illustrating a high uncertainty business setting.

Â We have learned about reward and risk measures, and in this session,

Â we will look at how we can estimate the values of reward and

Â risk for any course of action we choose using a simulation toolkit.

Â Let's begin.

Â 0:24

Okay, in section two, we're going to build an algebraic model for

Â evaluating a wireless data plan, and then use the simulation toolkit

Â to obtain estimates of the reward and the risk measures associated with this plan.

Â 0:38

Here's a reminder of the business context we're looking at.

Â The current wireless data plan that our consultant has charges $10 per

Â gigabyte of data.

Â The new data plan charges a fixed fee for all data usage up to 20 gigabytes,

Â and then it also charges $15 per gigabyte of data above 20.

Â For example, if the data usage in a particular month is 22 gigabytes,

Â she'll have to pay $160 for the first 20 gigabytes and $30 for the next two.

Â So her total payment in such month will be $190.

Â 1:18

If, on the other hand, her data usage in a particular month

Â does not exceed 20 gigabytes, her monthly payment will be just $160.

Â Based on historical data usage values, our consultant estimates

Â that her family's monthly data usage is normally distributed with mean 23 and

Â a standard deviation of 5 gigabytes.

Â 1:39

So she also knows the distribution of her monthly payments under the current plan,

Â normal with mean $230 and a standard deviation of $50.

Â What about the distribution of monthly payments under the new plan?

Â If we want to estimate the reward and the risk measures for

Â this new plan, in other words, the expected monthly payment and

Â the standard deviation of monthly payments, what are they?

Â 2:06

Let us use the verbal description of the new plan to connect the random input,

Â data usage U to the random output,

Â whose probability distribution we're interested in, monthly payment P.

Â If U is below or at 20, then the monthly payment P is 160.

Â If U is above 20, then the monthly

Â payment is 160 + 15 x (U- 20).

Â In other words, extra payment of $15 for every gigabyte above 20.

Â Now we can combine both cases using one EXCEL formula, IF.

Â 2:49

The IF formula has the following form,

Â IF(Condition, Choice1, Choice2).

Â This function evaluates condition and IF happens to be true,

Â then U becomes equal to Choice1.

Â And if the condition happens to be false, then IF becomes equal to Choice2.

Â So in this case, if U is greater than 20,

Â then IF selects Choice1, which is 15 x (U- 20).

Â Otherwise, it selects Choice2, no extra payment.

Â 3:32

We have a formula that expresses the monthly payment P as a function of

Â monthly data usage U.

Â So let's return to our main question.

Â If U is distributed as a normal random variable with mean 23 gigabytes and

Â standard deviation of 5 gigabytes, what is the probability distribution of P?

Â 4:10

It's very tempting to try and

Â get the estimate of the expected value of a random key performance indicator

Â by replacing all the random factors it depends on by the expected values.

Â For example, if we plug in 23 gigabytes into that formula, we get a value of $205.

Â Is this the expected value of our monthly payment P?

Â 4:30

Unfortunately, while there are cases when you can get the correct estimate for

Â the expected value of a key performance indicator in this way,

Â in many other cases, this is the wrong way to go.

Â If you have a contract where payment, P, is a linear function of U,

Â in other words, P is equal to U times constant plus or

Â minus another constant, then this approach will work.

Â For example, in the old data plan, our consultant pays $10 for each gigabyte.

Â So under that plan, the monthly payment P is equal to 10 times the data usage U.

Â 10 times U is a linear function of U, and the expected value of P in this case is

Â just 10 times the expected value of U, or 10 times 23, which is 230.

Â 5:15

But here's a simple example showing us how it all can go wrong.

Â Let's say our data usage U can only take 2 values,

Â each with 50% probability, 18 gigabytes and 28 gigabytes.

Â The expected value of U under this probability distribution is still 23

Â gigabytes, and the standard deviation is still 5 gigabytes.

Â Let's see what kind of distribution we will get for

Â the monthly payment amount, P.

Â 5:42

If U is 18, then P is equal to 160.

Â If U is 28, then P is 280.

Â The expected value of P, is 0.5

Â x 160 + 0.5 x 280, $220.

Â Now this is very different from $205 we obtained earlier by

Â replacing the random variable, U, by its expectation.

Â Please keep this observation in mind.

Â In general, we cannot simply replace random variables in our formulas and

Â hope to get the right estimates of the key performance measures.

Â So we're back to asking the same question.

Â What are the reward and risk measures that describe our new plan?

Â How do we estimate them?

Â The answer is use simulation.

Â 6:37

Simulation is a tool for converting probability distributions of random

Â factors we cannot perfectly control, like data usage, into probability

Â distributions for outcomes we're interested in, like monthly payment.

Â In simulation, we'll call random factors like data usage random inputs, and

Â outcomes with distribution would like to obtain random outputs.

Â 6:59

Here's how simulation works.

Â In each simulation step,

Â we generate a random instance of the input quantity like data usage U.

Â In other words, we know the distribution of this random input, in our case,

Â it is normal with mean 23 and standard deviation of 5 gigabytes.

Â And we will instruct Excel or any other simulation tool we use

Â to pull one value from that distribution.

Â Then, we use the formula that connects our random input U, in our case, and

Â the random output P, in our case,

Â to calculate the value of the output random variable corresponding to that

Â instance of the random input variable we just generated.

Â In other words, we ask a simulation tool to generate an instance of a random data

Â usage value U, and then calculate the corresponding monthly payment value, P.

Â We can repeat the simulation step,

Â which we will call a simulation run As many times as we like.

Â Since at every simulation run, we convert a random input value, such as data usage U

Â into the corresponding output value, such as payment P, the simulation basically

Â converts the set of random input numbers into a set of random output numbers.

Â We will use the term input sample and

Â output sample to describe the sets of numbers generated during the simulation.

Â 8:25

Once we generate a sample of output values, for

Â example, is sample of payment values.

Â Or can use the sample to estimate the expected value of the output,

Â standard deviation etc.

Â In other words, we can estimate reward and

Â risk measures that we will use later to choose the best course of action.

Â 9:22

Okay.

Â We're ready to use Excel to set up and

Â run a simulation for the monthly payment values under the new wireless data plan.

Â We have created an Excel template, dataplan_0,

Â you can use to follow our setup.

Â In this first example, we'll set up and run a simulation with just

Â ten simulation runs to help us understand how simulation works.

Â Okay let's go to dataplan_0.

Â 9:49

We start with a template dataplan_0

Â that contains all the data we need to set up a simulation.

Â In our analysis, we'll use Excel 2013 on Windows.

Â We'll be setting up our simulation using Analysis Tool Pack,

Â which is a standard add-in in Excel.

Â It is usually located under data tab in the portion called analysis,

Â right next to the solver button.

Â 10:13

If you do not see data analysis there, you should go to file, options,

Â add-ins.

Â And here what says, manage Excel add-ins, you click go, and

Â you wanna make sure that the Analysis Tool Pak is checked.

Â 11:11

This header will indicate that in column C,

Â we will count the instances of our random variables.

Â In D1, let's put data usage,

Â U in gigabytes.

Â In column D, we will be generating random instances of the monthly data usage.

Â 11:50

Column E will contain the monthly payment amounts calculated

Â using the random instances of the monthly data usage from column D.

Â In this example, we will generate ten random instances of monthly data

Â usages U and calculate ten corresponding values of monthly payment P.

Â 12:09

Every time we generate a random instance of data usage,

Â we will count it as a simulation run.

Â So let's number the simulation runs will conduct 1 through 10,

Â and place the simulation run identifiers in column C, in cells C2 through C11.

Â 12:39

Okay, the actual random instances of monthly data usage for

Â each simulation run will be store in the cells D2 through D11.

Â In particular, the cell D2 will contain the first random instance of monthly data

Â usage, the cell D3 the second random instance of monthly data usage, and so on.

Â 13:00

But how do we generate those random instances?

Â Well, Analysis Tool Pad provides us with the tool to generate random numbers.

Â And that's the tool we're going to use.

Â Let's go to data, click on data analysis,

Â select random number generation, and click okay.

Â 13:19

In the random number generation dialogue,

Â let's put in one, into the number variables box.

Â This tells Excel that we're going to generate the instances of a single,

Â random variable monthly data usage in our case.

Â Next, we'll put ten.

Â 14:30

Now, let's put some combination of numbers say, one,

Â two, three into the random seed box.

Â Random seed instructs itself to generate random numbers from the distribution with

Â specified in a particular way.

Â 14:46

We do not need to worry to much about the seed value in order to run and

Â interpret the simulation.

Â Just keep in mind that if you are running Excel 2013 and Windows, and

Â you set up your model in the same way as I do, and

Â use the same number of simulation runs and the same seed, you will generate exactly

Â the same sequence of monthly usage values that I do.

Â 15:06

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 runs, like ten that I use in this case, and

Â the same seed, like 1, 2, 3 that I use in this case.

Â In practice, it does not matter much what seed you select as long as you run

Â a fairly long simulation.

Â In otherwords, as long as the value in the number of random numbers box is high.

Â We'll talk more about short versus long simulations and

Â about different seed values later this week.

Â Okay.

Â One last thing, in the output options,

Â let's select output range starting in the cell D2.

Â 15:50

This way Excel will put the first random monthly usage number it generates into

Â the cell D2.

Â The second random monthly usage number into the cell D3 and so on.

Â Now, when you click OK, Excel will generate 10 random

Â monthly data usage numbers in cells D2 from D11,

Â using the distribution and the parameters we specified.

Â The numbers in the cells D2 through D11 are all instances of

Â a normal random variable with mean 23 and standard deviation 5.

Â In other words Excel generated, or using another word, simulated for us,

Â ten instances of what the future might hold in terms of monthly data usage.

Â 16:35

Let's make sure that the values in the cells D2 through

Â D11 are visually distinguishable from the other values.

Â Let's change the font in the cells into green and bold.

Â We'll use this same visual designation for

Â the random inputs into our simulation models.

Â 17:05

Let's go into the cells E2 through E11 and put in formulas that will

Â calculate the monthly payment value P, for any monthly Data Usage, U.

Â Let's start with the cell E2.

Â The way the new data plan works is by

Â charging $160 up front, and then by adding $15 for each gigabyte above 20.

Â Let's put this formula into the cell E2 using the value in

Â the cell D2 as the first possible instance of the monthly data usage.

Â We have already discussed an algebraic formula that calculates

Â the monthly payment for any value of monthly data usage.

Â The algebraic formula we put in the cell E2 is $160,

Â that's B5, plus if the data usage, D2,

Â exceeds 20, B4, then we're charged extra $15 for

Â each gigabyte of usage above 20.

Â Otherwise, there are no extra charges.

Â The result in this instance is $160

Â since the monthly usage in this instance fell below 20.

Â If we want to calculate the values of monthly payment corresponding

Â to the remaining nine random instances of data usage We need to copy and

Â paste the formula in E2 into the cells E3 through E11.

Â But before we do this we must use absolute cell references for

Â the cells B4, B5, and B6 for the formula in the cell E2.

Â Those are the parameters of our data plan and

Â they do not change when we copy and paste the payment formula.

Â So we go to the cell E2, And

Â use the shortcut F4 to put the dollar signs around B4, B5, and B6.

Â Let's start with B5, And

Â then move to B4, and

Â then B6 and then B4 again.

Â 19:39

In other words,

Â Excel generated a random sample of the future data usage values and we have used

Â the payment formula to convert the sample into a sample of future payment values.

Â This random sample of payment values is the output of our simulation.

Â We will use blue color and bold font to make sure that those values look different

Â from the random input values, and from other values on the spreadsheet.

Â 20:15

To facilitate the future analysis of the results of our simulation,

Â let us calculate the average and the standard deviation of the simulated

Â samples of the monthly data usage, and the corresponding monthly payment values.

Â The average of a sample of random numbers is also called sample mean.

Â And the standard deviation of a sample of random numbers

Â is also called a sample standard deviation.

Â Let's put out these headers into the cells C13 and C14.

Â Sample mean, and

Â sample standard deviation.

Â 21:01

Let's look first at the sample of monthly data usage,

Â where we'll calculate the average of these ten numbers in the cell D13.

Â So we put in the formula,

Â average of D2 through D11.

Â 21:25

As we can see, the sample mean in this case is about 25.

Â 25 gigabytes.

Â And in the cell D14,

Â we will calculate the value of the standard deviation of the same sample.

Â The formula we put in the cell D14 is,

Â STDEV of the same cells, (D2:D11).

Â There's several formulas for calculating the standard deviation in Excel, and

Â those formulas are applicable in different settings.

Â The STDEV formula is used for a sample of random numbers.

Â The sample standard deviation for this particular sample is around 7.8 gigabytes.

Â Let's use the font for the cells D13 and

Â D14, as we did for the cells D2 and D11.

Â So it's a green font and bold.

Â 22:24

Now we're ready to compute the estimates of the reward and

Â risk measures associated with the new data plan.

Â As we discussed earlier, we will use the estimate of the expected value of

Â the monthly payments as a reward measure, and the estimate of the standard deviation

Â of the monthly payments as the measure of risk.

Â In order to calculate the sample mean and sample standard deviation for

Â our sample of payment values, all we have to do now is to just copy and

Â paste the formulas from the cells D13 and D14 into the cells E13 and E14.

Â We'll use the same font scheme for E13 and E14 as we did for

Â E2 through E11, so it's blue and bold.

Â So, The estimate for

Â the measure of reward associated with the new data plan is about $253 and

Â the estimate for the measure of risk is about $92.

Â 23:29

For completeness, here's the picture of the Excel file data plan 10 we created

Â with all the formulas that helped us to set up and run the simulation.

Â In the next session, we'll have a more detail look at the simulation results.

Â 23:43

In this session, we have learned to use simulation to estimate the reward and

Â risk measure associated with any potential decision we can make.

Â Next time, we'll look at the interpretation of the simulation results.

Â In particular, we will compare the results of short and long simulations,

Â to see how precise our simulation estimates are.

Â We will also look at histograms as a convenient way of presenting the results

Â of the simulation.

Â See you next time.

Â