Using information from the Intro tab and the example cases highlighted in green on each subsequent tab (Aetna, Blue Cross, and Cigna), please fill in the highlighted yellow cells for each payer. Hint - remember that reimbursement methodologies will be unique to each payer/account type and will tie to either: 1) total charges (Column G), 2) length of stay (Column F), or 3) service-based case rates (Columns L/M).

After completing the calculations required from Step 1, Step 2 is to draw observations from the entire data set given. Please also provide commentary on each underpayment discovered.

I need help calculating the:

Total Charges

Expected Payment

Potential Underpayment

Rate Type

A B Payer Account 2 Aetna 3 Aetna 4 Aetna 5 Aetna 6 Aetna 7 Aetna 8 Aetna 9 Aetna 10 Aetna 11 Aetna 12 Aetna 13 Aetna Account Type 1 IP 2 OP 2 IP 3 IP 4 IP 5 IP 7 IP 8 OP 9 OP 10 OP 11 OP 12 OP E Admit Disch 8/15/2017 8/22/2017 7/15/2017 7/16/2017 1/20/2017 1/31/2017 2/25/2017 4/25/2017 3/12/2017 3/14/2017 3/23/2017 5/8/2017 4/1/2017 4/10/2017 7/15/2017 7/16/2017 9/1/2017 9/2/2017 9/21/2017 9/23/2017 9/24/2017 9/25/2017 2/18/2017 2/20/2017 G Total Charges 7 $ 210,237.79 $ 1 $ 8,453.98 $ 11 $ 433,673.04 59 $1,027,205.19 2 S 18,403.22 46 $1,099,386.58 9 1$ 8,453.98 1$ 7,432.14 2$ 12,897.32 1$ 9,892.43 2$ 10,213.89 Length of Stay Expected Actual Potential Payment Payment Underpayment 84,095.12 $76,198.61 $ 3,500.00 $ 3,500.00 $ $ 173,469.22 $ 410,882.08 $ 3,500.00 $ 439,754.63 $ 132,081.02 $ 132,081.02 $ $ $ 3,500.00 $ $ 2,900.00 $ 3,500.00 K % Paid DRG 7,896.51 36% 41% 40% 40% 19% 40% #DIV/0! 0% 47% 0% 29% 34% M N Procedure Description Rate Type (General) 473 Spinal Surgery N/A Observation 454 Spinal Surgery 455 Spinal Surgery 473 Spinal Surgery 455 Spinal Surgery 460 Spinal Surgery N/A Observation N/A Observation N/A Observation N/A Observation N/A Observation A B Payer Account 2 Aetna 3 Aetna 4 Aetna 5 Aetna 6 Aetna 7 Aetna 8 Aetna 9 Aetna 10 Aetna 11 Aetna 12 Aetna 13 Aetna Account Type 1 IP 2 OP 2 IP 3 IP 4 IP 5 IP 7 IP 8 OP 9 OP 10 OP 11 OP 12 OP E Admit Disch 8/15/2017 8/22/2017 7/15/2017 7/16/2017 1/20/2017 1/31/2017 2/25/2017 4/25/2017 3/12/2017 3/14/2017 3/23/2017 5/8/2017 4/1/2017 4/10/2017 7/15/2017 7/16/2017 9/1/2017 9/2/2017 9/21/2017 9/23/2017 9/24/2017 9/25/2017 2/18/2017 2/20/2017 G Total Charges 7 $ 210,237.79 $ 1 $ 8,453.98 $ 11 $ 433,673.04 59 $1,027,205.19 2 S 18,403.22 46 $1,099,386.58 9 1$ 8,453.98 1$ 7,432.14 2$ 12,897.32 1$ 9,892.43 2$ 10,213.89 Length of Stay Expected Actual Potential Payment Payment Underpayment 84,095.12 $76,198.61 $ 3,500.00 $ 3,500.00 $ $ 173,469.22 $ 410,882.08 $ 3,500.00 $ 439,754.63 $ 132,081.02 $ 132,081.02 $ $ $ 3,500.00 $ $ 2,900.00 $ 3,500.00 K % Paid DRG 7,896.51 36% 41% 40% 40% 19% 40% #DIV/0! 0% 47% 0% 29% 34% M N Procedure Description Rate Type (General) 473 Spinal Surgery N/A Observation 454 Spinal Surgery 455 Spinal Surgery 473 Spinal Surgery 455 Spinal Surgery 460 Spinal Surgery N/A Observation N/A Observation N/A Observation N/A Observation N/A Observation