How to avoid references to specific years in a formula

michael_gould
michael_gould
in Modeling

I've just been asked about a problem with rolling forward a model from 2011 to 2012. The model had references to full year values, which had been made using a formula like this: Full Year Value = Source[SELECT: Time.FY11] While this works, it requires the formula to be updated every year when the timescale for the model is rolled forward. A better approach is to use the YEARVALUE() function: Full Year Value = YEARVALUE(Source) For each period this gives the corresponding year total from the source line item.  This isn't directly equivalent of the fixed reference to the FY11 total, but it will give the same result if timescale only spans one year. The advantage is that the reference still works when the timescale is rolled on. There are other functions QUARTERVALUE, MONTHVALUE and WEEKVALUE for referencing quarter, month or week totals. The last two are applicable if you’re using a daily or weekly timescale.

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    There are times when YearValue cannot be used:
    1) When the module in which it is being used has no time value (e.g. I have a module which I use to inflation annual values by inflation)
    2) When you need to reference an expression rather than simply a line item (e.g. I have a calculation based on core stores only)

    In these cases the only alternative I can think of it to use the FYxx time function, which you mention has to be manually rolled forward each year.  Is there an alternative?

Categories