mike falconer

brovanture consultant

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.