Tracking Project Grand Total Impact

[dianamruelas]

I work for a healthcare billing company. I identify large payment issues and impact. After I correct the issue, my goal is for there to be $0 impact. Generally in healthcare, the charge is higher than the actual expected payment. Example: Customer 1 did not send correct payment for 250 accounts. This resulted in an impact of $300,000 in charges with expected collections of $74,000 that was not paid. I worked with customer one on a partial correction and reduced the impact down to $210,000 charges/$58,000 collections. However, after the partial correction, more errors occurred and the impact increased up to $280,000 charges/ $65,000 collections. We finally received a bulk payment for $65,000, resulting in and impact of $0 charges/$0 collections. This is PROJECT 1 for CUSTOMER 1. I will need to track the impact of multiple projects for multiple customers.

My available resources only allow me to run periodic reports for impact. I would like an automated output, perhaps via a pivot table. I only have data to complete columns A-D (first 5 columns). The remaining columns need to be automatically calculated based on the last dated entry:

Customer	Project	Review Date	Impacted Charges	Impacted Collections	Charge Variance from Previous Review Date	Collection Variance from previous Review Date
CUSTOMER 1	PROJECT 1	11/1/2019​	$300,000​	$74,000​	N/A	N/A
CUSTOMER 1	PROJECT 1	1/2/2020​	$210,000​	$58,000​	($90,000)​	($16,000)​
CUSTOMER 1	PROJECT 1	2/12/2020​	$280,000​	$65,000​	$70,000​	$7,000​
CUSTOMER 1	PROJECT 1	2/19/2020​	$0​	$0​	($280,000)​	($65,000)​
CUSTOMER 1	PROJECT 2	1/13/2020​	$150,000​	$32,500​	N/A
CUSTOMER 1	PROJECT 2	1/29/2020​	$0​	$0​	($150,000)​	($32,000)​
CUSTOMER 2	PROJECT 1	11/13/2019​	$12,000​	$2,800​	N/A
CUSTOMER 2	PROJECT 1	11/28/2019​	$6,000​	$1,300​	($6,000)​	($1,500)​
CUSTOMER 2	PROJECT 2	12/20/2019​	$196,000​	$52,000​	N/A	N/A

Here is the output summary I would like:

CUSTOMER	PROJECT	total gross impact REVIEWED	total net impact REVIEWED	total gross impact RESOLVED	total net impact RESOLVED
CUSTOMER 1		$520,000​	$113,500​	$520,000​	$113,500​
	PROJECT 1	$370,000​	$81,000​	$370,000​	$81,000​
	PROJECT 2	$150,000​	$32,500​	$150,000​	$32,500​
CUSTOMER 2		$208,000​	$54,800​	$6,000​	$1,500​
	PROJECT 1	$12,000​	$2,800​	$6,000​	$1,500​
	PROJECT 2	$196,000​	$52,000​	$0​	$0​

This is what I see as the best way to present progress. But I am open to suggestions on different methods. The logging data is the only thing that cannot change. That is all the data I have available and cannot own any other processes that are not automated.

 

[pbornemeier]

What numbers from the first part was used to generate the following numbers in the second part?
$370,000
$81,000
Even more generally, if the posted ranges above each started on A1 of separate worksheets what are the formulas in the cells in the 2nd worksheet that pull data from the first sheet?