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:

NOT 'RENT: CTS Month 1 Exclusion'.'Month 1 Indicator'

THEN

Market Rent Increase + Starting Market Rent

ELSE

Starting Market Rent

Old Grid:

New Grid:

Accepted answers

TristanS

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

All comments

Misbah

@DavidHunter

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

DavidHunter

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.

Misbah

@DavidHunter

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

TristanS

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

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…
ADO File Upload - Semicolon Delimiter
Hi, Based on the regional settings, my default delimiter/separator for CSV files is a Semicolon. In the classic way of uploading a csv file, I was able to select the delimiter. When using ADO, I don't have the option to select a delimiter. Uploading a Semicolon delimited file returns an error, or an upload with only one…