Reverse engineer prices in an Insurance Plan

[DRSteele]

Can some math genius please help me?

I wish to reckon the base prices for extended healthcare plan. There are three classes in the plans: Health, Dental and Prescriptions.

Each of those three classes has three available variables: A, B and C. ​

By clicking through the co's website, I was able to come up with the various prices for all possible permutations of the various plans. [It only takes seven prices to compute the changes between the plans, so I didn't click through all 3^3=27 of them.]

So we know the total prices for each of the 3^3=27 permutations. I wish to know the prices for each of the base plans (i.e., H-A, D-A, P-A)

​

Once those are known the rest falls into place. Right? or am I missing something? I tried matrix algebra and I became sorely vexed.


Here is the data. I hope to be able to fill out all the purple stuff.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H
5	Plan	Health	Dental	Prescriptions	Comment	Parameters
6	A
7	B
8	C
9
10	Selected	Health	Dental	Prescriptions	Health	Dental	Prescriptions	Price
11		A	A	A				69.50
12		A	A	B				71.90
13		A	B	A				80.90
14		A	B	B				83.30
15		B	B	B				102.40
16		B	A	B				91.00
17		B	B	A				100.00
18		B	A	A				88.60
19		B	B	C				118.90
20		B	C	B				132.80
21		B	C	C				149.30
22		C	B	B				114.90
23		C	B	C				131.40
24		C	C	B				145.30
25		C	C	C				161.80
26		A	A	C				88.40
27		A	B	C				99.80
28		A	C	B				113.70
29		A	C	C				130.20
30		B	A	C				107.50
31		B	C	A				130.40
32		B	C	C				149.30
33		C	A	B				103.50
34		C	B	A				112.50
35		C	A	A				101.10
36		C	B	B				114.90
37		C	C	A				142.90
Sheet2

[/FONT]

 

[DRSteele]

Re: Reverse engineer prices an an Insurance Plan

Obrigado por me ajudar com isso. (Bing translator - ha!)

I don't understand the results in G6. We know that AAA must total 69.50, not 50+5+5=60, and BBB must be 102.40, not 55+10+10=75. The same goes for all the others in the grid. All the Totals for each permutation (Price in H10+ in my grid) are known values.

I suspect that Solver cannot solve this.

 

[Worf]

Re: Reverse engineer prices an an Insurance Plan

That was just an example. I made up the desired values column, which are known, and Solver found a solution, note that the formulas and values columns are the same.
You have to plug in the actual numbers there. Can you do that or would you prefer that I update the example?

 

[joeu2004]

Re: Reverse engineer prices an an Insurance Plan

(Sorry for the late response. I'm recovering from major surgery, and my attention span has been limited.)

I suspect that Solver cannot solve this.

Actually, it can. I agree that Worf's LP model does not make sense. But it is true: with a correct LP model (and even with Worf's), Solver can find many solutions using the Simplex LP method. But as I demonstrate below, it is more work than necessary.

I also don't believe your method (allocating according to percentage differences) makes much sense. Arguably, it's not wrong. It's just arbitrary. The fact is: the percentage differences are constant, regardless of the solution -- as you proved.

In fact, as I demonstrate below, there is an "infinite" number of solutions. Actually, the number of solutions is limited by the 2-decimal-place precision of the solutions.

Your approach of looking at the differences of total costs is good. But I think you over-complicated it.

We can use the BAA-AAA and CAA-BAA to derive the incremental differences between HA, HB and HC. Similarly, ABA-AAA and ACA-ABA derive the incremental differences between DA, DB and DC; and AAB-AAA and AAC-AAB derive the incremental differences between PA, PB and PC.

(Of course, we would use BAA-AAA and CAA-AAA to derive differences from AAA instead of incremental differences. But the latter information might be of some use to you.)

Then, we can allocate the total cost of AAA among HA, DA and PA randomly (!). Yes, 58%, 35% and 7% are possible. But any other percentage allocation is equally possible.

The following is one implementation. Press f9 repeatedly to see many solutions. Note that the relationships among HA, DA and PA can vary arbitrarily. For example, HA<=DA and HA>=DA are both possible.

(FYI, I have had trouble posting Excel HTML to this forum. I don't know what my problem is. Y'all do so much better than I.)

	A	B	C	D	E	F	G	H	I	J	K
