cash flow modeling with payment terms

Post Reply
cerend
Posts: 10
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

cash flow modeling with payment terms

Post 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' ));
Best Regards,

Ceren
Top
Adam
Posts: 124
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: cash flow modeling with payment terms

Post 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);
I've started blogging about TM1, check it out: www.havaslabs.com

Take care,
Adam
Top
cerend
Posts: 10
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

Re: cash flow modeling with payment terms

Post 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!
Best Regards,

Ceren
Top
Post Reply

Return to “IBM TM1, Planning Analytics, PAx and PAW”