Say you had quarterly data and you want to collect this data onto a yearly page? How would you go about it? Probably a hundred different ideas came to your head when I raised that question. Video Financial Modelling has seen all of these. One sticks out ahead of the rest, the MOD function.
Using the MOD function
The MOD function is quite simply awesome. In simple terms it takes a number and a divisor and spits out a remainder. The equation is shown below.
= MOD(number,divisor)
Does that bring back memories of school maths? Well for all of those people who can't remember, here is a couple of examples to help the mental juices:
- mod(11,3) - 11/3 is 3, with a remainder of 2. So MOD gives us 2.
- mod(12,3) - yes you guessed it. 0.
- mod(-11,3) - ok so this is getting a bit harder. The answer is 1. Why? Well because the MOD function gives the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case the largest integer multiple that doesn't exceed -11 is -12. Still confused. Have a play around with the function.
MOD function Example
Ok, so I promised you that I would show you how to grab data from a quarterly page and aggregate it on a yearly page so let's get started. Download the associated YouTube video and Excel spreadsheet.
We have the following quarterly dates and associated revenue numbers.
We want to aggregate these numbers on an annual page. Let's firstly put in annual dates in. You can put these on a seperate page or keep it simple and have it on the same page. This is shown below.
Now the magic. Below the quartlery dates, let's put in the following formula. Don't worry we will explain the components soon.
= EOMONTH(quarter end date, MOD(month(first period ending date)-month(quarter end date),12)
This should look something like this:
Copy this fomrula across. Wow magic... hopefully this formula gives you the year ending for each of the dates? It sure does, now how does it work. Let's break it down:
= EOMONTH(Component 1,Component 2)
- Component 1: quarter end date
- Component 2: MOD(month(first period ending date)-month(quarter end date),12)
The first component should be pretty self explanatory, so let's look at the second component by looking at some brief examples.
Ok given that the first period ending date is always 31 Dec 2012, the month(first period ending date) will always equal 12. Simplifying the formula we get:
= MOD(12-month(quarter end date),12)
There are 4 possible options for the month(quarter end date). 3, 6, 9 and 12. i.e. corresponding to March, June, September and December. Let's look at each in turn.
- March - MOD(12-3,12)=9
- June - MOD(12-6,12)=6
- Sep - MOD(12-9,12)=3
- Dec - MOD(12-12,12)=0
If you're switched on you should see a pattern above. The result is equivalent to the number of months to the year end. So if we combine this with an EOMONTH formula we will get to the year end.
Now if we do a sumif formula (as below) we can aggregate the revenue numbers into yearly results.
We should get the following:
Now you may be wondering why we didn't just use a Year formula and then a sumif. Well that is definitely another option, but say you now had a monthly construction page and you want to aggregate or lookup this data in a quarterly page. Trust me this happens all the time in project finance deals. Well the answer.... use the above methodology and you can't go wrong.
If you liked this blog tutorial check out our new advanced training course, Advanced Toll Road Model Training Course.
For more free blog tutorials go to Video Financial Modelling's Blog.
No comments:
Post a Comment