When a data point extends beyond model's time range

JMiddleton
JMiddleton
in Modeling

I have run into an issue with correctly mapping monthly depreciation costs to the periods within the fixed asset's useful life.

 

Module A (screen cap attached) contains the user inputs for the fixed asset including the depreciation start and end dates for the asset. In this module there are also two line items that use the PERIOD() function to get the period for the depreciation start and end dates which is then used in a suboridinate view to map the monthly depreciation costs for the associated periods.

 

Module A does NOT have Time as a dimension since it used to collect information on fixed asset purchase requests. The subordinate view DOES have Time as a dimension (Periods: Month).

 

The formula I was trying to get to work is:

 

IF PERIOD(START()) >= Purchase Date Period AND PERIOD(END()) < Depn End Date Period THEN Monthly Depn ELSE 0

 

The formula works fine when comparing the period of the subordinate view to the period of the depreciation start date but fails once I include the additional criteria for comparing the period of the depreciation end date to the period in the subordinate view (the red portion of the formula). I believe the issue stems from the fact that any fixed asset has a useufl life of 3 or more years which means the depreciation end date line item falls outside of the model's time range.

 

Is there a way to correctly calculate this formula without extending the time range of the model (and thereby increase the model's sparsity)?

 

 

 

 

Tagged:

Answers

  • JMiddleton
    JMiddleton

    Found a solution!

     

    I created an extended Time Range in the Time section of Settings that started in the model's current year and extending it for 20 periods (years in this context). I then applied this new time range to the subordinate view line item for the module, applied the formula above in the original post and it works!

     

    Unfortunately this creates an additional maintenance requirement in the model but is easy enough to use/update that it's not an issue.

  • PaulRitner
    PaulRitner

    Hi,

     

    I usually think about this a little differently:  In the Monthly Depn Mapping (i.e. the monthly depreciation calculation), ask the question (pseudocode):  If Purchase Date >= start() and Depn End Date <= End() then Monthly Depn else 0.

     

    In this way, you can use the model timescale and things will work just fine.

     

    Cheers!

     

    Paul

Categories