Please show work step-by-step for each question!

F G H J A B B C 1 PurchaseID LPurchase Date Customer 2 2 1/1/16 3 1 1/1/16 4 3 1/2/16 5 4 1/6/16 6 6 6 1/8/16 7 5 1/8/16 8 7 1/9/16 9 9 1/11/16 10 8 1/11/16 11 10 1/13/16 12 12 1/22/16 13 11 1/22/16 14 13 1/27/16 15 14 1/30/16 16 15 2/2/16 17 16 2/7/16 18 17 2/10/16 19 18 2/12/16 20 19 2/13/16 21 20 2/13/16 22 21 2/14/16 23 22 2/14/16 24 23 2/17/16 25 24 2/21/16 26 25 2/22/16 27 27 2/28/16 28 26 2/28/16 29 28 3/1/16 30 29 3/4/16 31 30 3/6/16 32 31 3/6/16 33 33 3/8/16 34 32 3/8/16 35 34 3/8/16 36 35 3/9/16 37 36 3/16/16 38 37 3/18/16 39 38 3/20/16 40 39 3/22/16 41 40 3/24/16 42 41 3/28/16 43 42 3/29/16 44 43 4/3/16 45 44 4/3/16 46 45 4/13/16 47 46 4/14/16 48 47 4/16/16 49 48 4/17/16 50 49 4/18/16 51 50 4/18/16 52 51 4/19/16 53 52 4/23/16 54 53 4/25/16 55 54 4/25/16 56 55 4/26/16 57 56 4/28/16 58 57 5/1/16 59 58 5/2/16 60 59 5/7/16 61 60 5/9/16 62 61 5/10/16 63 62 5/11/16 64 63 5/11/16 65 64 5/13/16 D E E Employee Category 42 Adams Intimates 38 Adams Jewelry 30 English Clothing 46 Adams Intimates 32 Kidd Shoes 38 Adams Clothing 4 Brock Shoes 49 English Intimates 2 Brock Jewelry 7 Adams Jewelry 39 Adams Shoes 33 English Clothing 43 Adams Intimates 28 English Jewelry 32 English Jewelry 11 English Clothing 39 English Clothing 7 Brock Jewelry 12 Kidd Shoes 46 English Intimates 1 Brock Shoes 34 Adams Intimates 7 Kidd Clothing 21 Adams Clothing 43 English Jewelry 49 Adams Jewelry 3 Adams Clothing 8 Kidd Intimates 34 Kidd Intimates 31 Adams Jewelry 4 English Clothing 4 Adams Shoes 35 Adams Clothing 15 Adams Jewelry 25 English Intimates 2 Brock Intimates 47 English Jewelry 21 Brock Shoes 44 Adams Jewelry 31 English Jewelry 37 Adams Clothing 27 English Intimates 41 Brock Jewelry 8 English Intimates 24 Adams Shoes 15 Kidd Clothing 22 English Shoes 6 English Shoes 5 Brock 5 Shoes 2 English Intimates 29 Kidd Intimates 36 Kidd Jewelry 47 Kidd Clothing 48 English Intimates 8 English Intimates 44 Kidd Shoes 18 English Intimates 26 Adams Shoes 50 English Jewelry 18 English Clothing 34 Kidd Clothing 39 Adams Jewelry 48 Kidd Shoes 18 Brock Jewelry Price Quantity $ 24.99 $ 7.99 $ 8.50 $ 13.00 $15.99 $ 9.00 $ 24.99 $ 24.99 $ 15.99 $ 15.99 $ 10.99 $ 12.00 $ 24.99 $ 15.99 $ 24.99 $ 9.00 $ 12.00 $ 8.50 $ 24.99 $ 15.99 $ 7.99 $ 24.99 $ 15.99 $ 65.00 $ 7.99 $ 13.00 $ 10.99 $ 15.99 $ 10.99 $ 15.99 $ 9.00 $ 7.99 $ 24.99 $ 24.99 $ 9.00 $ 7.99 $ 65.00 $ 8.50 $ 24.99 $ 24.99 $ 15.99 $ 9.00 $ 15.99 $ 9.00 $ 8.50 $ 15.99 $ 8.50 $ 24.99 $ 24.99 $ 24.99 $ 8.50 $ 9.00 $ 7.25 $ 7.99 $ 12.00 $15.99 $ 12.00 $ 15.99 $ 15.99 $ 8.99 $ 15.99 $ 24.99 $ 15.99 $ 24.99 Sales Total Month Year 3 $74.97 01-Jan 2016 6 $47.94 01-Jan 2016 4 $34.00 01-Jan 2016 3 $39.00 01-Jan 2016 3 3 $47.97 01-Jan 2016 3 $27.00 01-Jan 2016 3 $74.97 01-Jan 2016 6 6 $149.94 01-Jan 2016 9 $143.91 01-Jan 2016 8 $127.92 01-Jan 2016 6 $65.94 01-Jan 2016 1 $12.00 01-Jan 2016 7 $174.93 01-Jan 2016 2 $31.98 01-Jan 2016 8 $199.92 02-Feb 2016 3 $27.00 02-Feb 2016 1 $12.00 02-Feb 2016 4 $34.00 02-Feb 2016 9 $224.91 02-Feb 2016 2 $31.98 02-Feb 2016 8 $63.92 02-Feb 2016 9 $ 224.91 02-Feb 2016 6 $95.94 02-Feb 2016 7 $455.00 02-Feb 2016 6 $47.94 02-Feb 2016 3 $39.00 02-Feb 2016 8 $87.92 02-Feb 2016 4 $63.96 02-Feb 2016 8 $87.92 03-Mar 2016 9 $143.91 03-Mar 2016 5 $45.00 03-Mar 2016 7 $55.93 03-Mar 2016 8 $199.92 03-Mar 2016 3 $74.97 03-Mar 2016 9 $81.00 03-Mar 2016 3 $23.97 03-Mar 2016 2 $ 130.00 03-Mar 2016 5 $42.50 03-Mar 2016 3 $74.97 03-Mar 2016 5 $124.95 03-Mar 2016 1 1 $15.99 03-Mar 2016 8 $72.00 03-Mar 2016 8 $127.92 04-Apr 2016 1 $9.00 04-Apr 2016 5 $42.50 04-Apr 2016 3 $47.97 04-Apr 2016 6 $51.00 04-Apr 2016 2 2 $49.98 04-Apr 2016 9 $224.91 04-Apr 2016 6 $149.94 04-Apr 2016 8 $68.00 04-Apr 2016 5 $45.00 04-Apr 2016 7 $50.75 04-Apr 2016 9 $71.91 04-Apr 2016 6 $72.00 04-Apr 2016 6 $95.94 04-Apr 2016 9 $108.00 05-May 2016 1 $15.99 05-May 2016 9 $143.91 05-May 2016 2 $17.98 05-May 2016 3 3 $47.97 05-May 2016 1 $24.99 05-May 2016 1 $15.99 05-May 2016 2 $49.98 05-May 2016 PurchaselD PurchaseDate CustomerlD Price Quantity Year 66 65 67 66 67 68 2016 2016 2016 2016 2016 2016 2016 68 69 70 71 72 73 69 70 71 72 2016 2016 74 73 76 2016 2016 75 74 77 75 76 77 78 79 80 81 82 83 84 85 86 78 79 81 2016 2016 2016 2016 2016 2016 2016 2016 80 83 82 84 2016 2016 85 87 86 87 88 89 88 2016 2016 2016 2016 2016 89 90 91 92 93 2016 2016 2016 2016 2016 2016 94 95 96 Employee 33 Brock 5 Adams 3 Adams 43 Brock 44 Kidd 6 English 16 Kidd 36 Kidd 14 Adams 11 Kidd 43 Kidd 31 Brock 35 Brock 49 Adams 18 Kidd 39 Brock 2 Adams 42 English 27 English 26 Brock 33 Brock 41 Brock 24 English 24 Kidd 28 Adams 9 Kidd 23 English 16 Adams 43 Kidd 16 Kidd 29 Kidd 20 Brock 8 Brock 23 English 32 Adams 14 English 27 Kidd 41 Adams 39 Kidd 15 Adams 11 English 12 Adams 19 Adams 28 English 32 Brock 43 Brock 28 English 44 English 13 Kidd 26 Kidd 27 Brock 22 Kidd 8 Adams 23 Adams 10 Brock 28 English 8 Brock 8 10 English 33 Kidd 15 Brock 22 English 23 English 39 Brock 43 Adams 24 Adams 5/16/16 5/17/16 5/19/16 5/28/16 5/30/16 5/31/16 6/3/16 6/5/16 6/6/16 6/7/16 6/7/16 6/7/16 6/8/16 6/10/16 6/13/16 6/18/16 6/18/16 6/22/16 6/22/16 6/29/16 7/4/16 7/5/16 7/5/16 7/7/16 7/11/16 7/13/16 7/14/16 7/17/16 7/22/16 7/23/16 7/26/16 7/28/16 7/29/16 7/31/16 8/1/16 8/3/16 8/7/16 8/9/16 8/11/16 8/11/16 8/15/16 8/16/16 8/16/16 8/18/16 8/19/16 8/22/16 8/27/16 8/28/16 9/1/16 9/2/16 9/3/16 9/3/16 9/5/16 9/7/16 9/10/16 9/10/16 9/11/16 9/18/16 9/20/16 9/20/16 9/24/16 9/27/16 10/2/16 10/4/16 10/6/16 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 Category Shoes Shoes Clothing Jewelry Clothing Clothing Intimates Shoes Shoes Jewelry Jewelry Jewelry Clothing Shoes Shoes Shoes Jewelry Clothing Shoes Intimates Shoes Intimates Intimates Jewelry Jewelry Intimates Clothing Clothing Intimates Clothing Shoes Jewelry Shoes Intimates Jewelry Jewelry Jewelry Intimates Clothing Intimates Shoes Shoes Shoes Clothing Shoes Intimates Shoes Intimates Jewelry Shoes Clothing Clothing Intimates Jewelry Intimates Intimates Intimates Shoes Jewelry Jewelry Intimates Clothing Clothing Clothing Intimates 97 Sales Total Month 1 $24.99 05-May 4 4 $63.96 05-May 4 $99.96 05-May 6 $53.94 05-May 8 $199.92 05-May 1 1 $15.99 05-May 4 $63.96 06-Jun 4 4 $63.96 06-Jun 5 $60.00 06-Jun 4 4 $34.00 06-Jun 7 $91.00 06-Jun 1 $15.99 06-Jun 8 $127.92 06-Jun 3 $74.97 06-Jun 1 $12.99 06-Jun 6 $149.94 06-Jun 7 $111.93 06-Jun 8 $127.92 06-Jun 7 $59.50 06-Jun 1 1 $8.99 06-Jun 7 $455.00 07-Jul 2 $49.98 07-Jul 9 $76.50 07-Jul 8 $199.92 07-Jul 8 $63.92 07-Jul 4 $51.96 07-Jul 1 $15.99 07-Jul 7 $174.93 07-Jul 8 $63.92 07-Jul 6 $41.94 07-Jul 7 $174.93 07-Jul 7 $455.00 07-Jul 4 $63.96 07-Jul 9 $224.91 07-Jul 9 $585.00 08-Aug 2 $31.98 08-Aug 3 3 $47.97 08-Aug 5 $325.00 08-Aug 9 $ 224.91 08-Aug 4 $63.96 08-Aug 1 $12.00 08-Aug 8 $127.92 08-Aug 1 $6.99 08-Aug 7 $174.93 08-Aug 2 $17.00 08-Aug 1 $24.99 08-Aug 6 $65.94 08-Aug 9 $81.00 08-Aug 5 $60.00 09-Sep 8 $127.92 09-Sep 6 $51.00 09-Sep 9 9 $ 224.91 09-Sep 5 $79.95 09-Sep 5 $124.95 09-Sep 1 $15.99 09-Sep 3 $39.00 09-Sep 4 $34.00 09-Sep 8 $199.92 09-Sep 2 $49.98 09-Sep 8 $55.92 09-Sep 4 $63.96 09-Sep 3 $74.97 09-Sep 4 $48.00 10-Oct 4 $99.96 10-Oct 8 $127.92 10-Oct $ 24.99 $15.99 $ 24.99 $ 8.99 $ 24.99 $15.99 $ 15.99 $ 15.99 $ 12.00 $ 8.50 $ 13.00 $ 15.99 $ 15.99 $ 24.99 $ 12.99 $ 24.99 $ 15.99 $15.99 $ 8.50 $ 8.99 $ 65.00 $24.99 $ 8.50 $ 24.99 $ 7.99 $ 12.99 $ 15.99 $ 24.99 $ 7.99 $ $ 6.99 $ 24.99 $ 65.00 $ 15.99 $ 24.99 $ 65.00 $ 15.99 $15.99 $ 65.00 $ 24.99 $ 15.99 $ 12.00 $ 15.99 $ 6.99 $ 24.99 $ 8.50 $ 24.99 $ 10.99 $ 9.00 $ 12.00 $ 15.99 $8.50 $ 24.99 $ 15.99 $ 24.99 $ 15.99 $ 13.00 $ 8.50 $ 24.99 $ 24.99 $ 6.99 $ 15.99 $ 24.99 $ 12.00 $ 24.99 $ 15.99 2016 98 2016 99 100 101 102 103 104 105 107 106 108 109 110 111 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 108 109 110 111 112 113 112 113 114 115 116 114 116 115 2016 2016 2016 2016 117 118 119 120 117 118 119 121 120 121 122 123 124 125 122 123 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 124 125 126 126 127 128 129 130 127 128 2016 2016 129 PurchaselD PurchaseDate CustomerlD Employee Category Price Quantity Sales Total Month 6 Year 2016 2016 130 131 132 131 132 133 134 135 4 2 2016 133 9 9 9 9 136 137 138 6 7 134 135 136 137 138 139 2016 2016 2016 2016 2016 8 139 6 140 4 2016 2016 2016 2016 4 9 141 142 143 144 140 142 141 143 5 2016 3 2016 145 9 9 2016 144 145 146 3 3 146 9 147 3 9 9 148 149 5 2016 2016 2016 2016 2016 2016 2016 2016 2016 150 9 151 147 148 149 150 151 152 153 154 155 156 157 158 1 152 5 153 8 154 1 2016 155 4 156 1 1 2016 2016 2016 157 4 159 1 2016 1 158 159 160 161 162 1 2016 2016 2016 160 161 162 163 164 165 166 8 10/7/16 10/8/16 10/9/16 10/15/16 10/20/16 10/21/16 10/23/16 10/23/16 10/26/16 10/28/16 10/29/16 11/2/16 11/2/16 11/4/16 11/7/16 11/9/16 11/13/16 11/13/16 11/14/16 11/17/16 11/18/16 11/24/16 11/26/16 11/28/16 11/29/16 12/3/16 12/6/16 12/6/16 12/8/16 12/10/16 12/11/16 12/11/16 12/16/16 12/17/16 12/17/16 12/20/16 12/25/16 12/27/16 12/30/16 1/6/17 1/9/17 1/12/17 1/13/17 1/13/17 1/14/17 1/17/17 1/18/17 1/18/17 1/23/17 1/25/17 1/25/17 1/26/17 1/28/17 2/2/17 2/2/17 2/6/17 2/6/17 2/9/17 2/9/17 2/9/17 2/10/17 2/10/17 2/15/17 2/17/17 2/22/17 50 Kidd 24 Brock 48 Adams 32 Brock 46 English 26 English 7 English 18 Adams 44 Adams 32 English 12 Kidd 35 English 36 Brock 44 English 4 English 2 Brock 6 Kidd 15 English 31 Adams 30 English 17 English 14 Kidd 12 English 14 English 5 English 25 English 43 English 39 Adams 48 Kidd 40 Kidd 21 English 30 Adams 19 Adams 16 Kidd 14 English 14 Adams 46 English 40 Kidd 40 Brock 35 English 23 English 43 Kidd 33 Brock 21 Kidd 29 English 12 English 45 Kidd 13 English 8 English 29 Brock 47 Kidd 4 Kidd 16 Brock 30 Adams 25 Kidd 37 Adams 15 Adams 9 9 Brock 39 Brock 5 Adams 8 Brock 24 Adams 44 English 39 Brock 45 Kidd Clothing Clothing Jewelry Clothing Intimates Shoes Clothing Clothing Clothing Intimates Shoes Jewelry Clothing Clothing Jewelry Clothing Shoes Shoes Intimates Shoes Jewelry Jewelry Jewelry Shoes Jewelry Shoes Intimates Shoes Shoes Jewelry Clothing Shoes Clothing Clothing Clothing Intimates Clothing Jewelry Clothing Shoes Intimates Shoes Jewelry Jewelry Intimates Clothing Shoes Clothing Intimates Intimates Shoes Clothing Clothing Shoes Intimates Shoes Jewelry Clothing Shoes Shoes Shoes Shoes Clothing Jewelry Shoes $ 12.99 $ $ 9.00 $ 15.99 $ 15.99 $ 24.99 $15.99 $ 24.99 $ 24.99 $ 65.00 $ 13.00 $ 24.99 $ 65.00 $ 24.99 $ 12.99 $ 24.99 $ 10.99 $ 7.99 $ 15.99 $ 7.25 $ 65.00 $ 24.99 $ 8.50 $ 24.99 $ 24.99 $ $ 8.50 $ 24.99 $ 24.99 $ 12.00 $ 12.00 $ 24.99 $ 24.99 $ 7.99 $ 24.99 $15.99 $ 24.99 $ 15.99 $ 24.99 $ 12.00 $15.99 $ 24.99 $ 7.99 $ 24.99 $ 8.50 $ 12.00 $ 10.99 $ 12.00 $ 6.99 $ 7.25 $ 7.99 $ 24.99 $ $ 8.50 $ 9.00 $ 7.25 $ 12.00 $ 24.99 $ 24.99 $ 6.99 $15.99 $ $ 8.99 $ 24.99 $ 6.99 $ 24.99 $ 15.99 $ 8.99 $ 7.25 3 $77.94 10-Oct $36.00 10-Oct $31.98 10-Oct $143.91 10-Oct $224.91 10-Oct $95.94 10-Oct $174.93 10-Oct $199.92 10-Oct $390.00 10-Oct $52.00 10-Oct $99.96 10-Oct $585.00 11-Nov $124.95 11-Nov $38.97 11-Nov $224.91 11-Nov $32.97 11-Nov $71.91 11-Nov $47.97 11-Nov $65.25 11-Nov $325.00 11-Nov $224.91 11-Nov $8.50 11-Nov $124.95 11-Nov $199.92 11-Nov $8.50 11-Nov $99.96 12-Dec $24.99 12-Dec $48.00 12-Dec $12.00 12-Dec $24.99 12-Dec $24.99 12-Dec $63.92 12-Dec $74.97 12-Dec $15.99 12-Dec $24.99 12-Dec $127.92 12-Dec $24.99 12-Dec $48.00 12-Dec $63.96 12-Dec $124.95 01-Jan $55.93 01-Jan $224.91 01-Jan $25.50 01-Jan $108.00 01-Jan $76.93 01-Jan $84.00 01-Jan $34.95 01-Jan $58.00 01-Jan $7.99 01-Jan $24.99 01-Jan $59.50 01-Jan $72.00 01-Jan $50.75 01-Jan $12.00 02-Feb $224.91 02-Feb $24.99 02-Feb $20.97 02-Feb $143.91 02-Feb $44.95 02-Feb $49.98 02-Feb $20.97 02-Feb $224.91 02-Feb $31.98 02-Feb $80.91 02-Feb $14.50 02-Feb 2016 1 164 163 2016 2016 1 165 2016 8 12 1 167 166 167 168 4 4 2016 2016 2016 2017 168 4 169 170 171 5 169 170 171 2017 7 7 9 172 173 3 173 174 175 9 172 174 175 7 2017 2017 2017 2017 2017 2017 2017 176 7 177 5 176 8 1 2017 178 179 1 2017 180 7 2017 2017 181 8 177 178 179 180 181 182 183 184 185 186 187 188 189 182 7 2017 2017 184 1 1 183 9 2017 1 2017 186 185 189 3 2017 2017 9 188 5 2017 2017 190 187 2 191 3 2017 191 192 193 190 9 2017 192 2 2017 194 9 9 193 194 2017 2017 195 2 PurchaselD PurchaseDate CustomerlD Employee Category Price Quantity Sales Total Month Year 2 195 196 4 4 197 198 3 194 195 196 197 198 199 200 201 202 2017 2017 2017 2017 2017 2017 2017 2017 5 199 200 201 202 203 5 9 6 2017 8 2 204 203 2017 7 205 204 207 4 $ 7.25 $ 15.99 $ 12.00 $ 15.99 $ 15.99 $ 7.99 $ 24.99 $ $ 8.50 $ 15.99 $ 12.00 $ 7.99 $ 24.99 $15.99 $ 65.00 $ 15.99 $ 15.99 $ 12.00 $ 15.99 $ 15.99 $15.99 15.99 24.9899 8.99 15.99 8.99 205 206 207 208 209 210 211 3 2017 2017 2017 2017 2017 2017 206 7 209 208 210 5 3 5 212 1 5 2017 2017 2017 2017 2017 5 4 4 213 214 215 216 217 218 211 212 213 214 215 216 217 218 219 7 5 6 219 3 3 5 15.99 220 7 220 221 222 223 224 1 221 222 223 224 225 2 2 3 225 3 9 9 226 227 2/22/17 2/26/17 2/26/17 3/7/17 3/7/17 3/8/17 3/10/17 3/14/17 3/15/17 3/15/17 3/18/17 3/18/17 3/21/17 3/21/17 3/23/17 3/23/17 3/24/17 4/1/17 4/6/17 4/7/17 4/8/17 4/11/17 4/12/17 4/13/17 4/13/17 4/14/17 4/20/17 4/22/17 4/25/17 4/26/17 4/30/17 5/2/17 5/6/17 5/7/17 5/8/17 5/10/17 5/11/17 5/13/17 5/14/17 5/16/17 5/19/17 5/20/17 5/23/17 5/29/17 5/31/17 6/3/17 6/3/17 6/3/17 6/8/17 6/8/17 6/15/17 6/15/17 6/16/17 6/18/17 6/19/17 6/22/17 6/22/17 6/23/17 6/24/17 7/2/17 7/4/17 7/12/17 7/12/17 7/12/17 7/15/17 45 Kidd 26 English 23 Brock 13 English 10 Kidd 50 Kidd 20 English 22 English 41 Brock 9 Kidd 22 Kidd 44 Kidd 39 English 21 Brock 8 English 50 Adams 11 English 21 Brock 5 English 18 Brock 11 English 4 Kidd 26 Adams 7 Adams 4 Adams 26 English 22 Adams 17 Adams 48 English 36 Kidd 24 Kidd 29 Adams 23 Kidd 41 Brock 44 English 8 English 21 English 49 English 27 Adams 14 Adams 14 English 19 Brock 6 Adams 36 English 28 English 4 Kidd 22 English 11 Adams 45 Brock 18 English 46 Brock 24 Brock 33 English 17 Brock 16 Adams 26 Adams 29 Brock 46 Brock 9 Kidd 23 Adams 22 Adams 35 Adams 11 Brock 25 English 11 English 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 Shoes Jewelry Intimates Shoes Intimates Intimates Clothing Shoes Intimates Jewelry Intimates Intimates Clothing Shoes Jewelry Shoes Shoes Intimates Shoes Jewelry Intimates Intimates Jewelry Clothing Shoes Jewelry Shoes Jewelry Jewelry Shoes Jewelry Intimates Jewelry Clothing Jewelry Intimates Shoes Clothing Shoes Jewelry Shoes Clothing Shoes Intimates Intimates Intimates Intimates Clothing Intimates Intimates Intimates Intimates Shoes Jewelry Clothing Jewelry Shoes Jewelry Intimates Clothing Intimates Jewelry Intimates Shoes Clothing 226 227 228 229 2 $14.50 02-Feb $63.96 02-Feb $48.00 02-Feb $47.97 03-Mar $79.95 03-Mar $39.95 03-Mar $224.91 03-Mar $51.00 03-Mar $127.92 03-Mar $24.00 03-Mar $55.93 03-Mar $99.96 03-Mar $47.97 03-Mar $455.00 03-Mar $79.95 03-Mar $47.97 03-Mar $60.00 03-Mar $15.99 04-Apr $79.95 04-Apr $79.95 04-Apr $63.96 04-Apr $174.93 04-Apr $44.95 04-Apr $95.94 04-Apr $26.97 04-Apr $79.95 04-Apr $63.00 04-Apr $24.99 04-Apr $49.98 04-Apr $47.97 04-Apr $36.00 04-Apr $143.91 05-May $49.98 05-May $124.95 05-May $23.97 05-May $199.92 05-May $74.97 05-May $15.98 05-May $174.93 05-May $49.98 05-May $38.97 05-May $18.00 05-May $34.00 05-May $224.91 05-May $111.93 05-May $24.00 06-Jun $31.98 06-Jun $95.94 06-Jun $53.94 06-Jun $76.93 06-Jun $81.00 06-Jun $127.92 06-Jun $65.25 06-Jun $90.93 06-Jun $74.97 06-Jun $79.95 06-Jun $15.99 06-Jun $99.96 06-Jun $224.91 06-Jun $199.92 07-Jul $63.96 07-Jul $12.00 07-Jul $103.92 07-Jul $15.99 07-Jul $99.96 07-Jul 9 24.9899 24.9899 15.99 12 15.99 24.9899 24.9899 7.99 24.9899 24.9899 7.99 24.9899 24.9899 12.99 228 5 3 229 230 8 230 231 232 3 3 2 233 231 232 233 7 7 2 3 3 2017 234 235 236 237 2 4 4 9 238 7 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 9 8.5 24.9899 15.99 12 15.99 15.99 8.99 10.99 241 240 239 2 2 6 243 6 7 242 244 9 9 2017 245 15.99 8 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249) 250 251 252 253 254 255 256 257 258 259 2017 2017 246 9 7 2017 247 248 3 2017 2017 5 250 249 1 7.25 12.99 24.9899 15.99 15.99 24.9899 24.9899 24.9899 15.99 12 2017 2017 251 4 252 9 2017 2017 253 8 4 254 257 1 12.99 8 256 255 2017 2017 2017 2017 2017 15.99 1 258 24.9899 4 PurchaselD PurchaseDate Customer Category Price Quantity Year 260 261 259 260 261 262 15.99 15.99 24.9899 10.99 24.9899 2017 2017 2017 2017 2017 263 262 263 264 265 266 15.99 2017 264 265 266 267 267 268 269 24.9899 7.99 7.99 8.99 15.99 268 270 269 12 270 271 272 273 274 275 276 277 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 278 279 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289) 290 291 292 293 15.99 8.5 15.99 24.9899 15.99 24.9899 15.99 7.25 9 12.99 24.9899 15.99 24.9899 65 24.9899 15.99 280 281 282 283 284 285 286 287 9 288 289 290 Employee 2 Brock 50 Kidd 32 Adams 32 English 50 Adams 39 Brock 5 Adams 19 English 12 Kidd 40 Kidd 14 Adams 11 Brock 43 Kidd 31 Kidd 9 English 28 English 35 Brock 45 Brock 40 Kidd 29 Adams 27 Kidd 47 Kidd 18 Adams 26 Brock 16 Kidd 1 English 11 Kidd 46 Brock 33 English 21 English 32 Brock 43 English 6 Kidd 44 English 13 Brock 28 Kidd 11 Adams 48 Kidd 38 English 8 Kidd 37 Adams 15 Adams 49 Adams 34 Kidd 16 Kidd 49 Adams 42 English 38 English 44 English 7 Brock 17 Brock 2 Kidd 17 Brock 35 English 29 Brock 33 Brock 12 Kidd 9 Adams 5 Kidd 46 English 12 Brock 6 English 35 Brock 27 Adams 15 English 7/16/17 7/22/17 7/24/17 7/26/17 7/27/17 7/29/17 7/29/17 7/31/17 8/2/17 8/8/17 8/9/17 8/9/17 8/10/17 8/17/17 8/18/17 8/19/17 8/21/17 8/21/17 8/23/17 8/24/17 8/25/17 8/26/17 9/2/17 9/5/17 9/7/17 9/13/17 9/16/17 9/17/17 9/18/17 9/20/17 9/21/17 9/23/17 9/23/17 9/27/17 9/29/17 9/30/17 10/5/17 10/7/17 10/9/17 10/10/17 10/14/17 10/15/17 10/18/17 10/18/17 10/22/17 10/24/17 10/26/17 10/27/17 10/28/17 10/30/17 11/3/17 11/3/17 11/4/17 11/7/17 11/8/17 11/14/17 11/16/17 11/18/17 11/20/17 11/22/17 11/23/17 11/24/17 11/25/17 11/30/17 12/2/17 Shoes Intimates Intimates Intimates Jewelry Clothing Jewelry Intimates Clothing Jewelry Clothing Shoes Jewelry Clothing Shoes Jewelry Jewelry Shoes Shoes Intimates Intimates Jewelry Jewelry Jewelry Intimates Shoes Jewelry Jewelry Shoes Clothing Jewelry Clothing Shoes Clothing Intimates Intimates Jewelry Intimates Shoes Clothing Jewelry Shoes Jewelry Shoes Jewelry Jewelry Shoes Shoes Shoes Jewelry Intimates Intimates Intimates Intimates Jewelry Clothing Clothing Clothing Jewelry Shoes Jewelry Shoes Jewelry Clothing Intimates 15.99 15.99 24.9899 8.5 Sales Total Month 3 3 $47.97 07-Jul - 3 $47.97 07-Jul 5 $124.95 07-Jul 3 3 $32.97 07-Jul 6 $149.94 07-Jul 4 4 $63.96 07-Jul 4 $99.96 07-Jul 4 $31.96 07-Jul 3 $23.97 08-Aug 5 $44.95 08-Aug 5 $79.95 08-Aug 5 $60.00 08-Aug 7 $111.93 08-Aug 3 $25.50 08-Aug 3 $47.97 08-Aug 5 $124.95 08-Aug 5 $79.95 08-Aug 5 $124.95 08-Aug 7 $111.93 08-Aug 9 $65.25 08-Aug 5 $45.00 08-Aug 6 6 $77.94 08-Aug 9 9 $224.91 09-Sep 8 $127.92 09-Sep 4 $99.96 09-Sep 9 $585.00 09-Sep 5 $124.95 09-Sep 6 $95.94 09-Sep 1 $9.00 09-Sep 4 4 $63.96 09-Sep 2 2 $31.98 09-Sep 8 $199.92 09-Sep 9 $76.50 09-Sep 7 $174.93 09-Sep 7 $111.93 09-Sep 2 $26.00 09-Sep 2 $18.00 10-Oct 6 $95.94 10-Oct 5 $79.95 10-Oct 5 $79.95 10-Oct 7 $111.93 10-Oct 1 $65.00 10-Oct 7 $111.93 10-Oct 4 4 $63.96 10-Oct 2 2 $31.98 10-Oct 1 1 $8.50 10-Oct 6 $95.94 10-Oct 8 $72.00 10-Oct 4 $29.00 10-Oct 3 $20.97 10-Oct 5 $36.25 11-Nov 1 $15.99 11-Nov 8 $72.00 11-Nov 3 $74.97 11-Nov 7 $111.93 11-Nov 9 $224.91 11-Nov 6 $51.00 11-Nov - 9 $81.00 11-Nov 4 $52.00 11-Nov 8 $199.92 11-Nov 2 2 $31.98 11-Nov 7 $174.93 11-Nov 4 $48.00 11-Nov 6 $54.00 11-Nov 4 $36.00 12-Dec 291 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 292 294 24.9899 15.99 293 294 295 13 9 9 15.99 15.99 296 15.99 297 298 299 300 302 15.99 2017 2017 2017 2017 65 15.99 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 15.99 2017 301 303 15.99 2017 8.5 2017 304 305 15.99 2017 9 2017 306 307 308 309 7.25 6.99 7.25 311 15.99 310 311 312 313 314 315 316 312 313 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 314 9 24.9899 15.99 24.9899 8.5 9 9 13 24.9899 15.99 315 317 318 319 320 316 317 318 319 2017 2017 321 320 2017 24.9899 12 321 2017 322 323 324 322 9 9 2017 324 9 2017 A B D F G H I J K L M N 0 P Q Q R S T Top 10 Customers by Total Sales 1 1 2 3 4 Year 1400 2016 2017 1200 5 Employee Sales by Category 1000 6 7 Month 800 01-Jan 02-Feb Kidd 600 Willi 03-Mar 04-Apr 06-Jun 400 05-May English 200 07-Jul 08-Aug Clothing Intimates Jewelry 0 09-Sep 10-Oct Brock 12 21 24 32 33 3529 46 Shoes 11-Nov - 12-Dec Adams Sales Total by Category $0.00 $2,000.00 $4000 OD $6,000.00 $8,000.00 $10,000.00 $12,000.00 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Data Dashboard VBA BlarneysVBA Documentation + B D E F 1 Description By Whom Firstname Lastname By Whom Workbook Name c07ch 14 Blarneys.xlsx Last Version Backup Name Mod. Description Sheet Name Creator Purpose 2 3 4 5 Create Date 6 mm/dd/yyyy 7 Mod. Date 8 9 10 11 12 12 13 15 14 14 15 16 17 18 19 Create Date 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Data Dashboard VBA BlarneysVBA Documentation + Step Instructions Points Possible 0 Start Excel. Open the file named e07ch14_grader_a1_Blarneys.xlsx. Save the file as a Macro- Enabled workbook with the name e07ch 14 grader_a1_Blarneys_LastFirst.xlsm using your last and first name. Ensure the Developer tab is displayed. 2 8 Create a new VBA module with the name Blarnex&VBA. Create a new Sub procedure with the name bideSheets 3 8 Add a comment that describes the procedure, type 'This procedure hides the data and dashboard worksheets. (include the period). Press ENTER two times before writing the next lines of code. 4 9 Make the Data and Dashboard worksheets hidden. Press ENTER two times before writing the next lines of code. 5 8 Place the insertion point at the end of the End Sub statement and press ENTER two times. Create a new Sub procedure named showSheats. This procedure will declare a password variable, and then prompt the user to provide the password in order to show the hidden worksheets in the workbook. 6 Declare a variable with the name asw. with a data type of String. 9 7 Set the value of the psw variable to be Password1234. 9 8 9 Create an input box using a variable named auth to store the user's response. The input message should be Enter the password to view the hidden sheets. (include the period). The input box should have a title of Authorized Personnel Only (do not include a period). Press ENTER two times before writing the next lines of code. 9 9 Create an If statement that compares the auth variable to the est variable, and if they are the same, unhide the Data and Dashboard worksheets. Press ENTER two times before writing the next lines of code. 10 9 To make the Dashboards worksheet activated use Sheets("Dashboard"Activate. Press ENTER two times before writing the next lines of code. 11 9 Create the Else statement to display a message box. Name the variable invalid for the message box. The message box text should be the password is invalid! Use vbCritical as the message box style and Invalid Password as the title. Press ENTER two times before writing the next lines of code. 12 End the If statement and press ENTER two times. 8 13 5 Copy the VBA code in the Blarneys KRA module. Paste the code in cell A1 of the VBA worksheet. The copied code will fill the range A1:A25. F G H J A B B C 1 PurchaseID LPurchase Date Customer 2 2 1/1/16 3 1 1/1/16 4 3 1/2/16 5 4 1/6/16 6 6 6 1/8/16 7 5 1/8/16 8 7 1/9/16 9 9 1/11/16 10 8 1/11/16 11 10 1/13/16 12 12 1/22/16 13 11 1/22/16 14 13 1/27/16 15 14 1/30/16 16 15 2/2/16 17 16 2/7/16 18 17 2/10/16 19 18 2/12/16 20 19 2/13/16 21 20 2/13/16 22 21 2/14/16 23 22 2/14/16 24 23 2/17/16 25 24 2/21/16 26 25 2/22/16 27 27 2/28/16 28 26 2/28/16 29 28 3/1/16 30 29 3/4/16 31 30 3/6/16 32 31 3/6/16 33 33 3/8/16 34 32 3/8/16 35 34 3/8/16 36 35 3/9/16 37 36 3/16/16 38 37 3/18/16 39 38 3/20/16 40 39 3/22/16 41 40 3/24/16 42 41 3/28/16 43 42 3/29/16 44 43 4/3/16 45 44 4/3/16 46 45 4/13/16 47 46 4/14/16 48 47 4/16/16 49 48 4/17/16 50 49 4/18/16 51 50 4/18/16 52 51 4/19/16 53 52 4/23/16 54 53 4/25/16 55 54 4/25/16 56 55 4/26/16 57 56 4/28/16 58 57 5/1/16 59 58 5/2/16 60 59 5/7/16 61 60 5/9/16 62 61 5/10/16 63 62 5/11/16 64 63 5/11/16 65 64 5/13/16 D E E Employee Category 42 Adams Intimates 38 Adams Jewelry 30 English Clothing 46 Adams Intimates 32 Kidd Shoes 38 Adams Clothing 4 Brock Shoes 49 English Intimates 2 Brock Jewelry 7 Adams Jewelry 39 Adams Shoes 33 English Clothing 43 Adams Intimates 28 English Jewelry 32 English Jewelry 11 English Clothing 39 English Clothing 7 Brock Jewelry 12 Kidd Shoes 46 English Intimates 1 Brock Shoes 34 Adams Intimates 7 Kidd Clothing 21 Adams Clothing 43 English Jewelry 49 Adams Jewelry 3 Adams Clothing 8 Kidd Intimates 34 Kidd Intimates 31 Adams Jewelry 4 English Clothing 4 Adams Shoes 35 Adams Clothing 15 Adams Jewelry 25 English Intimates 2 Brock Intimates 47 English Jewelry 21 Brock Shoes 44 Adams Jewelry 31 English Jewelry 37 Adams Clothing 27 English Intimates 41 Brock Jewelry 8 English Intimates 24 Adams Shoes 15 Kidd Clothing 22 English Shoes 6 English Shoes 5 Brock 5 Shoes 2 English Intimates 29 Kidd Intimates 36 Kidd Jewelry 47 Kidd Clothing 48 English Intimates 8 English Intimates 44 Kidd Shoes 18 English Intimates 26 Adams Shoes 50 English Jewelry 18 English Clothing 34 Kidd Clothing 39 Adams Jewelry 48 Kidd Shoes 18 Brock Jewelry Price Quantity $ 24.99 $ 7.99 $ 8.50 $ 13.00 $15.99 $ 9.00 $ 24.99 $ 24.99 $ 15.99 $ 15.99 $ 10.99 $ 12.00 $ 24.99 $ 15.99 $ 24.99 $ 9.00 $ 12.00 $ 8.50 $ 24.99 $ 15.99 $ 7.99 $ 24.99 $ 15.99 $ 65.00 $ 7.99 $ 13.00 $ 10.99 $ 15.99 $ 10.99 $ 15.99 $ 9.00 $ 7.99 $ 24.99 $ 24.99 $ 9.00 $ 7.99 $ 65.00 $ 8.50 $ 24.99 $ 24.99 $ 15.99 $ 9.00 $ 15.99 $ 9.00 $ 8.50 $ 15.99 $ 8.50 $ 24.99 $ 24.99 $ 24.99 $ 8.50 $ 9.00 $ 7.25 $ 7.99 $ 12.00 $15.99 $ 12.00 $ 15.99 $ 15.99 $ 8.99 $ 15.99 $ 24.99 $ 15.99 $ 24.99 Sales Total Month Year 3 $74.97 01-Jan 2016 6 $47.94 01-Jan 2016 4 $34.00 01-Jan 2016 3 $39.00 01-Jan 2016 3 3 $47.97 01-Jan 2016 3 $27.00 01-Jan 2016 3 $74.97 01-Jan 2016 6 6 $149.94 01-Jan 2016 9 $143.91 01-Jan 2016 8 $127.92 01-Jan 2016 6 $65.94 01-Jan 2016 1 $12.00 01-Jan 2016 7 $174.93 01-Jan 2016 2 $31.98 01-Jan 2016 8 $199.92 02-Feb 2016 3 $27.00 02-Feb 2016 1 $12.00 02-Feb 2016 4 $34.00 02-Feb 2016 9 $224.91 02-Feb 2016 2 $31.98 02-Feb 2016 8 $63.92 02-Feb 2016 9 $ 224.91 02-Feb 2016 6 $95.94 02-Feb 2016 7 $455.00 02-Feb 2016 6 $47.94 02-Feb 2016 3 $39.00 02-Feb 2016 8 $87.92 02-Feb 2016 4 $63.96 02-Feb 2016 8 $87.92 03-Mar 2016 9 $143.91 03-Mar 2016 5 $45.00 03-Mar 2016 7 $55.93 03-Mar 2016 8 $199.92 03-Mar 2016 3 $74.97 03-Mar 2016 9 $81.00 03-Mar 2016 3 $23.97 03-Mar 2016 2 $ 130.00 03-Mar 2016 5 $42.50 03-Mar 2016 3 $74.97 03-Mar 2016 5 $124.95 03-Mar 2016 1 1 $15.99 03-Mar 2016 8 $72.00 03-Mar 2016 8 $127.92 04-Apr 2016 1 $9.00 04-Apr 2016 5 $42.50 04-Apr 2016 3 $47.97 04-Apr 2016 6 $51.00 04-Apr 2016 2 2 $49.98 04-Apr 2016 9 $224.91 04-Apr 2016 6 $149.94 04-Apr 2016 8 $68.00 04-Apr 2016 5 $45.00 04-Apr 2016 7 $50.75 04-Apr 2016 9 $71.91 04-Apr 2016 6 $72.00 04-Apr 2016 6 $95.94 04-Apr 2016 9 $108.00 05-May 2016 1 $15.99 05-May 2016 9 $143.91 05-May 2016 2 $17.98 05-May 2016 3 3 $47.97 05-May 2016 1 $24.99 05-May 2016 1 $15.99 05-May 2016 2 $49.98 05-May 2016 PurchaselD PurchaseDate CustomerlD Price Quantity Year 66 65 67 66 67 68 2016 2016 2016 2016 2016 2016 2016 68 69 70 71 72 73 69 70 71 72 2016 2016 74 73 76 2016 2016 75 74 77 75 76 77 78 79 80 81 82 83 84 85 86 78 79 81 2016 2016 2016 2016 2016 2016 2016 2016 80 83 82 84 2016 2016 85 87 86 87 88 89 88 2016 2016 2016 2016 2016 89 90 91 92 93 2016 2016 2016 2016 2016 2016 94 95 96 Employee 33 Brock 5 Adams 3 Adams 43 Brock 44 Kidd 6 English 16 Kidd 36 Kidd 14 Adams 11 Kidd 43 Kidd 31 Brock 35 Brock 49 Adams 18 Kidd 39 Brock 2 Adams 42 English 27 English 26 Brock 33 Brock 41 Brock 24 English 24 Kidd 28 Adams 9 Kidd 23 English 16 Adams 43 Kidd 16 Kidd 29 Kidd 20 Brock 8 Brock 23 English 32 Adams 14 English 27 Kidd 41 Adams 39 Kidd 15 Adams 11 English 12 Adams 19 Adams 28 English 32 Brock 43 Brock 28 English 44 English 13 Kidd 26 Kidd 27 Brock 22 Kidd 8 Adams 23 Adams 10 Brock 28 English 8 Brock 8 10 English 33 Kidd 15 Brock 22 English 23 English 39 Brock 43 Adams 24 Adams 5/16/16 5/17/16 5/19/16 5/28/16 5/30/16 5/31/16 6/3/16 6/5/16 6/6/16 6/7/16 6/7/16 6/7/16 6/8/16 6/10/16 6/13/16 6/18/16 6/18/16 6/22/16 6/22/16 6/29/16 7/4/16 7/5/16 7/5/16 7/7/16 7/11/16 7/13/16 7/14/16 7/17/16 7/22/16 7/23/16 7/26/16 7/28/16 7/29/16 7/31/16 8/1/16 8/3/16 8/7/16 8/9/16 8/11/16 8/11/16 8/15/16 8/16/16 8/16/16 8/18/16 8/19/16 8/22/16 8/27/16 8/28/16 9/1/16 9/2/16 9/3/16 9/3/16 9/5/16 9/7/16 9/10/16 9/10/16 9/11/16 9/18/16 9/20/16 9/20/16 9/24/16 9/27/16 10/2/16 10/4/16 10/6/16 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 Category Shoes Shoes Clothing Jewelry Clothing Clothing Intimates Shoes Shoes Jewelry Jewelry Jewelry Clothing Shoes Shoes Shoes Jewelry Clothing Shoes Intimates Shoes Intimates Intimates Jewelry Jewelry Intimates Clothing Clothing Intimates Clothing Shoes Jewelry Shoes Intimates Jewelry Jewelry Jewelry Intimates Clothing Intimates Shoes Shoes Shoes Clothing Shoes Intimates Shoes Intimates Jewelry Shoes Clothing Clothing Intimates Jewelry Intimates Intimates Intimates Shoes Jewelry Jewelry Intimates Clothing Clothing Clothing Intimates 97 Sales Total Month 1 $24.99 05-May 4 4 $63.96 05-May 4 $99.96 05-May 6 $53.94 05-May 8 $199.92 05-May 1 1 $15.99 05-May 4 $63.96 06-Jun 4 4 $63.96 06-Jun 5 $60.00 06-Jun 4 4 $34.00 06-Jun 7 $91.00 06-Jun 1 $15.99 06-Jun 8 $127.92 06-Jun 3 $74.97 06-Jun 1 $12.99 06-Jun 6 $149.94 06-Jun 7 $111.93 06-Jun 8 $127.92 06-Jun 7 $59.50 06-Jun 1 1 $8.99 06-Jun 7 $455.00 07-Jul 2 $49.98 07-Jul 9 $76.50 07-Jul 8 $199.92 07-Jul 8 $63.92 07-Jul 4 $51.96 07-Jul 1 $15.99 07-Jul 7 $174.93 07-Jul 8 $63.92 07-Jul 6 $41.94 07-Jul 7 $174.93 07-Jul 7 $455.00 07-Jul 4 $63.96 07-Jul 9 $224.91 07-Jul 9 $585.00 08-Aug 2 $31.98 08-Aug 3 3 $47.97 08-Aug 5 $325.00 08-Aug 9 $ 224.91 08-Aug 4 $63.96 08-Aug 1 $12.00 08-Aug 8 $127.92 08-Aug 1 $6.99 08-Aug 7 $174.93 08-Aug 2 $17.00 08-Aug 1 $24.99 08-Aug 6 $65.94 08-Aug 9 $81.00 08-Aug 5 $60.00 09-Sep 8 $127.92 09-Sep 6 $51.00 09-Sep 9 9 $ 224.91 09-Sep 5 $79.95 09-Sep 5 $124.95 09-Sep 1 $15.99 09-Sep 3 $39.00 09-Sep 4 $34.00 09-Sep 8 $199.92 09-Sep 2 $49.98 09-Sep 8 $55.92 09-Sep 4 $63.96 09-Sep 3 $74.97 09-Sep 4 $48.00 10-Oct 4 $99.96 10-Oct 8 $127.92 10-Oct $ 24.99 $15.99 $ 24.99 $ 8.99 $ 24.99 $15.99 $ 15.99 $ 15.99 $ 12.00 $ 8.50 $ 13.00 $ 15.99 $ 15.99 $ 24.99 $ 12.99 $ 24.99 $ 15.99 $15.99 $ 8.50 $ 8.99 $ 65.00 $24.99 $ 8.50 $ 24.99 $ 7.99 $ 12.99 $ 15.99 $ 24.99 $ 7.99 $ $ 6.99 $ 24.99 $ 65.00 $ 15.99 $ 24.99 $ 65.00 $ 15.99 $15.99 $ 65.00 $ 24.99 $ 15.99 $ 12.00 $ 15.99 $ 6.99 $ 24.99 $ 8.50 $ 24.99 $ 10.99 $ 9.00 $ 12.00 $ 15.99 $8.50 $ 24.99 $ 15.99 $ 24.99 $ 15.99 $ 13.00 $ 8.50 $ 24.99 $ 24.99 $ 6.99 $ 15.99 $ 24.99 $ 12.00 $ 24.99 $ 15.99 2016 98 2016 99 100 101 102 103 104 105 107 106 108 109 110 111 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 108 109 110 111 112 113 112 113 114 115 116 114 116 115 2016 2016 2016 2016 117 118 119 120 117 118 119 121 120 121 122 123 124 125 122 123 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 124 125 126 126 127 128 129 130 127 128 2016 2016 129 PurchaselD PurchaseDate CustomerlD Employee Category Price Quantity Sales Total Month 6 Year 2016 2016 130 131 132 131 132 133 134 135 4 2 2016 133 9 9 9 9 136 137 138 6 7 134 135 136 137 138 139 2016 2016 2016 2016 2016 8 139 6 140 4 2016 2016 2016 2016 4 9 141 142 143 144 140 142 141 143 5 2016 3 2016 145 9 9 2016 144 145 146 3 3 146 9 147 3 9 9 148 149 5 2016 2016 2016 2016 2016 2016 2016 2016 2016 150 9 151 147 148 149 150 151 152 153 154 155 156 157 158 1 152 5 153 8 154 1 2016 155 4 156 1 1 2016 2016 2016 157 4 159 1 2016 1 158 159 160 161 162 1 2016 2016 2016 160 161 162 163 164 165 166 8 10/7/16 10/8/16 10/9/16 10/15/16 10/20/16 10/21/16 10/23/16 10/23/16 10/26/16 10/28/16 10/29/16 11/2/16 11/2/16 11/4/16 11/7/16 11/9/16 11/13/16 11/13/16 11/14/16 11/17/16 11/18/16 11/24/16 11/26/16 11/28/16 11/29/16 12/3/16 12/6/16 12/6/16 12/8/16 12/10/16 12/11/16 12/11/16 12/16/16 12/17/16 12/17/16 12/20/16 12/25/16 12/27/16 12/30/16 1/6/17 1/9/17 1/12/17 1/13/17 1/13/17 1/14/17 1/17/17 1/18/17 1/18/17 1/23/17 1/25/17 1/25/17 1/26/17 1/28/17 2/2/17 2/2/17 2/6/17 2/6/17 2/9/17 2/9/17 2/9/17 2/10/17 2/10/17 2/15/17 2/17/17 2/22/17 50 Kidd 24 Brock 48 Adams 32 Brock 46 English 26 English 7 English 18 Adams 44 Adams 32 English 12 Kidd 35 English 36 Brock 44 English 4 English 2 Brock 6 Kidd 15 English 31 Adams 30 English 17 English 14 Kidd 12 English 14 English 5 English 25 English 43 English 39 Adams 48 Kidd 40 Kidd 21 English 30 Adams 19 Adams 16 Kidd 14 English 14 Adams 46 English 40 Kidd 40 Brock 35 English 23 English 43 Kidd 33 Brock 21 Kidd 29 English 12 English 45 Kidd 13 English 8 English 29 Brock 47 Kidd 4 Kidd 16 Brock 30 Adams 25 Kidd 37 Adams 15 Adams 9 9 Brock 39 Brock 5 Adams 8 Brock 24 Adams 44 English 39 Brock 45 Kidd Clothing Clothing Jewelry Clothing Intimates Shoes Clothing Clothing Clothing Intimates Shoes Jewelry Clothing Clothing Jewelry Clothing Shoes Shoes Intimates Shoes Jewelry Jewelry Jewelry Shoes Jewelry Shoes Intimates Shoes Shoes Jewelry Clothing Shoes Clothing Clothing Clothing Intimates Clothing Jewelry Clothing Shoes Intimates Shoes Jewelry Jewelry Intimates Clothing Shoes Clothing Intimates Intimates Shoes Clothing Clothing Shoes Intimates Shoes Jewelry Clothing Shoes Shoes Shoes Shoes Clothing Jewelry Shoes $ 12.99 $ $ 9.00 $ 15.99 $ 15.99 $ 24.99 $15.99 $ 24.99 $ 24.99 $ 65.00 $ 13.00 $ 24.99 $ 65.00 $ 24.99 $ 12.99 $ 24.99 $ 10.99 $ 7.99 $ 15.99 $ 7.25 $ 65.00 $ 24.99 $ 8.50 $ 24.99 $ 24.99 $ $ 8.50 $ 24.99 $ 24.99 $ 12.00 $ 12.00 $ 24.99 $ 24.99 $ 7.99 $ 24.99 $15.99 $ 24.99 $ 15.99 $ 24.99 $ 12.00 $15.99 $ 24.99 $ 7.99 $ 24.99 $ 8.50 $ 12.00 $ 10.99 $ 12.00 $ 6.99 $ 7.25 $ 7.99 $ 24.99 $ $ 8.50 $ 9.00 $ 7.25 $ 12.00 $ 24.99 $ 24.99 $ 6.99 $15.99 $ $ 8.99 $ 24.99 $ 6.99 $ 24.99 $ 15.99 $ 8.99 $ 7.25 3 $77.94 10-Oct $36.00 10-Oct $31.98 10-Oct $143.91 10-Oct $224.91 10-Oct $95.94 10-Oct $174.93 10-Oct $199.92 10-Oct $390.00 10-Oct $52.00 10-Oct $99.96 10-Oct $585.00 11-Nov $124.95 11-Nov $38.97 11-Nov $224.91 11-Nov $32.97 11-Nov $71.91 11-Nov $47.97 11-Nov $65.25 11-Nov $325.00 11-Nov $224.91 11-Nov $8.50 11-Nov $124.95 11-Nov $199.92 11-Nov $8.50 11-Nov $99.96 12-Dec $24.99 12-Dec $48.00 12-Dec $12.00 12-Dec $24.99 12-Dec $24.99 12-Dec $63.92 12-Dec $74.97 12-Dec $15.99 12-Dec $24.99 12-Dec $127.92 12-Dec $24.99 12-Dec $48.00 12-Dec $63.96 12-Dec $124.95 01-Jan $55.93 01-Jan $224.91 01-Jan $25.50 01-Jan $108.00 01-Jan $76.93 01-Jan $84.00 01-Jan $34.95 01-Jan $58.00 01-Jan $7.99 01-Jan $24.99 01-Jan $59.50 01-Jan $72.00 01-Jan $50.75 01-Jan $12.00 02-Feb $224.91 02-Feb $24.99 02-Feb $20.97 02-Feb $143.91 02-Feb $44.95 02-Feb $49.98 02-Feb $20.97 02-Feb $224.91 02-Feb $31.98 02-Feb $80.91 02-Feb $14.50 02-Feb 2016 1 164 163 2016 2016 1 165 2016 8 12 1 167 166 167 168 4 4 2016 2016 2016 2017 168 4 169 170 171 5 169 170 171 2017 7 7 9 172 173 3 173 174 175 9 172 174 175 7 2017 2017 2017 2017 2017 2017 2017 176 7 177 5 176 8 1 2017 178 179 1 2017 180 7 2017 2017 181 8 177 178 179 180 181 182 183 184 185 186 187 188 189 182 7 2017 2017 184 1 1 183 9 2017 1 2017 186 185 189 3 2017 2017 9 188 5 2017 2017 190 187 2 191 3 2017 191 192 193 190 9 2017 192 2 2017 194 9 9 193 194 2017 2017 195 2 PurchaselD PurchaseDate CustomerlD Employee Category Price Quantity Sales Total Month Year 2 195 196 4 4 197 198 3 194 195 196 197 198 199 200 201 202 2017 2017 2017 2017 2017 2017 2017 2017 5 199 200 201 202 203 5 9 6 2017 8 2 204 203 2017 7 205 204 207 4 $ 7.25 $ 15.99 $ 12.00 $ 15.99 $ 15.99 $ 7.99 $ 24.99 $ $ 8.50 $ 15.99 $ 12.00 $ 7.99 $ 24.99 $15.99 $ 65.00 $ 15.99 $ 15.99 $ 12.00 $ 15.99 $ 15.99 $15.99 15.99 24.9899 8.99 15.99 8.99 205 206 207 208 209 210 211 3 2017 2017 2017 2017 2017 2017 206 7 209 208 210 5 3 5 212 1 5 2017 2017 2017 2017 2017 5 4 4 213 214 215 216 217 218 211 212 213 214 215 216 217 218 219 7 5 6 219 3 3 5 15.99 220 7 220 221 222 223 224 1 221 222 223 224 225 2 2 3 225 3 9 9 226 227 2/22/17 2/26/17 2/26/17 3/7/17 3/7/17 3/8/17 3/10/17 3/14/17 3/15/17 3/15/17 3/18/17 3/18/17 3/21/17 3/21/17 3/23/17 3/23/17 3/24/17 4/1/17 4/6/17 4/7/17 4/8/17 4/11/17 4/12/17 4/13/17 4/13/17 4/14/17 4/20/17 4/22/17 4/25/17 4/26/17 4/30/17 5/2/17 5/6/17 5/7/17 5/8/17 5/10/17 5/11/17 5/13/17 5/14/17 5/16/17 5/19/17 5/20/17 5/23/17 5/29/17 5/31/17 6/3/17 6/3/17 6/3/17 6/8/17 6/8/17 6/15/17 6/15/17 6/16/17 6/18/17 6/19/17 6/22/17 6/22/17 6/23/17 6/24/17 7/2/17 7/4/17 7/12/17 7/12/17 7/12/17 7/15/17 45 Kidd 26 English 23 Brock 13 English 10 Kidd 50 Kidd 20 English 22 English 41 Brock 9 Kidd 22 Kidd 44 Kidd 39 English 21 Brock 8 English 50 Adams 11 English 21 Brock 5 English 18 Brock 11 English 4 Kidd 26 Adams 7 Adams 4 Adams 26 English 22 Adams 17 Adams 48 English 36 Kidd 24 Kidd 29 Adams 23 Kidd 41 Brock 44 English 8 English 21 English 49 English 27 Adams 14 Adams 14 English 19 Brock 6 Adams 36 English 28 English 4 Kidd 22 English 11 Adams 45 Brock 18 English 46 Brock 24 Brock 33 English 17 Brock 16 Adams 26 Adams 29 Brock 46 Brock 9 Kidd 23 Adams 22 Adams 35 Adams 11 Brock 25 English 11 English 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 Shoes Jewelry Intimates Shoes Intimates Intimates Clothing Shoes Intimates Jewelry Intimates Intimates Clothing Shoes Jewelry Shoes Shoes Intimates Shoes Jewelry Intimates Intimates Jewelry Clothing Shoes Jewelry Shoes Jewelry Jewelry Shoes Jewelry Intimates Jewelry Clothing Jewelry Intimates Shoes Clothing Shoes Jewelry Shoes Clothing Shoes Intimates Intimates Intimates Intimates Clothing Intimates Intimates Intimates Intimates Shoes Jewelry Clothing Jewelry Shoes Jewelry Intimates Clothing Intimates Jewelry Intimates Shoes Clothing 226 227 228 229 2 $14.50 02-Feb $63.96 02-Feb $48.00 02-Feb $47.97 03-Mar $79.95 03-Mar $39.95 03-Mar $224.91 03-Mar $51.00 03-Mar $127.92 03-Mar $24.00 03-Mar $55.93 03-Mar $99.96 03-Mar $47.97 03-Mar $455.00 03-Mar $79.95 03-Mar $47.97 03-Mar $60.00 03-Mar $15.99 04-Apr $79.95 04-Apr $79.95 04-Apr $63.96 04-Apr $174.93 04-Apr $44.95 04-Apr $95.94 04-Apr $26.97 04-Apr $79.95 04-Apr $63.00 04-Apr $24.99 04-Apr $49.98 04-Apr $47.97 04-Apr $36.00 04-Apr $143.91 05-May $49.98 05-May $124.95 05-May $23.97 05-May $199.92 05-May $74.97 05-May $15.98 05-May $174.93 05-May $49.98 05-May $38.97 05-May $18.00 05-May $34.00 05-May $224.91 05-May $111.93 05-May $24.00 06-Jun $31.98 06-Jun $95.94 06-Jun $53.94 06-Jun $76.93 06-Jun $81.00 06-Jun $127.92 06-Jun $65.25 06-Jun $90.93 06-Jun $74.97 06-Jun $79.95 06-Jun $15.99 06-Jun $99.96 06-Jun $224.91 06-Jun $199.92 07-Jul $63.96 07-Jul $12.00 07-Jul $103.92 07-Jul $15.99 07-Jul $99.96 07-Jul 9 24.9899 24.9899 15.99 12 15.99 24.9899 24.9899 7.99 24.9899 24.9899 7.99 24.9899 24.9899 12.99 228 5 3 229 230 8 230 231 232 3 3 2 233 231 232 233 7 7 2 3 3 2017 234 235 236 237 2 4 4 9 238 7 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 9 8.5 24.9899 15.99 12 15.99 15.99 8.99 10.99 241 240 239 2 2 6 243 6 7 242 244 9 9 2017 245 15.99 8 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249) 250 251 252 253 254 255 256 257 258 259 2017 2017 246 9 7 2017 247 248 3 2017 2017 5 250 249 1 7.25 12.99 24.9899 15.99 15.99 24.9899 24.9899 24.9899 15.99 12 2017 2017 251 4 252 9 2017 2017 253 8 4 254 257 1 12.99 8 256 255 2017 2017 2017 2017 2017 15.99 1 258 24.9899 4 PurchaselD PurchaseDate Customer Category Price Quantity Year 260 261 259 260 261 262 15.99 15.99 24.9899 10.99 24.9899 2017 2017 2017 2017 2017 263 262 263 264 265 266 15.99 2017 264 265 266 267 267 268 269 24.9899 7.99 7.99 8.99 15.99 268 270 269 12 270 271 272 273 274 275 276 277 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 278 279 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289) 290 291 292 293 15.99 8.5 15.99 24.9899 15.99 24.9899 15.99 7.25 9 12.99 24.9899 15.99 24.9899 65 24.9899 15.99 280 281 282 283 284 285 286 287 9 288 289 290 Employee 2 Brock 50 Kidd 32 Adams 32 English 50 Adams 39 Brock 5 Adams 19 English 12 Kidd 40 Kidd 14 Adams 11 Brock 43 Kidd 31 Kidd 9 English 28 English 35 Brock 45 Brock 40 Kidd 29 Adams 27 Kidd 47 Kidd 18 Adams 26 Brock 16 Kidd 1 English 11 Kidd 46 Brock 33 English 21 English 32 Brock 43 English 6 Kidd 44 English 13 Brock 28 Kidd 11 Adams 48 Kidd 38 English 8 Kidd 37 Adams 15 Adams 49 Adams 34 Kidd 16 Kidd 49 Adams 42 English 38 English 44 English 7 Brock 17 Brock 2 Kidd 17 Brock 35 English 29 Brock 33 Brock 12 Kidd 9 Adams 5 Kidd 46 English 12 Brock 6 English 35 Brock 27 Adams 15 English 7/16/17 7/22/17 7/24/17 7/26/17 7/27/17 7/29/17 7/29/17 7/31/17 8/2/17 8/8/17 8/9/17 8/9/17 8/10/17 8/17/17 8/18/17 8/19/17 8/21/17 8/21/17 8/23/17 8/24/17 8/25/17 8/26/17 9/2/17 9/5/17 9/7/17 9/13/17 9/16/17 9/17/17 9/18/17 9/20/17 9/21/17 9/23/17 9/23/17 9/27/17 9/29/17 9/30/17 10/5/17 10/7/17 10/9/17 10/10/17 10/14/17 10/15/17 10/18/17 10/18/17 10/22/17 10/24/17 10/26/17 10/27/17 10/28/17 10/30/17 11/3/17 11/3/17 11/4/17 11/7/17 11/8/17 11/14/17 11/16/17 11/18/17 11/20/17 11/22/17 11/23/17 11/24/17 11/25/17 11/30/17 12/2/17 Shoes Intimates Intimates Intimates Jewelry Clothing Jewelry Intimates Clothing Jewelry Clothing Shoes Jewelry Clothing Shoes Jewelry Jewelry Shoes Shoes Intimates Intimates Jewelry Jewelry Jewelry Intimates Shoes Jewelry Jewelry Shoes Clothing Jewelry Clothing Shoes Clothing Intimates Intimates Jewelry Intimates Shoes Clothing Jewelry Shoes Jewelry Shoes Jewelry Jewelry Shoes Shoes Shoes Jewelry Intimates Intimates Intimates Intimates Jewelry Clothing Clothing Clothing Jewelry Shoes Jewelry Shoes Jewelry Clothing Intimates 15.99 15.99 24.9899 8.5 Sales Total Month 3 3 $47.97 07-Jul - 3 $47.97 07-Jul 5 $124.95 07-Jul 3 3 $32.97 07-Jul 6 $149.94 07-Jul 4 4 $63.96 07-Jul 4 $99.96 07-Jul 4 $31.96 07-Jul 3 $23.97 08-Aug 5 $44.95 08-Aug 5 $79.95 08-Aug 5 $60.00 08-Aug 7 $111.93 08-Aug 3 $25.50 08-Aug 3 $47.97 08-Aug 5 $124.95 08-Aug 5 $79.95 08-Aug 5 $124.95 08-Aug 7 $111.93 08-Aug 9 $65.25 08-Aug 5 $45.00 08-Aug 6 6 $77.94 08-Aug 9 9 $224.91 09-Sep 8 $127.92 09-Sep 4 $99.96 09-Sep 9 $585.00 09-Sep 5 $124.95 09-Sep 6 $95.94 09-Sep 1 $9.00 09-Sep 4 4 $63.96 09-Sep 2 2 $31.98 09-Sep 8 $199.92 09-Sep 9 $76.50 09-Sep 7 $174.93 09-Sep 7 $111.93 09-Sep 2 $26.00 09-Sep 2 $18.00 10-Oct 6 $95.94 10-Oct 5 $79.95 10-Oct 5 $79.95 10-Oct 7 $111.93 10-Oct 1 $65.00 10-Oct 7 $111.93 10-Oct 4 4 $63.96 10-Oct 2 2 $31.98 10-Oct 1 1 $8.50 10-Oct 6 $95.94 10-Oct 8 $72.00 10-Oct 4 $29.00 10-Oct 3 $20.97 10-Oct 5 $36.25 11-Nov 1 $15.99 11-Nov 8 $72.00 11-Nov 3 $74.97 11-Nov 7 $111.93 11-Nov 9 $224.91 11-Nov 6 $51.00 11-Nov - 9 $81.00 11-Nov 4 $52.00 11-Nov 8 $199.92 11-Nov 2 2 $31.98 11-Nov 7 $174.93 11-Nov 4 $48.00 11-Nov 6 $54.00 11-Nov 4 $36.00 12-Dec 291 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 292 294 24.9899 15.99 293 294 295 13 9 9 15.99 15.99 296 15.99 297 298 299 300 302 15.99 2017 2017 2017 2017 65 15.99 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 15.99 2017 301 303 15.99 2017 8.5 2017 304 305 15.99 2017 9 2017 306 307 308 309 7.25 6.99 7.25 311 15.99 310 311 312 313 314 315 316 312 313 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 314 9 24.9899 15.99 24.9899 8.5 9 9 13 24.9899 15.99 315 317 318 319 320 316 317 318 319 2017 2017 321 320 2017 24.9899 12 321 2017 322 323 324 322 9 9 2017 324 9 2017 A B D F G H I J K L M N 0 P Q Q R S T Top 10 Customers by Total Sales 1 1 2 3 4 Year 1400 2016 2017 1200 5 Employee Sales by Category 1000 6 7 Month 800 01-Jan 02-Feb Kidd 600 Willi 03-Mar 04-Apr 06-Jun 400 05-May English 200 07-Jul 08-Aug Clothing Intimates Jewelry 0 09-Sep 10-Oct Brock 12 21 24 32 33 3529 46 Shoes 11-Nov - 12-Dec Adams Sales Total by Category $0.00 $2,000.00 $4000 OD $6,000.00 $8,000.00 $10,000.00 $12,000.00 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Data Dashboard VBA BlarneysVBA Documentation + B D E F 1 Description By Whom Firstname Lastname By Whom Workbook Name c07ch 14 Blarneys.xlsx Last Version Backup Name Mod. Description Sheet Name Creator Purpose 2 3 4 5 Create Date 6 mm/dd/yyyy 7 Mod. Date 8 9 10 11 12 12 13 15 14 14 15 16 17 18 19 Create Date 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Data Dashboard VBA BlarneysVBA Documentation + Step Instructions Points Possible 0 Start Excel. Open the file named e07ch14_grader_a1_Blarneys.xlsx. Save the file as a Macro- Enabled workbook with the name e07ch 14 grader_a1_Blarneys_LastFirst.xlsm using your last and first name. Ensure the Developer tab is displayed. 2 8 Create a new VBA module with the name Blarnex&VBA. Create a new Sub procedure with the name bideSheets 3 8 Add a comment that describes the procedure, type 'This procedure hides the data and dashboard worksheets. (include the period). Press ENTER two times before writing the next lines of code. 4 9 Make the Data and Dashboard worksheets hidden. Press ENTER two times before writing the next lines of code. 5 8 Place the insertion point at the end of the End Sub statement and press ENTER two times. Create a new Sub procedure named showSheats. This procedure will declare a password variable, and then prompt the user to provide the password in order to show the hidden worksheets in the workbook. 6 Declare a variable with the name asw. with a data type of String. 9 7 Set the value of the psw variable to be Password1234. 9 8 9 Create an input box using a variable named auth to store the user's response. The input message should be Enter the password to view the hidden sheets. (include the period). The input box should have a title of Authorized Personnel Only (do not include a period). Press ENTER two times before writing the next lines of code. 9 9 Create an If statement that compares the auth variable to the est variable, and if they are the same, unhide the Data and Dashboard worksheets. Press ENTER two times before writing the next lines of code. 10 9 To make the Dashboards worksheet activated use Sheets("Dashboard"Activate. Press ENTER two times before writing the next lines of code. 11 9 Create the Else statement to display a message box. Name the variable invalid for the message box. The message box text should be the password is invalid! Use vbCritical as the message box style and Invalid Password as the title. Press ENTER two times before writing the next lines of code. 12 End the If statement and press ENTER two times. 8 13 5 Copy the VBA code in the Blarneys KRA module. Paste the code in cell A1 of the VBA worksheet. The copied code will fill the range A1:A25