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]

 

[joeu2004]

Re: Reverse engineer prices an an Insurance Plan

FYI, you have BCC twice (rows 21 and 32), and you are missing ACA. Unless ACA is also 149.30, that will affect the correctness of any solution.

Also, IMHO, it would be better to do a 3-level sort in the order of columns B, C and D. Thus, all 9 H-A are together, followed by all 9 H-B, followed by all 9 H-C. I don't know if that makes a solution any easier. It just follows the more natural way to generate all permutations, namely: "increment" column D (A, B, C), then column C, then column B; the same way that we increment numbers (lowest digit to highest).

Off-hand, I do not have a methodology for a solution. I suspect it is a matrix method, but I'm afraid I've forgotten that. Perhaps your matrix solution will work when you correct the BCC error.

 

[sandy666]

Re: Reverse engineer prices an an Insurance Plan

hope it will help

Column1	Custom	Custom.1	Price
A	A	A	69.5​
B	A	A	88.6​
C	A	A	101.1​
A	B	A	80.9​
B	B	A	100​
C	B	A	112.5​
A	C	A
B	C	A	130.4​
C	C	A	142.9​
A	A	B	71.9​
B	A	B	91​
C	A	B	103.5​
A	B	B	83.3​
B	B	B	102.4​
C	B	B	114.9​
A	C	B	113.7​
B	C	B	132.8​
C	C	B	145.3​
A	A	C	88.4​
B	A	C	107.5​
C	A	C
A	B	C	99.8​
B	B	C	118.9​
C	B	C	131.4​
A	C	C	130.2​
B	C	C	149.3​
C	C	C	161.8​

 

[sandy666]

Re: Reverse engineer prices an an Insurance Plan

is that what you want?

(table doesn't contain prices from yellow cells above because they doesn't exist in source)

Plan	Health	Dental	Prescription
A	737.7​	721.5​	825.9​
B	1020.9​	944.1​	958.8​
C	1013.4​	1106.4​	987.3​

 

[DRSteele]

Re: Reverse engineer prices an an Insurance Plan

Thank you both for your responses. Excuse my large set of tables here.

I reorganised things properly so that the columns cascade in a proper manner and so that all permutations are covered and unique. ACA was missing and BBC was repeated. [Sorry about that. I don't know what happened to the formula cell references when I pasted here.]

Sandy, I don't really understand your suggested prices. They are all in the hundred of dollars and far exceed the known total prices.

I tried this method to infer the prices by linear interpolation. I calculated the differences between levels A and B for each H, D and P. Then I calculated the proportion each of those contributed to the overall change. I then applied that proportion to the total price to arrive at an inferred price. So to get Health, AAA is 69.50 and BAA is 88.60, so the change is 19.10. For Dental, ABA is 80.90, less AAA 69.50 gives 11.40. And Prescriptions, AAB is 71.90, less AAA 69.50 gives 2.40. Summing the 19.10+11.40+2.40 = 32.90, and thus their weights are 58%, 35% and 7%. Apply those to 69.50 and that means (I hope) HA is 40.35, DA 24.08, and PA 5.07. I added the changes for B to C and came up with the entire price grid.

Is this sensible?

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

Cell Formulas
Range	Formula
B6	=M12
B7	=B6+K12
B8	=B7+K18
C6	=M13
C7	=C6+K13
C8	=C7+K19
D6	=M14
D7	=D6+K14
D8	=D7+K20
E11	=VLOOKUP(B11,$A$6:$D$8,COLUMNS($B$6:B6)+1,0)
F11	=VLOOKUP(C11,$A$6:$D$8,COLUMNS($B$6:C6)+1,0)
G11	=VLOOKUP(D11,$A$6:$D$8,COLUMNS($B$6:D6)+1,0)
H11	=SUM(E11:G11)

Book1
	J	K	L	M	N	O	P	Q
10	Magic inferrence engine
11	A to B	Change	%of total Change	Inferred PriceA	known prices	H	D	P
12	Health	19.10	58.0547%	40.34802	$69.50	A	A	A
13	Dental	11.40	34.6505%	24.08207	$88.60	B	A	A
14	Prescriptions	2.40	7.2948%	5.06991	$80.90	A	B	A
15		32.90			$71.90	A	A	B
16
17	B to C	Change
18	Health	12.50			$102.40	B	B	B
19	Dental	30.40			$114.90	C	B	B
20	Prescriptions	16.50			$132.80	B	C	B
21		59.40			$118.90	B	B	C
22
23
Sheet1

Cell Formulas
Range	Formula
K12	=N13-N12
K13	=N14-N12
K14	=N15-N12
K15	=SUM(K12:K14)
K18	=N19-N18
K19	=N20-N18
K20	=N21-N18
K21	=SUM(K18:K20)
L12	=K12/$K$15
L13	=K13/$K$15
L14	=K14/$K$15
M12	=$N$12*L12
M13	=$N$12*L13
M14	=$N$12*L14

 

[sandy666]

Re: Reverse engineer prices an an Insurance Plan

so I misunderstood your way of calculating prices - it was not clear

have a good day

 

[DRSteele]

Re: Reverse engineer prices an an Insurance Plan

Thanks very much for you help, Sandy. You have a good day too.

 

[Worf]

Re: Reverse engineer prices an an Insurance Plan

Dr Steele

At a quick glance, this is a nine variable problem, suitable for Solver, at the Data tab. We would need nine independent equations.
Have you ever used it before?

 

[DRSteele]

Re: Reverse engineer prices an an Insurance Plan

Worf, thanks for you reply. I have used it. What do you suggest for the equations? [I wish we could just easily attach screenshots in this forum so we wouldn't be forced to type out long prose.]

 

[Worf]

Re: Reverse engineer prices an an Insurance Plan

• The following example should give you a good idea of the method I am proposing. I got lazy and did not include all possible permutations.
• Remember that a set of equations may have multiple solutions or no solution at all.
• Use the constraints to restrict the solution space. Note that I created nine named ranges to make it more readable.
• I am posting a Portuguese version of the Solver dialog box, but it is easy to compare with the English version.
• Feel free to ask if you have any doubts.
• Imgur hosts my screenshot.

Sheet1

	F	G	H	I	J
4	This table is the Solver solution
5	Column1	H	D	P
6	A	50	5	5
7	B	55	10	10
8	C	60	13	14
9
10				Formulas	Desired values
11				60	60
12				75	75
13				87	87
14				65	65
15				69	69
16				79	79
17				70	70
18				78	78
19				83	83
20				74	74
21				75	75

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:94px;"><col style="width:64px;"><col style="width:64px;"><col style="width:101px;"><col style="width:133px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I11 =ha+da+pa I12 =hb+db+pb I13 =hc+dc+pc I14 =ha+da+pb I15 =ha+da+pc I16 =hb+db+pc I17 =hb+db+pa I18 =hc+dc+pa I19 =hc+dc+pb I20 =ha+db+pc I21 =hc+db+pa <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4