Predicting a number

[noobythebad]

Hello,

So I've been tasked with an assignment at work that involves me having to figure out how to estimate what we will be paid from a claim using previous payments. Is there any formula that will allow me to predict a future number fairly accurately from a group of previous numbers?

 

[mmm4444bot]

how to estimate what we will be paid from a claim using previous payments

Hello noobythebad. Your request is too vague (as worded) because there are many variations on claim payments, and previous payments may affect future payments in more than one possible way.

Are you able to describe a specific scenario? Tutors will need a starting point. Thanks.

?

 

[noobythebad]

Hello noobythebad. Your request is too vague (as worded) because there are many variations on claim payments, and previous payments may affect future payments in more than one possible way.

Are you able to describe a specific scenario? Tutors will need a starting point. Thanks.

?

Hello mmm4444bot, thank you for your reply. I will try to be as detailed as possible. So were a medical billing company and we track all of our patient claims on an excel spreadsheet. The image below is how our main estimate report looks, but it is so much information so I condensed the pertinent information to a separate excel spreadsheet in order to calculate estimates.

On this separate excel spreadsheet (seen below) I wrote a macro for it to filter through all of the data. The first image is the data I pull directly from the main estimate report that is condensed, and the second image is after the macro filters through the data and calculates an estimate.


The current way I calculate an estimate for a specific insurance/member is I take all of the associated claims that have a similar "Per Diem Allowed Amount" and total them up, and use the "Per Diem Allowed Amount" that appears the most. For example, Row 4/Prefix 968/DTX, out of 11 claims between 2 different patients $1,092 was paid the most times out of those 11 claims. I calculate the "AA%" the exact same way. So out of 11 claims between 2 different patients 21% occurred the most. I also added an average column to see if the average of the numbers for the claims would be closer to calculating the next possible payment we would receive for that specific insurance/member. So basically, out of a specific set of information, like the image below, I would like to be able to estimate our next payment within a few hundred dollars or 1%-2%. Would there be any formula that would allow me to do this? Please let me know if I need to go into more detail or provide you with more examples. I can also hop on zoom or skype if that would help. Thank you!

 

[BigBeachBanana]

Hello,

So I've been tasked with an assignment at work that involves me having to figure out how to estimate what we will be paid from a claim using previous payments. Is there any formula that will allow me to predict a future number fairly accurately from a group of previous numbers?

It sounds like a job for an actuary.
Not sure how experienced you are with modeling and programming, but I can offer a few options:
1) Use Bayesian Inference (make an assumption about the distribution of your current claims and update your model with new experience)
2) Predictive model: Linear-Regression, Generalized Linear Model, Logistic-Regression, Decision Tree, Random Forest
3) Loss reserving methods: Expect-Loss Ratio Method, Chain-Ladder Method, Bornhuetter-Ferguson Method
Also, you need to consider whether you have sufficient data to provide full credibility; if not might need to blend external experience using Credibility Theory. Some common theories are Bühlmann's Credibility and the Square-Root rules.

Note: There isn't one correct way to model your claims. It is up to the analyst to determine/justify the method that they're using