Tasks

1 Complete all the individual sheets as was shown in class

2 Once you have gone to the Pivot, create only one pivot sheet, no need to create individual pivot sheet for individual objectives
3 From the pivot sheet and objectives given, share an email draft (in MS Word), addressed to HDNB TA Turzo.
4 Your assessment submission will still be in Google Classroom. Just attach the completed Excel file and the email draft Word file
5 The Word file of email draft will be assessed based on what you put in the Subject, what you put in the email body as summary for the Pivot finding, how yo

_x000D_#5A5A5A Internal
or the Pivot finding, how you start and end your email body

_x000D_#5A5A5A Internal
 Product Region Sales Quantity Objectives:
Widget A North 1500 10 1 SUM
Widget B South 2300 15 2 SUBTOTAL
Widget C East 1800 12 3 AVERAGE
Widget A West 1700 11 4 Sorting & Filter
Widget B North 2600 17
Widget C South 1950 13
Widget A East 1600 10
Widget B West 2500 16
Widget C North 2000 14

SUM 17950
AVERAGE 1994.444 13.11111

_x000D_#5A5A5A Internal
 Method Method Method
Write =SUM Alt + = press "AutoSum"
Write =subtotal
Write =AVERAGE
Home>Editing>Sort & Filter Ctrl + shift + L

_x000D_#5A5A5A Internal
 Region Sales Objectives:
Widget A North 1500 1 2000 or above sales is green
Widget B South 2300 2 1700 or below is red
Widget C East 1800
Widget A West 1700
Widget B North 2600
Widget C South 1950
Widget A East 1600
Widget B West 2500
Widget C North 2000

_x000D_#5A5A5A Internal
Method
Home>Styles>conditional formatting
Home>Styles>conditional formatting

_x000D_#5A5A5A Internal
 Item Category List Objectives:
Item 1 Semi-finished Raw material 1 Create list drop down
Item 2 Raw material Semi-finished
Item 3 Finished Finished
Item 4 Packaging Packaging

_x000D_#5A5A5A Internal
Method
Data>Data tools>Data validation

_x000D_#5A5A5A Internal
Product ID Product Name Price Order ID Product ID Qty Product Name Price Total value
101 Alpha 120 1001 101 2 Alpha 120 240
102 Beta 150 1002 103 4 Gamma 100 400
103 Gamma 100 1003 104 8 Delta 130 1040
104 Delta 130 1004 102 10 Beta 150 1500
1005 101 4 Alpha 120 480
1006 103 3 Gamma 100 300
1007 104 2 Delta 130 260
1008 102 7 Beta 150 1050
1009 101 9 Alpha 120 1080
1010 101 5 Alpha 120 600
1011 103 4 Gamma 100 400
1012 102 1 Beta 150 150
1013 102 8 Beta 150 1200
1014 104 5 Delta 130 650
1015 102 2 Beta 150 300
1016 101 7 Alpha 120 840
1017 101 9 Alpha 120 1080
1018 103 11 Gamma 100 1100
1019 101 6 Alpha 120 720
1020 103 3 Gamma 100 300
1021 101 8 Alpha 120 960
1022 103 13 Gamma 100 1300
1023 104 2 Delta 130 260
1024 102 5 Beta 150 750
1025 101 8 Alpha 120 960
146 17920
TOTAL qty TOTAL value

_x000D_#5A5A5A Internal
 Objective
1 Bring product name and price
2 find total against qty sold

_x000D_#5A5A5A Internal
Employee Department Salary Performance Bonus eligible
A Sales 10000 4.5 YES 1
B HR 20000 4.3 NO 2
C Sales 15000 4.6 YES 3
D Finance 18000 4.2 NO 4
E HR 17000 4.8 YES
80000

SUMIF
HR 37000
Sales 25000
Finance 18000
80000

Countif
Bonus eligible employee total 3

_x000D_#5A5A5A Internal
Objectives Method
Enter bonus eligibality by "yes" "no", 4.5 is eligible IF formula
Find total salary payable to each department SUMIF
Locking using $ use $ before column and row number in the formula
How many employees are eligible for bonus Countif formula

_x000D_#5A5A5A Internal
First Name Last Name Full Name Objective Method
John Doe John Doe 1 Find full name CONCATENATE formula
Jane Smith Jane Smith
Emily Brown Emily Brown
Mark Taylor Mark Taylor
Sara Wilson Sara Wilson

