a. Create a formula in column F to calculate how many days a particular invoice has been outstanding. b. Create a formula in column G that attaches the correct label to each invoice (e.g., if the invoice is 0-30 days old, the label should be "0-30"; if it is 31-60 days old, the label should be "31-60", etc.) according to the table in the upper right corner of the spreadsheet. c. Create a pivot table that shows the sum, count, and average values for each of the four categories of accounts receivable. d. Create a pivot table that shows total amounts and count of invoices by both category (e.g., 0-30, 31-60, etc.) and region (East, West, North, South). e. Modify the pivot table in step d to be able to filter the results by salesperson. Suggestions: First, use a formula to calculate the age of AR (Column F). It is the difference between the invoice date and the aging date. Second, create a formula to classify each invoice under the different categories of days outstanding such as 0-30, 31-60, etc. You can use an "If" statement or a "VLookup". There may be other ways to do this as well. (Column G) Third, insert pivot tables to answer parts c,d, and e. 12345678922222222222222 1 Aging Date: 9/24/2020 Age (days) Group 0-30 0-30 Customer Region Salesperson Balance Invoice Date Age (Days) Group 31-60 31-60 1 East A 435.22 9/1/2020 61-90 61-90 2 South B 598.55 8/20/2020 Over 90 Over 90 3 East C 124.55 9/1/2020 4 West D 199.99 7/15/2020 5 North E 945.00 9/2/2020 6 South F 831.64 10 7 West G 728.95 9/2/2020 9/2/2020 11 8 East H 9 North A 333.79 362.59 9/3/2020 9/10/2020 10 North B 184.65 6/6/2020 11 South C 12 East D 499.99 299.99 8/15/2020 9/11/2020 13 West E 399.99 6/30/2020 14 West F 259.95 9/12/2020 15 North G 784.75 9/13/2020 16 South H 666.66 9/11/2020 20 17 South A 444.44 7/15/2020 18 South B 234.56 9/17/2020 19 East C 345.67 6/30/2020 20 West D 456.78 9/18/2020 21 West E 567.89 6/6/2020 22 East F 987.65 9/17/2020 23 North G 876.54 9/17/2020 24 South H 765.43 8/15/2020 25 South A 135.79 9/18/2020 Sheet2 Sheet1 28 A 25 South B TT C D E F A 29 26 North B 135.79 9/18/2020 246.80 9/13/2020 30 27 North C 147.98 9/18/2020 31 28 East D 259.85 9/13/2020 32 29 West E 445.66 8/15/2020 33 30 East F 554.33 7/15/2020 34 31 West G 222.22 9/17/2020 35 32 North H 111.11 9/11/2020 36 33 South A 427.99 9/11/2020 37 34 East A 444.44 9/11/2020 38 35 North B 39 36 South C 40 37 West C 41 38 East D 555.55 9/13/2020 666.66 9/17/2020 777.77 9/20/2020 888.88 9/17/2020 42 39 North E 222.55 9/13/2020 43 40 South E 987.65 8/15/2020 44 41 West E 975.31 9/20/2020 45 42 West F 864.20 6/30/2020 46 43 North G 741.26 9/20/2020 47 44 South G 357.99 9/18/2020 48 45 East H 631.22 9/21/2020 49 46 East A 764.99 6/7/2020 50 47 East A 333.95 9/20/2020 51 48 West B 244.99 9/11/2020 52 49 West C 188.75 9/13/2020 53 50 West C 988.99 9/21/2020 54 51 West D 55 52 West E 989.95 8/15/2020 777.99 7/15/2020 Sheet2 Sheet1 (+ 55 A B C D E F 52 West 56 53 West 57 54 West 58 55 West 59 56 South 60 57 North 61 58 North 62 59 North 63 60 North 64 61 North 65 62 North 66 63 North 67 64 North 68 65 North 69 66 North EEEEGGHAABCCDEE 777.99 7/15/2020 666.99 7/15/2020 555.95 9/13/2020 F 444.95 9/22/2020 333.99 9/21/2020 222.95 9/11/2020 111.99 9/13/2020 101.00 9/22/2020 204.00 9/17/2020 303.55 7/15/2020 412.87 9/13/2020 732.66 6/8/2020 567.23 9/20/2020 367.33 6/30/2020 399.95 9/13/2020 70 67 North E 370.99 9/18/2020 71 68 North F 951.99 9/11/2020 72 69 North G 505.82 9/17/2020 73 70 South G 777.99 9/17/2020 74 71 South H 111.99 9/22/2020 75 72 South A 222.95 9/18/2020 76 73 South B 333.99 9/13/2020 77 74 East B 449.95 9/17/2020 78 75 West B 501.77 9/22/2020 79 76 West C 666.77 9/17/2020 80 77 East C 777.88 9/20/2020 81 78 West D 888.99 9/17/2020 82 79 West E 444.31 9/20/2020 83 80 East E 333 64 6/30/2020 Sheet2 Sheet1 Ready Accessibility: Investigate 77 A B C D E 74 East B 449.95 9/17/2020 78 75 West B 501.77 9/22/2020 79 76 West C 666.77 9/17/2020 80 77 East C 777.88 9/20/2020 81 78 West D 888.99 9/17/2020 82 79 West E 444.31 9/20/2020 83 80 East E 333.64 6/30/2020 84 81 West F 111.23 9/22/2020 85 82 West F 222.34 9/11/2020 86 83 North F 555.43 9/18/2020 87 84 North F 123.45 9/13/2020 88 85 North G 123.32 9/13/2020 89 86 South G 876,78 9/17/2020 90 87 South H 987.89 8/15/2020 91 88 East H 444.56 9/22/2020 92 89 East A 555.76 9/17/2020 93 90 East B 777.55 9/20/2020 94 91 West B 888.44 9/17/2020 95 92 South B 222.95 9/11/2020 96 93 North C 267.95 9/21/2020 97 94 North C 832.88 9/17/2020 98 95 East D 157.99 9/18/2020 99 96 West E 743.77 9/20/2020 100 97 South E 905.95 6/6/2020 101 98 North F 959.59 7/15/2020 102 99 North F 103 100 East F 595.95 9/13/2020 777.88 9/17/2020 104 105 Sheet2 Sheet1 +