Revenue and expenses positive

Hello everyone,

I had this question a few times now and I have always tried to talk clients out of it, but I thought I ask the community in case someone came up with a solution.

Is it possible to have positive values for revenue and expenses and still have subtotals and grand totals that make sense?

BOARD just aggregates values with a simple sum formula, so if your revenue and expenses are both positive then the aggregate gross profit or EBITDA will make no sense.

I know that you can create an entity with rules for the totals and format them to your needs.
But I had bad experience with that in the past where it became quite time consuming to update that entity, especially when it is required to show a PnL report by account.

Other tools offer a "weight" for example which you can use to influence the aggregation.

Did anyone else come across this request and found a solution for it?

Cheers,

Daniel

------------------------------
Daniel Zillmann
Senior consultant
Mecklemore
Australia
------------------------------

Find more posts tagged with

Accepted answers

All comments

Etienne CAUSSE

Hi,
I don't have this issue in my company, but I can think of two options :

If it's only about reporting, with the data coming from another source, you could use an ETL rule to reverse the sign during the import. Looks heavy to do and maintain though
Another way would be to have your original data by P&L dimension with all positive values, then a vector cube by P&L line with only 1 and -1 values depending on the type ( you can even use an aggregated revenue/expense entity to maintain it faster). Then before displaying the data you just do a data flow : original cube * vector cube.

As a general comment, I always found it difficult to manage subtotals like net results / gross margin etc as a standard subtotals ; cascading subtotals and presentation issues always make it complicated. At the end, a rule (or a materialization procedure) is more efficient even if the standard total value of the cube makes no sense.

Hope it helps,
Etienne

------------------------------
Etienne CAUSSE
Finance Project Manager
SEB FRANCE
France
------------------------------
-------------------------------------------

Pietro Ferrari

Hi Daniel/Etienne,
FYI, we are developing a "flip-sign" feature that allows to associate to entity items, the reversal of the sign. This will allow to correctly add or subtract revenues and expenses while visualizing revs and exp as positive values.
You can expect thie new feature to be released in Q3,

regards,
Pietro

------------------------------
Pietro Ferrari
Chief Tecnology Officier
Board Community
Switzerland
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Pietro Ferrari
This looks like a nice feature. Is it included in both engines roadmaps (10 and 11) ? Or only 11?

------------------------------
Etienne CAUSSE
Finance Project Manager
SEB FRANCE
France
------------------------------
-------------------------------------------

Chris Hannan

Hi Daniel / all,

The 'flip sign' feature will be great, looking forward to that.

Similar to Etienne's comments, I have to date used a relationship to capture the account type (Revenue, Expense, Liability...) and then maintained two sets of cubes (TB view in natural sign) and Reporting view (all positive). Upon loading, apply a selection for account type and then just multiply by 1 / -1 depending upon whether the source data is in natural account sign or not. Beyond the obvious advantage of being able to confirm TB is balanced, having the natural account sign allows great top-down modelling when used with P&L hierarchy as relationships...change EBIT and see it break back to all P&L accounts.

Chris

------------------------------
Chris Hannan
Senior Solution Architect
Board Community
Australia
------------------------------
-------------------------------------------

Andrew Hodgson

Hey there,

Not that it helps with normal layout totals/sub-totals, but of course you can always use a rule to create a really nice P&L view - all numbers could be represented as positive, but for example a profit subtotal can be calculated in the rule by removing expenses from revenue etc.

I've created a single reporting cube that actually holds a number of measures that I load to different members of the same entity - and I use a rule to calculate KPIs on the different member entities. Note that this only works when using the rule, otherwise looking at the cube results in a normal layout would be unpleasant (as they don't roll-up correctly).

Just something else as an option.

------------------------------
Andrew Hodgson
Senior Finance Manager | Product & Customer Finance
Bankwest
Australia
------------------------------
-------------------------------------------