Apply Different Tax Rates

Post Reply
jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Apply Different Tax Rates

Post by jrock » Wed Sep 19, 2018 7:38 am

Hi-

I searched high and low and scoured the internet, but couldn't find an answer...

I have the following dimensions: 1) Period, 2) Product, and 3) BusinessUnit

I wanted to have a rule apply a different tax rate to different product rollups. My product rollups are as such:

Product
Cars
Car 1, Car 2, etc.
Vans
Van 1, Van 2, etc.
Planes
Plane 1, Plane 2, etc.
Trains
Train 1, Train 2, etc.

So far, I am able to apply a global tax rate as such:
['TAX_CALC'] = C: ['INCOME_BEFORE_TAX'] * .30

This only applies at the C level, but I can't quite figure out how to make it work at the N level.

I would like to apply different tax rates at different product group levels (e.g. Cars = 25%, Vans = 27%, all else = 30%).
How would I write this at the node level?

Thanks in advance for any help!

GeorgeB
Posts: 4
Joined: Thu Jun 21, 2018 10:10 am
OLAP Product: IBM Cognos TM1 / PA
Version: 10.2.2
Excel Version: Excel 2016

Re: Apply Different Tax Rates

Post by GeorgeB » Wed Sep 19, 2018 9:17 am

Create an attribute on the product dimension called 'Tax Rate' for instance.

Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.

The rule will look something like this (assuming cars1, cars2 etc only have 1 parent, "Cars"):
['TAX_CALC']=N: ['INCOME_BEFORE_TAX'] * AttrN('Product', ElPar('Product', !Product, 1), 'Tax Rate');

The consolidation will just be the sum of the income*tax rate at each N level, which is the correct calculation.

bgregs
Posts: 25
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Apply Different Tax Rates

Post by bgregs » Wed Sep 19, 2018 11:53 am

George is exactly right. Just don't forget your feeders!

['INCOME_BEFORE_TAX'] => ['TAX_CALC']

Mark RMBC
Regular Participant
Posts: 162
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Apply Different Tax Rates

Post by Mark RMBC » Wed Sep 19, 2018 12:27 pm

If these tax rates change frequently or you want to model over versions, over years and the tax rates could differ over time etc, then an alternative would be to add an attribute called type to the product dimension, so each element is either car, van, plane etc and hold the tax rates in a tax rate cube.

Then in the rule db to the tax rate cube, with the element in the db cube being the type attribute of the product.

And don’t forget the feeders!

Hope that makes sense

Cheers, Mark

jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Re: Apply Different Tax Rates

Post by jrock » Wed Sep 19, 2018 8:31 pm

Create an attribute on the product dimension called 'Tax Rate' for instance.

I think I was able to do this.

Apply the respective tax rates to the parents such as cars = .25, vans = .27 etc.

Not sure what this means. Where/how do I apply the tax rate?

Thanks.

bgregs
Posts: 25
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Apply Different Tax Rates

Post by bgregs » Thu Sep 20, 2018 11:53 am

Hi Jrock,

What Mark is saying is to create a separate cube (probably simple with ~2 - 3 dimensions) that has a dimension which contains elements like "car, van, etc.". Then from there you can book the rates to each element. Once that is completed, you can assign an attribute to each element in the product dimension that matches this new dimension (we'll call this Product Category). Then your rule would look like the following:

Code: Select all

['TAX_CALC']=N: ['INCOME_BEFORE_TAX'] * DB('Rates', AttrS('Product', !Product, 'Product Category'), 'Rate');
^ Assuming your new "Rates" cube has 2 dimensions: one for product categories and one for measures.

I would also strongly recommend that you review the general cube design threads that are in this forum. They will provide you with a good foundation to start designing implementations like this in the future.

jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Re: Apply Different Tax Rates

Post by jrock » Mon Oct 01, 2018 9:37 pm

This worked like a charm. Thanks for the help!

Post Reply