Module 04 Course Project - Classify and Analyze Data

Pinay1972
dataretrieval.xls
Home>Human Resource Management homework help> Module 04 Course Project - Classify and Analyze Data

October 2014

Date of Sale Sales Consultant ID Office Region Tax Type Total Contracts Total Sales Total Cancellations
10/1/14 35353356 MANHATTAN NORTH W2 10 $30,291.00 1
10/1/14 66464667 ATLANTA SOUTH W2 4 $39,999.00 0
10/3/14 57575758 MIAMI FL/HI 1099 11 $92,829.00 1
10/3/14 75265980 MANHATTAN NORTH W2 4 $44,255.00 2
10/5/14 83748200 EL PASO SOUTH 1099 0 $0.00 0
10/10/14 26354410 MIAMI FL/HI 1099 5 $20,100.00 1
10/11/14 33407197 Arkansas SOUTHWEST 1099 0 $7,881.00 0
10/12/14 40278386 Arkansas SOUTHWEST 1099 1 $10,000.00 0
10/13/14 41107218 Tri-State SOUTHWEST 1099 11 $83,641.00 5
10/14/14 26150025 MANHATTAN NORTH W2 13 $0.00 13
10/20/14 26354410 MIAMI FL/HI 1099 0 $0.00 0
10/20/14 26354410 MIAMI FL/HI 1099 7 $44,242.00 0
10/20/14 26356778 MIAMI FL/HI W2 1 $555.00 0
10/20/14 26457518 MANHATTAN NORTH W2 9 $80,000.00 0
10/20/14 26520168 MANHATTAN NORTH W2 10 $45,000.00 0
10/20/14 26526109 MANHATTAN NORTH W2 25 $54,535.00 0
10/25/14 12658262 MANHATTAN NORTH W2 9 $24,222.00 0
10/25/14 26161204 MANHATTAN NORTH W2 4 $14,344.00 0
10/25/14 26531823 CHICAGO NORTH W2 1 $6,566.00 0
10/26/14 33011618 LOUISVILLE SOUTH 1099 1 $250.00 0
10/26/14 33255099 MANHATTAN NORTH W2 16 $50,000.00 1
10/28/14 33233704 BRONX NORTH 1099 0 $321.00 0
10/28/14 43207148 Arkansas SOUTHWEST 1099 3 $600.00 0
10/30/14 26325752 MANHATTAN NORTH W2 13 $72,990.00 0
10/30/14 26621106 NEW ORLEANS SOUTH W2 7 $35,000.00 0
10/30/14 26621106 NEW ORLEANS SOUTH W2 10 $87,382.00 1
10/31/14 33135393 LOUISVILLE SOUTH 1099 9 $21,222.00 0

November 2014

Date of Sale Sales Consultant ID Office Region Tax Type Total Contracts Total Sales Total Cancellations
11/1/14 26354410 MIAMI FL/HI 1099 5 $20,100.00 1
11/2/14 33407197 Arkansas SOUTHWEST 1099 0 $0.00 0
11/2/14 40278386 Arkansas SOUTHWEST 1099 1 $10,000.00 0
11/2/14 41107218 Tri-State SOUTHWEST 1099 4 $4,000.00 0
11/3/14 33011618 LOUISVILLE SOUTH 1099 1 $900.00 0
11/4/14 33255099 MANHATTAN NORTH W2 9 $30,000.00 1
11/5/14 66464667 ATLANTA SOUTH W2 1 $550.00 0
11/5/14 35353356 MANHATTAN NORTH W2 3 $10,300.00 0
11/5/14 26520168 MANHATTAN NORTH W2 2 $5,000.00 0
11/5/14 26152889 MIAMI FL/HI 1099 14 $1,000.00 13
11/6/14 26356778 MIAMI FL/HI W2 1 $5,000.00 0
11/7/14 26354410 MIAMI FL/HI 1099 7 $500.00 0
11/8/14 26531823 CHICAGO NORTH W2 1 $3,000.00 0
11/10/14 33233704 BRONX NORTH 1099 0 $0.00 0
11/10/14 43207148 Arkansas SOUTHWEST 1099 3 $0.00 0
11/11/14 26526109 MANHATTAN NORTH W2 16 $20,000.00 0
11/12/14 26457518 MANHATTAN NORTH W2 2 $1,010.00 0
11/13/14 12658262 MANHATTAN NORTH W2 2 $211.00 0
11/15/14 75265980 MANHATTAN NORTH W2 4 $20,100.00 1
11/16/14 57575758 MIAMI FL/HI 1099 11 $500.00 10
11/17/14 26161204 MANHATTAN NORTH W2 4 $10,000.00 0
11/23/14 83748200 EL PASO SOUTH 1099 0 $0.00 0
11/27/14 26621106 NEW ORLEANS SOUTH W2 10 $87,382.00 1
11/29/14 26150025 MANHATTAN NORTH W2 5 $0.00 5
11/29/14 33135393 LOUISVILLE SOUTH 1099 7 $20,000.00 0
11/30/14 26325752 MANHATTAN NORTH W2 5 $20,000.00 0
11/30/14 26621106 NEW ORLEANS SOUTH W2 2 $1,000.00 0