_x000D_#5A5A5A Internal
ENATE formula

_x000D_#5A5A5A Internal
 Data
Salesperson Region Sum of Total Sales Average of Units Sold
Alice 67450 9.866667
East 14200 9.444444
North 21400 10.92308
South 11800 11.57143
West 20050 8.5
bob 90850 9.66129
East 19100 8.5
North 27350 10.11765
South 11850 10.125
West 32550 9.869565
Charlie 76950 12
East 20600 13.55556
North 13600 13.11111
South 21700 10.84615
West 21050 11.25
Diana 75000 9.66
East 17150 9.615385
North 14400 10.22222
South 12900 7.454545
West 30550 10.82353
Total Result 310250 10.21
 Month Region Salesperson Product Units SoldUnit Price Total Sales
Apr-2024 South Charlie Widget A 3 100 300 1
Jun-2024 North Alice Widget A 16 200 3200 2
Apr-2024 West Diana Widget C 11 150 1650 3
Jan-2024 South Charlie Widget B 12 100 1200 4
Apr-2024 West Diana Widget A 10 100 1000 5
Mar-2024 South bob Widget C 16 150 2400 6
Jan-2024 West Alice Widget B 8 150 1200 7
Apr-2024 East bob Widget C 6 150 900 8
May-2024 West Charlie Widget A 12 200 2400
Mar-2024 East bob Widget A 8 200 1600
Mar-2024 East bob Widget A 4 100 400 1
Apr-2024 West Charlie Widget C 8 150 1200 2
Apr-2024 East Diana Widget C 18 100 1800 3
Apr-2024 North bob Widget A 5 100 500
May-2024 East Diana Widget C 9 100 900
May-2024 South bob Widget A 4 100 400
May-2024 West Diana Widget A 17 150 2550
Feb-2024 North Charlie Widget B 9 100 900
Jan-2024 West bob Widget C 1 150 150
Mar-2024 South Charlie Widget A 13 150 1950
Jun-2024 South bob Widget A 16 200 3200
Feb-2024 South Charlie Widget C 13 150 1950
May-2024 East Charlie Widget B 14 200 2800
Jan-2024 East bob Widget B 3 150 450
Jun-2024 West bob Widget A 6 150 900
Feb-2024 West bob Widget C 18 150 2700
Jan-2024 North Alice Widget B 19 200 3800
Mar-2024 North Diana Widget C 5 200 1000
Feb-2024 West Alice Widget C 15 100 1500
Feb-2024 East Alice Widget A 2 200 400
Jun-2024 South Charlie Widget B 10 200 2000
Jan-2024 West bob Widget A 18 200 3600
Jun-2024 North Diana Widget B 13 200 2600
Feb-2024 East Charlie Widget B 5 100 500

_x000D_#5A5A5A Internal
Apr-2024 West Alice Widget B 1 200 200
Feb-2024 West bob Widget B 1 150 150
Mar-2024 East Charlie Widget B 18 150 2700
May-2024 West bob Widget C 15 200 3000
Feb-2024 East Charlie Widget A 17 200 3400
May-2024 South bob Widget C 11 150 1650
Jan-2024 East Alice Widget B 17 150 2550
Mar-2024 East Diana Widget A 13 100 1300
Jun-2024 East Alice Widget B 1 150 150
Jan-2024 West Diana Widget A 2 150 300
May-2024 West bob Widget C 9 200 1800
Mar-2024 East Charlie Widget A 3 150 450
Feb-2024 West Alice Widget A 1 100 100
Jan-2024 North Diana Widget A 16 150 2400
Jan-2024 North Alice Widget A 6 200 1200
Mar-2024 West Diana Widget A 17 200 3400
Feb-2024 West Alice Widget B 5 150 750
May-2024 East Diana Widget A 5 200 1000
Mar-2024 South Diana Widget A 6 100 600
Apr-2024 West bob Widget C 3 100 300
Apr-2024 North Diana Widget A 5 150 750
Jun-2024 East bob Widget C 5 150 750
Jan-2024 West Diana Widget B 10 150 1500
Jun-2024 North Charlie Widget A 10 200 2000
Feb-2024 North Charlie Widget C 19 100 1900
Mar-2024 South Charlie Widget C 17 100 1700
Jun-2024 East Diana Widget B 14 200 2800
Feb-2024 East bob Widget A 9 100 900
Apr-2024 South bob Widget A 14 150 2100
May-2024 South Diana Widget C 1 150 150
Jan-2024 East Charlie Widget A 19 150 2850
May-2024 East Charlie Widget C 13 100 1300
Feb-2024 West bob Widget A 13 150 1950
Apr-2024 West Charlie Widget C 4 200 800
Jan-2024 South Alice Widget A 1 200 200

