Reverse engineer prices in an Insurance Plan

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,654
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
5PlanHealthDentalPrescriptionsCommentParameters
6A
7B
8C
9
10SelectedHealthDentalPrescriptionsHealthDentalPrescriptionsPrice
11AAA69.50
12AAB71.90
13ABA80.90
14ABB83.30
15BBB102.40
16BAB91.00
17BBA100.00
18BAA88.60
19BBC118.90
20BCB132.80
21BCC149.30
22CBB114.90
23CBC131.40
24CCB145.30
25CCC161.80
26AAC88.40
27ABC99.80
28ACB113.70
29ACC130.20
30BAC107.50
31BCA130.40
32BCC149.30
33CAB103.50
34CBA112.50
35CAA101.10
36CBB114.90
37CCA142.90
Sheet2
[/FONT]
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
Re: Reverse engineer prices an an Insurance Plan

hope it will help

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Price[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
69.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]A[/td][td]A[/td][td]
88.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
101.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]B[/td][td]A[/td][td]
80.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]B[/td][td]A[/td][td]
112.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#FFFF00][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]C[/td][td]A[/td][td]
130.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
142.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]A[/td][td]B[/td][td]
71.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]
91​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]A[/td][td]B[/td][td]
103.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]
83.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]B[/td][td]B[/td][td]
102.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]
114.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]C[/td][td]B[/td][td]
113.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]
132.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]C[/td][td]B[/td][td]
145.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
88.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]A[/td][td]C[/td][td]
107.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#FFFF00][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]B[/td][td]C[/td][td]
99.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
118.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]B[/td][td]C[/td][td]
131.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
130.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]C[/td][td]C[/td][td]
149.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
161.8​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
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)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Plan[/td][td=bgcolor:#70AD47]Health[/td][td=bgcolor:#70AD47]Dental[/td][td=bgcolor:#70AD47]Prescription[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
737.7​
[/td][td=bgcolor:#E2EFDA]
721.5​
[/td][td=bgcolor:#E2EFDA]
825.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
1020.9​
[/td][td]
944.1​
[/td][td]
958.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
1013.4​
[/td][td=bgcolor:#E2EFDA]
1106.4​
[/td][td=bgcolor:#E2EFDA]
987.3​
[/td][/tr]
[/table]
 
Upvote 0
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
ABCDEFGH
5PlanHealthDentalPrescriptions
6A40.3524.085.07
7B59.4535.487.47
8C71.9565.8823.97
9
10SelectedHealthDentalPrescriptionsHealthDentalPrescriptionsPrice
11AAA40.3524.085.0769.50
12AAB40.3524.087.4771.90
13AAC40.3524.0823.9788.40
14ABA40.3535.485.0780.90
15ABB40.3535.487.4783.30
16ABC40.3535.4823.9799.80
17ACA40.3565.885.07111.30
18ACB40.3565.887.47113.70
19ACC40.3565.8823.97130.20
20BAA59.4524.085.0788.60
21BAB59.4524.087.4791.00
22BAC59.4524.0823.97107.50
23DRSBBA59.4535.485.07100.00
24BBB59.4535.487.47102.40
25BBC59.4535.4823.97118.90
26BCA59.4565.885.07130.40
27BCB59.4565.887.47132.80
28BCC59.4565.8823.97149.30
29CAA71.9524.085.07101.10
30CAB71.9524.087.47103.50
31CAC71.9524.0823.97120.00
32CBA71.9535.485.07112.50
33CBB71.9535.487.47114.90
34CBC71.9535.4823.97131.40
35CCA71.9565.885.07142.90
36CCB71.9565.887.47145.30
37CCC71.9565.8823.97161.80
Sheet1
Cell Formulas
RangeFormula
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
JKLMNOPQ
10Magic inferrence engine
11A to BChange%of total ChangeInferred PriceAknown pricesHDP
12Health19.1058.0547%40.34802$69.50AAA
13Dental11.4034.6505%24.08207$88.60BAA
14Prescriptions2.407.2948%5.06991$80.90ABA
1532.90$71.90AAB
16
17B to CChange
18Health12.50$102.40BBB
19Dental30.40$114.90CBB
20Prescriptions16.50$132.80BCB
2159.40$118.90BBC
22
23
Sheet1
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0
Re: Reverse engineer prices an an Insurance Plan

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

have a good day
 
Upvote 0
Re: Reverse engineer prices an an Insurance Plan

Thanks very much for you help, Sandy. You have a good day too.
 
Upvote 0
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?
 
Upvote 0
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.]
 
Upvote 0
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.

XziA6cX.jpg


Sheet1

FGHIJ

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="colspan: 4, align: center"]This table is the Solver solution[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]Column1[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]P[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: center"]Formulas[/TD]
[TD="align: center"]Desired values[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: center"]75[/TD]
[TD="align: center"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: center"]87[/TD]
[TD="align: center"]87[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: center"]65[/TD]
[TD="align: center"]65[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: center"]69[/TD]
[TD="align: center"]69[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: center"]79[/TD]
[TD="align: center"]79[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: center"]70[/TD]
[TD="align: center"]70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="align: center"]78[/TD]
[TD="align: center"]78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="align: center"]83[/TD]
[TD="align: center"]83[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="align: center"]74[/TD]
[TD="align: center"]74[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="align: center"]75[/TD]
[TD="align: center"]75[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top