[AccessD] My Excel project...you won't believe this one

Darryl Collins darryl at whittleconsulting.com.au
Thu Dec 22 17:36:46 CST 2011


Hi Mark,

They had four risk bands (1-4) with each band having a heavier weighting to compensate for the higher risk.  There were also four questions (the matching 4's with the questions and banding is a coincidence and not related at all - althought the 4 questions within each combobox is related to the risk banding scale).

In this instance it related to property insurance (mainly for fire risk) so the questions were about type of construction, roof,  interior and how old the wiring and plumbing was.  You get the idea. - it was fairly high level stuff.

The maths worked like this.  Each of the 4 questions had 4 possible responses.  The 4 responses were scaled from 1 to 4 depending on how risky they were.  The sum of the four responses were divided by 4 to give a risk band rating.  As the division can result in non-integers the value needed to be rounded up or down to the nearest whole to give the correct rating.

For example.  If the responses were
1
1
2
3
(Total of 7)
7/4 = 1.75

So this customer should be paying the rate from risk band 2.  However the code was doing this

INT(7/4) to make the integer which would always return a 1 regardless.  The only time you would ever get a 2 rating was if the result was > than 2 already.  So they were underquoting on their risk for many of their clients.  This was really bad for when high level rating 3 types were not being rated as 4 (some 4's would be a decline, but they would have taken on the risk as a 3 would be returned).

Had a fun one at an oil company with rounding and zeros too.  Story for another day.

Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Friday, 23 December 2011 10:06 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] My Excel project...you won't believe this one

Omigosh, this one is a great story for the EUSprig group...they track all business-related spreadsheet disasters that have a financial consequence. So let me get this straight, their scaling went like 1.0, 1.5, 2.0, etc ?
So that required a special rounding function....rounding to the nearest 0.5.
So 1.26 would go to 1.5, 1.75 would go to 2.0....correct ?

The issue was a series of 4 questions would return a value based on a risk matrix.  So you would have a value like 1.99 or 1.67 and it would all be regrouped as a 1.  They were wondering why their rating engine were returning screwy results (it was basically a risk scale so anything with a risk rating >= 1.5 should have been priced using risk band 2 for example).
Given that this data was used to price customer quotes based on their risk profiles they were underquoting consistently until I spotted the error



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com






More information about the AccessD mailing list