_x000D_#5A5A5A Internal
Feb-2024 West bob Widget B 17 100 1700
May-2024 North Charlie Widget A 8 200 1600
Feb-2024 West bob Widget A 2 200 400
Jun-2024 West Alice Widget B 8 100 800
Jan-2024 North bob Widget A 7 150 1050
Apr-2024 South bob Widget B 2 150 300
Feb-2024 North bob Widget A 3 150 450
Jan-2024 West bob Widget C 18 100 1800
Feb-2024 South Alice Widget C 12 200 2400
Mar-2024 West Diana Widget A 1 150 150
Apr-2024 North bob Widget A 12 150 1800
Feb-2024 North bob Widget A 5 100 500
Jan-2024 West Charlie Widget C 17 150 2550
Jun-2024 East bob Widget A 16 100 1600
May-2024 East Charlie Widget C 15 200 3000
Feb-2024 North Diana Widget B 15 100 1500
Jun-2024 North Alice Widget C 5 100 500
Jan-2024 East Alice Widget C 14 100 1400
Feb-2024 East Diana Widget B 2 100 200
Mar-2024 West Alice Widget C 11 200 2200
Mar-2024 West Diana Widget A 19 200 3800
Mar-2024 East Diana Widget A 7 150 1050
Jan-2024 West Diana Widget C 6 200 1200
Mar-2024 West Alice Widget A 2 200 400
May-2024 South Charlie Widget C 6 150 900
May-2024 West Diana Widget A 18 200 3600
Mar-2024 North bob Widget A 2 100 200
May-2024 West Alice Widget C 18 100 1800
Mar-2024 West Diana Widget A 15 150 2250
May-2024 East Diana Widget C 19 150 2850
Jun-2024 North bob Widget A 2 150 300
Jun-2024 West Charlie Widget A 6 100 600
Feb-2024 West Charlie Widget B 1 200 200
May-2024 South Charlie Widget B 15 100 1500
Jan-2024 West Diana Widget C 10 150 1500

_x000D_#5A5A5A Internal
Feb-2024 West Diana Widget B 19 200 3800
Jan-2024 South Diana Widget C 17 200 3400
May-2024 East Alice Widget B 5 100 500
Mar-2024 West bob Widget A 4 150 600
Jan-2024 South Diana Widget C 10 100 1000
Jun-2024 North bob Widget C 17 200 3400
Feb-2024 East bob Widget A 10 100 1000
Feb-2024 North Charlie Widget C 17 100 1700
May-2024 North Alice Widget C 5 150 750
Mar-2024 South Diana Widget B 2 150 300
Feb-2024 West Charlie Widget C 6 200 1200
Apr-2024 South Charlie Widget A 2 100 200
Jun-2024 South Diana Widget B 13 100 1300
Apr-2024 South bob Widget B 11 100 1100
Feb-2024 South Charlie Widget A 11 200 2200
Apr-2024 East bob Widget A 16 200 3200
Jun-2024 West Alice Widget B 11 150 1650
May-2024 North bob Widget C 19 150 2850
Jan-2024 North Alice Widget B 15 150 2250
Jan-2024 West bob Widget A 16 100 1600
May-2024 North Charlie Widget B 11 100 1100
Apr-2024 West Charlie Widget A 16 150 2400
Jan-2024 North Alice Widget B 8 100 800
May-2024 South Diana Widget C 4 150 600
Jan-2024 North Alice Widget B 8 200 1600
Mar-2024 West Alice Widget B 4 150 600
Feb-2024 West bob Widget A 3 200 600
May-2024 West bob Widget A 3 100 300
Jun-2024 West Alice Widget A 18 150 2700
Mar-2024 West Charlie Widget B 19 100 1900
Apr-2024 East bob Widget C 19 200 3800
Feb-2024 East Diana Widget A 5 200 1000
Apr-2024 North bob Widget A 18 200 3600
May-2024 West bob Widget B 10 100 1000
May-2024 North Diana Widget A 6 100 600

