I have added the answers at the bottom of the file, just need to know how to get there! What formulas were used!

Please use the accompanying Excel File (posted below) to complete the following steps:

Create a pivot table that has each customer listed in a separate row. Add the pivot table to a new worksheet within the same Excel file as the source data. Change the column heading to Customer

For each customer we want a sum of the Impact on Customer Balance column. Given that this will represent the change in each customers balance, re-label this column 2013 change in customer balance.

Assume your audit senior is concerned with any balances that increased by more than $10,000 or decreased by more than $5,000. Conditionally format the table such that any customer whose balance has increased by more than $10,000 is highlighted in red and any that have decreased by more than $5,000 in green.

Add a report filter for the Payment or Purchase field and examine whether any concerns are highlighted by separately assessing each customers total payments and total purchases.

Customer	Payment or Purchase	Date of the Transaction	Debit	Credit	Impact on Customer Balance
17	Payment	1/7/2013		$ (860.00)	$ (860.00)
20	Payment	1/7/2013		$ (1,237.00)	$ (1,237.00)
4	Payment	1/8/2013		$ (1,852.00)	$ (1,852.00)
7	Purchase	1/8/2013	$ 4,057.00		$ 4,057.00
4	Payment	1/9/2013		$ (235.00)	$ (235.00)
20	Payment	1/10/2013		$ (763.00)	$ (763.00)
6	Payment	1/18/2013		$ (1,523.00)	$ (1,523.00)
4	Purchase	1/25/2013	$ 6,301.00		$ 6,301.00
5	Payment	1/27/2013		$ (1,655.00)	$ (1,655.00)
13	Payment	1/31/2013		$ (1,067.00)	$ (1,067.00)
20	Payment	2/3/2013		$ (1,837.00)	$ (1,837.00)
13	Purchase	2/3/2013		$ (3,772.00)	$ (3,772.00)
5	Payment	2/12/2013		$ (948.00)	$ (948.00)
14	Payment	2/19/2013		$ (595.00)	$ (595.00)
16	Payment	2/21/2013		$ (869.00)	$ (869.00)
13	Payment	2/22/2013		$ (1,624.00)	$ (1,624.00)
4	Purchase	2/24/2013	$ 5,439.00		$ 5,439.00
10	Payment	2/25/2013		$ (1,370.00)	$ (1,370.00)
20	Purchase	3/1/2013	$ 4,272.00		$ 4,272.00
8	Payment	3/2/2013		$ (264.00)	$ (264.00)
12	Payment	3/8/2013		$ (764.00)	$ (764.00)
7	Purchase	3/9/2013	$ 5,464.00		$ 5,464.00
19	Payment	3/11/2013		$ (1,652.00)	$ (1,652.00)
19	Payment	3/13/2013		$ (259.00)	$ (259.00)
3	Payment	3/17/2013		$ (1,557.00)	$ (1,557.00)
1	Purchase	3/18/2013	$ 4,293.00		$ 4,293.00
10	Payment	3/20/2013		$ (360.00)	$ (360.00)
15	Purchase	3/20/2013		$ (1,006.00)	$ (1,006.00)
6	Purchase	3/21/2013	$ 6,736.00		$ 6,736.00
8	Payment	4/2/2013		$ (1,421.00)	$ (1,421.00)
3	Purchase	4/7/2013	$ 4,032.00		$ 4,032.00
20	Payment	4/12/2013		$ (516.00)	$ (516.00)
10	Payment	4/18/2013		$ (1,511.00)	$ (1,511.00)
8	Purchase	5/5/2013	$ 6,262.00		$ 6,262.00
1	Purchase	5/8/2013	$ 5,227.00		$ 5,227.00
15	Payment	5/16/2013		$ (506.00)	$ (506.00)
2	Purchase	5/17/2013	$ 3,276.00		$ 3,276.00
9	Payment	5/21/2013		$ (817.00)	$ (817.00)
3	Payment	5/24/2013		$ (1,116.00)	$ (1,116.00)
13	Payment	5/25/2013		$ (452.00)	$ (452.00)
17	Payment	5/29/2013		$ (552.00)	$ (552.00)
12	Purchase	5/29/2013	$ 6,060.00		$ 6,060.00
8	Payment	6/1/2013		$ (629.00)	$ (629.00)
1	Purchase	6/13/2013	$ 6,488.00		$ 6,488.00
16	Payment	6/15/2013		$ (847.00)	$ (847.00)
9	Purchase	6/16/2013	$ 3,365.00		$ 3,365.00
1	Purchase	6/20/2013	$ 4,873.00		$ 4,873.00
4	Payment	6/21/2013		$ (652.00)	$ (652.00)
15	Purchase	6/24/2013	$ 3,943.00		$ 3,943.00
13	Payment	6/28/2013		$ (439.00)	$ (439.00)
13	Payment	7/3/2013		$ (1,253.00)	$ (1,253.00)
10	Payment	7/8/2013		$ (1,932.00)	$ (1,932.00)
7	Payment	7/13/2013		$ (359.00)	$ (359.00)
11	Payment	7/17/2013	$ 5,048.00		$ 5,048.00
10	Purchase	7/22/2013	$ 5,365.00		$ 5,365.00
11	Purchase	7/27/2013	$ 4,042.00		$ 4,042.00
13	Purchase	7/28/2013		$ (3,416.00)	$ (3,416.00)
8	Payment	8/8/2013		$ (601.00)	$ (601.00)
1	Purchase	8/8/2013	$ 5,900.00		$ 5,900.00
18	Purchase	8/17/2013	$ 4,700.00		$ 4,700.00
13	Payment	9/1/2013		$ (682.00)	$ (682.00)
10	Purchase	9/4/2013	$ 3,140.00		$ 3,140.00
19	Payment	9/7/2013		$ (426.00)	$ (426.00)
20	Purchase	9/17/2013	$ 6,948.00		$ 6,948.00
20	Payment	9/23/2013		$ (704.00)	$ (704.00)
3	Payment	10/2/2013		$ (6,360.00)	$ (6,360.00)
19	Payment	10/3/2013		$ (1,282.00)	$ (1,282.00)
5	Payment	10/6/2013		$ (770.00)	$ (770.00)
15	Payment	10/6/2013		$ (278.00)	$ (278.00)
13	Payment	10/8/2013		$ (565.00)	$ (565.00)
11	Purchase	10/8/2013	$ 5,236.00		$ 5,236.00
5	Purchase	10/11/2013	$ 6,876.00		$ 6,876.00
16	Purchase	10/15/2013	$ 3,700.00		$ 3,700.00
13	Payment	10/21/2013		$ (349.00)	$ (349.00)
6	Purchase	10/22/2013	$ 4,313.00		$ 4,313.00
19	Payment	10/27/2013		$ (627.00)	$ (627.00)
12	Payment	10/28/2013		$ (748.00)	$ (748.00)
18	Payment	11/1/2013		$ (395.00)	$ (395.00)
1	Payment	11/5/2013		$ (388.00)	$ (388.00)
14	Payment	11/6/2013		$ (1,230.00)	$ (1,230.00)
18	Payment	11/10/2013		$ (1,603.00)	$ (1,603.00)
13	Purchase	11/13/2013		$ (6,713.00)	$ (6,713.00)
20	Purchase	11/18/2013	$ 6,991.00		$ 6,991.00
5	Payment	11/19/2013		$ (1,122.00)	$ (1,122.00)
20	Payment	11/20/2013		$ (1,299.00)	$ (1,299.00)
8	Payment	11/25/2013		$ (326.00)	$ (326.00)
2	Purchase	11/27/2013	$ 3,768.00		$ 3,768.00
14	Payment	12/1/2013		$ (1,130.00)	$ (1,130.00)
2	Purchase	12/3/2013	$ 5,725.00		$ 5,725.00
14	Payment	12/7/2013		$ (892.00)	$ (892.00)
20	Purchase	12/8/2013	$ 6,523.00		$ 6,523.00
2	Payment	12/9/2013		$ (1,987.00)	$ (1,987.00)
4	Payment	12/9/2013		$ (941.00)	$ (941.00)
18	Purchase	12/12/2013	$ 5,750.00		$ 5,750.00
11	Purchase	12/14/2013	$ 5,734.00		$ 5,734.00
20	Purchase	12/18/2013	$ 4,759.00		$ 4,759.00
6	Purchase	12/19/2013	$ 3,595.00		$ 3,595.00
10	Purchase	12/25/2013	$ 4,920.00		$ 4,920.00
16	Payment	12/26/2013		$ (1,427.00)	$ (1,427.00)

