Question on ACCTYPE property

[Former Member]

I am using BPC for legal consolidations and have come across a question on the ACCTYPE property. Here on the forum I have found an explanation saying that

ACCTYPE INC is saved with the opposite sign to the database and aggregated to the OLAP cube

ACCTYPE LEQ is saved with the opposite sign to the database and not aggregated to the OLAP cube

ACCTYPE EXP is saved as entered to the database and aggregated to the OLAP cube

ACCTYPE AST is saved as entered to the database and aggregated to the OLAP cube

The questions I have are:

1) Aggregation

Our application is set as a YTD application, so I assume that INC and EXP are NOT aggregated, is that correct?

2) system usage of ACCTYPE

Where is the ACCTYPE actually used (apart from when saving data to the database and displaying data in Excel) by the system or, in other words, would I be able to build an application with only accounts of ACCTYPE EXP and AST? As this is for a legal consolidation we do make us of currency conversion and other business rules.

Thanks

[Former Member]

Hi Arnold,

1. Your understanding is correct.

2. Technically speaking, there is no limitation. You can definitely have only EXP and AST. However, if we look at it functionally, Asset should be equal to liability. This means that if you have AST, then you should have LEQ as well.

Hope this helps.

[Former Member]

Hi,

it does help. The reason why I want to use only EXP and AST accounts is the following.

When entering data we want to enter income as negative and expenses as positive. If we does this in an application with the correct usage of INC and EXP the roll-up into the profit for the year doesn't work.

Equally, when entering data we want to enter assets as positive and liabilities/equity as negative. We also want an account that shows the sum of the balance sheet as zero when it balances. If we use AST and LEQ, this summation account shows the wrong value.

[Former Member]

Hi Arnold,

1. I didnt understand the reson behind entering the INC as negative. Usually, lets take the below scenario

Income = 300

Expense = 100

In the DB,

INC = -300

EXP = 100

Profit = -200

Profit should be maintained as INC. So, while reporting Profit will be displayed as 200, which is absolutely right (considering the original example).

However, if you want to enter the income as negative values, then you need to change the measure formula maintained in your database.

Hope this helps.

[Former Member]

If I take your example, what we want to see when entering data is as follows:

Income -300

Expense 100

Profit -200

If I enter the values as above and have my Income account as INC and my expense account as EXP my profit, defined as INC, does not show -200.

How do I change the measure formula?

[Former Member]

Hi Arnold,

If you are entering the values as shown by you, then in the database,

Income = 300

Expense = 100

Profit = 400

While displaying, it will display the profit as -400 (if profit's acctype is INC).

This is the reason, i requested you to change the measure formula. By default, whenever an account is of type INC or AST, it is multiplied by -1. So, if you remove this and multiply the amount only by 1 (instead of -1), then your databse will have

Income = -300

Expense = 100

Profit = -200

The formula needs to be changed in the measureformula table in your appset in SQL.

Hope this helps.

[Former Member]

I will make that change and test it. I assume such changes will remain even when we migrate from our current version of BPC to a later one?

[Former Member]

Hi Arnold,

The measure formulae are specific to a particular appset. formulae of Appset A has no relation with formulae of Appset B.

Having said this, in the later versions, if you create a new appset by copying Appshell, then you will have the default formulae (since appshell is designed like that). However, if you take a backup of your custom appset and then restore it, then the measure formulae will remain as you have designed.

Hope this helps.

[Former Member]

Hi,

in the measureformula table I have four entries that seem to deal with factors, two with the name FINSIGN and two with the name LOADSIGN.

The FINSIGN ones list account types INC and AST, the LOADSIGN ones list account types INC and LEQ.

I have changed all references to -1 so that they now read 1 but when I enter 100 on a LEQ account the factwblegal table still shows -100 in SQL.

are there any other steps I have to do before this works?

Edited by: ArnoldWarhonowicz on Sep 9, 2010 12:01 PM

[Former Member]

Hi Arnold,

You dont have any other measures apart from these?

There should be predioc measure.

[Former Member]

I do have periodic, WTD, MTD, QTD, HALFYTD, YTD, FINSIGN and LOADSIGN. The application is YTD, so do I need to change all of them or just YTD?

[Former Member]

Hi Arnold,

It completely depends on the measures, which you will be using. If you are going to use only the periodic measure, then you can change only that one.

One more thing is that if you change the measure formula, it will be reflected in all the applications.

Hope you got my point.

[Former Member]

So as I am only using YTD, I can change only YTD measure formulas. But there are two of them.

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",SUM(YTD(),-MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",SUM(YTD(),MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),-MEASURES.[SIGNEDDATA]))))

