Create Forecast based on date and occurence in Property Module

Highlighted
Frequent Contributor

Create Forecast based on date and occurence in Property Module

Hi,

 

I have a Property module of Costs with following line items:

* Amount (Number format)

* Occurency (List-formatted with options One-off, Monthly, Quarterly, Yearly)

* Cost Start (Month Period format)

* Number of Months (Number format)

 

I want to feed this information into a Calculation model that has Time as a dimension. It should then take the Amount value and depending on other 3 line items populate the amount value accordingly. Example if:

Amount: $100

Occurency: Monthly

Cost Start: January

Number of Months: 12

 

Then the Calculation module should have 100 on every cell for that year.

1 REPLY 1
Highlighted
Master Anaplanner/Community Boss

Re: Create Forecast based on date and occurence in Property Module

Hi Johan,

In the Property Module, create 2 line items. One formatted by Quarter and one by year; name them Cost Start Quarter and Cost Start Year. Formula for both is PARENT(ITEM(Cost Start))

In the Calculation module, create a number formatted line item; formula:

IF Property Module.Occurency = Occurency List.One Off
    THEN IF ITEM(Time) = Property Module.Cost Start
        THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Monthly
    THEN IF ITEM(Time) >= Property Module.Cost Start AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Quarterly
    THEN IF PARENT(ITEM(Time)) >= Property Module.Cost Start Quarter AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Quarterly
    THEN IF PARENT(ITEM(Time)) >= Property Module.Cost Start Year AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE 0

I'm assuming the number of months driver is applied at the month level. Ex: Cost start is set to May 2020, Occurency is set to Quarterly and Number of months set to 4. The amount will be populated for Apr 2020 to Jul 2020

Let me know if this works for you

Regards,
Anirudh