Row Labels Sum of Impact on Customer Balance Row Labels Sum of Debit Sum of Credit Difference 388.00 26,393.00 1,987.00 10,782.00 9,033.005,001.00 3,680.00 8,060.00 -4,495.00 2,381.00 1,523.00 13,121.00 359.00 9,162.00 3,241.00 3,021.00 817.00 2,548.00 5,173.00 8,252.00 20,060.00 1,512.00 4,548.00 20,332.00 -20,332.00 3,847.00 -3,847.00 1,790.00 2,153.00 557.00 -1,412.00 1,412.00 1,998.00 8,452.00 -4,246.00 4,246.00 -6,356.00 23,137.00 26,393.00 10,782.00 26,781.00 12,769.00 4,032.00 11,740.00 6,876.00 14,644.00 9,521.00 6,262.00 3,365.00 13,425.00 20,060.00 6,060.00 2 2 8,060.00 2,381.00 13,121.00 9,162.00 3,021.00 2,548.00 8,252.00 20,060.00 4,548.00 10 10 12 13 14 12 13 14 -3,847.00 2,153.00 557.00 1,412.00 8,452.00 -4,246.00 23,137.00 1,07,789.00 3,943.00 3,700.00 16 17 18 16 17 18 3,143.00 10,450.00 20 Grand Total 20 Grand Total 29,493.00 1,83,121.00 -75,332.00 Concerns are that customers have paid more than the amount of purchases and in some cases haven't paid or paid very less amount