Previous year unitary calculations and summing

mikhivin
mikhivin
in Modeling

Hello everybody!

 

I've come across a problem with making accurate calculations for a line item in currency/unit for previous year.

My model has system version called "current" which has 5 years (2019-2023).

Time options: months, quarter, ytd, FY.

 

I'm making a module that is based on line item subset (included are: Volume in ton, Absolute cost Amount, cost per ton), plants of production, time (months), no model's versions. 

Line items stand for: current year, previous year, budget and their differences.

Something like:

 

mikhivin_1-1584112551954.png

 

 

I have line item 'collect' that is used as data-bank for other line items (for instance, RoFC stands for current system versions).

I retrieve data per line item as: collect[select: versions.current].

For previous year (FY 19) I use function POST (RoFC,12).

It works fine until it comes to summing data at plant level (all production plants) and for Q, YTD and FY for cost/t line items (even at the level of a particular plant).

Such functions as POST, LAG, OFFSET, MOVINGSUM do not allow to have summary method set as Formula.

 

In that case what will be correct way to do this calculations for previous year with proper summing for Time and Plants?

 

Thank you.

 

Tagged:

Answers

  • anirudh
    anirudh

    Hi @mikhivin,

     

    You will need separate line items that contain the summarized time data before you can apply typical time functions, such as POST, on the result.

     

    For example, when your line item is at month level and summary is quarter; applying POST at a month level does not figure at the quarter level. Would POST(Line Item, 1) mean the Quarter summary should show you:

    Case 1; the previous quarter's value for that line item, or should it show you

    Case 2; The summed up value at quarter after applying POST at the month level?

     

    If Case 1: You will need a separate line item dimensioned by Quarters and then you can simply apply POST to this line item

    If Case 2: Simply apply Formula for the main summary and Sum for the Time Summary (screenshot below)

    anirudh_0-1584156864139.png

     

     

     

    Let me know if this helps

     

    Regards,

    Anirudh

  • mikhivin
    mikhivin

    Dear Anirudh,

     

    Thank you for your answer, it seems to work with quarter and year, though YTD does not work in that case.

     

    The only solution I can think of if is to make a separate module with only previous year data and then reference it line by line in the resulting module, but the formula is going to be huge.

Categories