LAG() Rent Increases without Time Scale
Hello,
New Model Builder here! I'm working to enhance an older model that uses a hierarchical list as a Custom Time Scale (CTS Months). The list is used in lieu of a time scale for most modules, however one of the enhancements involves applying market rent increases to the next 'time period'. i.e. a 5% market rent increase should not be applied to Month 1, but should apply to all other months.
We have a similar model for budgeting on a calendar year that does not have this custom time scale, and a simple LAG(rent increase,1,rent per unit) works just fine because it has a 'real' time scale. Unfortunately, I can't do that here as the module in question uses the CTS Months list instead of Time:
CTS Months List:
I was able to achieve this with the solution below, but is there a better way?
My solve: creating a separate module with a single line item that has a boolean indicator be true for just Month 1, then applied the formula below to the 'Increased Rent Per Unit line item pictured above:
New Grid:
Best Answer
-
It looks like the reason the formula in the example works is because the "Market Rent Increase" is constant at 7. But if you change the values e.g. $8 for Month 2, $6 for Month 3 then the formula above may not result in the desired result. If you can assume that the "Market Rent Increase" value will always be constant then you can keep the formula.
There's several ways to address this:
1. Is it possible to push the "Market Rent Increase" value to the next month? E.g. In your example,
* Month 1, Market Rent Increase = 0
* Month 2, Market Rent Increase = 7
2. If recommendation 1 above is not possible then do the following:
2.1 Add new line item
Line Item name: Last Month CTS
format: CTS Months List
formula: FINDITEM(CTS Months List, TEXT(VALUE(CODE(CTS Months)) - 1))
2.2. Update formula for "Increased Rent Per Unit" to
Starting Market Rent + Market Rent Increase[LOOKUP: Last Month CTS]
What recommendation 2 does is it tries to get the Market Rent Increase from the previous month
0
Comments
-
Glad that you have found out the solution. I don't see any issues whatsoever with the way you have built it. Formula looks clean and as per best practice.
However if there is a need to iterate previous periods value into next period then you might have to fall back to Anaplan Native time scale, use Time based functions, map it with Custom Time scale and pull back the values.
Thanks,
Misbah
Miz Logix
0 -
Thanks Misbah! I've ran into another LAG() issue in a different part of this model so I'd like to understand your proposal a little better. I need to charge a % based project fee due 2 months after project start. That project start is defined within the Custom Time Scale list mentioned in the original posting, and all modules containing this data use the CTS Month list instead of Time.
You're saying I'd need to create a new module and translate the CTS list into Anaplan's native time scale, perform the calculation, then translate back to the CTS list and reference that result? I know my model has this already built in, but I struggle with executing it.
0 -
Yes, if there is a need to use TIME based functions like LAG, PREVIOUS etc then its better to use Native TIme Scale, calculate it in there, create SYS mapping module and then pull back the values into Custom TIme based module by using mapping module.
Thanks,
Misbah
Miz Logix
0
