Deposit Offset Calculation

Steps

A list of all deposits was prepared.

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.

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.

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.

depos.aggr|>
  group_by(status.deposit) |> 
  summarise(deposit.sum=sum(deposit, na.rm=T))

## # A tibble: 2 × 2
##   status.deposit deposit.sum
##   <chr>                <dbl>
## 1 Deposit Exists    -995338.
## 2 No Deposit              0

List of rows/contracts for which deposits were not found