WEBVTT
00:00:01.200 --> 00:00:05.366
In this excel tutorial I’m going to be talking to you about economic simulation.
00:00:05.366 --> 00:00:10.066
An economic simulation is when you use random sampling from probability distributions
00:00:10.066 --> 00:00:11.699
for one or more of your variables.
00:00:11.700 --> 00:00:15.033
Basically, it’s miss..mixing statistics with econ.
00:00:15.033 --> 00:00:19.699
And it helps you with..with getting a more comfortable or a better idea
00:00:19.700 --> 00:00:25.700
about what your economic analysis might be by adding in probability or variability to your process.
00:00:25.700 --> 00:00:30.433
Now an example we have..I have here for you, our first cost is $25,000,
00:00:30.433 --> 00:00:36.999
our interest rate is 8%. Our life follows a uniform distribution between 8 and 15 years
00:00:37.000 --> 00:00:41.133
and our annual benefit follows a normal distribution with a mean of fifteen three..
00:00:41.133 --> 00:00:44.233
$5300 and a standard deviation of $1200.
00:00:44.233 --> 00:00:48.999
So, let’s go in and see how you get this to work when you have this type of simulation.
00:00:49.000 --> 00:00:57.166
In order to calculate your life, your life is the minimum of 8 up to 15, somewhere in between uniformly distributed.
00:00:57.166 --> 00:01:04.499
To do this it is equals C6, which is my minimum life of 8 years, and I’ve hardcoded this
00:01:04.500 --> 00:01:07.033
so I can just grab the corner and pull it down.
00:01:07.033 --> 00:01:14.966
Plus, I’m gonna do INT which will round to the ne..to a whole integer and inside there my number.
00:01:14.966 --> 00:01:20.432
It’s going to between..the reason I have 7 code there is because that’s the difference between 8 and 15
00:01:20.433 --> 00:01:25.699
and its gonna be times a random number, which you just pull some random number between 0 and 1.
00:01:25.700 --> 00:01:31.466
So it calculates a life for me, somewhere between 8 and 15 following that uniform distribution.
00:01:31.466 --> 00:01:33.232
Notice I got a 14 there.
00:01:33.233 --> 00:01:38.566
Now, when I grab the corner and pull this formula down, notice what happens.
00:01:38.566 --> 00:01:47.299
I get a different random number between 8 and 15 following a uniform distribution over my 20 iterations.
00:01:47.300 --> 00:01:51.933
Now to calculate my payment, remember this follows a normal distribution
00:01:51.933 --> 00:01:55.399
with a mean of $5300 and a standard deviation of $1200.
00:01:55.400 --> 00:01:58.433
To get the economic simulation working in there,
00:01:58.433 --> 00:02:03.566
this cell is calculated by typing in equals NORM INV,
00:02:03.566 --> 00:02:05.832
so N..O..R..M INV.
00:02:05.833 --> 00:02:10.533
Okay, it needs probability, the probability is, I wanted to draw a random number
00:02:10.533 --> 00:02:15.766
between 0 and 1, so my probability is RAND with an open and closed parenthesis comma.
00:02:15.766 --> 00:02:22.232
Then it wants my mean, which is in C6, and my standard deviation…
00:02:22.233 --> 00:02:26.699
I’m sorry, not C6, E6. My standard deviation is E7.
00:02:26.700 --> 00:02:30.466
I want both E6 and E7 to be locked because those will never change.
00:02:30.466 --> 00:02:32.666
I do want my random var..number to change.
00:02:32.666 --> 00:02:37.032
So I simply hit return. And you can see that I calculated an A.
00:02:37.033 --> 00:02:41.033
Now did you catch when I hit return that all my life values changed?
00:02:41.033 --> 00:02:45.266
This is what goes on whenever you’re doing a simulation or a random value in this.
00:02:45.266 --> 00:02:48.166
Anytime you click on these cells they’re gonna change over
00:02:48.166 --> 00:02:51.866
because it sees a new iteration going in.
00:02:51.866 --> 00:02:54.066
So just be aware that that’s going to happen.
00:02:54.066 --> 00:02:57.799
Now to calculate my present value, that’s gonna equal present value,
00:02:57.800 --> 00:02:59.800
remember where I need to know my rate?
00:02:59.800 --> 00:03:03.200
Which my 8% never changes, so notice I’ve hardcoded.
00:03:03.200 --> 00:03:09.100
My NPER is gonna be in column B, the..for the first one B10.
00:03:09.100 --> 00:03:16.533
I’m not hardcoding it because I wanted to iterate as I go down the..the number of iteration lives.
00:03:16.533 --> 00:03:18.999
My payment is in C10.
00:03:19.000 --> 00:03:22.800
Again, that’s gonna change with each iteration so I’ve not locked it down.
00:03:22.800 --> 00:03:27.000
And then I have to subtract off my first cost of B2.
00:03:27.000 --> 00:03:31.300
And so I just simply hit return and I can see my value.
00:03:31.300 --> 00:03:39.766
Now, I can just grab these both, come across, and you can see that it did something really weird.
00:03:39.766 --> 00:03:41.166
What happened here?
00:03:41.166 --> 00:03:45.499
Oh, I did not lock my B2, did you notice how I got no values here?
00:03:45.500 --> 00:03:49.000
So, let me lock that B2 because it never changes.
00:03:49.000 --> 00:03:51.333
Okay, and hit return.
00:03:51.333 --> 00:03:54.633
Now when I grab that corner I should be in good shape.
00:03:54.633 --> 00:03:56.433
There, now we have values.
00:03:56.433 --> 00:04:03.933
Now, if I wanna calculate the mean and standard deviation of my iterations this is simply equals,
00:04:03.933 --> 00:04:10.599
equals average and I’m gonna average and I’m gonna highlight my numbers.
00:04:10.600 --> 00:04:20.100
And I can see that my mean, present worth is this..is there and my standard deviation is gonna equal,
00:04:20.100 --> 00:04:28.766
standard deviation, I gotta think about what the coding is, standard dev there it is.
00:04:28.766 --> 00:04:40.066
Standard deviation, okay S..T..T..D..E..V and I’m just gonna highlight my 20 different iterations
00:04:40.066 --> 00:04:43.399
and you can see I get a standard deviation.
00:04:43.400 --> 00:04:50.966
Now, what this has done for you is now over 20 iterations using different dis..dist..using different distributions
00:04:50.966 --> 00:04:52.532
for the information you have.
00:04:52.533 --> 00:04:55.966
I can get a mean and standard deviation over my 20 iterations,
00:04:55.966 --> 00:05:02.566
which hopefully will give me a better or more confidence in what my present worth is.