How to Coda Monthly Installments?
Based on payments in previous months — Part II
In my previous blog on monthly Installments I ended with two open issues.
First, is there a threshold beyond which we do not withhold? Apparently there is the agreement that at least 80% of a normal monthly salary should be paid out after deduction.
Assuming that somebody makes 50 per day and the month has 20 working days (weekends and holidays excluded) we have a monthly standard salary of 1000. 80% of the standard salary is 800. In case the monthly salary is lower than — in our example — 800 + 60 (the standard deduction) we skip this month. We need thus at least 860. So far so good.
Second: the scenario that one employee (thus one salary) should pay for multiple files, let’s assume 3. We take again the above scenario and the earnings are in this example 900 (instead of 1000) and thus the delta is 100. How to proceed? From all the options you might see, the company standard pays out only the full amount (thus 60) unless it concerns the last part to close the debt which can be any number lower than 60.
With 3 open files each worth 60 the question is which one to pay? The practical solution is to pay the oldest first and thus the other amounts are not withhold.
Writing this summary is easier than coding Coda in this regard. Let us have a look how to translate these business rules into code.
1 — Defining the threshold
The threshold function is created in a few steps:
- A working day in this context counts 8 hours.
- We calculate the working days this month; all days minus weekends and holidays. I apply an already created named canvas formula that does the job for every active month. More info below.
- We filter out the pay rate value for the actual date, see my blog about this topic (which is a bit more difficult as you expect at first).
- So far we have the active days, the hourly rate, the standard working hours and together it tells what a standard salary would look like for this month. We multiply by 0.8 (80%), next we add the standard monthly deduction.
Below the end result, but before we get there, we need to solve an other puzzle which is a bit more complicated, but very well doable, nothing to worry about.
2 — Oldest Files First
The second issue was about how to deal with not enough salary to withhold for one or more deductions. We first show the outcome, which will lead us to the crucial intermediate step. Below secondary school math (multiply first, then add) that outputs a number in case the ActiveSalary is not blank.
Let’s focus on the AmountToCheck, because this is the key in our set-up.
To demonstrate how it works, we split this calculation in few steps.
- First we filter on the employee. No matter how many files, there is only one salary per employee.
- We create a virtual sequence by counting the files per employee. Jessalyn is unlucky and has 3 files to pay for in our example.
- We use this number in our slice and this requires a bit more explanation.
This code snippets results in sub lists and these sub lists contain the amounts to pay back as you see below in order of the dates and this because of the sort on the date (which is a number) in the first part of of the code.
These sub lists are crucial as we observe in our second step and we have to relate these sub lists to their proper row. We do that via a group index logic.
You might recognize this group index logic since I applied it earlier this week in an approach to keep unique records, see below. The Group Index gives the position of a row in a filtered table and here the filtered table is our ‘base’.
We are almost at the finish (in the back ground I listen to Le Tour de France) but not before we take step 3: we pick the sub list we need via adding Last() to our function
and our real finish is adding the Sum(), which brings as back to our departure.
What this function does is giving us per row the amounts we have to take into account. The payment for the first file, requires only the amount related to this file, for second however amount you need already 2 amounts, while for the 3 files, you need sufficient money to cover the sum of file one, two and three. This is what you see above.
This brings us to the check box column that contains a formula. It is a simple statement that gives a false when the employee with a file is not active and checks if the threshold is lower than the standard salary. If that is the case (checked) we can deduct the amount the court ordered us to.
Voila, this is it. We developed a logic that permits us to evaluate if we can pay out the required amounts for each file. As shown in the previous blog, passing a month is okay . We can keep track of it as you can read in this blog. I like the approach of this company, the try to keep it human and rather okay for their employees with this rule.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