5		H	D	P		Random:			Random Sum:
6	A	10.45	33.82	25.23		0.306548	0.992216	0.740324	2.039088
7	B	29.55	45.22	27.63
8	C	42.05	75.62	44.13
9
10	Orig Row#	H	D	P	H	D	P	Orig H+D+P	Rand H+D+P	Orig =Rand?	#Orig =Rand
11	11	A	A	A	10.45	33.82	25.23	69.50	69.50	TRUE	27
12	12	A	A	B	10.45	33.82	27.63	71.90	71.90	TRUE
13	26	A	A	C	10.45	33.82	44.13	88.40	88.40	TRUE
14	13	A	B	A	10.45	45.22	25.23	80.90	80.90	TRUE
15	14	A	B	B	10.45	45.22	27.63	83.30	83.30	TRUE
16	27	A	B	C	10.45	45.22	44.13	99.80	99.80	TRUE
17	32	A	C	A	10.45	75.62	25.23	111.30	111.30	TRUE
18	28	A	C	B	10.45	75.62	27.63	113.70	113.70	TRUE
19	29	A	C	C	10.45	75.62	44.13	130.20	130.20	TRUE
20	18	B	A	A	29.55	33.82	25.23	88.60	88.60	TRUE
21	16	B	A	B	29.55	33.82	27.63	91.00	91.00	TRUE
22	30	B	A	C	29.55	33.82	44.13	107.50	107.50	TRUE
23	17	B	B	A	29.55	45.22	25.23	100.00	100.00	TRUE
24	15	B	B	B	29.55	45.22	27.63	102.40	102.40	TRUE
25	19	B	B	C	29.55	45.22	44.13	118.90	118.90	TRUE
26	31	B	C	A	29.55	75.62	25.23	130.40	130.40	TRUE
27	20	B	C	B	29.55	75.62	27.63	132.80	132.80	TRUE
28	21	B	C	C	29.55	75.62	44.13	149.30	149.30	TRUE
29	35	C	A	A	42.05	33.82	25.23	101.10	101.10	TRUE
30	33	C	A	B	42.05	33.82	27.63	103.50	103.50	TRUE
31	36	C	A	C	42.05	33.82	44.13	120.00	120.00	TRUE
32	34	C	B	A	42.05	45.22	25.23	112.50	112.50	TRUE
33	22	C	B	B	42.05	45.22	27.63	114.90	114.90	TRUE
34	23	C	B	C	42.05	45.22	44.13	131.40	131.40	TRUE
35	37	C	C	A	42.05	75.62	25.23	142.90	142.90	TRUE
36	24	C	C	B	42.05	75.62	27.63	145.30	145.30	TRUE
37	25	C	C	C	42.05	75.62	44.13	161.80	161.80	TRUE

<tbody>
</tbody>

Formulas:
F6:H6: =RAND()
I6:    =SUM(F6:H6)
B6:    =ROUND($H$11*F6/$I$6,2)
C6:    =ROUND($H$11*G6/$I$6,2)
D6:    =ROUND(H11-SUM(B6:C6),2)
B7:    =ROUND(B6+(H20-H11),2)
C7:    =ROUND(C6+(H14-H11),2)
D7:    =ROUND(D6+(H12-H11),2)
B8:    =ROUND(B7+(H29-H20),2)
C8:    =ROUND(C7+(H17-H14),2)
D8:    =ROUND(D7+(H13-H12),2)
E11:   =INDEX($B$6:$D$8,MATCH(B11,$A$6:$A$8,0),COLUMNS($E$10:E10))
I11:   =ROUND(SUM(E11:G11),2)
J11:   =I11=H11
K11:   =COUNTIF(J11:J37,TRUE)
Copy E11 not E11:G37
Copy I11 into I12:J37

(A10:A37 are vestigial and unnecessary. They refer to your original posting, which you corrected later. I incorporated your corrections for ACA and CAC.)

The individual costs of HA, DA, PA are determined by the random ratios of F6/I6, G6/I6 and H6/I6, where I6 = F6+G6+H6, times the total cost of AAA.

The individual costs of HB, DB, PB and HC, DC, PC are derived directly from HA, DA, PA based on the constant differences derived from the differences of the total costs, as described above.

I hope that helps.

 

[joeu2004]

Re: Reverse engineer prices an an Insurance Plan

To late to edit....

E11: =INDEX($B$6:$D$8,MATCH(B11,$A$6:$A$8,0),COLUMNS($E$10:E10))

KISS:

=VLOOKUP(B11,$A$6:$D$8,1+COLUMNS($E$10:E10),0)

 

[joeu2004]

Re: Reverse engineer prices an an Insurance Plan

Some other typos, "to late" [sic] to edit (sigh)....

Copy E11 into E11:G37
Copy I11:J11 into I12:J37

 

[shg]

If you know the price of any two items not in the same class (H & D, H & P, D & P), you can get an exact solution by solving as an overdetermined system:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

