TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

cash flow modeling with payment terms

https://www.tm1forum.com/viewtopic.php?t=15791

Page 1 of 1

cash flow modeling with payment terms

Posted: Tue Mar 16, 2021 2:37 pm
by cerend
Hello all,

I am fairly new to TM1 and I'm trying to build a PoC model by myself for practice with some dummy data. Anyway, I have (among others) two cubes: P&L and CashFlow. Some members in the P&L cube have payment terms so for example if a member in P&L cube has a payment term of 2 months and a value of 90 $ on June 2021, in CashFlow cube that 90 $ should be shown on Aug 2021. I have written the below abomination which checks whether to get the value from actual or forecast and I guess supports up to payment term of 10 years. It calculates correctly as far as I'm aware, but I'm sure there are better ways to go about reflecting payments with terms in their respective months.

I would be grateful if someone could give some tips about how to model cash flow with payment terms.

Thanks a bunch!


Code: Select all

['Renewable FiT', 'Forecast'] =
N: IF(ATTRN('Time',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jan',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jan',
   'Dec'))))))))))))))))))))))
   | ' ' |
   SUBST(STR(NUMBR(ATTRS('Time', !Time, 'Year')) - 
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '00' %
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1)) + 1,
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1))), 4, 0), 1, 4), 
   'IsActual') = 1,
   
   DB('PL', 
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jan',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jan',
   'Dec'))))))))))))))))))))))
   | ' ' |
   SUBST(STR(NUMBR(ATTRS('Time', !Time, 'Year')) - 
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '00' %
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-',
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1)) + 1,
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1))), 4, 0), 1, 4), 'Actual', 'Hydro Revenue -FiT' ),
   
   DB('PL',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jan',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '08' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '17' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '25' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '33' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '42' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '50' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jun',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Jul',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '58' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'May',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Aug',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '67' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Apr',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Sep',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '75' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Mar',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Oct',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '83' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Feb',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= ' ', 'Nov',
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '92' &
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-', 'Jan',
   'Dec'))))))))))))))))))))))
   | ' ' |
   SUBST(STR(NUMBR(ATTRS('Time', !Time, 'Year')) - 
   IF(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 4, 2) @= '00' % 
      SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 1, 1) @= '-',
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1)) + 1,
   NUMBR(SUBST(STR(ROUNDP((NUMBR(ATTRS('Time', !Time, 'Month')) - ATTRN('CashFlow Metrics', !CashFlow Metrics, 'PT')) / 12, 2), 5, 2), 2, 1))), 4, 0), 1, 4), !Version, 'Hydro Revenue -FiT' ));

Re: cash flow modeling with payment terms

Posted: Tue Mar 16, 2021 6:58 pm
by Adam
Ceren,

Have you considered 3 cubes:

1. Input Payment -- this is a new cube in your mix
2. P&L
3. Cash Flow

Input Payment has the following measures elements:
* Amount ----- input by user
* Time for P&L ----- input by user
* Time for Cash Flow ----- input by user
* Amount (calc) ----- TM1 calculation if you need to somehow transform the amount input by user
does not have:
* Time dimension

Then in P&L (pseudocode), something like:

Code: Select all

# One liner!
['Renewable FiT', 'Forecast'] = IF(DB('Input Payment' ... 'Time for P&L') @= !Time, DB('Input Payment' ... 'Amount (Calc)'), 0);
Then in Cash Flow (pseudocode), something like:

Code: Select all

# One liner!
['Renewable FiT', 'Forecast'] = IF(DB('Input Payment' ... 'Time for Cash Flow') @= !Time, DB('Input Payment' ... 'Amount (Calc)'), 0);

Re: cash flow modeling with payment terms

Posted: Wed Mar 17, 2021 8:17 am
by cerend
Hi Adam,

For some reason, I didn't even think it was possible to refer to dimension elements in logical expressions like @= !Time. I was always trying make do with transforming ATTRS()'s, as evident from the rule I've written. So not only did you answer my first question, you also taught me something extremely useful that I probably wouldn't have picked up for a very long time (maybe never), left to my own devices.

I'll go ahead and apply your solution in my model.

Thank you so much for your help!
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited