# 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

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.