WEBVTT
NOTE Copyright (c) GoSkills Ltd, 2013 - 2024
00:00:05.496 --> 00:00:07.040
I'm just going to go straight into MOD.
00:00:07.040 --> 00:00:14.130
Because I think it's easier to just explain in Excel, see what you think?
00:00:14.130 --> 00:00:17.440
MOD is a good old fashioned mathematical function.
00:00:17.440 --> 00:00:20.970
Some people think it has no place in financial modeling.
00:00:20.970 --> 00:00:24.720
They'd be wrong, it's very, very useful.
00:00:24.720 --> 00:00:28.990
MOD is basically the good old clock arithmetic you learned from school.
00:00:28.990 --> 00:00:30.967
Let's take an example,
00:00:30.967 --> 00:00:35.494
using the illustration in row 7 here, 3 divided by 4.
00:00:35.494 --> 00:00:40.430
Now, 3 divided by 4 is 0, 0.75, but I don't want to show it that way.
00:00:40.430 --> 00:00:43.490
I want to say it's not remainder 3.
00:00:43.490 --> 00:00:44.210
See what I'm saying?
00:00:44.210 --> 00:00:47.960
It's, what's the whole number and what's the remainder left over?
00:00:47.960 --> 00:00:50.130
Well, the whole number's easy to calculate.
00:00:50.130 --> 00:00:51.930
There's a good old Yorkshire function.
00:00:51.930 --> 00:00:57.512
Intit equals INT for integer and we just take that divided that and
00:00:57.512 --> 00:01:00.740
it just takes the whole number part.
00:01:00.740 --> 00:01:04.220
So it's zero, but what about the remainder?
00:01:04.220 --> 00:01:08.937
Well, I could too okay, the remainder's
00:01:08.937 --> 00:01:13.523
going to be equal to this divided by that,
00:01:14.703 --> 00:01:18.253
0.75, minus the integer.
00:01:22.335 --> 00:01:27.180
So you got 0.75, multiplied by this will do it.
00:01:28.370 --> 00:01:29.560
Isn't that easy to follow?
00:01:29.560 --> 00:01:34.090
We're not gonna do it that way, there's an easier way.
00:01:35.200 --> 00:01:38.390
The MOD function works out the remainder for you.
00:01:38.390 --> 00:01:40.140
It counts, it does the modules.
00:01:40.140 --> 00:01:45.777
So it goes =mod, and it's just simply the number and the divisor.
00:01:47.974 --> 00:01:50.114
Is that simpler?
00:01:50.114 --> 00:01:54.611
If I copy these both down, I see it,
00:01:54.611 --> 00:01:58.410
13 divided by 6, is 2 remainder 1.
00:01:58.410 --> 00:02:03.626
23 divided by 8 is 2 times 8 is 16, plus 7 is 23.
00:02:03.626 --> 00:02:06.380
This is what MOD does, is it gives you the remainder.
00:02:07.390 --> 00:02:12.360
So, if I have a count down here, we can see how it actually works in the model.
00:02:12.360 --> 00:02:19.622
I can just go, =mod of this counter comma, the modules.
00:02:22.044 --> 00:02:24.176
And it goes therefore, copying it across.
00:02:26.389 --> 00:02:32.990
1, 2, 0, 1, 2, 0, 1, 2, 0, because 1 divided by 3 is 0 remainder 1.
00:02:32.990 --> 00:02:39.160
2 divided by 3 is 0 remainder 2, 3 divided by 3 is 1 remainder 0, and so on.
00:02:39.160 --> 00:02:40.100
Now if I'm in the model, and
00:02:40.100 --> 00:02:44.000
I'm doing something quarterly, I'll have three months in that quarter.
00:02:44.000 --> 00:02:46.620
So, I'll have month, one month, two month, three of quarter one.
00:02:46.620 --> 00:02:49.850
Then month, one month, two month, three of quarter two and so on.
00:02:49.850 --> 00:02:53.580
Wouldn't it be better to have 123, 123.
00:02:53.580 --> 00:02:57.545
What I could do is I could go equals mod open brackets.
00:02:57.545 --> 00:03:00.610
Of this comma that.
00:03:00.610 --> 00:03:05.888
Plus if all of this mod of that by
00:03:05.888 --> 00:03:10.762
this happens to equal nau ght
00:03:10.762 --> 00:03:16.260
then I want it to be the modulus.
00:03:17.850 --> 00:03:21.560
And we just need to make sure everything that C15 is absolute.
00:03:23.610 --> 00:03:29.300
And if you're thinking that's a horrible formula, you'd be right, but it works.
00:03:30.740 --> 00:03:33.352
An easier way is to do the following.
00:03:33.352 --> 00:03:38.060
You do =mod of this, -1,
00:03:38.060 --> 00:03:44.830
comment the Modulus +1 outside of it.
00:03:44.830 --> 00:03:51.727
And believe it or not, that's the same thing, because what happens is,
00:03:51.727 --> 00:03:56.750
if 1-1 is 0, 0 divided by 3 is 0, plus 1 is 1.
00:03:56.750 --> 00:04:02.690
2 minus 1 is 1, 1 divided by 3 gives you a remainder 1, plus 1 is 2.
00:04:02.690 --> 00:04:04.210
But this one, when I get to 3,
00:04:04.210 --> 00:04:08.000
that one would give me module of 0 before, it's actually gonna be,
00:04:08.000 --> 00:04:11.990
3 minus 1 is 2, 2 divided by 3 is not remainder 2 plus 1 is 3.
00:04:11.990 --> 00:04:13.500
See how it works?
00:04:13.500 --> 00:04:17.770
If I change it to 4, I can have a data validation here that's 4.
00:04:17.770 --> 00:04:20.820
It's all working, 6 and so on.
00:04:21.960 --> 00:04:25.610
Let's say I have to pay tax quarterly.
00:04:27.350 --> 00:04:29.423
So, let's go back to Modulus 03.
00:04:29.423 --> 00:04:33.470
And I have to pay it in the second period of each quarter.
00:04:33.470 --> 00:04:36.090
Do you see how easy it is with flags to do this?
00:04:36.090 --> 00:04:42.258
You just say equals, this here equals that number.
00:04:47.021 --> 00:04:52.246
And then we copy that across, and wherever it's a one, that's where we pay our tax.
00:04:52.246 --> 00:04:57.670
Or we can do a dividend calculation or we can actually do a GST payment or
00:04:57.670 --> 00:05:00.640
another indirect tax or whatever it is in our model.
00:05:00.640 --> 00:05:03.790
These are things you have to do all the time and people build much
00:05:03.790 --> 00:05:08.500
more complex calculations, to do this where MOD would come to their rescue.
00:05:08.500 --> 00:05:09.870
Simple as that.
00:05:09.870 --> 00:05:14.850
MOD, not so well known, but a very, very useful function for financial modeling.
NOTE Copyright (c) GoSkills Ltd, 2013 - 2024