Working with Ceilings or Max Thresholds

Join the discussion on using Formulas and Functions in Quantrix

Moderator: Moderators

Working with Ceilings or Max Thresholds

Postby RLopez » Tue Jun 16, 2015 6:56 pm

Here is a challenge for you that I've been trying to wrap my mind around for a few days. I suspect it has to be easier done in Quantrix than what I'm doing, so I'm asking for suggestions.

In the attached model I am attempting to calculate tax payments. The model has 3 matrices

1. PayRate - By employee by month by year is a monthly wage.
2. TaxParameters -By Tax the monthly rate paid up to the Salary Cap. After that ceiling is met the tax is 0.
3. TaxDue - Here I perform the calculations to compute the tax due.

Step 1: Calculates a running total of the pay rate over the months .
Step 2: Calculates a tax to be paid on the running total applying the tax rate in Tax_Parameters
Step 3: Used to identify if the tax ceiling is reached in step 2.
Step 4: Logical expression to compute the tax due based off the tax parameters.
Because step 4 formula will not work for month 1 I've inserted another formula for month 1.

Any thoughts on how to condense this or simplify possibly into one formula without it looking hideous is appreciated.

--Rich
You do not have the required permissions to view the files attached to this post.
RLopez
Quantrix Forum Member
 
Posts: 43
Joined: Wed Mar 12, 2014 10:38 am

Re: Working with Ceilings or Max Thresholds

Postby QDirector » Thu Jun 18, 2015 2:28 pm

Hi - this may still fit the 'hideous' category :-) but I got the steps down to two and used a switch statement instead of nested IF's to make the formula more readable. Model is attached. Hope this helps!

-Mike
You do not have the required permissions to view the files attached to this post.
Mike Salisbury
Director - Quantrix
Official Quantrix support is provided through the IDBS Help Desk via support@quantrix.com
QDirector
Quantrix Forum Member
 
Posts: 123
Joined: Fri Mar 29, 2013 4:30 am

Re: Working with Ceilings or Max Thresholds

Postby RLopez » Mon Jun 22, 2015 11:59 am

I knew there was a better way. Thanks for the example!
RLopez
Quantrix Forum Member
 
Posts: 43
Joined: Wed Mar 12, 2014 10:38 am

Re: Working with Ceilings or Max Thresholds

Postby RLopez » Thu Jul 30, 2015 6:11 pm

So I've been looking at this further and for this to work in subsequent years beyond the first listed a couple of things need to be changed.
Step 1 Running Total in the sublist function needs to be updated from
sublist(PayRate::Year:Month, 1, #Month)
to
sublist(PayRate::Year:Month
, CASE(#Year, 1,1,
#Year + ((#Year -1)*12) ) //Take the year index and add it to the year index less 1 and times that by 12. This gets you to the correct index position to sublist from.
, #Year:Month)
)


Step 2 Tax Due also needs to be changed to use soft recursion on all months other than 1 (We want the Running total to reset at the start of each year). Therefore a case statement has been added to this formula anytime soft recursion was used (I suppose maybe removing timeline would negate this but...I'm sticking with timeline)
CASE(#Month,1, 0, 'Step 1 - Tax Running Total':Month[~PREV])


The entire goal of this question was to come up with a more simple way to calculate..not sure it was accomplished, but nonetheless this is a solution.

See the attached model.

Maybe one of these days I will do a video on this... :)
You do not have the required permissions to view the files attached to this post.
RLopez
Quantrix Forum Member
 
Posts: 43
Joined: Wed Mar 12, 2014 10:38 am

Re: Working with Ceilings or Max Thresholds

Postby S a u » Fri Oct 14, 2016 12:14 am

Hi Rich,

Maybe this model would be the most simple way for the Tax Due calculation.
You do not have the required permissions to view the files attached to this post.
Best regards,
SAU
S a u
Quantrix Forum Member
 
Posts: 29
Joined: Fri Jun 20, 2014 8:57 am

Re: Working with Ceilings or Max Thresholds

Postby RLopez » Mon Nov 28, 2016 12:10 pm

SAU,

You are a Quantrix Master! Thanks for your help.
RLopez
Quantrix Forum Member
 
Posts: 43
Joined: Wed Mar 12, 2014 10:38 am


Return to Formulas and Functions

Who is online

Users browsing this forum: No registered users and 1 guest