You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
269 lines
6.8 KiB
269 lines
6.8 KiB
---
|
|
title: "Deposit Offset Calculation"
|
|
author: "Scary Scarecrow"
|
|
output:
|
|
prettydoc::html_pretty:
|
|
theme: architect
|
|
highlight: github
|
|
---
|
|
|
|
```{r setup, include=FALSE}
|
|
knitr::opts_chunk$set(echo = TRUE)
|
|
library(dplyr)
|
|
library(fst)
|
|
library(DT)
|
|
|
|
dt<-fst::read.fst("./data/open_items_new.fst")
|
|
deposits<-dt |> filter(o.type=="Deposit")
|
|
betza<-read.csv("betzalist.csv", sep=";",dec=",", colClasses = c("character","numeric","factor"))
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
## Steps
|
|
|
|
A list of all deposits was prepared.
|
|
|
|
```{r deposit_table, echo=FALSE, message=FALSE, warning=FALSE, paged.print=FALSE}
|
|
deposits<-deposits |>
|
|
select(c(contract_account,amount)) |>
|
|
rename(deposit=amount)
|
|
|
|
deposits1<-deposits |>
|
|
group_by(contract_account) |>
|
|
summarise(deposit=sum(deposit)) |>
|
|
ungroup()
|
|
|
|
deposits |> DT::datatable()
|
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
```{r}
|
|
betza |>
|
|
mutate(credit=ifelse(amount<0,"Credit","Debit")) |>
|
|
group_by(contract_account, source, credit) |>
|
|
summarise(amount=sum(amount)) |>
|
|
arrange(contract_account)
|
|
```
|
|
|
|
|
|
```{r}
|
|
depoffagg<-betza |>
|
|
mutate(credit=ifelse(amount<0,"Credit","Debit")) |>
|
|
group_by(contract_account, source) |>
|
|
summarise(amount=sum(amount)) |>
|
|
tidyr::pivot_wider(names_from = source, values_from = amount) |>
|
|
left_join(deposits, by="contract_account") |>
|
|
arrange(contract_account)
|
|
|
|
depoffagg
|
|
|
|
```
|
|
|
|
|
|
```{r}
|
|
depoffagg1<-betza |>
|
|
mutate(credit=ifelse(amount<0,"Credit","Debit")) |>
|
|
group_by(contract_account, source) |>
|
|
summarise(amount=sum(amount)) |>
|
|
tidyr::pivot_wider(names_from = source, values_from = amount) |>
|
|
left_join(deposits1, by="contract_account") |>
|
|
arrange(contract_account)
|
|
|
|
dummy<-betza |>
|
|
count(contract_account) |> arrange(desc(n)) |> rename(numberofoccurences=n) |>
|
|
left_join(depoffagg1, by="contract_account") |> arrange(desc(numberofoccurences))
|
|
|
|
dummy<-dummy |>
|
|
mutate(left_passed=ifelse(is.na(left_passed),0,left_passed),
|
|
kvk=ifelse(is.na(kvk),0,kvk),
|
|
morethan=ifelse(is.na(morethan),0,morethan),
|
|
threshold=ifelse(is.na(threshold),0,threshold)
|
|
) |>
|
|
mutate(credit=ifelse(left_passed<0 | kvk<0 | morethan<0 | threshold<0, "Credit","Debit"))
|
|
|
|
dummy |>
|
|
group_by(credit) |>
|
|
summarise(deposit=sum(deposit, na.rm = T))
|
|
|
|
|
|
write.csv(dummy, "aggregateddepositsoffset.csv")
|
|
|
|
```
|
|
|
|
```{r}
|
|
dummy |>
|
|
filter(credit=="Credit")
|
|
```
|
|
|
|
|
|
The list of contract accounts that are being proposed for writing off was prepared from the four files shared. A dummy variable `amount` was created for technical convenience. All values in the variable was set to `0`, since the actual amount is of no significance in our calculation. Please note that *unique* `contract_accounts` have been extracted.
|
|
|
|
```{r unique_ontracts, echo=FALSE, message=FALSE, warning=FALSE}
|
|
betza.unique<-betza |>
|
|
mutate(amount=0) |>
|
|
unique()
|
|
betza.unique |> DT::datatable()
|
|
```
|
|
|
|
Now these two tables were compared. It was discovered that record of deposits of all the considered contract account does not exist. Please use the filter below to verify. Additional Sheet shall also be provided.
|
|
|
|
|
|
|
|
```{r matched, echo=TRUE, message=FALSE, warning=FALSE}
|
|
depos.aggr<-deposits |>
|
|
right_join(betza.unique, by="contract_account") |>
|
|
mutate(status.deposit= ifelse(is.na(deposit), "No Deposit", "Deposit Exists")) |> select(-amount)
|
|
depos.aggr|> DT::datatable()
|
|
```
|
|
|
|
Then, the sum of the deposits which could be matched with the considered contract accounts were used to determine the amount that can be offset.
|
|
|
|
|
|
```{r summa, echo=TRUE, message=FALSE, warning=FALSE}
|
|
depos.aggr|>
|
|
group_by(status.deposit) |>
|
|
summarise(deposit.sum=sum(deposit, na.rm=T))
|
|
```
|
|
|
|
|
|
List of rows/contracts for which deposits were not found
|
|
|
|
```{r nodeptable, echo=FALSE, message=FALSE, warning=FALSE}
|
|
depos.aggr |>
|
|
filter(status.deposit =="No Deposit") |> DT::datatable()
|
|
```
|
|
|
|
|
|
|
|
|
|
```{r csvsa, eval=FALSE, include=FALSE}
|
|
write.csv(depoffagg,"depositoffset.csv")
|
|
```
|
|
|
|
|
|
```{r}
|
|
betza.unique.dummy<-betza.unique |>
|
|
rename(dummy=amount) |>
|
|
mutate(dummy="writeoff")
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
```{r}
|
|
oi<-fst::read.fst("./data/open_items_new.fst")
|
|
d1<-oi |>
|
|
filter(!negative) |>
|
|
filter(o.type %in% c("Installments","Real")) |>
|
|
left_join(thres, by="contract_account") |>
|
|
full_join(betza.unique.dummy, by="contract_account") |>
|
|
filter(dummy=="writeoff") |>
|
|
mutate(amountoconsider=
|
|
case_when(
|
|
rel.status =="long past" ~ "To be considered",
|
|
AGE_BUCKET == "5+ years" ~ "To be considered",
|
|
!is.na(thramount) ~ "To be considered"
|
|
)
|
|
)
|
|
thres<-oi |>
|
|
filter(!negative) |>
|
|
group_by(contract_account) |>
|
|
summarise(amount=sum(amount)) |>
|
|
ungroup() |>
|
|
filter(amount<=500) |> rename(thramount=amount)
|
|
|
|
|
|
d1<-oi |>
|
|
filter(!negative) |>
|
|
filter(o.type %in% c("Installments","Real")) |>
|
|
left_join(thres, by="contract_account") |>
|
|
full_join(betza.unique.dummy, by="contract_account") |>
|
|
filter(dummy=="writeoff") |>
|
|
mutate(amountoconsider= ifelse(e.status %in% c("Passed","Left","Discontinued") && rel.status == "long past", "To be considered",ifelse(AGE_BUCKET == "5+ years", "To be considered",
|
|
ifelse(!is.na(thramount), "To be considered","Not to be considered")
|
|
)
|
|
)
|
|
)
|
|
|
|
d1<-oi |>
|
|
filter(!negative) |>
|
|
filter(o.type %in% c("Installments","Real")) |>
|
|
left_join(thres, by="contract_account") |>
|
|
full_join(betza.unique.dummy, by="contract_account") |>
|
|
filter(dummy=="writeoff")
|
|
d1$amountoconsider<-NULL
|
|
for(i in 1:nrow(d1)){
|
|
d1$amountoconsider[i]= if(d1$e.status[i] %in% c("Passed","Left","Discontinued") && d1$rel.status[i] == "long past"){
|
|
"To be considered"
|
|
} else{
|
|
if(d1$AGE_BUCKET[i] == "5+ years"){
|
|
"To be considered"
|
|
} else {
|
|
if(!is.na(d1$thramount[i])) {
|
|
"To be considered"
|
|
} else {
|
|
"Not to be considered"
|
|
}
|
|
}
|
|
}
|
|
print(i)
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
d1agg |> filter(amountoconsider=="To be considered")
|
|
|
|
d1agg<-d1 |>
|
|
group_by(contract_account,amountoconsider) |>
|
|
summarise(amount=sum(amount)) |>
|
|
mutate(amount=ifelse(is.na(amount),0,amount))
|
|
d1agg |> filter(is.na(amountoconsider)) |> pull(amount) |> sum()
|
|
|
|
d1agg |>
|
|
filter(amountoconsider=="To be considered") |>
|
|
left_join(deposits1, by="contract_account") |>
|
|
mutate(deposit=ifelse(is.na(deposit),0,deposit)) |>
|
|
mutate(recovery_possible=
|
|
ifelse(amount==0,0, ifelse(amount+deposit < 0, amount, deposit))
|
|
) |> pull(recovery_possible) |> sum() #|> write.csv("depositstobeadjusted.csv")
|
|
d1
|
|
```
|
|
|
|
|
|
|
|
|
|
```{r}
|
|
te<-betza |>
|
|
filter(source=="kvk") |>
|
|
group_by(source) |>
|
|
left_join(deposits1, by="contract_account") |>
|
|
mutate(deposit=ifelse(is.na(deposit),0,deposit)) |>
|
|
mutate(recovery_possible=
|
|
ifelse(amount==0,0, ifelse(amount+deposit < 0, amount, deposit))
|
|
)
|
|
sum(te$deposit)
|
|
sum(te$recovery_possible)
|
|
|
|
oi |>
|
|
filter(e.status == "Passed") |>
|
|
group_by(rel.status) |>
|
|
summarise(amount=sum(amount))
|
|
```
|
|
|
|
|
|
|
|
|
|
|