Overwrite calculated line items

vd_ghnst
vd_ghnst
in Modeling

Hi all

 

I'm currently working on a large P&L module with a lot of line items (100+) that are either populated via a lookup to calculations modules or calculated by taking a YTD average + inflation. 

 

The problem is that some of those average YTD values turn out to be negative due to a one-off negative transaction in the actual months. In these cases the end user would like to overwrite these values. At the moment, this is not possible because the line item is calculated.

 

I thought about doubling all line items with a calculated line and an overwrite. The final P&L would then take whichever line is appropriate. This would be a very tedious and cumbersome process since there are a lot of line items. The end user will only need this functionality in rare situations.

 

Does anyone have a better way of solving this issue? Ideally, the end user can indicate which line item to overwrite, do it and leave the rest untouched.

 

Thanks in advance

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    I would recommend using a line item subset for the override and final P&L modules based on the line items from the draft module. As you add or remove line item from this draft view they will be automatically added or removed from the draft and override modules. More sustainable and user friendly.

    Pull the data into the new Line Item SUbset module using COLLECT(). Create a line item for the override and final view. 

Answers

  • ChrisAHeathcote
    ChrisAHeathcote

    @vd_ghnst 

    How about creating a line item subset (LISS).

    Create a module using the LISS and and two line items for the calculation and override. 

    Reference the two line items in the original module and populate the final P&L using formulas into the LISS module.

     

  • nathan_rudman
    nathan_rudman

    you can simply create an override module and add it up to the appropriate line item.

    User see the value they don't like, override to compensate it and it modifies the final LI they see.

  • vd_ghnst
    vd_ghnst
    So if I understand your suggestion well, I would need to:

    1) Use the current P&L module as a 'draft' with calculated numbers
    2) Create an overwrite module with identical line items
    3) Create a new P&L module that checks for values in the overwrite module. If none are found, use values from the original module

Categories