- #1
DianaCorine
- 2
- 0
Summary: I work in healthcare and I have to come up with a formula that will correctly reimburse members their out of pocket costs in the correct plan when they are enrolled in two or more plans following the rules below:
1. All of the transactions for Plan A will count towards Plan B limits.
2. I can only reimburse the money in the plan the transaction came in.
Below is my example:
Member A was enrolled in Plan A from January through March 2019 AND Plan B from April through May 2019. Both plans had different plan limits.
Plan A: $200 plan limit
Plan B: $400 plan limit
Transaction 1 came in 01/05/2019 for Plan A for $250, therefore the Plan A Cumulative Reimbursement so far is $50.
Transaction 2 came in 04/05/2019 for Plan B for $200, for some reason or another no transaction to reimburse Plan A has come through, so I still need to count the $250 towards plan Bs limit of $400. If I add $250 +$200=$450, that is $50 over my plan B limit and therefore the reimbursement due to my customer is $50 for Plan A and $50 for plan B.
There has to be some mathematical formula for this to work out, if someone can help me figure this out that would be great, i can then transalate it to an Excel formula. I have attached my spreadsheet so you can see the work I am doing and perhaps this will help. the formula would go into Column K (Reimbursement by Plan).
[Moderator's note: Moved from a technical forum and thus no template.]
1. All of the transactions for Plan A will count towards Plan B limits.
2. I can only reimburse the money in the plan the transaction came in.
Below is my example:
Member A was enrolled in Plan A from January through March 2019 AND Plan B from April through May 2019. Both plans had different plan limits.
Plan A: $200 plan limit
Plan B: $400 plan limit
Transaction 1 came in 01/05/2019 for Plan A for $250, therefore the Plan A Cumulative Reimbursement so far is $50.
Transaction 2 came in 04/05/2019 for Plan B for $200, for some reason or another no transaction to reimburse Plan A has come through, so I still need to count the $250 towards plan Bs limit of $400. If I add $250 +$200=$450, that is $50 over my plan B limit and therefore the reimbursement due to my customer is $50 for Plan A and $50 for plan B.
There has to be some mathematical formula for this to work out, if someone can help me figure this out that would be great, i can then transalate it to an Excel formula. I have attached my spreadsheet so you can see the work I am doing and perhaps this will help. the formula would go into Column K (Reimbursement by Plan).
[Moderator's note: Moved from a technical forum and thus no template.]
Last edited by a moderator:
