FIFO in TM1

Post Reply
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

FIFO in TM1

Post by tomok »

Has anyone out there ever done anything in TM1 with a FIFO (first-in, first-out) distribution of data. Here’s the scenario:

I’ve got a situation where I need to calculate the FX gain on an intercompany note interest payment. The gain is calculated by taking the difference between the FX rate in the month when the interest was accrued and the month when it was actually paid. I have the interest accrual transactions in one cube, by note, recorded in a time dimension as to the month in which it was accrued. I have the interest payments in another cube, by note, recorded in the month in which the payment was made. The trick is I now need to merge the data from these two cubes into another cube with the data spread across two time dimensions, one for when the interest was accrued and one for when the payment was made.

Merging the payment data into the cube along the payment time dimension is easy as it can come directly from the payment cube time dimension. My problem is how to distribute that payment along the time dimension as to when it was accrued. The rule is that the interest should treated as FIFO, meaning when a payment is made it should count against the oldest interest still outstanding and move forward in time until the payment is exhausted. For example, let's assume the following facts:

Code: Select all

Interest outstanding coming into 2017 - $0
Interest accrued January 2017 - $10,000
Interest accrued February 2017 - $10,000
Interest accrued March 2017 - $10,000
Accrued interest at the end of March 2017 - $30,000
Now, let's say I make a payment of $25,000 in April 2017. That payment should be applied $10,000 to January, $10,000 to February and $5,000 to March. The next payment made should then start with the remaining $5,000 that was left from March and move forward to any interest accrued since the first payment.

Right now I’m having a mental block and cannot seem to figure out the best way to spread the data into the accrual time dimension like the above examples. A rule-based solution would be best but TI is also acceptable. Any suggestions?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: FIFO in TM1

Post by mattgoff »

I don't see why you need to do any specific accounting to match interest accrual and payment periods at all. I'm attaching a little model that shows how I would do it in Excel-- is this what you're trying to accomplish or am I missing it?

Matt
Attachments
fx.xlsx
(11.46 KiB) Downloaded 338 times
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: FIFO in TM1

Post by st2000 »

I would think of a mixup of both measures like interest (with negative sign) and payment (kept with positive sign) to be generalized as accounting transactions.
Then ordering this by time and making a cumulation per transaction, similar as my bank is doing in my account statements.
After each iteration by day (or whatever period length makes up your interest frequency, but I guess it will beside of your example in fact follow rather a daily pattern ( 30/360)) could be calculated the interest days applied to this. Based on these interest days and the interest rate the gain could be calculated over the period to regard.
I hope, it does help more than it confuses... :| but better than nothing. For a more TM1 based solution I have to leave my TM1-rookie state behind of me first, I fear... :oops:
-----------------------------------
Best regards,
Stefan
Post Reply