Use the modified spreadsheet that you submitted in Module 03 for this deliverable. Examine the fields in the spreadsheet. Classify, sort, or group the data in a way that the outliers become apparent. An example would be to sort by the total cancellations
lisagaray1981
lgaray_dataretreival_060516.xlsxHome>Business & Finance homework help>Management homework help>Use the modified spreadsheet that you submitted in Module 03 for this deliverable. Examine the fields in the spreadsheet. Classify, sort, or group the data in a way that the outliers become apparent. An example would be to sort by the total cancellations
October 2014
| Sales System Number | Date of Sale | Extract Date | Sales Consultant ID | Office | Region | Tax Type | Total Contracts | Total Sales | Total Cancellations |
| 265801-2014111 | 10/1/14 | 11/1/14 | 35353356 | MANHATTAN | NORTH | W2 | 10 | $30,291.00 | 1 |
| 265801-2014111 | 10/1/14 | 11/1/14 | 66464667 | ATLANTA | SOUTH | W2 | 4 | $39,999.00 | 0 |
| 315320-2014111 | 10/3/14 | 11/1/14 | 57575758 | MIAMI | FL/HI | 1099 | 11 | $92,829.00 | 1 |
| 265980-2014111 | 10/3/14 | 11/1/14 | 75265980 | MANHATTAN | NORTH | W2 | 4 | $44,255.00 | 2 |
| 329247-2014111 | 10/5/14 | 11/1/14 | 83748200 | EL PASO | SOUTH | 1099 | 0 | $0.00 | 0 |
| 399053-2014111 | 10/10/14 | 11/1/14 | 26354410 | MIAMI | FL/HI | 1099 | 5 | $20,100.00 | 1 |
| 407197-2014111 | 10/11/14 | 11/1/14 | 33407197 | Arkansas | SOUTHWEST | 1099 | 0 | $7,881.00 | 0 |
| 407886-2014111 | 10/12/14 | 11/1/14 | 40278386 | Arkansas | SOUTHWEST | 1099 | 1 | $10,000.00 | 0 |
| 410718-2014111 | 10/13/14 | 11/1/14 | 41107218 | Tri-State | SOUTHWEST | 1099 | 11 | $83,641.00 | 5 |
| 265005-2014111 | 10/14/14 | 11/1/14 | 26150025 | MANHATTAN | NORTH | W2 | 13 | $0.00 | 13 |
| 265288-2014111 | 10/20/14 | 11/1/14 | 26354410 | MIAMI | FL/HI | 1099 | 0 | $0.00 | 0 |
| 265440-2014111 | 10/20/14 | 11/1/14 | 26354410 | MIAMI | FL/HI | 1099 | 7 | $44,242.00 | 0 |
| 265778-2014111 | 10/20/14 | 11/1/14 | 26356778 | MIAMI | FL/HI | W2 | 1 | $555.00 | 0 |
| 265758-2014111 | 10/20/14 | 11/1/14 | 26457518 | MANHATTAN | NORTH | W2 | 9 | $80,000.00 | 0 |
| 265068-2014111 | 10/20/14 | 11/1/14 | 26520168 | MANHATTAN | NORTH | W2 | 10 | $45,000.00 | 0 |
| 265609-2014111 | 10/20/14 | 11/1/14 | 26526109 | MANHATTAN | NORTH | W2 | 25 | $54,535.00 | 0 |
| 265862-2014111 | 10/25/14 | 11/1/14 | 12658262 | MANHATTAN | NORTH | W2 | 9 | $24,222.00 | 0 |
| 266104-2014111 | 10/25/14 | 11/1/14 | 26161204 | MANHATTAN | NORTH | W2 | 4 | $14,344.00 | 0 |
| 265823-2014111 | 10/25/14 | 11/1/14 | 26531823 | CHICAGO | NORTH | W2 | 1 | $6,566.00 | 0 |
| 330618-2014111 | 10/26/14 | 11/1/14 | 33011618 | LOUISVILLE | SOUTH | 1099 | 1 | $250.00 | 0 |
| 332509-2014111 | 10/26/14 | 11/1/14 | 33255099 | MANHATTAN | NORTH | W2 | 16 | $50,000.00 | 1 |
| 333704-2014111 | 10/28/14 | 11/1/14 | 33233704 | BRONX | NORTH | 1099 | 0 | $321.00 | 0 |
| 407148-2014111 | 10/28/14 | 11/1/14 | 43207148 | Arkansas | SOUTHWEST | 1099 | 3 | $600.00 | 0 |
| 265752-2014111 | 10/30/14 | 11/1/14 | 26325752 | MANHATTAN | NORTH | W2 | 13 | $72,990.00 | 0 |
| 265848-2014111 | 10/30/14 | 11/1/14 | 26621106 | NEW ORLEANS | SOUTH | W2 | 7 | $35,000.00 | 0 |
| 266106-2014111 | 10/30/14 | 11/1/14 | 26621106 | NEW ORLEANS | SOUTH | W2 | 10 | $87,382.00 | 1 |
| 331393-2014111 | 10/31/14 | 11/1/14 | 33135393 | LOUISVILLE | SOUTH | 1099 | 9 | $21,222.00 | 0 |
November 2014
| Sales System Number | Date of Sale | Extract Date | Sales Consultant ID | Office | Region | Tax Type | Total Contracts | Total Sales | Total Cancellations |
| 265005-2014111 | 11/1/14 | 12/1/14 | 26354410 | MIAMI | FL/HI | 1099 | 5 | $20,100.00 | 1 |
| 265068-2014111 | 11/2/14 | 12/1/14 | 33407197 | Arkansas | SOUTHWEST | 1099 | 0 | $0.00 | 0 |
| 265288-2014111 | 11/2/14 | 12/1/14 | 40278386 | Arkansas | SOUTHWEST | 1099 | 1 | $10,000.00 | 0 |
| 265440-2014111 | 11/2/14 | 12/1/14 | 41107218 | Tri-State | SOUTHWEST | 1099 | 4 | $4,000.00 | 0 |
| 329247-2014111 | 11/3/14 | 12/1/14 | 33011618 | LOUISVILLE | SOUTH | 1099 | 1 | $900.00 | 0 |
| 399053-2014111 | 11/4/14 | 12/1/14 | 33255099 | MANHATTAN | NORTH | W2 | 9 | $30,000.00 | 1 |
| 265609-2014111 | 11/5/14 | 12/1/14 | 66464667 | ATLANTA | SOUTH | W2 | 1 | $550.00 | 0 |
| 265758-2014111 | 11/5/14 | 12/1/14 | 35353356 | MANHATTAN | NORTH | W2 | 3 | $10,300.00 | 0 |
| 265778-2014111 | 11/5/14 | 12/1/14 | 26520168 | MANHATTAN | NORTH | W2 | 2 | $5,000.00 | 0 |
| 265823-2014111 | 11/5/14 | 12/1/14 | 26152889 | MIAMI | FL/HI | 1099 | 14 | $1,000.00 | 13 |
| 265862-2014111 | 11/6/14 | 12/1/14 | 26356778 | MIAMI | FL/HI | W2 | 1 | $5,000.00 | 0 |
| 266104-2014111 | 11/7/14 | 12/1/14 | 26354410 | MIAMI | FL/HI | 1099 | 7 | $500.00 | 0 |
| 330618-2014111 | 11/8/14 | 12/1/14 | 26531823 | CHICAGO | NORTH | W2 | 1 | $3,000.00 | 0 |
| 407197-2014111 | 11/10/14 | 12/1/14 | 33233704 | BRONX | NORTH | 1099 | 0 | $0.00 | 0 |
| 407886-2014111 | 11/10/14 | 12/1/14 | 43207148 | Arkansas | SOUTHWEST | 1099 | 3 | $0.00 | 0 |
| 265801-2014111 | 11/11/14 | 12/1/14 | 26526109 | MANHATTAN | NORTH | W2 | 16 | $20,000.00 | 0 |
| 265801-2014111 | 11/12/14 | 12/1/14 | 26457518 | MANHATTAN | NORTH | W2 | 2 | $1,010.00 | 0 |
| 265980-2014111 | 11/13/14 | 12/1/14 | 12658262 | MANHATTAN | NORTH | W2 | 2 | $211.00 | 0 |
| 332509-2014111 | 11/15/14 | 12/1/14 | 75265980 | MANHATTAN | NORTH | W2 | 4 | $20,100.00 | 1 |
| 333704-2014111 | 11/16/14 | 12/1/14 | 57575758 | MIAMI | FL/HI | 1099 | 11 | $500.00 | 10 |
| 315320-2014111 | 11/17/14 | 12/1/14 | 26161204 | MANHATTAN | NORTH | W2 | 4 | $10,000.00 | 0 |
| 407148-2014111 | 11/23/14 | 12/1/14 | 83748200 | EL PASO | SOUTH | 1099 | 0 | $0.00 | 0 |
| 410718-2014111 | 11/27/14 | 12/1/14 | 26621106 | NEW ORLEANS | SOUTH | W2 | 10 | $87,382.00 | 1 |
| 265752-2014111 | 11/29/14 | 12/1/14 | 26150025 | MANHATTAN | NORTH | W2 | 5 | $0.00 | 5 |
| 265848-2014111 | 11/29/14 | 12/1/14 | 33135393 | LOUISVILLE | SOUTH | 1099 | 7 | $20,000.00 | 0 |
| 266106-2014111 | 11/30/14 | 12/1/14 | 26325752 | MANHATTAN | NORTH | W2 | 5 | $20,000.00 | 0 |
| 331393-2014111 | 11/30/14 | 12/1/14 | 26621106 | NEW ORLEANS | SOUTH | W2 | 2 | $1,000.00 | 0 |
December 2014
| Sales System Number | Date of Sale | Extract Date | Sales Consultant ID | Office | Region | Tax Type | Total Contracts | Total Sales | Total Cancellations |
| 265005-2014111 | 12/1/14 | 1/1/15 | 26354410 | MIAMI | FL/HI | 1099 | 0 | $0.00 | 0 |
| 265288-2014111 | 12/1/14 | 1/1/15 | 40278386 | Arkansas | SOUTHWEST | 1099 | 5 | $10,000.00 | 3 |
| 410718-2014111 | 12/1/14 | 1/1/15 | 26621106 | NEW ORLEANS | SOUTH | W2 | 10 | $0.00 | 10 |
| 265609-2014111 | 12/2/14 | 1/1/15 | 66464667 | ATLANTA | SOUTH | W2 | 9 | $7,000.00 | 3 |
| 265440-2014111 | 12/3/14 | 1/1/15 | 41107218 | Tri-State | SOUTHWEST | 1099 | 3 | $4,000.00 | 0 |
| 266104-2014111 | 12/3/14 | 1/1/15 | 26354410 | MIAMI | FL/HI | 1099 | 1 | $500.00 | 0 |
| 333704-2014111 | 12/4/14 | 1/1/15 | 57575758 | MIAMI | FL/HI | 1099 | 9 | $2,000.00 | 5 |
| 331393-2014111 | 12/5/14 | 1/1/15 | 26621106 | NEW ORLEANS | SOUTH | W2 | 1 | $1,000.00 | 0 |
| 407148-2014111 | 12/6/14 | 1/1/15 | 83748200 | EL PASO | SOUTH | 1099 | 0 | $0.00 | 0 |
| 265752-2014111 | 12/6/14 | 1/1/15 | 26150025 | MANHATTAN | NORTH | W2 | 18 | $7,000.00 | 5 |
| 265823-2014111 | 12/7/14 | 1/1/15 | 26152889 | MIAMI | FL/HI | 1099 | 14 | $1,000.00 | 13 |
| 407886-2014111 | 12/8/14 | 1/1/15 | 26621106 | NEW ORLEANS | SOUTH | W2 | 11 | $0.00 | 11 |
| 265801-2014111 | 12/9/14 | 1/1/15 | 26526109 | MANHATTAN | NORTH | W2 | 16 | $20,000.00 | 0 |
| 315320-2014111 | 12/9/14 | 1/1/15 | 26161204 | MANHATTAN | NORTH | W2 | 4 | $10,000.00 | 0 |
| 399053-2014111 | 12/10/14 | 1/1/15 | 33255099 | MANHATTAN | NORTH | W2 | 9 | $30,000.00 | 1 |
| 265778-2014111 | 12/11/14 | 1/1/15 | 26520168 | MANHATTAN | NORTH | W2 | 0 | $5,000.00 | 0 |
| 265758-2014111 | 12/12/14 | 1/1/15 | 35353356 | MANHATTAN | NORTH | W2 | 3 | $10,300.00 | 0 |
| 265801-2014111 | 12/13/14 | 1/1/15 | 26457518 | MANHATTAN | NORTH | W2 | 18 | $7,000.00 | 10 |
| 332509-2014111 | 12/14/14 | 1/1/15 | 75265980 | MANHATTAN | NORTH | W2 | 16 | $20,000.00 | 0 |
| 265068-2014111 | 12/15/14 | 1/1/15 | 33407197 | Arkansas | SOUTHWEST | 1099 | 0 | $0.00 | 0 |
| 329247-2014111 | 12/19/14 | 1/1/15 | 33011618 | LOUISVILLE | SOUTH | 1099 | 2 | $900.00 | 0 |
| 266106-2014111 | 12/20/14 | 1/1/15 | 26325752 | MANHATTAN | NORTH | W2 | 16 | $20,000.00 | 0 |
| 265862-2014111 | 12/21/14 | 1/1/15 | 26356778 | MIAMI | FL/HI | W2 | 1 | $5,000.00 | 0 |
| 265848-2014111 | 12/22/14 | 1/1/15 | 33135393 | LOUISVILLE | SOUTH | 1099 | 7 | $20,000.00 | 0 |
| 407197-2014111 | 12/23/14 | 1/1/15 | 33233704 | BRONX | NORTH | 1099 | 1 | $0.00 | 0 |
| 330618-2014111 | 12/24/14 | 1/1/15 | 26621106 | NEW ORLEANS | SOUTH | W2 | 2 | $0.00 | 2 |
| 265980-2014111 | 12/30/14 | 1/1/15 | 12658262 | MANHATTAN | NORTH | W2 | 16 | $20,000.00 | 0 |
Module 3 Explantions
| Sales system number | This value is unique to each location. If we are including location in the analysis, the sales system number is not required. It is easier to use the name. | nalysis | |||||||
| Extract date | The extract date does not addd any value to the analysis. | ||||||||
| Last name | The last name does not add any value to the analyis | ||||||||
| First name | The first name does not add any value to the analysis. | ||||||||
| State | State is unique to each location. | ||||||||
| Territory | Territory is unique to each location. | ||||||||
| Columns to be kept | |||||||||
| Date of sale | Date is important to keep track of sales per month, we | week, day, and year. | |||||||
| Sales consultant ID | ID is unique to each consultant. | ||||||||
| Office | The office is unique to each state. | ||||||||
| Region | The region is unique to each state. | ||||||||
| Tax Type | Tax type is important to each state to be applied to each sale. | ||||||||
| Total contracts | Important to know how many contracts per consultant. | ||||||||
| Total sales | Important to know the total sales per consultant. | ||||||||
| Total Cancellations | Important to know the number of cancellations per consultant. |
