PMPM cost change from change in Utilization vs. Cost

randolphoralph

New member
Joined
Aug 23, 2018
Messages
4
I have a spreadsheet that calculates PerMember Per Month (PMPM) cost, and the change in PMPM cost from one time periodto another. Where I am encountering an issue is that I am trying to determinewhat portion of the PMPM change resulted from a change in utilization (visits)versus a change in cost. As an example a PMPM change of $0.65 could be theresult of -$0.81 Visit Change PMPM and $1.47 from Cost Change PMPM (PMPM Change= Visit Change PMPM + Cost Change PMPM).

In order to calculate the Visit ChangePMPM, and Cost Change PMPM I came up with the following formulas.

Cost ChangePMPM = ((Current Year Cost Per Visit-Last Year’sCost Per Visit) x This Year’s Number of Visits) /This Year’s Member Months

Visit ChangePMPM = (((Current Year # Visits – Current YearMember Months) – (Last Year # Visits – Last Year Member Months)) x Current YearMember Months) x Last Year’s Cost Per Visit) x (Last Year’s Cost PerVisit/Current Year Member Months)

The formula works as expected at the rowlevel, but when the formulas are used to calculate the Total Row (row 6) I donot get the result that I was expecting. I was expecting to get the sum of theVisit Change PMPM (column N) and Cost Change PMPM (column P), but the formulaproduces a different result than the sum of the column. The column total forVisit Change PMPM is ($6.13), but the calculation in cell N6 returns ($1.03).The column total for Cost Change PMPM is $2.73, but the calculation in cell P6returns ($2.36).

How can I alter the formula in order to achieve the results without calculating a sum of the column?
 

Attachments

  • Book_1.zip
    5.9 KB · Views: 6
Last edited:
I have a spreadsheet that calculates PerMember Per Month (PMPM) cost, and the change in PMPM cost from one time periodto another. Where I am encountering an issue is that I am trying to determinewhat portion of the PMPM change resulted from a change in utilization (visits)versus a change in cost. As an example a PMPM change of $0.65 could be theresult of -$0.81 Visit Change PMPM and $1.47 from Cost Change PMPM (PMPM Change= Visit Change PMPM + Cost Change PMPM)...

How can I alter the formula in order toachieve the results without calculating a sum of the column?
Just FYI: Most people with a basic knowledge of computer security will not open a ZIP file of unknown origin, nor a file which may included malicious macros (which, for all we know, are what is causing your errors).

Please make sure that all of the necessary information and specifics are posted openly, where the volunteers may safely access it. Thank you!
 


Not sure what happen to my original post. I went in toedit it and then saved the edited post and it disappeared.



I apologize about not providing all the necessaryinformation. I had zipped an excel worksheet thinking it would help anyonetrying to assist.


Here are the calculations I am using for each of thefields.


Location = Location of Office

2017 Member Months = Sum of each month’s eligiblemembers for CY2017




2018 Member Months = Sum of each month’s eligiblemembers for the period of time being evaluated in 2018 (Jan – Mar 2018)


2017 Visits = Total number of visits in 2017


2018 Visits = Total number of visits for theperiod of time being evaluated in 2018 (Jan – Mar 2018)


2017 Cost = Sum of cost for the eligiblemembers in 2017


2018 Cost = Sum of cost for the eligiblemembers for the time period being evaluated in 2018 (Jan – Mar 2018)


2017 Cost/Visit = 2017 Cost / 2017 Visits


2018 Cost/Visit = 2018 Cost / 2018 Visits


2017 PMPM = 2017 Cost / 2017 Member Months


2018 PMPM = 2018 Cost / 2018 Member Months


PMPM Δ = 2018 PMPM – 2017 PMPM


Visit Δ PMPM = (((2018 Visits – 2018 MemberMonths) – (2017 Visits – 2017 Member Months)) x 2018 Member Months) x 2017Cost/Visit) x (2017 Cost/Visit / 2018 Member Months)
Cost Change PMPM= ((2018 Cost/Visit– 2017 Cost/Visit) x 2018 Visits) / 2018 Member Months





Here is theoriginal post that got deleted:


I have a spreadsheet that calculates Per Member Per Month(PMPM) cost, and the change in PMPM cost from one time period to another. WhereI am encountering an issue is that I am trying to determine what portion of thePMPM change resulted from a change in utilization (visits) versus a change incost. As an example a PMPM change of $0.65 could be the result of -$0.81 VisitChange PMPM and $1.47 from Cost Change PMPM (PMPM Change= Visit Change PMPM +Cost Change PMPM).

In order tocalculate the Visit Change PMPM, and Cost Change PMPM I came up with thefollowing formulas.

Cost Change PMPM = ((CurrentYear Cost Per Visit-Last Year’s Cost Per Visit) x This Year’s Number of Visits)/This Year’s Member Months

Visit Change PMPM = (((CurrentYear # Visits – Current Year Member Months) – (Last Year # Visits – Last YearMember Months)) x Current Year Member Months) x Last Year’s Cost Per Visit) x(Last Year’s Cost Per Visit/Current Year Member Months)

The formulaworks as expected at the row level, but when the formulas are used to calculatethe Total Row (row 6) I do not get the result that I was expecting. I wasexpecting to get the sum of the Visit Change PMPM (column N) and Cost ChangePMPM (column P), but the formula produces a different result than the sum ofthe column. The column total for Visit Change PMPM is ($6.13), but thecalculation in cell N6 returns ($1.03).The column total for Cost Change PMPM is$2.73, but the calculation in cell P6returns ($2.36).

How can I alterthe formula in order to achieve the results without calculating a sum of thecolumn?
 

Attachments

  • Book_1.zip
    5.9 KB · Views: 4
  • Spreadsheet.JPG
    Spreadsheet.JPG
    107.6 KB · Views: 4
Visit ChangePMPM = (((Current Year # Visits – Current YearMember Months) – (Last Year # Visits – Last Year Member Months)) x Current YearMember Months) x Last Year’s Cost Per Visit) x (Last Year’s Cost PerVisit/Current Year Member Months)
That's quite unwieldy and hard to follow...
Plus the bracketing is faulty: 5 "(" and 6 ")"
Suggest you repost using single variables, like v = "Visit ChangePMPM"
 
I am not sure what you are asking. Let's try to get a convenient notation.

\(\displaystyle n_1 = \text { number of member months in period 1.}\)

\(\displaystyle n_2 = \text { number of member months in period 2.}\)

\(\displaystyle v_ 1 = \text { total number of visits in period 1.}\)

\(\displaystyle v_2 = \text { total number of visits in period 2.}\)

\(\displaystyle c_1 = \text { total cost in period 1.}\)

\(\displaystyle c_2 = \text { total cost in period 2.}\)

Now that may be more complex than we need, but at least now we can write formulas compactly.

\(\displaystyle \dfrac{c_1}{n_1}= \text { average cost per member month in period 1.}\)

\(\displaystyle \dfrac{c_2}{n_2} = \text { average cost per member month in period 2.}\)

\(\displaystyle \dfrac{c_1}{v_1} = \text { average cost per visit in period 1.}\)

\(\displaystyle \dfrac{c_2}{v_2} = \text { average cost per visit in period 2.}.\)

\(\displaystyle \dfrac{v_1}{n_1} = \text { average number of visits per member month in period 1.}\)

\(\displaystyle \dfrac{v_2}{n_2} = \text { average number of visits per member month in period 2.}\)

And you correctly see that

\(\displaystyle \dfrac{c_1}{v_1} * \dfrac{v_1}{n_1} = \dfrac{c_1}{n_1}.\)

\(\displaystyle \dfrac{c_2}{v_2} * \dfrac{v_2}{n_2} = \dfrac{c_2}{n_2}.\)

Am I sort of on the right track? If so let's talk about the deltas.
 
Last edited:
Thank you JeffM! You are definitely on the right track. With regard to the deltas here are the formulas that I am currently using.

The last two delta formulas work as expected at the individual location level, but do not produce the expected results at the total level.

The question I am trying to answer is how these two formulas could be changed to work at the total level?





 

Attachments

  • Formulas.JPG
    Formulas.JPG
    49.2 KB · Views: 5
Last edited:
Visit Δ PMPM = (((2018 Visits – 2018 Member Months) – (2017 Visits – 2017 Member Months))
x 2018 Member Months) x 2017Cost/Visit) x (2017 Cost/Visit / 2018 Member Months)
Bracketing is still erroneous...
Multiplication symbol should be * (x no longer used)
Cost/Visit would be clearer as CostperVisit (/ is division indicator)
 
This is a quick reply because I am in the middle of an ugly mess. I shall look more carefully tonight and give a more detailed answer then.

In general, there is a technical problem when working with deltas of a product.

\(\displaystyle \text {If } \gamma = \alpha \beta \text {, then } \Delta \gamma = (\alpha + \Delta \alpha) (\beta + \Delta \beta) - \alpha \beta =\)

\(\displaystyle \alpha \beta + \alpha \Delta \beta + \beta \Delta \alpha + \Delta \alpha \Delta \beta - \alpha \beta = \)

\(\displaystyle \alpha \Delta \beta +\beta \Delta \alpha + \Delta \alpha \Delta \beta.\)

BUT THAT DOES NOT EQUAL

\(\displaystyle \alpha \Delta \beta +\beta \Delta \alpha.\)

With relatively small deltas, you get a good approximation

\(\displaystyle \Delta \gamma \approx \alpha \Delta \beta +\beta \Delta \alpha.\)

But it is not exact. And the bigger the deltas get, the worse the approximation is.

It really becomes a presentation problem. There are various ways to handle it.
 
Last edited:
This is a quick reply because I am in the middle of an ugly mess. I shall look more carefully tonight and give a more detailed answer then.

In general, there is a technical problem when working with deltas of a product.

\(\displaystyle \text {If } \gamma = \alpha \beta \text {, then } \Delta \gamma = (\alpha + \Delta \alpha) (\beta + \Delta \beta) - \alpha \beta =\)

\(\displaystyle \alpha \beta + \alpha \Delta \beta + \beta \Delta \alpha + \Delta \alpha \Delta \beta - \alpha \beta = \)

\(\displaystyle \alpha \Delta \beta +\beta \Delta \alpha + \Delta \alpha \Delta \beta.\)

BUT THAT DOES NOT EQUAL

\(\displaystyle \alpha \Delta \beta +\beta \Delta \alpha.\)

With relatively small deltas, you get a good approximation

\(\displaystyle \Delta \gamma \approx \alpha \Delta \beta +\beta \Delta \alpha.\)

But it is not exact. And the bigger the deltas get, the worse the approximation is.

It really becomes a presentation problem. There are various ways to handle it.
OK. I won't have to deal with lawyers again until Monday.

I hope you understand the math above. The basic idea is this:

\(\displaystyle \alpha \Delta \beta\) measures what

\(\displaystyle \Delta \gamma\) would have been if \(\displaystyle \alpha\) had not changed.

\(\displaystyle \beta \Delta \alpha\) measures what

\(\displaystyle \Delta \gamma\) would have been if \(\displaystyle \beta\) had not changed.

But except in unusual cases both \(\displaystyle \alpha\) and \(\displaystyle \beta\) change.

In that case, \(\displaystyle \alpha \Delta \beta + \beta \Delta \alpha\) CANNOT measure what actually happened.

\(\displaystyle \Delta \alpha \Delta \beta\) measures the correction needed if both change.

So you basically have two choices on how to proceed.

One is to ignore the needed correction term, disclose that what you are presenting is an approximation, and hope nobody wants all this explained.

The other is to calculate all the deltas independently and breakdown the overall delta into its three true components. You then can label things along the lines of "change due only to alpha," "change due only to beta," "change due to interactions."

Any help?
 
Top