Directions:Complete the following steps:
1. Examine the data file below, "Sustain Dye Source Data." Askthreequestions that can be answered by the data. (You cannot use the same questions that I used in Part 2).
2. Use pivot tables (or pivot charts) to answer your three questions in Step 1. Make sure to upload your Excel file that contains your pivot tables.
3. Summarize your findings in one or two sentences for each pivot table or graph.
4. Based on your findings provide at least one recommendation on how SustainDye can improve its cash collections. (By doing so, you are engaging in prescriptive analytics.)
3 Sustain Dye Source Data Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat '+ Share Conditional Formatting Insert Calibri (Body) 11 A - A General Format as Table Delete B A .00 Editing Create and Share Paste I U $ % Cell Styles Format Adobe PDF 110 X V fx 5 B C D E G H M Q R Customer Mnth Day Year InvDate BaseAmt Dispute Terms PayClass PayDate NumPaid TotalPaid NumLate TotalLate RatioNum RatioTotal DaysLate AvgDaysLat NumOut StartLate DOTTOO1 11 26 2015 11/26/15 23384 N 30 3 1/29/16 9 389536 209 3895360 34 97.09091 49 12/26/15 DOTTOO1 14 2015 6/14/15 15918 No 30 11/10/15 136 2554741 136 2554741 119 96.52941 55 7/14/15 DOTTOO1 12 1 2015 12/1/15 19287 No 30 4/3/16 209 3895360 209 3895360 94 97.09091 51 12/31/15 DOTTOO1 25 2015 7/25/15 22476 No 30 12/18/15 155 2922709 155 2922709 116 97.14194 53 8/24/15 DOTTOO1 2 15 2015 2/15/15 38769 No 30 6/26/15 103 1934352 103 1934352 101 95.53398 35 3/17/15 DOTTOO1 7 28228 N 30 51 2015 5/7/15 9/16/15 124 2315706 124 2315706 102 96.55645 6/6/15 DOTTOO1 4 27 2014 4/27/14 26635 Ye 30 9/23/14 63650 63650 119 24 39 5/27/14 DOTTOO1 5 27 2014 5/27/14 18781 No 30 10/22/14 LO 167509 10 167509 118 65.3 40 6/26/14 DOTTOO1 11 27 2015 11/27/15 16425 No 30 UI UI UT 4/15/16 9 3895360 209 3895360 110 97.09091 50 12/27/15 DOTTOO 9 11 2014 /11/14 20033 No 30 1/19/15 15 815857 45 815857 100 90.62222 48 10/11/14 DOTTOO1 10 25 2015 10/25/15 17903 No 30 1/1/16 360329 193 3603299 38 96.5129 51 11/24/15 DOTTOO1 2015 7/2/15 27444 No 30 n u W w 9/19/15 143 2705869 143 2705869 49 96.83217 58 8/1/15 AH N 14 DOTTOO1 2014 6/1/14 15019 No 30 10/24/14 12 217503 12 217503 115 69.25 41 7/1/14 54 15 DOTTO01 2015 8/1/15 5851 N 30 12/15/15 158 3015011 158 3015011 106 97.07595 8/31/15 DOTTOO1 18 7/18/14 8/17/14 2014 28128 N 30 12/15/14 27 468702 27 468702 1 120 83.9629 47 DOTTOO1 2014 4/9/14 18027 No 30 3/25/14 63650 63650 BOT 32 5/9/14 18 DOTTOO1 6 21 2015 ;/21/15 14232 No 30 9/12/15 138 2598100 138 2598100 1 53 96.62319 57 7/21/15 19 DOTTOO1 2014 7/1/14 22111 No 30 11/14/14 24 423102 24 423102 106 81.625 38 7/31/14 47 20 DOTTOO1 11 11 2015 11/11/15 26803 N 30 3/23/16 203 3786872 203 3786872 103 96.97044 12/11/15 DOTTOO1 7 2015 7/7/15 23100 No 30 11/25/15 145 2742171 145 2742171 111 96.84828 50 8/6/15 DOTTOO1 21 2015 4/21/15 18907 No 30 8/12/15 121 2264786 121 2264786 83 96.23967 47 5/21/15 DOTTOO1 11 2015 1/3/15 8767 No 30 3/12/16 198 3701547 198 3701547 100 96.70707 49 12/3/15 w w 2 2014 2/3/14 29877 N 30 5/30/14 86 17 DOTTOO1 3/5/14 1 UI UI UI A UI A UTI 10 22 2015 10/22/15 28231 No 30 2/21/16 192 3602849 192 3602849 92 96.44792 49 11/21/15 DOTTOO1 DOTTOO1 5 2015 6/5/15 450 No 30 10/22/15 136 2554741 136 2554741 109 96.52941 50 7/5/15 12 24 2015 12/24/15 11911 No 30 5/17/16 219 4065314 219 4065314 115 51 1/23/16 DOTTOO1 97.3379 DOTTOO1 25 2015 7/25/15 4168 No 30 12/18/15 155 2922709 155 2922709 116 97.14194 53 8/24/15 29 DOTTOO1 27 2015 3/27/15 21964 No 30 5/20/15 113 2127644 113 2127644 24 95.67257 45 4/26/15 5w 30 DOTTOO1 10 6 2014 10/6/14 29235 N 30 2/28/15 51 941682 51 941682 115 92.19608 47 11/5/14 DOTTOO1 2 15 2015 2/15/15 22892 No 30 6/29/15 103 1934352 103 1934352 1 104 95.53398 35 3/17/15 12 27 2014 12/27/14 20915 No 30 5/10/15 86 1606593 86 1606593 104 95.98837 38 DOTTOO1 1/26/15 33 DOTTO01 3 26 2015 3/26/15 43004 No 30 7/25/15 112 2098112 112 2098112 1 91 95.59821 44 4/25/15 37 7/25/14 DOTTOO1 25 2014 5/25/14 14601 No 30 11/1/14 23 393618 23 393618 81 35 DOTTO01 25 2014 1/25/14 23744 No 30 6/12/14 0 0 10 2/24/14 PO DOTTOO1 7 2014 8/7/14 22058 No 30 12/6/14 32 574563 32 574563 91 87.5625 47 9/6/14 DOTTOO1 24 2015 12/24/15 16613 No 30 5/9/16 219 4065314 219 4065314 1 97.3379 51 1/23/16 38 DOTTO01 28 2015 5/28/15 28821 N 30 10/29/15 140 266178 140 2661785 93 96.78571 60 7/28/15 10 8 48 39 DOTTOO1 2014 10/8/14 9395 No 30 11/7/14 UT 2/13/15 52 962983 52 962983 1 98 91.92308 48 12/6/14 DOTTO01 11 2014 11/6/14 24006 No 30 12/31/14 52 1129339 ;2 1129339 25 94.70968 Sustain Dye Revised 100% Ready3 Sustain Dye Source Data Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat '+ Share Calibri (Body) 11 A General Conditional Formatting Insert Format as Table Delete .00 Paste B I U A $ % Editing Create and Share Cell Styles Format Adobe PDF 110 X V fx 5 B C D E G H M R S Customer Moth Day Year InvDate BaseAmt Dispute Terms PayClass PayDate NumPaid TotalPaid d NumLate TotalLate RatioNum RatioTotal DaysLate AvgDaysLat NumOut StartLate 55 DOTTO01 15 2015 3/15/15 34060 No 30 51 7/21/15 112 2098112 112 2098112 98 95.59821 39 4/14/15 5DOTTOO1 24 2014 4/24/14 33873 No 30 8/28/14 63650 63650 96 24 38 5/24/14 n UT DOTTOO1 28 2015 10/28/15 18225 No 30 3/4/16 193 3603299 193 3603299 98 96.51295 51 11/27/15 58 DOTTO01 1 18 2014 1/18/14 13811 No 30 6/3/14 0 0 0 106 8 2/17/14 59 DOTTO01 10 2015 9/10/15 22033 No 30 1/23/16 176 3319368 176 3319368 105 97.03409 53 10/10/15 60 DOTTOO1 18 2015 8/18/15 13126 No 30 10/20/15 166 3138787 166 3138787 33 96.78916 52 9/17/15 61 DOTTO01 9 28 2014 /28/14 8531 No 30 2/22/15 48 874810 48 874810 117 91.27083 47 10/28/14 OHH 62 DOTTOO1 25 2014 1/25/14 13714 No 30 6/4/14 0 0 100 10 2/24/14 63 DOTTO01 5 14 2015 5/14/15 12536 No 30 9/21/15 129 2419451 129 2419451 1 100 96.67442 50 6/13/15 64 DOTTOO1 2014 8/5/14 18143 No 30 12/24/14 32 574563 32 574563 1 87.5625 46 9/4/14 65 DOTTO01 3 1 2015 3/1/15 450 No 30 6/30/15 110 2072183 110 2072183 1 91 95.25455 37 3/31/15 66 DOTTOO1 11 27 2015 11/27/15 22004 No 30 4/9/16 209 3895360 209 3895360 1 104 97.09091 50 12/27/15 67 DOTTOO1 16 2014 1/16/14 1626 No 30 3/3/14 16 2/15/14 68 DOTTOO1 20 2015 2/20/15 2623 No 30 7/12/15 103 1934352 103 1934352 1 112 95.53398 38 3/22/15 69 DOTTO01 11 17 2014 11/17/14 20739 N 30 4/5/15 66 1215818 56 1215818 109 95.19697 48 12/17/14 70 DOTTOO1 11 19 2014 11/19/14 29888 No 30 4/7/15 58 1255923 58 1255923 1 109 95.44118 48 12/19/14 DOTTOO1 7 18 2014 7/18/14 30373 No 30 11/26/14 27 468702 27 468702 1 101 83.96296 17 8/17/14 DOTTO01 10 2015 4/10/15 450 No 30 8/14/15 120 2243643 120 2243643 96 96.35833 40 5/10/15 DOTTOO1 25 2015 6/25/15 28983 No 30 11/20/15 139 2623016 139 2623016 18 96.7554 58 7/25/15 DOTTOO1 9 2015 8/9/15 22308 N 30 12/10/1 161 3053590 161 3053590 93 97.11801 53 9/8/15 DOTTOO1 12 2015 5/12/15 1691 No 30 9/24/15 129 2419451 129 2419451 105 96.67442 48 6/11/15 DOTTOO1 24 2014 2/24/14 14659 N 30 7/6/14 0 102 24 3/26/14 D ANU DOTTO01 3 2014 4/3/14 5124 No 30 8/20/14 3 56722 3 56722 1 109 13.33333 31 5/3/14 DOTTOO1 25 2015 4/25/15 15408 N 30 9/9/15 121 2264786 121 2264786 107 96.23967 50 5/25/15 DOTTOO1 12 28 2015 12/28/15 7357 No 30 5/26/16 220 4073594 220 4073594 1 120 97.31364 54 1/27/16 8 17 2015 8/17/15 18436 No 30 1/11/16 166 3138787 166 3138787 117 96.78916 51 80 DOTTO01 9/16/15 81 DOTTO01 10 11 2015 10/11/15 27080 No 30 1/2/16 189 3554206 189 3554206 1 53 96.71429 51 11/10/15 82 DOTTO01 14 2014 1/14/14 27119 No 30 5/19/14 ol 95 2/13/14 DOTTOO1 11 6 2015 11/6/15 18398 No 30 3/19/16 198 3701547 198 3701547 1 104 96.70707 49 12/6/15 84 DOTTO01 22 2015 7/22/15 32380 No 30 12/13/15 152 2893464 152 2893464 114 96.88816 54 8/21/15 85 DOTTO01 26 2015 5/26/15 20721 No 30 10/28/15 139 2623016 139 2623016 94 96.7554 58 7/26/15 86 DOTTOO1 2015 7/8/15 17186 No 30 11/10/15 145 2742171 145 2742171 95 96.84828 60 8/7/15 87 DOTTOO1 4 2014 4/1/14 7518 No 30 8/19/14 56722 3 56722 110 13.33333 31 5/1/14 88 DOTTO01 10 2014 10/4/14 8634 No 30 12/7/14 51 941682 51 941682 34 92.19608 47 11/3/14 89 DOTTOO1 5 28 2014 5/28/14 26144 N 30 10/2/14 L1 187626 11 187626 97 67.72727 41 6/27/14 90 DOTTOO1 25 2015 11/25/15 19840 No 30 4/8/16 208 3872260 208 3872260 1 105 97.02404 48 12/25/15 91 DOTTOO1 23 2015 9/23/15 11794 No 30 12/1/15 183 3479561 183 3479561 1 39 96.62295 49 10/23/15 92 DOTTOO1 25 2014 12/25/14 14819 N 30 5/2/15 86 1606593 86 1606593 1 98 95.98837 36 1/24/15 93 DOTTOO1 21 2014 3/21/14 20278 N 30 8/18/14 1626 1626 1 120 16 29 4/20/14 Sustain Dye Revised Ready Lod 100%3 Sustain Dye Source Data Q Search Sheet Home Insert Draw Page Layout Formulas Data Review View Acrobat '+ Share Conditional Formatting Insert Calibri (Body) 11 A General Format as Table Delete Paste B I U A $ % .00 Editing Create and Share Cell Styles Format Adobe PDF 110 X V fx 5 R A B C D E G H M D S Customer Mnth Day Year InvDate BaseAmt Dispute Terms PayClass PayDate 4/ 10715 NumPaid TotalPaid NumLate TotalLate RatioNum RatioTotal DaysLate AvgDaysLat NumOut StartLate DUTTOUT CHTTZ QCOCACT OrOctICT 0Z JJ. 040 04 1/ 10 / 15 DOTTOO1 4 14 2015 1/14/15 30171 N 30 8/31/15 121 2264786 121 2264786 109 96.23967 44 5/14/15 DOTTOO1 10 6 2015 10/6/15 30152 No 30 2/18/16 187 3516874 187 3516874 105 96.74866 50 11/5/15 DOTTOO1 9 2015 3/7/15 9533 No 30 2/2/16 175 3303960 175 3303960 118 96.97714 52 10/7/15 4 DOTTOO1 2014 9/1/14 17172 No 30 1/23/15 14 794346 44 794346 114 90.34091 42 10/1/14 .5 DOTTO01 9 2014 /7/14 18227 No 30 1/31/15 44 794346 44 794346 116 90.34091 45 10/7/14 DOTTOO1 2015 13/15 19077 No 30 11/9/15 143 2705869 143 2705869 99 96.83217 60 8/2/15 W DOTTOO1 2015 1/9/15 19650 No 30 5/29/15 89 1654971 89 1654971 110 94.10112 41 2/8/15 8 DOTTOO1 14 2014 5/14/14 9177 No 30 9/13/14 108146 108146 92 51.71429 38 6/13/14 100 UTH DOTTOO1 15 2015 3/15/15 16711 No 30 1/1/1 164 3095101 164 3095101 109 96.65244 52 9/14/15 DOTTOO1 21 2014 10/21/14 26738 No 30 2/22/15 56 1034125 56 1034125 94 92.92857 50 11/20/14 1 DOTTOO1 17 2015 5/17/15 7616 No 30 9/23/15 130 2443529 130 2443529 99 96.63846 51 6/16/15 2 DOTTOO1 11 16 2015 11/16/15 19379 No 30 4/6/16 3 3786872 203 3786872 112 96.97044 51 12/16/15 12 6 12/6/15 16510 No 30 5/2/16 211 3922933 211 3922933 118 96.91469 53 3 DOTTO01 2015 1/5/16 DOTTOO1 11 17 2015 11/17/15 17108 No 30 4/11/16 203 3786872 203 3786872 116 96.97044 51 12/17/15 5 DOTTOO1 23 2015 2/23/15 1704 No 30 7/22/15 106 2003117 106 2003117 119 95.79245 36 3/25/15 JANI DOTTOO1 24 2015 4/24/15 6089 No 30 8/24/15 121 2264786 121 2264786 1 92 96.23967 49 5/24/15 127 DOTTO01 12 9 2015 12/9/15 31680 No 30 5/5/16 211 3922933 211 3922933 118 96.91469 54 1/8/16 DOTTOO1 1 2015 1/4/15 19994 No 30 5/7/15 89 1654971 89 1654971 1 93 94.10112 38 2/3/15 19 DOTTOO1 22 2014 2/22/14 29484 No 30 6/28/14 o 96 0 23 3/24/14 30 DOTTOO1 27 2014 4/27/14 21511 No 30 9/7/14 63650 4 63650 1 103 24 39 5/27/14 131 DOTTO01 28 2014 5/28/14 28972 No 30 11/14/14 23 393618 23 39361 1 10 81 37 7/28/14 DO HOAN 132 DOTTOO1 8 2015 1/8/15 25421 No 30 5/31/15 89 1654971 89 1654971 1 113 94.10112 40 2/7/15 133 DOTTO01 15 2015 6/15/15 13121 No 30 11/6/15 137 2586668 37 2586668 114 96.48905 55 7/15/15 DOTTOO1 21 2014 8/21/14 14518 No 30 12/23/14 39 686831 39 686831 1 94 88.87179 44 9/20/14 DOTTOO1 23 2015 1/23/15 12908 No 30 2/27/15 94 1751910 94 1751910 5 94.723 40 2/22/15 DOTTOO1 21 2015 5/21/15 19870 N 30 9/15/15 132 2475606 132 2475606 87 96.1969 50 6/20/15 140 00 UT H DOTTOO1 2014 8/3/14 9175 No 30 12/20/14 32 574563 32 574563 109 87.5625 45 9/2/14 00 W 138 DOTTO01 2014 9/8/14 25212 No 30 1/15/15 15 815857 45 815857 99 90.62222 46 10/8/14 DOTTOO1 10 13 2014 10/13/14 10832 No 30 2/24/15 53 972761 3 972761 104 92.09434 50 11/12/14 DOTTOO1 12 26 2014 12/26/14 26335 No 30 5/11/15 86 1606593 86 1606593 106 95.98837 37 1/25/15 1 DOTTOO1 4 2015 4/1/15 25996 No 30 8/8/15 116 2145503 116 2145503 99 96.02586 42 5/1/15 142 DOTTO01 1 2015 1/19/15 29894 N 30 5/25/15 2 1711178 92 1711178 96 94.42391 40 2/18/15 DOTTOO1 14 2015 9/14/15 17951 No 30 1/23/16 178 3358173 178 3358173 101 96.86517 52 10/14/15 DOTTOO1 15 2014 3/15/14 27590 No 30 8/2/14 1626 1626 110 28 4/14/14 45 DOTTO01 8 00 00 A W 10 + + 15 2015 1/15/15 19493 No 30 7/19/15 121 2264786 121 2264786 1 65 96.23967 45 5/15/15 146 DOTTO01 26 2015 8/26/15 19286 No 30 1/5/16 7 3144876 167 3144876 102 96.76048 52 9/25/15 147 DOTTOO1 24 2015 8/24/15 8280 No 30 12/24/15 166 3138787 166 3138787 1 92 96.78916 52 9/23/15 DOTTOO1 3 2015 10/3/15 15789 No 30 2/14/16 7 3516874 187 3516874 104 96.7486 50 11/2/15 149 DOTTOO1 6 12 2015 6/12/15 18603 No 30 11/1/15 136 2554741 136 2554741 1 112 96.52941 55 7/12/15 Sustain Dye Revised Ready 100%
