Best Practices For Specifying Months And Years for Income Statements And Balance Sheets

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Best Practices For Specifying Months And Years for Income Statements And Balance Sheets

    I'm working on a forecasting model. Ideally, I'd like to be able to have the Income Statement and Balance Sheet on the same tab. This presents a problem because the income statement is a flow and the balance sheet is a point in time. I tried creating a VBA function that would create a special period row for the balance sheet elements (i.e. If Year then Dec, If Qtr. 1 then Mar, etc). I went into the formula for all the balance sheet element and had them reference the periods in my new balance sheet row. Unfortunately, any time there is a change in a dimension or drill down Palo seems to copy all the formulas form scratch and thereby writes over my modified formula.

    I was curious on how other have handled this situation. Is it possible to have alternative dimension for the balance sheet elements or is there simply a better way of setting up the months.

    Any input or suggestions would be greatly appreciated.

    Thanks,
    Jim
  • I understand your pain.

    In Jedox there is no inbuild Line Item, time balance property like Last, First or Balance.

    Unclean solution
    ===============
    Have a seperate consolidation hierarchy for example

    BS_Qtr1
    having Jan weightage as 0
    Feb weightage as 0
    Mar weightage as 1

    and so on...


    Solution 1
    ============
    Its best to have seperate cubes for Income Statement & Balance sheet & Cashflows.
    You could then have a seperate dimension to cleanly manage consolidation where for example
    Qtr1 consolidation would have Jan weightage as 0, Feb weightage as 0 and March weightage as 1

    Else here's yet another suggestion ..
    Solution 2
    ==========
    Define alias for Line Item dimension - like Time_Balance

    In alias cube fill in properties for each balance sheet accounts like - LAST

    Rearrange your Months dimension in this order

    Jan
    Feb
    Mar
    Qtr 1
    Apr
    May
    Jun
    Qtr 2
    Jul
    Aug
    Sep
    Qtr 3
    Oct
    Nov
    Dec
    Qtr 4


    The trick is Previous element of Qtr1 will be Mar and so on for Qtr 4 will be December.

    Then in your Palo.DATA formula for consolidated Months dimension members check if Line item alias for Time_Balance is last if so get the previous member of Time
    IF(PALO.DATA("Line_Item","#_Measure","Time_Balance",!'Line_Item')=="LAST",PALO.EPREV("Database",Months",!'Months'),!'Months')

    The full formula will look like:
    [TARGET] = PALO.DATA("Database","Cube","Dim1",,,,,"Dim N-1",IF(PALO.ELEVEL("Database","Months",!'Months')==0,!'Months',IF(PALO.DATA("Line_Item","#_Measure","Time_Balance",!'Line_Item')=="LAST",PALO.EPREV("Database",Months",!'Months'),!'Months')))

    This second solution could be performance costly.

    Hope it helps!

    Noel.