Flaw of average (FOA) – Stochastic Model in Excel

So far, we used deterministic approach in my previous blogs, e.g. we specify the age that we want to retire, how many years of survival or the annual incremental rates.  These values are all predetermined; we can add more scenarios but all the scenarios are still determined by us.  These values may be our best estimate and most probable to happen.  However, there may be situations that are very rate but the consequence can be huge, e.g. 2008 financial market meltdown.  This is why we should consider stochastic model.

What is stochastic model?  There are many definitions but generally involve random variables.  Let me use a very simple example to illustrate.  Let’s say we want to estimate the cost of dental claims for a new client who have 2,500 employees. We have dental expense records for 1 million employee and the average claim amount per employee per year is $7,256.00.  So it is simple and the estimate total for 2,500 employee would be $18,140,000.

As I mentioned in my previous blog, Excel to handle flaw of average (FOA) – Data Table, the problem of average is that 50% of the outcome is bigger and 50% is smaller.  50% of outcome can be bigger?  How much bigger? We may like to find out more about our estimation.  This is where stochastic model would help. 

Let’s first populate all 1 million dental claims in column B and use column A as an index (lookup) column.

imageimage

We would randomly select the claims amount by generating a random number between 1 to 1,000,000 and use VLOOKUP to retrieve the corresponding claim amount.

=VLOOKUP(RANDBETWEEN(1, 1000000),$A$1:$B$1000000, 2)

Then we generate 2,500 records for our first scenario and the total claim is $18,467,408.17.

image

We repeat the same procedures to randomly generate 200 scenarios.

image

Now we have a distribution of total claim costs, then we can use Excel Percentile function to determine the percentile of of the total claim costs distributions.

=PERCENTILE($H$2503:$GY$2503,I2505/100)

image

The total claim costs at 50th percentile is $18,104,248 which is very close to our total claim costs based on the average claim ($18,140,000).  This distribution give us a better understanding on what would happen, e.g. there is only 1% chance that the total claim cost would be bigger than $19,698,418.

This is a very simple stochastic model, we may want to consider age, gender, family size, location or even use a more robust random generator when we develop a real life stochastic model.  However, I just want to illustrate what a stochastic model is and demonstrate how we can build a stochastic model in Excel.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: