Create Forecast based on date and occurence in Property Module

johan.marketoft
johan.marketoft
in Modeling

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.

Tagged:

Answers

  • anirudh
    anirudh
    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

Categories