This assignment is to be completed on an excel spreadsheet please include formulas to enter into spreadsheet.

You will be asked to analyze the accounts receivable of a company. The aging schedule is in this unit's template. Assume the following percentages in determining the total amount of uncollectable receivables. The percent likely to be uncollectable: 1% (Current), 5% (0-30 Days), 10% (31-60 Days), 20% (61-90 Days) and 50% (Over 90 Days) In addition, analyze each individual account to determine if the customer is current or past due. (Past due is defined as the total balance being more than the amount that is current) Use formulas and conditional formatting to determine the accounts that are current and the past due accounts. In addition, create a pivot table indicating the number of customers by their current and past due status, how many of the accounts are current and how many of the past due accounts fall under each receivable category (current, 0-30 days, etc.). Use the following Excel template:

Accounts Receivable Aging Schedule Account Customer Total Balance Current 0-30 Days 31-60 Days 61-90 Days Over 90 Days Status 100 $ 37,232.00 $ 37,232.00 101 $ 93,981.00 $ 6,432.00 $ 23,000.00 $50,000.00 $ 5,000.00 $ 9,549.00 102 $ 38,790.00 $ 15,000.00 $ 18,000.00 $ 5,790.00 103 $ 30,409.00 $ 20,000.00 $ 7,893.00 $ 2,516.00 104 $ 9,343.00 $ 6,789.00 $ 2,554.00 105 $ 44,332.00 $ 1,357.00 $ 42,975.00 106 $ 64,750.00 $ 36,902.00 $ 27,848.00 107 $ 25,548.00 $ 10,490.00 $ 9,543.00 $ 5,515.00 108 $ 5,780.00 $ 5,780.00 109 $ 42,559.00 $ 27,349.00 $ 4,989.00 $ 3,879.00 $ 6,342.00 110 $ 58,982.00 $50,000.00 $ 8,982.00 111 $ 79,295.00 $ 79,295.00 112 $ 56,476.00 $ 56,476.00 113 $ 51,925.00 $ 51,925.00 114 $ 49,657.00 $ 49,657.00 115 $ 53,898.00 $ 53,898.00 116 $ 90,256.00 $ 90,256.00 117 $ 17,574.00 $ 17,574.00 118 $ 46,015.00 $ 46,015.00 119 $ 16,192.00 $ 16,192.00 120 $ 72,594.00 $ 72,594.00 121 $ 48,886.00 $ 4,930.00 $ 40,000.00 $ 3,956.00 122 $ 20,004.00 $ 20,004.00 123 $ 59,694.00 $ 59,694.00 124 $ 4,079.00 $ 4,079.00 125 $ 78,543.00 $ 78,543.00 126 $ 78,049.00 $ 78,049.00 127 $ 18,068.00 $ 18,068.00 128 $ 97,274.00 $ 97,274.00 129 $ 84,020.00 $ 84,020.00 120 00 107 on $99 1970 309 $ 94,002.00 $ 94,002.00 310 $ 74,436.00 $ 74,436.00 311 $ 45,639.00 $ 45,639.00 213 214 215 216 217 Total 218 0.01 0.05 0.1 0.2 Estimated Bad Debt 219 Percentages 220 Estimated 221 Bad Debt 0.5 Total Bad Debt 222 223 224 Accounts Receivable Aging Schedule Account Customer Total Balance Current 0-30 Days 31-60 Days 61-90 Days Over 90 Days Status 100 $ 37,232.00 $ 37,232.00 101 $ 93,981.00 $ 6,432.00 $ 23,000.00 $50,000.00 $ 5,000.00 $ 9,549.00 102 $ 38,790.00 $ 15,000.00 $ 18,000.00 $ 5,790.00 103 $ 30,409.00 $ 20,000.00 $ 7,893.00 $ 2,516.00 104 $ 9,343.00 $ 6,789.00 $ 2,554.00 105 $ 44,332.00 $ 1,357.00 $ 42,975.00 106 $ 64,750.00 $ 36,902.00 $ 27,848.00 107 $ 25,548.00 $ 10,490.00 $ 9,543.00 $ 5,515.00 108 $ 5,780.00 $ 5,780.00 109 $ 42,559.00 $ 27,349.00 $ 4,989.00 $ 3,879.00 $ 6,342.00 110 $ 58,982.00 $50,000.00 $ 8,982.00 111 $ 79,295.00 $ 79,295.00 112 $ 56,476.00 $ 56,476.00 113 $ 51,925.00 $ 51,925.00 114 $ 49,657.00 $ 49,657.00 115 $ 53,898.00 $ 53,898.00 116 $ 90,256.00 $ 90,256.00 117 $ 17,574.00 $ 17,574.00 118 $ 46,015.00 $ 46,015.00 119 $ 16,192.00 $ 16,192.00 120 $ 72,594.00 $ 72,594.00 121 $ 48,886.00 $ 4,930.00 $ 40,000.00 $ 3,956.00 122 $ 20,004.00 $ 20,004.00 123 $ 59,694.00 $ 59,694.00 124 $ 4,079.00 $ 4,079.00 125 $ 78,543.00 $ 78,543.00 126 $ 78,049.00 $ 78,049.00 127 $ 18,068.00 $ 18,068.00 128 $ 97,274.00 $ 97,274.00 129 $ 84,020.00 $ 84,020.00 120 00 107 on $99 1970 309 $ 94,002.00 $ 94,002.00 310 $ 74,436.00 $ 74,436.00 311 $ 45,639.00 $ 45,639.00 213 214 215 216 217 Total 218 0.01 0.05 0.1 0.2 Estimated Bad Debt 219 Percentages 220 Estimated 221 Bad Debt 0.5 Total Bad Debt 222 223 224