1​

H​

D​

P​

Price​

HA​

HB​

HC​

DA​

DB​

DC​

PA​

PB​

PC​

2​

HA​

DA​

PA​

69.50​

1​

0​

0​

1​

0​

0​

1​

0​

0​

HA​

20.0000​

F2: =COUNTIF($A2:$C2, F$1)

3​

HA​

DA​

PB​

71.90​

1​

0​

0​

1​

0​

0​

0​

1​

0​

HB​

39.1000​

Q2:Q10: {=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(F2:N30), F2:N30)), TRANSPOSE(F2:N30)), D2:D30)}

4​

HA​

DA​

PC​

88.40​

1​

0​

0​

1​

0​

0​

0​

0​

1​

HC​

51.6000​

5​

HA​

DB​

PA​

80.90​

1​

0​

0​

0​

1​

0​

1​

0​

0​

DA​

20.0000​

6​

HA​

DB​

PB​

83.30​

1​

0​

0​

0​

1​

0​

0​

1​

0​

DB​

31.4000​

7​

HA​

DB​

PC​

99.80​

1​

0​

0​

0​

1​

0​

0​

0​

1​

DC​

61.8000​

8​

HA​

DC​

PA​

111.30​

1​

0​

0​

0​

0​

1​

1​

0​

0​

PA​

29.5000​

9​

HA​

DC​

PB​

113.70​

1​

0​

0​

0​

0​

1​

0​

1​

0​

PB​

31.9000​

10​

HA​

DC​

PC​

130.20​

1​

0​

0​

0​

0​

1​

0​

0​

1​

PC​

48.4000​

11​

HB​

DA​

PA​

88.60​

0​

1​

0​

1​

0​

0​

1​

0​

0​

12​

HB​

DA​

PB​

91.00​

0​

1​

0​

1​

0​

0​

0​

1​

0​

13​

HB​

DA​

PC​

107.50​

0​

1​

0​

1​

0​

0​

0​

0​

1​

14​

HB​

DB​

PA​

100.00​

0​

1​

0​

0​

1​

0​

1​

0​

0​

15​

HB​

DB​

PB​

102.40​

0​

1​

0​

0​

1​

0​

0​

1​

0​

16​

HB​

DB​

PC​

118.90​

0​

1​

0​

0​

1​

0​

0​

0​

1​

17​

HB​

DC​

PA​

130.40​

0​

1​

0​

0​

0​

1​

1​

0​

0​

18​

HB​

DC​

PB​

132.80​

0​

1​

0​

0​

0​

1​

0​

1​

0​

19​

HB​

DC​

PC​

149.30​

0​

1​

0​

0​

0​

1​

0​

0​

1​

20​

HC​

DA​

PA​

101.10​

0​

0​

1​

1​

0​

0​

1​

0​

0​

21​

HC​

DA​

PB​

103.50​

0​

0​

1​

1​

0​

0​

0​

1​

0​

22​

HC​

DA​

PC​

120.00​

0​

0​

1​

1​

0​

0​

0​

0​

1​

23​

HC​

DB​

PA​

112.50​

0​

0​

1​

0​

1​

0​

1​

0​

0​

24​

HC​

DB​

PB​

114.90​

0​

0​

1​

0​

1​

0​

0​

1​

0​

25​

HC​

DB​

PC​

131.40​

0​

0​

1​

0​

1​

0​

0​

0​

1​

26​

HC​

DC​

PA​

142.90​

0​

0​

1​

0​

0​

1​

1​

0​

0​

27​

HC​

DC​

PB​

145.30​

0​

0​

1​

0​

0​

1​

0​

1​

0​

28​

HC​

DC​

PC​

161.80​

0​

0​

1​

0​

0​

1​

0​

0​

1​

29​

HA​

​

​

20.00​

1​

0​

0​

0​

0​

0​

0​

0​

0​

30​

​

DA​

​

20.00​

0​

0​

0​

1​

0​

0​

0​

0​

0​

 

[shg]

Or you could remove the redundant rows and solve it conventionally:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

1​

H​

D​

P​

Price​

HA​

HB​

HC​

DA​

DB​

DC​

PA​

PB​

PC​

2​

HA​

DA​

PA​

69.50​

1​

0​

0​

1​

0​

0​

1​

0​

0​

HA​

20.0000​

F2: =COUNTIF($A2:$C2, F$1)

3​

HA​

DA​

PB​

71.90​

1​

0​

0​

1​

0​

0​

0​

1​

0​

HB​

39.1000​

Q2:Q10: {=MMULT(MINVERSE(F2:N10), D2:D10)}

4​

