Dates. They’ve been the bane of many a Hyperion Administrator’s life
for about 20 years now and this is mostly due to the inconsistent and
unfriendly business rule formulae surrounding them. The difficulties of dealing
with dates makes calculating something like depreciation or amortisation very
awkward, particularly when the same calculation is a dream to calculate in
Excel.
As an example, let’s look at a lease with the following characteristics
(you’ll need to calculate this for all leases in IFRS16 going forward so we may
as well start now):
Lease Start Date: 1st
January 2019
Lease End Date: 31st
December 2028
Annual Rent: £10,000
For Depreciation, that's all we really need. We'll make some
broad-brush assumptions that it will depreciate in every period that the start
date falls in, starting from 01/2019 and will reach a £0 asset value in
12/2028. This gives it a life of 10 years x 12 months = 120 months. Because we
are simplifying and not considering the present value of the rental payments,
we assume the Initial Asset Value is
10 x £10,000 = £100,000
(FYI - for IFRS16 you'll also need an interest rate and I'll explore
how you can achieve that in Hyperion Planning or PBCS in future blogs - the
maths is really neat)
For now, all that means is we need to stick a depreciation amount of
-£833.33 into every month between the start and end date and we will achieve
our simple goal. So how do we calculate the correct dates in PBCS and match up
the months we want to the life of the lease?
The answer is to define two accounts, Period Start Date and Period
End Date which will both be Date
type members. We will populate these members with the start and end date of
each period and use them as reference points for our calculations. Seems too
simple to be true right? If it's simple and it works, don't mess with it.
However, manually keying in the date is rather inelegant and frankly
I'm disappointed in myself that for a while that's exactly what I did; until my
colleague Alin came up with a brilliant solution. The key starting point is
recognising how Hyperion loads in dates. They are loaded in the format YYYYMMDD
which the clever of you will notice is an 8-digit number. You'll also be aware
through a strong primary school education that months always have a consistent
number of days (except February but we'll come to that). Therefore, it stands
to reason that we can calculate the YYYYMMDD format number for any date.
The logic starts by defining a variable, that I usually define as &IFRS16_FIRST_CALENDAR_YEAR and set
it to, for example, 2018. That is the number 2018, not the member FY18. I'll
usually also have a sister variable &IFRS16_FIRST_YEAR
with the value "FY18". You can work the calculation with any starting
point but if you make it match your Year substitution variable then it's
simpler to maintain.
We also need to define a Year
Number, which we will set to 0 for the &IFRS16_FIRST_YEAR
and increment by 1 every following year. For simplicity of calculation I just
evaluated this in BegBalance but for
optimal calculation speed you may want to put it in every period.
/*Initialise the Year Number in the IFRS16 FIRST YEAR member */
FIX(&IFRS16_FIRST_YEAR,"BegBalance")
"Year Number"(
@CREATEBLOCK("Working");
"Working"
= 0;
)
ENDFIX
/* Run a prior function to roll
the year number forward. */
FIX(@RSIBLINGS(&IFRS16_FIRST_YEAR),"Working","BegBalance")
"Year
Number"(
@PRIOR("Year
Number"->"BegBalance",1,@DESCENDANTS("All
Years"))+1;
)
ENDFIX
We then use the following business rule to calculate the Period Start Date for January:
FIX
("Period Start Date")
/*For this installation, Jan FY18 = Jan-18 so we take
&IFRS16_FIRST_CALENDAR_YEAR (2018) + Year Number (0) to get 2018, multiply
it by 10000 to get 20180000 and add 101 to get 20180101 which is 1st January
2018. */
/*The next year, we will get &IFRS16_FIRST_CALENDAR_YEAR
(2018) + Year Number (1) to get 2019, multiply it by 10000 to get 20190000 and
add 101 to get 20190101 which is 1st January 2019. */
Jan
(
(&IFRS16_FIRST_CALENDAR_YEAR
+ "Year Number"->"BegBalance") * 10000 + 101;
)
ENDFIX
/*Similarly, we take &IFRS16_FIRST_CALENDAR_YEAR (2018) +
Year Number (0) to get 2018, multiply it by 10000 to get 20180000 and add 131
to get 20180131 which is 31st January 2018. */
FIX
("Period End Date")
Jan
(
(&IFRS16_FIRST_CALENDAR_YEAR + "Year
Number"->"BegBalance") * 10000 + 131;
)
ENDFIX
This approach works in the same way for all months except for Period End Date of February, which as
we know differs on a leap year. Fortunately, Oracle have done us a solid with
the @CalcMgrIsLeapYear function.
Feb (
IF
(@CalcMgrIsLeapYear((&IFRS16_FIRST_CALENDAR_YEAR + "Year
Number"->"BegBalance") * 10000 + 228))
(&IFRS16_FIRST_CALENDAR_YEAR
+ "Year Number"->"BegBalance") * 10000 + 229;
ELSE
(&IFRS16_FIRST_CALENDAR_YEAR
+ "Year Number"->"BegBalance") * 10000 + 228;
ENDIF
)
You can, of course, bring all the start dates together into one member
formula and do the same for each of the end dates, and since it's fairly
trivial to follow the logic I will do you all a solid and present the full code
below:
FIX ("Period Start Date")
Jan ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 101;)
Feb ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 201;)
Mar ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 301;)
Apr ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 401;)
May ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 501;)
Jun ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 601;)
Jul ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 701;)
Aug ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 801;)
Sep ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 901;)
Oct ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1001;)
Nov ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1101;)
Dec ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1201;)
ENDFIX
FIX ("Period End Date")
Jan ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 131;)
Feb (IF (@CalcMgrIsLeapYear((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228))
(&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 229;
ELSE
(&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228;
ENDIF;)
Mar ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 331;)
Apr ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 430;)
May ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 531;)
Jun ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 630;)
Jul ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 731;)
Aug ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 831;)
Sep ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 930;)
Oct ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1031;)
Nov ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1130;)
Dec ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1231;)
ENDFIX
This generates the start and end dates for you for as many years as you
have in the model:
Back to the point of the post, which was simple depreciation. Now that
we have these weapons in our arsenal, we can calculate depreciation with ease.
Simply load the lease start date, lease end date and asset value using
your preferred method, and you can simply use the > and < operators to
compare dates. For example:
IF("Lease
Start Date"->"BegBalance" >= "Period Start Date"
AND "Lease Start Date
"->"BegBalance"
<= "Period End Date") - will return Jan-19 only
IF("Lease
End Date"->"BegBalance" >= "Period Start Date"
AND "Lease End Date"->"BegBalance" <= "Period
End Date") - will return Dec-28 only
IF("Lease
End Date"->"BegBalance" >= "Period Start Date"
AND "Lease Start Date"->"BegBalance" <= "Period
End Date") - will return Jan-19 to Dec-28 inclusive
IF("Lease
End Date"->"BegBalance" > "Period End Date" AND
"Lease Start Date"->"BegBalance" < "Period Start
Date") - will return Jan-19 to Dec-28 exclusive
You can have a lot of fun with these comparators, because by using the
Jan-19 to Dec-28 inclusive option, your rule has identified all the months that
the depreciation calculation should place data into.
For a more complex solution you'll also need to calculate the Lease Life Remaining. This corresponds
to the number of months remaining from the current date.
FIX
(&IFRS16_CURRENT_YEAR, "BegBalance")
"Lease_Life_Remaining"(
/*If we don't have a Lease End Date then we will need to set
this to missing*/
IF("Lease
End Date" <> #Missing)
/*Here we use @CalcMgrDateDiff to find the number of months
between the period end date and the lease end date. If this is less than the
total lease life, we use this as the remaining life*/
IF (@CalcMgrDateDiff("Period End
Date"->&IFRS16_CURRENT_MONTH,"Lease End
Date","month") < @CalcMgrDateDiff("Lease Start
Date","Lease End Date","month"))
/*This evaluates as true in the situation where a lease started
prior to our first period of interest, so depreciation must be applied
retrospectively taking into account the depreciation that already happened*/
@CalcMgrDateDiff("Period End
Date"->&IFRS16_CURRENT_MONTH,"Lease End
Date","month");
ELSE
/*Otherwise, we use the Lease Life (in our example 119 months
will be calculated by the formula.)*/
@CalcMgrDateDiff("Lease
Start Date","Lease End Date","month");
ENDIF
ELSE
#Missing;
ENDIF
)
ENDFIX
So, at this point, we have the months that depreciation should be
calculated in, the total asset value to depreciate, and the lease life
remaining. Therefore, we simply need to place the depreciation in the correct
periods.
We need to add 1 to our Lease
Life Remaining to account for the fact that our model depreciates in the
first month too - this can be easily configured if you prefer not to do this.
The Initial Asset Value was simply the
payments multiplied by the years.
"Depreciation"(
IF
"Period End Date" >= "Commencement
Date"->"BegBalance" AND "Period Start Date" <=
"Termination Date"->"BegBalance)
(Initial
Asset Value" -> "YearTotal" -> "All Years") / -
("Lease_Life_Remaining" -> &IFRS16_CURRENT_YEAR ->
"BegBalance" + 1);
ENDIF
)
I've used a simplified version of our custom built IFRS16 model (using
an interest % of 0) to show you how this will appear in PBCS.
If you want to learn more about our IFRS16 model in PBCS or on-premise
Hyperion Planning, I’ll be posting more in-depth blogs on the calculations for
some of the interesting parts including the present value, calculating the
liability and visualising the different possible effects of interest rates, all
through using the power of Essbase.
As always, leave a comment below if this helped you at all.
