Welcome to Highline BI348 class, video number 67.
If you want to download this file, BI348 Chapter 11 Video 67
and follow along, click on the link below the video.
We've been doing lots of simulation.
And in this video, we want to look
at an example for simulation where
we have a binomial variable.
Here's the situation.
We have a company, Financial Freedom Firm.
And the Financial Firm runs free seminars
in hopes of generating new, paying clients.
Now we know that the total cost for each seminar is $3,000.
The average first year commission,
if someone from the seminar becomes a paying client,
is $4,500.
The number of people who can attend the seminar,
which is also the number of trials, is 35.
We will define a success as a new customer.
That means of the 35 attendees, if one of them
becomes a new customer, that will be a single success.
And then the probability of success
of getting a new customer from the attendees
for any particular one attendee is 0.065.
Now in order to build a simulation
based on a binomial variable, we've
got to check the four tests, to see whether this
is a binomial experiment.
Fixed number of trials?
Yep, 35 tries to get new customer.
Only two outcomes for each trial,
either a success or not success?
Yes, they either become a new customer or they don't.
Probability of success stays the same each time?
Yes, 0.065.
And each trial is independent?
We will assume yes.
So we have 1, 2, 3, 4 yeses.
We're going to use a binomial random variable.
Here's our define variable, number
of successes-- new customers in 35 tries equals x.
So x out of 35, with a probability for any one
try of 0.065.
There's our x.
Now we saw this a couple of videos
ago when we talked about creating random variables.
We are going to use the BINOM.INV function.
The DISTS for our distribution functions
always gives us probability.
The inverse always gives us our x value.
So tab.
We need to know the number of trials.
There it is, 35.
Comma, the probability for success
on any one of those trials, 0.065 comma.
And the alpha is the cumulative probability.
And we're going to use our RAND function.
That delivers a number between 0 and 1 with 15 digits.
That will represent our cumulative probability.
Now Cuba probability for a binomial distribution
means all the probability up to a given x.
So if we said what's the probability of zero successes
or one success or two successes, if we added up
all those probabilities, that would
be the cumulative probability of x equals to 2.
Now we actually saw great picture a couple of videos
ago of this distribution.
But here that we'll do it.
Alpha is a cumulative probability.
So when I Control-Enter, there is one random variable.
If I hit the F-9, there's another one,
there's another one.
So as I'm hitting the F-9 key, I'm randomly
generating a binomial variable with n equals 35
and p equals 0.065.
Now here's our math formula.
Random variable-- that's going to be that-- times
FYC-- our first year commission--
minus our TSE-- that is the total seminar cost.
All right, so let's come down here to our model area.
And we're going to use Data table and an empty cell,
as we have done a number of times already in this chapter.
Our formula to randomize, this is going to be--
and I'm going to click on this cell, that's
got our randomizing formula in there-- times that's
the number of new customers times the average amount
per year, minus the total cost for a seminar.
Control-Enter.
So if I hit F-9 here, wow, so sometimes we have a profit.
We get a loss, so we added 0, so we have the whole $3,000
as a loss.
Now we're going to simulate this 10,000 times, so Control-Enter.
I'm going to put a 1 there.
We're going to do our little trick.
We're going to take our cursor and point to the fill handle.
And when we see our cross hair, right
click, drag down, and back.
And when the secret menu pops up, point to Series.
I'm going to fill this series down a column, increments
of one, and I'm going to stop at 10,000.
And when I click OK, there's my 10,000 values.
Now I can highlight formula for our data table.
Control-Shift-down arrow, Control-Backspace to jump back
to the active cell.
Now we go up to Data, over to Data Tools, What
If, Data Table or the keyboard, Alt-D-T. D for data,
T for table.
That's an easy one to remember.
No row input.
Column input, this is the trick we've been
using in this chapter so far.
This empty cell will have no influence whatsoever
on that formula.
But it will force Data table to copy that formula down and get
it to randomize.
So when I click OK, boom, there we go.
We can see sometimes we're going to have profits,
sometimes we're going to have losses.
Now we need to take all of these values,
calculate some statistics for them, look at them
and make a decision.
Is this marketing plan of spending
$3,000 for a particular seminar, if we
have a probability of 0.065 and 35 people attending, is it
a good marketing plan?
Now in the past couple of videos,
we've been using average, count, max, min, standard deviation.
But at some point when I do this all the time,
I get tired of entering all of these different functions.
So we're going to look at this great function called
aggregate.
Now actually aggregate requires that you know which functions.
And since I tend to do this a lot, I know average is 1,
count is 2, max is 4, min is 5, standard deviation
of the sample is 7.
And there's a bunch of other ones here.
But watch this.
I already put the numbers here-- because I
do this a lot-- so I'm just going to say aggregate arrow,
to get my function number input, comma and I
have to tell it one of these options.
We're just going to say ignore nothing, so 4 comma.
And we're not using the array, the top one,
that's for array calculation.
We're using the bottom one, reference,
so I simply highlight.
Control-Shift-down arrow, Control-Backspace.
And that's it, that will work all the way down,
calculating various statistics.
Whoops, I almost forgot.
I'm going to click on the Reference
to highlight the whole range and F-4 to lock it.
Control-Enter and copy it down.
So the mean is 7,000.
Count, 10,000.
Oh, I don't want to have that number formatting,
so I'm going to Control-Shift Grave accent or Tilde
to remove all the formatting and revert back to general.
The max is 37, the min is 3,000.
Standard deviation, wow, that's a huge standard deviation.
The probability of a loss equals Count Ifs.
Highlight the whole range, Control-Shift-down arrow,
Control-Backspace, comma, I want less than zero,
end double quote-- remember, Count Ifs
requires that you have a comparative operator
and a number as text, so we put it in double quotes
like that-- close parentheses, Divided by My Count,
Control-Enter.
OK, so the probability 0.09, about 10%.
If I hit the F-9 key to randomize,
it doesn't look like that's changing very much.
So just from this analysis here, if we're
willing to assume that risk of a loss, a 10% loss,
for an average profit per seminar of $7,000,
then we're good to go.
Now of course these are some individual statistics.
We almost always want to build a frequency distribution and then
a relative frequency distribution.
So I've already put the upper limits
for each one of our counting categories.
And we're going to highlight one more cell.
Then we have upper limits.
And we're going to enter our frequency array function, so
frequency.
We need our data array.
That's the entire column of simulated values.
Control-Shift-Down Arrow, Control-Backspace comma.
And then I need upper limits for each one of these categories.
Now remember, for each one like this line right here,
it's going to count everything from 6,000
but not including 6,000, up to and including 10,500.
That will be the Count right there, close parentheses.
This has to be entered as an array function,
so we use this special key stroke, Control-Shift
and Enter.
We immediately look up to the formula bar.
If we see our curly brackets, we know
Excel understood that that was entered as an array function.
Come down here, Alt-Equals to add them up, 10,000, Control-B.
I'm going to copy this over.
All right, so are you ready?
We're going to calculate relative frequency.
Left arrow divided by left arrow,
Control-Down Arrow to get down to that total,
F-4 to lock it, Control-Enter, double click, and send it down.
So we can see we have about a 10% chance of getting
between 3,000 and 1,500.
But there are probabilities for 1,500 or more.
Now we've already analyzed this.
But this is not the end of it.
We can hit the F-9 key to see this randomized.
But we can also come up and change our static variables.
So let's just say we overestimated the probability
of success on any one try.
Let's say it was 0.04.
As soon as I enter that, that as a static variable
comes through.
The random variable still gets simulated 10,000 times.
Our analysis statistics are here.
And now we can see we have a slightly different picture.
24% chance of a loss.
So with simulation and random variables and static variables,
the random variable is automatically
going to change from the model we built.
But we're also totally allowed to change these and see
how that ripples through our analysis.
All right, in this video we saw how
to create a binomial random variable
and build a simulation to look at a marketing plan.
In our next video-- we have two more simulation videos.
And in our next video, we'll actually
see a construction project, where
you'll have to calculate the average time
for a particular long range project.
All right, we'll see you next video.