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?
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
Last edited: