You are given the current A/R invoices in this file which you should download: Invoices.xlsxDownload Invoices.xlsx(the attached picture)
1. Using a formula, compute the outstanding balance for each Invoice No. Implement a formula to compute the total outstanding balance for all invoices.
2. Create a 0 or 1 in the Paid? column by using VLOOKUP() to give a 0 to Invoices that have a $0.00 Outstanding Balance and a 1 to all other Invoices that have at least $1.00 owed.
3. Use the formula DAYS(). Use the formula NOW() to represent today's date. Create a formula that calculates the number of days outstanding since the Invoice Date in the column Days Since Invoice and multiply it by the Paid value for each invoice.
4. Use VLOOKUP() to assign an A/R Status to each invoice. Look up how many days since the invoice and assign a Status as follows:
0 days assign "Paid"
1 day assign "A/R"
30 days assign "Past Due"
60 days assign "Collections"
5. Sort all of the data based on the "Cust No" field. Provide recommendations for action for companies based on your observations, giving your rationale.
6. Use the CountIf() formula to count each of the Paid, A/R, Past Due and Collections invoices. Using the results, create a fully labeled pie chart that shows the percentages of each in A/R.
7. Use an orthogonal design for you spreadsheet layout. Make sure that your titles, column headings etc. are labeled correctly and facilitate the reading of the data. Put your name, course number and section in the top left-hand corner of the spreadsheet.
\begin{tabular}{|c|c|c|c|c|} \hline 1095 & 3052 & 2023/10/2 & 6287.85 & 6287.85 \\ \hline 1096 & 3054 & 2023/10/2 & 4029.55 & 4029.55 \\ \hline 1097 & 2163 & 2023/10/2 & 2099 & 2099 \\ \hline 1098 & 3052 & 2023/10/2 & 45160.1 & 451601 \\ \hline 1099 & 1651 & 2023/10/3 & 8305.95 & 8305.95 \\ \hline 1100 & 5515 & 2023/10/3 & 10054 & 10054 \\ \hline 1101 & 1356 & 2023/10/4 & 5330.9 & 5330.9 \\ \hline 1102 & 3984 & 2023/10/6 & 971.7 & 9717 \\ \hline 1103 & 5384 & 2023/10/6 & 14557.95 & 14557.95 \\ \hline 1104 & 2156 & 2023/10/6 & 784.9 & 784.9 \\ \hline 1105 & 2163 & 2023/10/6 & 1000 & 1000 \\ \hline 1106 & 5151 & 2023/10/6 & 6675.95 & 6675.95 \\ \hline 1107 & 3052 & 2023/10/6 & 8939.6 & 8939.6 \\ \hline 1108 & 1351 & 2023/10/6 & 3555 & 3555 \\ \hline 1109 & 5432 & 2023/10/7 & 14188 & 14188 \\ \hline 1110 & 4312 & 2023/10/7 & 6300 & 6300 \\ \hline 1111 & 4652 & 2023/10/9 & 1846 & 1846 \\ \hline 1112 & 2118 & 2023/10/10 & 11989.2 & 119892 \\ \hline 1113 & 2163 & 2023/10/10 & 906 & 906 \\ \hline 1115 & 5132 & 2023/10/12 & 12455 & 12455 \\ \hline 1116 & 2163 & 2023/10/12 & 44854 & 8970.8 \\ \hline 1117 & 2165 & 2023/10/13 & 325 & 325 \\ \hline 1118 & 1221 & 2023/10/13 & 13945 & 13945 \\ \hline 1119 & 1384 & 2023/10/14 & 33071 & 33071 \\ \hline 1120 & 1351 & 2023/10/14 & 23406 & 0 \\ \hline 1123 & 1354 & 2023/10/14 & 16102 & 0 \\ \hline 1124 & 1351 & 2023/10/15 & 16788 & 16788 \\ \hline 1125 & 1513 & 2023/10/15 & 6583.8 & 6583.8 \\ \hline 1126 & 2984 & 2023/10/15 & 7990 & 7990 \\ \hline 1127 & 1231 & 2023/10/15 & 10502.9 & 10502.9 \\ \hline 1128 & 3053 & 2023/10/16 & 13908 & 993.5 \\ \hline 1129 & 1231 & 2023/10/17 & 4205 & 4205 \\ \hline 1130 & 3984 & 2023/10/17 & 3546 & 0 \\ \hline 1131 & 2163 & 2023/10/18 & 2971 & 2971 \\ \hline 1132 & 1513 & 2023/10/18 & 19293.7 & 0 \\ \hline 1133 & 1984 & 2023/10/18 & 649 & 0 \\ \hline 1134 & 1624 & 2023/10/19 & 10107 & 10107 \\ \hline 1139 & 5515 & 2023/10/20 & 64050 & 64050 \\ \hline 1140 & 1984 & 2023/10/20 & 51730.8 & 51730.8 \\ \hline 1141 & 1221 & 2023/10/20 & 13682.85 & 0 \\ \hline 1143 & 4531 & 2023/10/23 & 1701 & 1701 \\ \hline 1144 & 5132 & 2023/10/23 & 3087 & 0 \\ \hline 1145 & 2354 & 2023/10/23 & 3562.9 & 0 \\ \hline 1146 & 6516 & 2023/10/24 & 24650 & 24650 \\ \hline 1147 & 1380 & 2023/10/24 & 4774.85 & 0 \\ \hline 1148 & 6812 & 2023/10/27 & 1240 & 1240 \\ \hline 1149 & 2984 & 2023/10/27 & 7073.95 & 0 \\ \hline 1150 & 1221 & 2023/10/27 & 325 & 0 \\ \hline 1151 & 9841 & 2023/10/28 & 7878 & 7878 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline Invoice No & Cust No & Invoice Date & Invoices & AmtPaid & \begin{tabular}{l} Outstanding \\ Balance \end{tabular} & Status & Past Due & Action \\ \hline 1018 & 2118 & 2023/6/30 & 5256 & 5256 & & 0 & 0 & o \\ \hline 1019 & 1384 & 2023/7/1 & 60948 & 60948 & & 1 & 128 & \\ \hline 1020 & 1351 & 2023/7/1 & 52729.25 & 52729.25 & & 0 & 0 & \\ \hline 1021 & 2135 & 2023/7/6 & 20602 & 20602 & & 1 & 123 & \\ \hline 1022 & 2156 & 2023/7/6 & 9955 & 9955 & & & 0 & 8 \\ \hline 1026 & 2135 & 2023/7/12 & 10064.65 & 1006465 & & & 0 & b \\ \hline 1027 & 1221 & 2023/7/13 & 1414 & 0 & & & & \\ \hline 1031 & 1563 & 2023/7/15 & 104 & 104 & & & & \\ \hline 1032 & 1231 & 2023/7/17 & 2844 & 1000 & & & & \\ \hline 1033 & 1221 & 2023/7/17 & 9471.95 & 947195 & & & & \\ \hline 1034 & 1231 & 2023/7/17 & 24485 & 5000 & & & & \\ \hline 1035 & 1624 & 2023/7/19 & 2920 & 2920 & & & & \\ \hline 1036 & 1351 & 2023/7/19 & 25210 & 25210 & & & & \\ \hline 1041 & 1231 & 2023/7/27 & 54 & 54 & & & & \\ \hline 1042 & 1354 & 2023/7/29 & 51673.15 & 5167315 & & & & \\ \hline 1043 & 1645 & 2023/7/30 & 20108 & 20108 & & & & \\ \hline 1044 & 1356 & 2023/8/1 & 31219.95 & 31219.95 & & & & \\ \hline 1045 & 1356 & 2023/8/1 & 3065 & 1500 & & & & \\ \hline 1046 & 3051 & 2023/8/2 & 13814.05 & 13814.05 & & & & \\ \hline 1047 & 3615 & 2023/8/6 & 559.6 & 559.6 & & & & \\ \hline 1048 & 1221 & 2023/8/6 & 4178.85 & 4178.85 & & & & \\ \hline 1053 & 1384 & 2023/8/13 & 28389 & 28389 & & & & \\ \hline 1054 & 1231 & 2023/8/13 & 551175 & 551175 & & & & \\ \hline 1055 & 1356 & 2023/8/17 & 3640 & 3640 & & & & \\ \hline 1056 & 2315 & 2023/8/21 & 766.8 & 766.8 & & & & \\ \hline 1057 & 4531 & 2023/8/21 & 11741.85 & 11741.85 & & & & \\ \hline 1058 & 1513 & 2023/8/23 & 203 & 203 & & & & \\ \hline 1059 & 3615 & 2023/8/23 & 465 & 465 & & & & \\ \hline 1060 & 1680 & 2023/8/25 & 18532 & 18532 & & & & \\ \hline 1061 & 1560 & 2023/8/28 & 560 & 0 & & & & \\ \hline 1062 & 1560 & 2023/8/30 & 4720.8 & 4720.8 & & & & \\ \hline 1065 & 5515 & 2023/9/5 & 15365 & 15365 & & & & \\ \hline 1066 & 5384 & 2023/9/6 & 4110 & 4110 & & & & \\ \hline 1067 & 1624 & 2023/9/6 & 2514.65 & 359.25 & & & & \\ \hline 1068 & 4684 & 2023/9/6 & 4894.95 & 4894.95 & & & & \\ \hline 1069 & 1513 & 2023/9/6 & 5709.85 & 5709.85 & & & & \\ \hline 1070 & 1680 & 2023/9/6 & 5037.1 & 50371 & & & & \\ \hline 1079 & 5384 & 2023/9/17 & 7868 & 7868 & & & & \\ \hline 1080 & 1513 & 2023/9/19 & 20711.9 & 207119 & & & & \\ \hline 1081 & 2354 & 2023/9/20 & 5983 & 5983 & & & & \\ \hline 1082 & 9841 & 2023/9/21 & 1809.85 & 180985 & & & & \\ \hline 1083 & 1513 & 2023/9/23 & 6731 & 6731 & & & & \\ \hline 1084 & 1984 & 2023/9/24 & 6734.85 & 2000 & & & & \\ \hline 1085 & 1680 & 2023/9/24 & 7572 & 7572 & & & & \\ \hline 1086 & 2135 & 2023/9/25 & 72089.9 & 10298.55 & & & & \\ \hline 1087 & 5163 & 2023/9/27 & 716 & 716 & & & & \\ \hline 50 & 3052 & 2023/10/2 & 6287.85 & 87.85 & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|} \hline 1095 & 3052 & 2023/10/2 & 6287.85 & 6287.85 \\ \hline 1096 & 3054 & 2023/10/2 & 4029.55 & 4029.55 \\ \hline 1097 & 2163 & 2023/10/2 & 2099 & 2099 \\ \hline 1098 & 3052 & 2023/10/2 & 45160.1 & 451601 \\ \hline 1099 & 1651 & 2023/10/3 & 8305.95 & 8305.95 \\ \hline 1100 & 5515 & 2023/10/3 & 10054 & 10054 \\ \hline 1101 & 1356 & 2023/10/4 & 5330.9 & 5330.9 \\ \hline 1102 & 3984 & 2023/10/6 & 971.7 & 9717 \\ \hline 1103 & 5384 & 2023/10/6 & 14557.95 & 14557.95 \\ \hline 1104 & 2156 & 2023/10/6 & 784.9 & 784.9 \\ \hline 1105 & 2163 & 2023/10/6 & 1000 & 1000 \\ \hline 1106 & 5151 & 2023/10/6 & 6675.95 & 6675.95 \\ \hline 1107 & 3052 & 2023/10/6 & 8939.6 & 8939.6 \\ \hline 1108 & 1351 & 2023/10/6 & 3555 & 3555 \\ \hline 1109 & 5432 & 2023/10/7 & 14188 & 14188 \\ \hline 1110 & 4312 & 2023/10/7 & 6300 & 6300 \\ \hline 1111 & 4652 & 2023/10/9 & 1846 & 1846 \\ \hline 1112 & 2118 & 2023/10/10 & 11989.2 & 119892 \\ \hline 1113 & 2163 & 2023/10/10 & 906 & 906 \\ \hline 1115 & 5132 & 2023/10/12 & 12455 & 12455 \\ \hline 1116 & 2163 & 2023/10/12 & 44854 & 8970.8 \\ \hline 1117 & 2165 & 2023/10/13 & 325 & 325 \\ \hline 1118 & 1221 & 2023/10/13 & 13945 & 13945 \\ \hline 1119 & 1384 & 2023/10/14 & 33071 & 33071 \\ \hline 1120 & 1351 & 2023/10/14 & 23406 & 0 \\ \hline 1123 & 1354 & 2023/10/14 & 16102 & 0 \\ \hline 1124 & 1351 & 2023/10/15 & 16788 & 16788 \\ \hline 1125 & 1513 & 2023/10/15 & 6583.8 & 6583.8 \\ \hline 1126 & 2984 & 2023/10/15 & 7990 & 7990 \\ \hline 1127 & 1231 & 2023/10/15 & 10502.9 & 10502.9 \\ \hline 1128 & 3053 & 2023/10/16 & 13908 & 993.5 \\ \hline 1129 & 1231 & 2023/10/17 & 4205 & 4205 \\ \hline 1130 & 3984 & 2023/10/17 & 3546 & 0 \\ \hline 1131 & 2163 & 2023/10/18 & 2971 & 2971 \\ \hline 1132 & 1513 & 2023/10/18 & 19293.7 & 0 \\ \hline 1133 & 1984 & 2023/10/18 & 649 & 0 \\ \hline 1134 & 1624 & 2023/10/19 & 10107 & 10107 \\ \hline 1139 & 5515 & 2023/10/20 & 64050 & 64050 \\ \hline 1140 & 1984 & 2023/10/20 & 51730.8 & 51730.8 \\ \hline 1141 & 1221 & 2023/10/20 & 13682.85 & 0 \\ \hline 1143 & 4531 & 2023/10/23 & 1701 & 1701 \\ \hline 1144 & 5132 & 2023/10/23 & 3087 & 0 \\ \hline 1145 & 2354 & 2023/10/23 & 3562.9 & 0 \\ \hline 1146 & 6516 & 2023/10/24 & 24650 & 24650 \\ \hline 1147 & 1380 & 2023/10/24 & 4774.85 & 0 \\ \hline 1148 & 6812 & 2023/10/27 & 1240 & 1240 \\ \hline 1149 & 2984 & 2023/10/27 & 7073.95 & 0 \\ \hline 1150 & 1221 & 2023/10/27 & 325 & 0 \\ \hline 1151 & 9841 & 2023/10/28 & 7878 & 7878 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline Invoice No & Cust No & Invoice Date & Invoices & AmtPaid & \begin{tabular}{l} Outstanding \\ Balance \end{tabular} & Status & Past Due & Action \\ \hline 1018 & 2118 & 2023/6/30 & 5256 & 5256 & & 0 & 0 & o \\ \hline 1019 & 1384 & 2023/7/1 & 60948 & 60948 & & 1 & 128 & \\ \hline 1020 & 1351 & 2023/7/1 & 52729.25 & 52729.25 & & 0 & 0 & \\ \hline 1021 & 2135 & 2023/7/6 & 20602 & 20602 & & 1 & 123 & \\ \hline 1022 & 2156 & 2023/7/6 & 9955 & 9955 & & & 0 & 8 \\ \hline 1026 & 2135 & 2023/7/12 & 10064.65 & 1006465 & & & 0 & b \\ \hline 1027 & 1221 & 2023/7/13 & 1414 & 0 & & & & \\ \hline 1031 & 1563 & 2023/7/15 & 104 & 104 & & & & \\ \hline 1032 & 1231 & 2023/7/17 & 2844 & 1000 & & & & \\ \hline 1033 & 1221 & 2023/7/17 & 9471.95 & 947195 & & & & \\ \hline 1034 & 1231 & 2023/7/17 & 24485 & 5000 & & & & \\ \hline 1035 & 1624 & 2023/7/19 & 2920 & 2920 & & & & \\ \hline 1036 & 1351 & 2023/7/19 & 25210 & 25210 & & & & \\ \hline 1041 & 1231 & 2023/7/27 & 54 & 54 & & & & \\ \hline 1042 & 1354 & 2023/7/29 & 51673.15 & 5167315 & & & & \\ \hline 1043 & 1645 & 2023/7/30 & 20108 & 20108 & & & & \\ \hline 1044 & 1356 & 2023/8/1 & 31219.95 & 31219.95 & & & & \\ \hline 1045 & 1356 & 2023/8/1 & 3065 & 1500 & & & & \\ \hline 1046 & 3051 & 2023/8/2 & 13814.05 & 13814.05 & & & & \\ \hline 1047 & 3615 & 2023/8/6 & 559.6 & 559.6 & & & & \\ \hline 1048 & 1221 & 2023/8/6 & 4178.85 & 4178.85 & & & & \\ \hline 1053 & 1384 & 2023/8/13 & 28389 & 28389 & & & & \\ \hline 1054 & 1231 & 2023/8/13 & 551175 & 551175 & & & & \\ \hline 1055 & 1356 & 2023/8/17 & 3640 & 3640 & & & & \\ \hline 1056 & 2315 & 2023/8/21 & 766.8 & 766.8 & & & & \\ \hline 1057 & 4531 & 2023/8/21 & 11741.85 & 11741.85 & & & & \\ \hline 1058 & 1513 & 2023/8/23 & 203 & 203 & & & & \\ \hline 1059 & 3615 & 2023/8/23 & 465 & 465 & & & & \\ \hline 1060 & 1680 & 2023/8/25 & 18532 & 18532 & & & & \\ \hline 1061 & 1560 & 2023/8/28 & 560 & 0 & & & & \\ \hline 1062 & 1560 & 2023/8/30 & 4720.8 & 4720.8 & & & & \\ \hline 1065 & 5515 & 2023/9/5 & 15365 & 15365 & & & & \\ \hline 1066 & 5384 & 2023/9/6 & 4110 & 4110 & & & & \\ \hline 1067 & 1624 & 2023/9/6 & 2514.65 & 359.25 & & & & \\ \hline 1068 & 4684 & 2023/9/6 & 4894.95 & 4894.95 & & & & \\ \hline 1069 & 1513 & 2023/9/6 & 5709.85 & 5709.85 & & & & \\ \hline 1070 & 1680 & 2023/9/6 & 5037.1 & 50371 & & & & \\ \hline 1079 & 5384 & 2023/9/17 & 7868 & 7868 & & & & \\ \hline 1080 & 1513 & 2023/9/19 & 20711.9 & 207119 & & & & \\ \hline 1081 & 2354 & 2023/9/20 & 5983 & 5983 & & & & \\ \hline 1082 & 9841 & 2023/9/21 & 1809.85 & 180985 & & & & \\ \hline 1083 & 1513 & 2023/9/23 & 6731 & 6731 & & & & \\ \hline 1084 & 1984 & 2023/9/24 & 6734.85 & 2000 & & & & \\ \hline 1085 & 1680 & 2023/9/24 & 7572 & 7572 & & & & \\ \hline 1086 & 2135 & 2023/9/25 & 72089.9 & 10298.55 & & & & \\ \hline 1087 & 5163 & 2023/9/27 & 716 & 716 & & & & \\ \hline 50 & 3052 & 2023/10/2 & 6287.85 & 87.85 & & & & \\ \hline \end{tabular}