_x000D_#5A5A5A Internal
May-2024 West Alice Widget C 1 100 100
Jun-2024 West Diana Widget C 5 100 500
Jan-2024 East Alice Widget A 9 200 1800
Feb-2024 East Alice Widget A 12 200 2400
Jan-2024 South Alice Widget B 14 150 2100
Jun-2024 East Diana Widget C 2 150 300
Mar-2024 South Alice Widget C 17 100 1700
Mar-2024 South Charlie Widget A 14 200 2800
Jan-2024 South Diana Widget B 13 200 2600
Mar-2024 South Charlie Widget C 9 200 1800
Jun-2024 South Alice Widget C 15 150 2250
Feb-2024 North bob Widget B 15 150 2250
Mar-2024 East Alice Widget B 11 200 2200
May-2024 South Charlie Widget C 16 200 3200
Jan-2024 West Charlie Widget C 17 200 3400
Jan-2024 East Diana Widget B 4 150 600
Apr-2024 East bob Widget C 1 200 200
Feb-2024 South Diana Widget A 8 200 1600
Mar-2024 North Charlie Widget A 17 100 1700
Jan-2024 South Alice Widget B 3 100 300
Mar-2024 North bob Widget B 15 150 2250
May-2024 West Alice Widget B 15 200 3000
Apr-2024 East Alice Widget A 14 200 2800
May-2024 West bob Widget C 14 100 1400
Apr-2024 North Diana Widget A 2 150 300
Jan-2024 North Alice Widget C 13 100 1300
Apr-2024 West Charlie Widget A 14 100 1400
Apr-2024 North Alice Widget C 19 100 1900
Jan-2024 West bob Widget A 7 100 700
Apr-2024 East bob Widget B 3 200 600
Jun-2024 West bob Widget A 15 200 3000
May-2024 East Diana Widget C 14 100 1400
Mar-2024 West Alice Widget A 11 150 1650
Jun-2024 North Diana Widget A 15 150 2250
Feb-2024 West Diana Widget A 13 150 1950

_x000D_#5A5A5A Internal
Jun-2024 North bob Widget B 18 150 2700
May-2024 West bob Widget A 6 100 600
Feb-2024 West Diana Widget B 3 200 600
Apr-2024 South Alice Widget B 19 150 2850
Jun-2024 North Charlie Widget A 15 100 1500
Apr-2024 South Diana Widget A 5 150 750
Jun-2024 North Alice Widget A 10 150 1500
Apr-2024 South bob Widget C 7 100 700
Mar-2024 East bob Widget A 17 200 3400
Feb-2024 West Alice Widget B 7 200 1400
Jan-2024 North Charlie Widget A 12 100 1200
Feb-2024 North bob Widget C 17 200 3400
May-2024 West bob Widget A 13 100 1300
May-2024 North bob Widget C 4 100 400
Apr-2024 North Alice Widget C 10 100 1000
Apr-2024 South Diana Widget A 3 200 600
Apr-2024 North bob Widget B 9 100 900
Mar-2024 East Diana Widget C 13 150 1950
Apr-2024 East Charlie Widget A 18 200 3600
Jan-2024 West bob Widget A 15 200 3000
May-2024 North bob Widget B 4 200 800
Feb-2024 West Charlie Widget C 15 200 3000
Mar-2024 East bob Widget C 2 150 300
May-2024 North Diana Widget A 15 200 3000
May-2024 North Alice Widget A 8 200 1600
May-2024 West Diana Widget B 8 100 800

_x000D_#5A5A5A Internal
Objective Method
Adjust the columns so the data is clearly visible
Replace all "bobbb" to "Bob" CTRL + F>Replace
Create pivot in separate sheet Insert>tables>pivot table
The draft email to be sent for
Find Region wise, individual product total sales in value
class assessment is ONLY for this
Pivot data and findings.
Find Region wise, individual product total sales in units
Find salesperson wise total sales in value and units
Find region wise highest selling salesperson
Find region wise salesperson average sales in unit

For drafting email (in MS Word)

Create an email body that acts as the summary to all your above findings
You can add any other statistics from the pivot you find interesting
Make sure your email is to the point, highlight things like top performers, highest value or highest units etc. do not put all your findings in the email body

_x000D_#5A5A5A Internal
ngs in the email body

_x000D_#5A5A5A Internal