and

IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), -1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))

do I need to change both? I don't quite understand the code itself.

[Former Member]

Hi Arnold,

Yes, both the formulas are for YTD measure. One formula is applicable, when your application is a periodic type application. Another formula is applicable when your application is a YTD type application. You can see in both the formulas that when ACCTYPE is INC or LEQ, the signeddata is multiplied by -1 and when ACCTYPE is EXP or AST, signeddata is multiplied by 1. You need to make changes accordingly.

Hope this helps.

[Former Member]

I have changed both YTD entries, as my application is YTD I would have thought that this is the one that applies though:

SEQ 200

NAME YTD

STATEMENT

IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), 1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))

SOLVEORDER 3

DESCRIPTION YTD

IS VISIBLE 1

IS YTD APP 1

IS SYSTEM 1

yet, when I enter data onto a LEQ account as a positive value, the database will contain a negative value.

[Former Member]

Hi Arnold,

You need to check few things?

1. Is your application YTD type?

2. What is the measure you are using while reporting?

Now, go to the table measureformula. The answer to the first question is the column "IsYTDApp". The answer to the second question is the column "NAME". Now, you can change the formula for this combination, accordingly.

Hope this helps.

[Former Member]

My app ist YTD and I am using the YTD measure for data entry which is where I am testing.

So, the change described above, as far as I can see should be the correct one.

ISYTDAPP is 1 which should be true

and NAME is YTD which should be the correct measure.

Yet, when I enter data onto a LEQ account, it is still saved in the DB with the opposite sign.

[Former Member]

Hi Arnold,

In your measure formula, it is always going to be of the same sign, irrespective of the ACCTYPE; since you are multiplying it by a factor of 1. After the change, please process all the applications.

Hope this helps.

[Former Member]

Hi,

I think I am missing something.

The BPC standard is to enter all values as positive values and the system will save values on INC and LEQ accounts as negative values in the database.

So, if I enter (I work in a YTD application and use the YTD measure, so I enter the cumulative YTD value and then ask for it back in a report)

INC 1000

EXP 500

AST 1000

LEQ 500

the system will save

INC -1000

EXP 500

AST 1000

LEQ -500

As the system will also transfer the -500 from the P&L to the LEQ side of the BS, my balance sheet does balance.

This behaviour is driven by the dbo.tblmeasureformula table. The entry responsible for this is

SEQ 
200

NAME 
YTD

STATEMENT 
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), -1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))

DESCRIPTION 
YTD

IS VISIBLE
1

IS YTD APP
1

IS SYSTEM
1

What I would like to see is:

I enter

INC -1000

EXP 500

AST 1000

LEQ -500

and the system saves

INC -1000

EXP 500

AST 1000

LEQ -500

The profit for the P&L is again brought into the BS through an account transformation rule, which means I can change the sign and therefore my balance sheet does balance.

I expted this to happen when I change the entry in dbo.tblmeasureformula to

SEQ 
200

NAME 
YTD

STATEMENT 
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), 1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))

DESCRIPTION 
YTD

IS VISIBLE
1

IS YTD APP
1

IS SYSTEM
1

Is this assumption correct?

[Former Member]

Hi

Here are example how you can treat an acc type in financial method.

TYPE	          INPUT        	TEMPLATE	              DATABASE	            Position
INC	           +	               +	                -	            Dr
INC	           -             	-               	+                   Cr
EXP	           +	               +	                +	            Dr
EXP	           -	               -	                -	            Cr
AST	           +	               +	                +	            Dr
AST	           -	               -	                -	            Cr
LEQ	           +	               +	                -	            Dr
LEQ	           -	               -	                +	            Cr
""	          +	               -	                -	            Cr
""	           -	               +	                +	            Dr

how to read table :

If type INC inputted in positive value, value will show in template positive either but in database recorded as minus and it given position debit in journal method.

basically if you do the following table it will not required any script that transform positive and minus value..

as example i give..

SALES as INC with value 300

COGS as EXP with value 150

NETPROFIT as INC with value 150

when you transfer an income type to lequity type is shown a same manner.

hope helps