HA​

DA​

PC​

88.40​

1​

0​

0​

1​

0​

0​

0​

0​

1​

HC​

51.6000​

5​

HA​

DB​

PA​

80.90​

1​

0​

0​

0​

1​

0​

1​

0​

0​

DA​

20.0000​

6​

HA​

DC​

PA​

111.30​

1​

0​

0​

0​

0​

1​

1​

0​

0​

DB​

31.4000​

7​

HB​

DA​

PA​

88.60​

0​

1​

0​

1​

0​

0​

1​

0​

0​

DC​

61.8000​

8​

HC​

DA​

PA​

101.10​

0​

0​

1​

1​

0​

0​

1​

0​

0​

PA​

29.5000​

9​

HA​

​

​

20.00​

1​

0​

0​

0​

0​

0​

0​

0​

0​

PB​

31.9000​

10​

​

DA​

​

20.00​

0​

0​

0​

1​

0​

0​

0​

0​

0​

PC​

48.4000​

 

[joeu2004]

If you know the price of any two items not in the same class (H & D, H & P, D & P) [....]

Or you could remove the redundant rows and solve it conventionally [....]

And if we know the price of all of the components, just enter them into B6:D8 of my table.

Ridiculous, of course! But once we start changing the parameters of the OP's problem, there is no limit to the changes that we can make in order to make the solution more tractable.

The point is: With the dearth of information, DRSteele thought there should be a single solution; he just wanted help to find out. I demonstrated that with the dearth of information, there is an "infinite" number of solutions; and I demonstrated how to see them, very simply I think.

And of course, once we add some additional constraints, we can reduce the problem to a single solution (or fewer solutions, at least).

Your solutions (to a very different problem) are "sexy", to say the least. I'm sure they appeal to people who like complicated one-solution-fits-all implementations.

(And I appreciate seeing your matrix implementations, because I struggled to find one of my own. It was similar to your design, but with just 9 equations with 9 unknowns. It had not occurred to me to change the problem to fit my solution.)

But if we're changing the problem, I prefer KISS.

If we know the price to two components (H, D, P) for any plan (A, B, C), the third is easily computable from the total cost of AAA, BBB or CCC. And the cost of the other components are easily derived from the difference relationships that I described, a simplification of DRSteele's approach.

In fact, if we know the price of two components, we probably know the price of the third. After all, the souce of the information is the insurance company; I cannot imagine why they would tell us two, but not three.

And if they do not tell us all nine component prices, the three that they do tell us are probably for the "base" plan. If that's A, just enter those numbers into B6:D6 of my table, and "Bob's yer uncle".

No need for 261 (now 81) helper cells and a lot of "magic" -- clever, that it be.

Oh well, to each his own.

 

[DRSteele]

Everyone, thanks for your deep thought, all your input and your immense amount of work.

Some of these methods arrive at conclusions which don't really match the reality of the plans. I happen to know that the majority of the total for each plan is H, then D and then (a distant third) P.

I suspected there is not a single solution and we have probably all arrived at that conclusion. I think there is no way for us to know with certainty the value of the base prices (those being AAA). I thought an estimate inferred from analysing the known changes between totals would suffice, but I see we are not convinced.

Whatever the case, the insurance company certainly doesn't want anyone to know how it arrives at it's pricing, especially its competitors! The actuarial estimates of risk outcomes set the prices (i.e., the premiums), and then the InsCo adds small, random factors randomly across plans and parameters (like age and Province) to set the public totals. They do this for the obvious reason that they don't want to declare their risk assessments of each health-related potential liability.

I'm afraid I've made a tempest in a teapot here. I was curious, is all.

Thanks again.

 

[joeu2004]

Some of these methods arrive at conclusions which don't really match the reality of the plans. I happen to know that the majority of the total for each plan is H, then D and then (a distant third) P.

I thought that, too. And I thought of ways to limit the random allocation to fit within some reasonable constraints. Not worth the trouble, at this point.

At the very least, with my model, you can just hardcode whatever allocation that you think is reasonable into F6:H6. Ideally, they will sum to 100%. But the formulas take care of the case when they don't, by mistake.

Think of it as sensitivity analysis.

I'm afraid I've made a tempest in a teapot here. I was curious, is all.

Not at all. Curosity is good, as is the journey to satisfy it.

BTW, I have an estimate for the "infinite" number of solutions, limited to rounding. If we round to 2 dp, there are 24 million. If we round to 1 dp (like the total costs), there are "only" 253 thousand. I derived those numbers empirically, not mathematically. They could be very wrong. But in both cases, the number seems countable. Again, not worth the trouble, at this point.