Working with Ceilings or Max Thresholds

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
Solved1.55K viewsFormulas and Functions
0

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

Selected answer as best
0

Hi Rich,

Maybe this model would be the most simple way for the Tax Due calculation.

Selected answer as best
0

SAU,

You are a Quantrix Master! Thanks for your help.

0

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
[b:q7syo37m]sublist(PayRate::Year:Month, 1, #Month) [/b:q7syo37m]
to
[b:q7syo37m]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)
)[/b:q7syo37m]

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)
[b:q7syo37m]CASE(#Month,1, 0, ‘Step 1 – Tax Running Total’:Month[~PREV])[/b:q7syo37m]

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… :)

0

I knew there was a better way. Thanks for the example!

0

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

Latest Questions

Pareto – 80/20 Rule 2 Answers | 0 Votes
Setting a cell to a fixed value 1 Answer | 0 Votes
Sum formula facing SIZE issue 2 Answers | 0 Votes
Model becomes slow 3 Answers | 0 Votes