December 2014

Date of Sale Sales Consultant ID Office Region Tax Type Total Contracts Total Sales Total Cancellations
12/1/14 26354410 MIAMI FL/HI 1099 0 $0.00 0
12/1/14 40278386 Arkansas SOUTHWEST 1099 5 $10,000.00 3
12/1/14 26621106 NEW ORLEANS SOUTH W2 10 $0.00 10
12/2/14 66464667 ATLANTA SOUTH W2 9 $7,000.00 3
12/3/14 41107218 Tri-State SOUTHWEST 1099 3 $4,000.00 0
12/3/14 26354410 MIAMI FL/HI 1099 1 $500.00 0
12/4/14 57575758 MIAMI FL/HI 1099 9 $2,000.00 5
12/5/14 26621106 NEW ORLEANS SOUTH W2 1 $1,000.00 0
12/6/14 83748200 EL PASO SOUTH 1099 0 $0.00 0
12/6/14 26150025 MANHATTAN NORTH W2 18 $7,000.00 5
12/7/14 26152889 MIAMI FL/HI 1099 14 $1,000.00 13
12/8/14 26621106 NEW ORLEANS SOUTH W2 11 $0.00 11
12/9/14 26526109 MANHATTAN NORTH W2 16 $20,000.00 0
12/9/14 26161204 MANHATTAN NORTH W2 4 $10,000.00 0
12/10/14 33255099 MANHATTAN NORTH W2 9 $30,000.00 1
12/11/14 26520168 MANHATTAN NORTH W2 0 $5,000.00 0
12/12/14 35353356 MANHATTAN NORTH W2 3 $10,300.00 0
12/13/14 26457518 MANHATTAN NORTH W2 18 $7,000.00 10
12/14/14 75265980 MANHATTAN NORTH W2 16 $20,000.00 0
12/15/14 33407197 Arkansas SOUTHWEST 1099 0 $0.00 0
12/19/14 33011618 LOUISVILLE SOUTH 1099 2 $900.00 0
12/20/14 26325752 MANHATTAN NORTH W2 16 $20,000.00 0
12/21/14 26356778 MIAMI FL/HI W2 1 $5,000.00 0
12/22/14 33135393 LOUISVILLE SOUTH 1099 7 $20,000.00 0
12/23/14 33233704 BRONX NORTH 1099 1 $0.00 0
12/24/14 26621106 NEW ORLEANS SOUTH W2 2 $0.00 2
12/30/14 12658262 MANHATTAN NORTH W2 16 $20,000.00 0

Module 3 Explanation

Columns to be deleted
Sales System Number This value is unique to each location. Hence if we are including location in our analysis, the sales system number is not required. Also its easier to analyse data with name which can be easily understood
Extract Date The default value of extract date is 1st date of subsequent month. This value does not add any value to our analysis.
SC Last name SC last name can be common between two SCs. Also analysing using SC id is easier as it is unique to each SC
SC First Name SC first name can be common between two SCs
State State, territory and office are unique for each location. Hence we use only one of them. I chose office
Territory State, territory and office are unique for each location. Hence we use only one of them. I chose office
Columns to be kept
Date of sale To recognise date and month. Keeping date can facilitate weekly, fortnightly, monthly, quarterly etc. data analysis
Sales Consultant ID Unique ID of each SC. To check each SC's performance and compare against each other and their allocated targets.
Office To analyse office wise sales.
Region To analyse office wise sales.
Tax Type Tax Type has to be kept to apply tax on sales. This is not unique to office name.
Total Contracts To know number of sales as cancellations are also there
Total Sales Essential number for analysis
Total Cancellations To know number of cancellations as number of sales is also there
a
c
e
f
g
j