Calculate Rent Balance Daily

[jagjit_singh]

Hi All,

I have customer rent data and was able to create a view as below using alteryx on a daily level. All customers have to be 2 weeks in advance and the rental systems charges the rent and calculates the balances on Monday.

Ideally all customers rent is collected for the week in advance the day the tenancy is started. In certain cases the payment is collected during the week and the tenancy started on a Monday as in the example below. In either case what I'm trying to achieve is how to calculate daily rent balance and if customer is in advance or arrears (i.e payment default)

In the below example customer made a payment on 26/03/2015 of $212.83 but the tenancy started on 30/03/2015. The customer is not charged rent from 26th to 29th March 2015.

On Monday 30/03/2015 we should be charging the customer $ 30.40 (based on Daily Rent Charged column) which should be deducted from the payment made on 26th March 2015 and show a balance of $182.43 on the 30/03/2015 and so forth.

Every time FORM_Note value is Income i.e customer has deposited rent and the account should be credited but we should be debiting\charging rent daily.

As per the rental system front end the customer paid up to 15/02/2017, 98 days in arrears and owes $4896.94. The sample data is attached in csv and if you can assist in the calculation.

Thanks

Jag

[NicoleJohnson]

Discovered a couple things looking at this one. I did figure out how to get to the same #'s as the rental system, but I'm not convinced that's an accurate calculation.

See attached - there are two branches, top one calculates what I believe to be the correct amount in arrears based on the daily rent calculations... the bottom, however, appears to be how the rental system arrived at those numbers. Seems to be just taking the amounts paid into the system for weekly income - weekly rent.

Top branch: 

Calculate daily rent amount (making sure to account for duplicate days in the dataset) using Multi-Row tools to accumulate amounts.

Lower branch:

Use Summarize tools to determine the total in the Amount column, then using the tenancy end date & latest rent amount to calculate the other metrics (days in arrears, last paid date). 

Can you take a look at the calculations and let us know if that at least answers your question about how the rental system arrived at those numbers? Even if it might have added a few more questions in the process... :)

Cheers,

NJ

[jagjit_singh]

Hi Nicole,

Thanks for helping and the workflow provided the desired result against the sample file. I ran the workflow against production data and had to replace append tool with join. I found a scenario where we had migrated the rent balances for few customer from the old system and the tenancy start date is prior to the transaction date and not getting desired result in such cases.

I have uploaded the workflow package file with additional sample data.

10271 -  tenant was transferred from the old rental system. We use Form Type to identify payments or adjustments

10271 desired result

50003 desired result

Thanks

Jag

[NicoleJohnson]

Okay, found a couple things to tweak and now I think you're getting closer. Two amounts were missing in the total Amount calculations: FORM_Note = Balanced Transferred, and a few cases where FORM_Note was Null but there were amounts in the Amount column. In the second case, they showed up after the Cross-Tab tool as column "_Null_", and had then been unchecked from join tool. I renamed to "Other Adjustments" (not sure what these might encompass) and then included both that one and the Balance transferred amounts in your Amount formula near the end of the workflow. Also fixed a formula issue with dates in that last formula tool.

Balances are now matching what is showing in the system, and the days are close - within 4 days, which would make sense if you ran the data from 4-ish days ago, this would account for the difference in Days in Advance/Arrears. :)

Does that get you where you need to be?

NJ

[jagjit_singh · Accepted solution]

Hi Nicole,

Found few transactions were missing in the sample data and have included them in the attached.I have also introduced 3 columns Credit, Adjustment and Debit and allocating the values in them from the Amount column based on the Form Type. Please find attached revised workflow

I'm getting the desired balance for all but not matching results for:

1 - Days in arrears - 50003 is out by one day

2 - Paid Through date ( as per front end Rent Paid up to) is not as desired.

Front End values

10271

50003

51680

Once we get the above, we need to derive the below which is the final result of the workflow.

1) Show numbers of days in arrears\advance - daily level

2) Status: In arrears or in advance - daily level

3) Days in Advance: Every time a tenant makes a payment ( indicated by the Income Form_Note column or credit column), we need to calculate by how many days tenant is advance.

The baseline is that every tenant should be 2 weeks in advance.

Thanks

Jag

[NicoleJohnson]

From first glance, if you add a Floor formula to your calculation for Days in Arrears, that should solve the problem for Days in Arrears. I have a feeling there is a calculation difference in how your system calculates the paid-through date, so not sure I'm going to be able to answer that question #2 for you since I don't know how you would validate how your system is arriving at that date. Does seem to only be one day off if it isn't matching though. 

For part 2, try looking at the Multi-Row tool applied to the original data (rather than the version with the summarization etc. to get you to the front end current totals). You should be able to create a statement that says if the FORM_Note column is "Income", subtract that amount, otherwise add the daily rent and then account for any other adjustments (as long as the FORM_Note column isn't "Rent Deducted"). Once you have the balance, you should be able to derive the same metrics as your formula tool in the workflow you attached for Days in Arrears and Paid Thru Date, just make sure you use the date in the data rather than the max-date used in your original calculations to match the front end. Does that make sense?

Let me know what additional clarification might be needed. Happy to review a workflow for part 2 if you get a solution that's close but needs tweaking as well :)

NJ

[jagjit_singh]

Hi Nicole,

Apologies for the delay in the response. Actually I was trying to get some documentation from the vendor on how their system calculates Paid to Date.

Please find attached how the system calculates the rents paid to date on a Weekly basis ( i.e every Monday)

Really appreciate your help :)

Thanks

Jag