Excel For Data Analytics

Module 1
Introduction

Problem 1A
a.) How do you paste the values of a data set?
10
2 20 2
4 40 4
6 60 6
8 80 8

b.) How do you paste the format of a data set?

10
2 20
4 40
6 60
8 80

c.) How do you paste the formulas of a data set?

10
2 20 20
4 40 40
6 60 60
8 80 80
Problem 2A

The client gives you the below table of their monthly Revenue and COGS; however, you noticed the revenue
and COGS signs are inverted. Using the Paste Special multiply feature, change the sign of the table below.

January February March April May

Revenue -$184,790 -$103,998 -$96,732 -$234,550 -$78,376
COGS $123,809 $69,679 $64,810 $157,149 $52,512
Gross Profit -$60,981 -$34,319 -$31,922 -$77,402 -$25,864

(1)
1,000

Problem 3A

You have the months listed in Column F, but you want to transpose the months to Row 87

January
February
March
April
May

$184,790 $103,998 $96,732 $234,550 $78,376

Problem 1a - Instructor Lead

Your manager has given you a table of raw data to perform analysis on. The data has been pulled from multiple sources and needs to be standardized prior to analysis.

ID/Staff name ID Staff Name

001Zainab
002Michael
003Raheem
004Jacob
005Sasuke
006Naruto

S/N Full name First Name Last Name

1 Michael Davies
2 Justin Gbenro
3 Sodiq Kowonleru
4 Benson Majeta
5 Hudson Filefuwon
6 Gbenro Thompson
7 Rufus Elewe
8 Farouk Basdmus
9 Gbesibe Tolulope
10 Jedesola Sapa
d prior to analysis.
Problem 1a - Instructor Lead

Your manager has given you a table of raw data to perform analysis on. The data has been pulled from multiple sources and needs to be standardized prior to analysis.

Product Product
PROGRAF
PARIET
CONCERTA
LEUSTATIN
VELCADE
LEUSTATIN
VELCADE

Find the total of these values

121211
226321
111243
224334
145532

TOTAL
d prior to analysis.
Problem 2a - Instructor Lead

Your manager has given you a table of raw data to perform analysis on. The data has been pulled from multiple sources and needs to be standardized prior to analysis.

Product Detail
PROGRAF 5MG 50CAPSULAS PROIBID
PARIET 20 MG 28 CP OR
CONCERTA 54MG FR 30CP OR
LEUSTATIN INJ CT C/ 1 FR AMP
VELCADE 3,5 MG OR
LEUSTATIN INJ CT C/ 1 FR AMP
VELCADE 3,5 MG OR

Trim
 Working with Cell References (Relative Versus Absolute Cell References in Formulas)

Class Exercise - Absolute Referencing

Customer Name Amount Discount Total Discount 20%
Adeiza 500,000
Tola 1,000,000
Naruto 335,000
Sasuke 850,000
Hulk 690,335
Logan 900,000

Class Exercise - Relative Referencing

Discount
Customer Name Amount 10% 20% 30%
Adeiza 500,000
Tola 1,000,000
Naruto 335,000
Sasuke 850,000
Hulk 690,335
Logan 900,000

Class Work - Relative Referencing

Multiply the rows by the column
1 2 3 4 5 6 7 8 9 10
1
2
3
4
5
6
7
8
9
10
Discount 15%
Commission
Agent Item Price Quantity Total Amt Discount (15%) Amt less Discount 10% 15% 20%
Elizabeth Shoe 500 50
John Water 250 300
Oke Potato 30 7000
Suleman Phones 6000 620
Efemena Cars 50000 100
Michael Sandals 2000 1000
Susan Television 350 600
Julie Mouse 100 350
Nene Keyboard 800 750
Layo Fan 560 900
Gbolahan Switch 950 700
Bayo CDs 750 640
Fisayo Headset 390 630
Temitope Books 580 555
Amaka Tissue 620 962
Santa Meat 410 410
Olamide Wrist watch 750 251
Dolapo Clock 1000 320
James Bags 5000 145
Use Absolute and Relative Referencing to fill the blank spaces
Class Work_Data Validation

Name Color Size Age Bracket Color Size

Sodiq Red Small
Frank Blue Medium
Ayodele Green Large
Feyisola White X-Large
Damilola
Ovie
Kowonleru

Class Work_Conditional Formatting

S/N Name Attendance Assignment Test 1 Test 2 Exam2
1 Feyi 10 8 8 4 15
2 Susan 2 7 9 5 20
3 Damola 5 4 7 5 40
4 Kemi 6 5 4 4 50
5 Taiwo 9 6 4 6 40
6 Janet 8 9 7 3 21
7 Ubby 7 9 8 3 32
8 Suzzy 4 8 9 2 35
9 Monisola 5 8 9 4 34
10 Dami 9 7 9 5 20
11 Oke 6 6 7 5 25
12 Sam 5 3 9 1 14
13 Daniel 3 3 9 1 20
14 Grace 8 4 9 8 10

Conditional Formatting_Identifying Duplicates

Age Bracket
20 - 25
26 - 30
31 - 35
36 - 40

Total
Contact Name Address City
Ana Trujillo Avda. de la Constitución 2222
México D.F.
Antonio Moreno Mataderos 2312 México D.F.
Christina Berglund Berguvsvägen 8 Luleå
Frédérique Citeaux 24, place Kléber Strasbourg
Thomas Hardy 120 Hanover Sq. London
Georg Pipps Geislweg 14 Salzburg
Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F.
Hanna Moos Forsterstr. 57 Mannheim
Henriette Pfalzheim Mehrheimerstr. 369 Köln
Henriette Pfalzheim Mehrheimerstr. 369 Köln
Horst Kloss Taucherstraße 10 Cunewalde
Isabel de Castro Estrada da saúde n. 58 Lisboa
Maria Anders Obere Str. 57 Berlin
Marie Bertrand 265, boulevard Charonne Paris
Hanna Moos Forsterstr. 57 Mannheim
Sergio Gutiérrez Av. del Libertador 900 Buenos Aires
Thomas Hardy 120 Hanover Sq. London
Maria Anders Obere Str. 57 Berlin
Thomas Hardy 120 Hanover Sq. London

Class Work_Data Validation & Conditional Formatting

Price
Qty Produced
2019 2020 2021 2022 2023

Revenue 10%

COGS 60%

Gross Profit
Scenario Price Qty Produced
Best Case 1500 10000
Base Case 1000 5000
Worst Case 500 1500
OPEX
Salaries and Wages 25%
Marketing expenses 40%
Supply and Logistics

Net Profit
Problem 1
Your manager has given you a table of raw data to perform analysis on. You have been tasked to find the Sum,
Average and Count of the Sales data provided below. Using the Sum, Average, count and subtotal formula's, fill in
the empty fields below.

Month Team Sales

Jan Team 3 ₦ 1,522.00
Jan Team 1 ₦ 1,589.00
Jan Team 2 ₦ 1,829.00
Jan Team 2 ₦ 1,797.00
Jan Team 1 ₦ 1,744.00
Feb Team 1 ₦ 1,868.00
Feb Team 4 ₦ 1,694.00
Mar Team 4 ₦ 1,626.00
Mar Team 1 ₦ 1,781.00
April Team 2 ₦ 1,279.00
April Team 3 ₦ 1,684.00

Using Sum, Average and Count, fill in the lines below:

Sum
Average
Count

Using Subtotal, fill in the lines below:

Sum
Average
Count

Problem 2
Month Team Sales
Jan Team 3 ₦ 1,522.00
Jan Team 1 ₦ 1,589.00
Jan Team 2 ₦ 1,829.00
Jan Team 2 ₦ 1,797.00
Jan Team 1 ₦ 1,744.00
Feb Team 1 ₦ 1,868.00
Feb Team 4 ₦ 1,694.00
Mar Team 4 ₦ 1,626.00
Mar Team 1 ₦ 1,781.00
April Team 2 ₦ 1,279.00
April Team 3 ₦ 1,684.00

Using SUM and SUBTOTAL, fill in the lines below:

Total Sales (using SUM)

Total Sales (using SUBTOTAL)
OrderDate Region Rep Item Units Unit Cost Unit Price
43,106 East Jones Pencil 95.00 2 4
43,123 Central Kivell Binder 50 20 21
43,140 Central Jardine Pencil 36 5 6
43,157 Central Gill Pen 27 20 21
43,174 West Sorvino Pencil 56 3 4
43,191 East Jones Binder 60 5 6
43,208 Central Andrews Pencil 75 2 3
43,225 Central Jardine Pencil 90 5 6
43,242 West Thompson Pencil 32 2 3
43,259 East Jones Binder 60 9 12
43,276 Central Morgan Pencil 90 5 7
43,293 East Howard Binder 29 2 5
43,310 East Parent Binder 81 20 25
43,327 East Jones Pencil 35 5 6
43,344 Central Smith Desk 2 125 130
43,361 East Jones Pen Set 16 16 19
43,378 Central Morgan Binder 28 9 12
43,395 East Jones Pen 64 9 12
43,412 East Parent Pen 15 20 24
43,429 Central Kivell Pen Set 96 5 6
43,446 Central Smith Pencil 67 1 4
43,463 East Parent Pen Set 74 16 18
43,480 Central Gill Binder 46 9 13
43,497 Central Smith Binder 87 15 19
43,514 East Jones Binder 4 5 8
43,531 West Sorvino Binder 7 20 25
43,548 Central Jardine Pen Set 50 5 24
43,565 Central Andrews Pencil 66 2 6
43,582 East Howard Pen 96 5 4
43,599 Central Gill Pencil 53 1 5
43,616 Central Gill Binder 80 9 7
43,633 Central Kivell Desk 5 125 140
43,650 East Jones Pen Set 62 5 6
43,667 Central Morgan Pen Set 55 12 15
43,684 Central Kivell Pen Set 42 24 24
43,701 West Sorvino Desk 3 275 277
43,718 Central Gill Pencil 7 1 5
43,735 West Sorvino Pen 76 2 4
43,752 West Thompson Binder 57 20 23
43,769 Central Andrews Pencil 14 1 5
43,786 Central Jardine Binder 11 5 6
43,803 Central Jardine Binder 94 20 23
43,820 Central Andrews Binder 28 5 5
43,208 Central Andrews Pencil 75 2 3
43,209 Central Jardine Pencil 90 5 5
43,210 West Thompson Pencil 32 2 6
43,211 East Jones Binder 60 9 9
43,212 Central Morgan Pencil 90 5 6
43,213 East Howard Binder 29 2 3
43,214 East Parent Binder 81 20 22
43,215 East Jones Pencil 35 5 6
43,216 Central Smith Desk 2 125 129
43,217 East Jones Pen Set 16 16 18
43,430 East Jones Pen Set 62 5 6
43,431 Central Morgan Pen Set 55 12 15
43,432 Central Kivell Pen Set 42 24 26
43,433 West Sorvino Desk 3 275 290
43,434 Central Gill Pencil 7 1 3
43,435 West Sorvino Pen 76 2 4
43,436 West Thompson Binder 57 20 26
43,437 Central Andrews Pencil 14 1 3
43,438 Central Jardine Binder 11 5 6
43,439 Central Jardine Binder 94 20 26
43,440 Central Andrews Binder 28 5 6
43,441 Central Andrews Pencil 75 2 4
43,442 Central Jardine Pencil 90 5 6
43,443 West Thompson Pencil 32 2 3
43,444 East Jones Binder 60 9 13
43,445 Central Morgan Pencil 90 5 6
43,446 East Howard Binder 29 2 4
49,273 East Jones Pencil 95 2 3
49,274 Central Kivell Binder 50 20 23
49,275 Central Jardine Pencil 36 5 6
49,276 Central Gill Pen 27 20 23
49,277 West Sorvino Pencil 56 3 5
49,278 East Jones Binder 60 5 6
49,279 Central Andrews Pencil 75 2 4
49,280 Central Jardine Pencil 90 5 8
49,281 West Thompson Pencil 32 2 3
49,282 East Jones Binder 60 9 13
49,283 Central Morgan Pencil 90 5 9
49,284 East Howard Binder 29 2 5
49,285 East Parent Binder 81 20 23
49,286 East Jones Pencil 35 5 9
49,287 Central Smith Desk 2 125 130
49,288 East Jones Pen Set 16 16 19
49,289 Central Morgan Binder 28 9 10
49,290 East Jones Pen 64 9 10
49,291 East Parent Pen 15 20 26
49,292 Central Kivell Pen Set 96 5 9
49,293 Central Smith Pencil 67 1 4
49,294 East Parent Pen Set 74 16 16
49,295 Central Gill Binder 46 9 9
49,296 Central Smith Binder 87 15 16
49,297 East Jones Binder 4 5 9
49,298 West Sorvino Binder 7 20 21
49,299 Central Jardine Pen Set 50 5 6
49,300 Central Andrews Pencil 66 2 4
49,301 East Howard Pen 96 5 9
49,302 Central Gill Pencil 53 1 4
49,303 Central Gill Binder 80 9 13
49,304 Central Kivell Desk 5 125 124
49,305 East Jones Pen Set 62 5 6
49,306 Central Morgan Pen Set 55 12 20
49,307 Central Kivell Pen Set 42 24 29
49,308 West Sorvino Desk 3 275 300
49,309 Central Gill Pencil 7 1 6
49,310 West Sorvino Pen 76 2 5
49,311 West Thompson Binder 57 20 26
49,312 Central Andrews Pencil 14 1 9
49,313 Central Jardine Binder 11 5 8
49,314 Central Jardine Binder 94 20 21
49,315 Central Andrews Binder 28 5 2
49,316 Central Andrews Pencil 75 2 7
49,317 Central Jardine Pencil 90 5 6
49,318 West Thompson Pencil 32 2 3
49,319 East Jones Binder 60 9 15
49,320 Central Morgan Pencil 90 5 9
49,321 East Howard Binder 29 2 4
49,322 East Parent Binder 81 20 26
49,323 East Jones Pencil 35 5 5
49,324 Central Smith Desk 2 125 144
49,325 East Jones Pen Set 16 16 21
49,326 East Jones Pen Set 62 5 6
49,327 Central Morgan Pen Set 55 12 15
49,328 Central Kivell Pen Set 42 24 30
49,329 West Sorvino Desk 3 275 350
49,330 Central Gill Pencil 7 1 4
49,331 West Sorvino Pen 76 2 4
49,332 West Thompson Binder 57 20 21
49,333 Central Andrews Pencil 14 1 3
49,334 Central Jardine Binder 11 5 7
49,335 Central Jardine Binder 94 20 21
49,336 Central Andrews Binder 28 5 8
49,337 Central Andrews Pencil 75 2 3
49,338 Central Jardine Pencil 90 5 7
49,339 West Thompson Pencil 32 2 4
49,340 East Jones Binder 60 9 12
49,341 Central Morgan Pencil 90 5 6
49,342 East Howard Binder 29 2 3
Name the Table
Apply Filters
Transform to a Table

Total Cost Total Price Profit/loss

Min
Max
Average
Sum

Subtotal
Total Cost Total Price Profit/loss
Min
Max
Average
Sum

Count
CountA
CountBlank
Distributor ID Distributor Name Country Product Code Product Sales Channel
23345 Devin Abbott France SUPA105 Super Soft Bulk - 2 Litres Online
23278 Aphrodite Brennan Malawi SUPA105 Super Soft Bulk - 2 Litres Direct
23303 Guinevere Key Colombia SUPA105 Super Soft Bulk - 2 Litres Retail
23353 Zahir Fields Canada SUPA105 Super Soft Bulk - 2 Litres Direct
23289 Deacon Craig Mongolia SUPA105 Super Soft Bulk - 2 Litres Retail
23378 Brynne Mcgowan Finland SUPA105 Super Soft Bulk - 2 Litres Online
23283 Lani Sweet Vanuatu SUPA105 Super Soft Bulk - 2 Litres Online
23324 Noble Warner Burkina Faso SUPA104 Super Soft - 1 Litre Retail
23264 Levi Douglas Tanzania, United Republic of DETA800 Detafast Stain Remover - 800ml Online
23291 Jelani Odonnell Albania DETA800 Detafast Stain Remover - 800ml Retail
23305 Jared Sandoval Botswana DETA800 Detafast Stain Remover - 800ml Online
23350 Hiroko Acevedo Burundi DETA800 Detafast Stain Remover - 800ml Online
23300 Rhona Clarke Zimbabwe SUPA104 Super Soft - 1 Litre Online
23348 Tad Mack Iceland SUPA104 Super Soft - 1 Litre Retail
23290 Rama Goodwin Tunisia DETA800 Detafast Stain Remover - 800ml Online
23328 Keaton Wolfe French Southern Territories SUPA105 Super Soft Bulk - 2 Litres Retail
23294 Samuel Ayala Brazil DETA800 Detafast Stain Remover - 800ml Retail
23371 Doris Williams Trinidad and Tobago SUPA103 Super Soft - 500ml Online
23288 Ingrid Bush Montserrat SUPA104 Super Soft - 1 Litre Direct
23347 Nell Maddox Azerbaijan DETA800 Detafast Stain Remover - 800ml Online
23361 Benedict Byrd Mauritania SUPA103 Super Soft - 500ml Online
23275 Ethan Gregory Tuvalu DETA800 Detafast Stain Remover - 800ml Retail
23297 Ursula Mcconnell Hungary DETA800 Detafast Stain Remover - 800ml Online
23327 Fletcher Jimenez Chad DETA200 Detafast Stain Remover - 200ml Retail
23325 Isadora Mcclure Indonesia DETA100 Detafast Stain Remover - 100ml Retail
23292 Liberty Mcbride Fiji SUPA105 Super Soft Bulk - 2 Litres Online
23335 Noble Gilbert United States DETA800 Detafast Stain Remover - 800ml Online
23314 Maxine Gentry Panama SUPA104 Super Soft - 1 Litre Retail
23329 Melinda Cobb Uruguay PURA250 Pure Soft Detergent - 250ml Retail
23332 Yael Carter Malaysia PURA250 Pure Soft Detergent - 250ml Direct
23317 Kay Buckley Malta PURA250 Pure Soft Detergent - 250ml Direct
23271 Athena Fitzpatrick Reunion SUPA103 Super Soft - 500ml Retail
Date Sold Month Sold Quantity Unit Price Revenue
41150 8 208 14.5 3016
41145 8 197 14.5 2856.5
41138 8 176 14.5 2552
41070 6 168 14.5 2436
41123 8 166 14.5 2407
41078 6 157 14.5 2276.5
41084 6 142 14.5 2059
41134 8 193 9.99 1928.07
41139 8 205 9 1845
41139 8 199 9 1791
41147 8 188 9 1692
41085 6 188 9 1692
40915 1 167 9.99 1668.33
41146 8 163 9.99 1628.37
41132 8 170 9 1530
40923 1 102 14.5 1479
41082 6 160 9 1440
41136 8 204 6.99 1425.96
41074 6 141 9.99 1408.59
41088 6 147 9 1323
40915 1 184 6.99 1286.16
40912 1 141 9 1269
41133 8 135 9 1215
40939 1 176 6.5 1144
41082 6 184 6 1104
40911 1 73 14.5 1058.5
41134 8 116 9 1044
41131 8 95 9.99 949.05
40931 1 203 4.5 913.5
40950 2 203 4.5 913.5
40956 2 196 4.5 882
40966 2 125 6.99 873.75
23287 Joy Vazquez Korea PURA250 Pure Soft Detergent - 250ml Retail
23349 Amery Frazier Georgia DETA200 Detafast Stain Remover - 200ml Retail
23309 Buckminster Hopkins Sierra Leone PURA200 Pure Soft Detergent - 200ml Online
23338 George Best Western Sahara PURA250 Pure Soft Detergent - 250ml Retail
23301 Maxwell Parker Falkland Islands (Malvinas) SUPA103 Super Soft - 500ml Retail
23320 Lance Little Croatia DETA100 Detafast Stain Remover - 100ml Direct
23365 Gwendolyn Walton Cuba SUPA102 Super Soft - 250ml Retail
23302 Isaac Wolf Panama PURA500 Pure Soft Detergent - 500ml Online
23266 Celeste Pugh Gabon PURA200 Pure Soft Detergent - 200ml Online
23307 Oprah Ellis Dominican Republic DETA100 Detafast Stain Remover - 100ml Retail
23368 Emerson Beard Niue SUPA102 Super Soft - 250ml Retail
23286 Renee Padilla Yemen DETA800 Detafast Stain Remover - 800ml Online
23373 Maite Henson Bangladesh DETA200 Detafast Stain Remover - 200ml Online
23380 Ivory Chang Tonga PURA500 Pure Soft Detergent - 500ml Retail
23284 Clark Weaver Palau PURA250 Pure Soft Detergent - 250ml Retail
23306 Ima Cummings Philippines DETA200 Detafast Stain Remover - 200ml Online
23281 Adria Kaufman Bouvet Island SUPA102 Super Soft - 250ml Retail
23351 Nyssa Quinn Cocos (Keeling) Islands PURA200 Pure Soft Detergent - 200ml Online
23282 Amir Alexander Liberia DETA100 Detafast Stain Remover - 100ml Retail
23376 Imogene Bradshaw Niger SUPA103 Super Soft - 500ml Direct
23354 Gwendolyn Mccarty Madagascar SUPA103 Super Soft - 500ml Online
23337 Bell Prince Guinea SUPA103 Super Soft - 500ml Retail
23326 Katelyn Joseph Slovenia SUPA102 Super Soft - 250ml Retail
23316 Robert Juarez Svalbard and Jan Mayen PURA200 Pure Soft Detergent - 200ml Retail
23362 Jerry Alvarado Korea, Republic of PURA100 Pure Soft Detergent - 100ml Online
23296 India Gilbert Denmark SUPA105 Super Soft Bulk - 2 Litres Retail
23352 Iliana Porter Poland DETA100 Detafast Stain Remover - 100ml Online
23304 Deanna Santana Solomon Islands PURA200 Pure Soft Detergent - 200ml Retail
23369 Ivor Mclaughlin United States Minor Outlying Islands PURA500 Pure Soft Detergent - 500ml Retail
23268 Latifah Wall Guadeloupe DETA100 Detafast Stain Remover - 100ml Online
23315 Anika Tillman Burkina Faso PURA250 Pure Soft Detergent - 250ml Retail
23342 Paul Duke Puerto Rico PURA200 Pure Soft Detergent - 200ml Online
41077 6 189 4.5 850.5
41112 7 126 6.5 819
41083 6 201 3.99 801.99
41133 8 178 4.5 801
41109 7 108 6.99 754.92
41075 6 125 6 750
41099 7 165 4.5 742.5
41117 7 105 6.5 682.5
41132 8 170 3.99 678.3
41094 7 113 6 678
41146 8 150 4.5 675
41129 8 69 9 621
41114 7 95 6.5 617.5
41112 7 95 6.5 617.5
41077 6 135 4.5 607.5
41068 6 93 6.5 604.5
41103 7 134 4.5 603
41124 8 151 3.99 602.49
41142 8 100 6 600
41113 7 85 6.99 594.15
41124 8 84 6.99 587.16
41097 7 82 6.99 573.18
41142 8 126 4.5 567
41061 6 137 3.99 546.63
41139 8 179 3 537
41068 6 37 14.5 536.5
41097 7 89 6 534
41061 6 131 3.99 522.69
41092 7 77 6.5 500.5
41102 7 82 6 492
41102 7 109 4.5 490.5
41088 6 122 3.99 486.78
23333 Sawyer Stokes Malta PURA250 Pure Soft Detergent - 250ml Online
23263 Xerxes Smith Panama DETA200 Detafast Stain Remover - 200ml Online
23270 Wanda Garza Kyrgyzstan SUPA103 Super Soft - 500ml Retail
23272 Anjolie Hicks Turks and Caicos Islands DETA200 Detafast Stain Remover - 200ml Direct
23274 Asher Weber Macedonia PURA100 Pure Soft Detergent - 100ml Retail
23364 Mercedes Humphrey Turkey DETA800 Detafast Stain Remover - 800ml Online
23276 Hayes Rollins Nepal PURA500 Pure Soft Detergent - 500ml Online
23343 Josiah Yates Bangladesh SUPA104 Super Soft - 1 Litre Online
23344 Winifred Cantu Kazakhstan DETA200 Detafast Stain Remover - 200ml Online
23299 Germaine Kidd Niger PURA200 Pure Soft Detergent - 200ml Retail
23310 Kenyon Joyce Canada SUPA104 Super Soft - 1 Litre Online
23358 Joel Rivers Australia SUPA104 Super Soft - 1 Litre Retail
23323 Colby Knapp Pakistan PURA100 Pure Soft Detergent - 100ml Online
23267 Vance Campos Syrian Arab Republic PURA100 Pure Soft Detergent - 100ml Online
23340 Lael Gould El Salvador SUPA102 Super Soft - 250ml Online
23269 Jane Hernandez Macedonia PURA100 Pure Soft Detergent - 100ml Online
23308 Dara Cunningham Saint Helena PURA100 Pure Soft Detergent - 100ml Retail
23356 Colette Sargent Norfolk Island PURA200 Pure Soft Detergent - 200ml Online
23318 Shea Cortez India DETA200 Detafast Stain Remover - 200ml Online
23357 Cyrus Whitley Denmark DETA100 Detafast Stain Remover - 100ml Retail
23377 Eleanor Hopper Madagascar PURA500 Pure Soft Detergent - 500ml Online
23311 Forrest Macdonald New Caledonia SUPA105 Super Soft Bulk - 2 Litres Retail
23379 Desirae Perkins Chile PURA200 Pure Soft Detergent - 200ml Online
23360 Barrett Mckinney Syrian Arab Republic SUPA103 Super Soft - 500ml Online
23339 Basil Vang Moldova DETA100 Detafast Stain Remover - 100ml Online
23341 Noel Key Gambia PURA100 Pure Soft Detergent - 100ml Retail
23374 Ebony Mercer Cape Verde PURA200 Pure Soft Detergent - 200ml Online
23273 Isaac Cooper Netherlands Antilles SUPA104 Super Soft - 1 Litre Online
23280 James Spencer Burkina Faso SUPA103 Super Soft - 500ml Online
23370 Clark Orr Indonesia PURA100 Pure Soft Detergent - 100ml Retail
23372 Phillip Perkins Nigeria PURA500 Pure Soft Detergent - 500ml Online
23265 Uriel Benton South Africa SUPA104 Super Soft - 1 Litre Retail
41126 8 106 4.5 477
41096 7 73 6.5 474.5
41067 6 67 6.99 468.33
41121 7 71 6.5 461.5
41143 8 153 3 459
41093 7 47 9 423
41122 8 65 6.5 422.5
41144 8 42 9.99 419.58
41265 12 64 6.5 416
41087 6 104 3.99 414.96
41077 6 41 9.99 409.59
41071 6 41 9.99 409.59
41272 12 135 3 405
41101 7 129 3 387
41095 7 85 4.5 382.5
41063 6 116 3 348
41099 7 112 3 336
41081 6 80 3.99 319.2
41099 7 48 6.5 312
41107 7 50 6 300
41075 6 43 6.5 279.5
41072 6 18 14.5 261
41270 12 65 3.99 259.35
41073 6 37 6.99 258.63
41101 7 41 6 246
41026 4 77 3 231
41257 12 57 3.99 227.43
41256 12 22 9.99 219.78
41002 4 30 6.99 209.7
41028 4 63 3 189
41255 12 22 6.5 143
41248 12 14 9.99 139.86
23346 Aretha Patton Bouvet Island SUPA104 Super Soft - 1 Litre Online
23312 Thomas Barnes Mayotte PURA200 Pure Soft Detergent - 200ml Online
23355 Victoria Solis Palau PURA250 Pure Soft Detergent - 250ml Online
23322 Arsenio Knowles Malaysia PURA100 Pure Soft Detergent - 100ml Retail
23298 Ryder Conner Virgin Islands, British PURA250 Pure Soft Detergent - 250ml Direct
23367 Roary Dixon Saudi Arabia PURA250 Pure Soft Detergent - 250ml Retail
23334 Silas Battle Niue PURA100 Pure Soft Detergent - 100ml Online
23285 Leonard Cardenas Madagascar SUPA102 Super Soft - 250ml Retail
23375 Brittany Burris Palau SUPA103 Super Soft - 500ml Retail
23336 Petra Mckenzie Morocco SUPA102 Super Soft - 250ml Retail
23279 Angela Wise Moldova PURA100 Pure Soft Detergent - 100ml Online
41119 7 13 9.99 129.87
41096 7 28 3.99 111.72
41026 4 16 4.5 72
41009 4 20 3 60
41118 7 12 4.5 54
41023 4 10 4.5 45
41260 12 14 3 42
41114 7 9 4.5 40.5
41029 4 5 6.99 34.95
41091 7 7 4.5 31.5
41020 4 10 3 30
Problem 5A- Instructor Lead

Identify if each of the product sales listed below has met or exceeded the target revenue.

Target Revenue $450.00 Retain or Probation

Region Product QTY Unit Price Revenue Analysis

North America Super Soft - 1 Litre 100 $ 4.50 $ 450.00
South Asia Detafast Stain Remover - 200ml 75 $ 13.00 $ 975.00
Pacific Islands Super Soft Bulk - 2 Litres 13 $ 25.00 $ 325.00
Africa Detafast Stain Remover - 800ml 200 $ 0.75 $ 150.00
Eastern Europe Pure Soft Detergent - 250ml 11 $ 55.00 $ 605.00

Problem 5B- Student Lead

Please return the Quantity Sold for each region that meets or exceeds the target revenue from above.

Region Product QTY Unit Price Revenue Analysis

North America Super Soft - 1 Litre 100 $ 4.50 $ 450.00
South Asia Detafast Stain Remover - 200ml 75 $ 13.00 $ 975.00
Pacific Islands Super Soft Bulk - 2 Litres 13 $ 25.00 $ 325.00
Africa Detafast Stain Remover - 800ml 200 $ 0.75 $ 150.00
Eastern Europe Pure Soft Detergent - 250ml 11 $ 55.00 $ 605.00
Find the number of sales of each of the following products

Product # of salesUsing COUNTIFS

Pure Soft Detergent - 100ml
Pure Soft Detergent - 200ml
Pure Soft Detergent - 250ml
Pure Soft Detergent - 500ml
Detafast Stain Remover - 100ml
Detafast Stain Remover - 200ml
Detafast Stain Remover - 800ml
Super Soft - 250ml
Super Soft - 500ml
Super Soft - 1 Litre
Super Soft Bulk - 2 Litres

Count the number of sales for which revenue meets the following criteria:

Greater than $1000

Count (Using COUNTIFS)

Problem 1B- Student Lead

Count the number of sales occurring in each of the sales channels.

Sales Channel # of sales(Using COUNTIFS)

Online
Direct
Retail
Less than $1000 Equals $1000 Does not Equal $1000
Count the number of sales with quantities less than 100, greater than 100, equal to 100, or not equal to 100.

Greater than 100

Count (Using COUNTIFS)
Less than 100 Equals 100 Does not Equal 100
Problem 2A - Instructor Lead

How many sales of Super Soft Bulk - 2 Litres were made through the following sales channels?

Product Super Soft Bulk - 2 Litres

Sales Channel # of Sales

Online
Direct
Retail

Problem 2B- Student Lead

How many sales of more than 100 units of Super Soft Bulk - 2 Litres were made through the following sales channels?

Sales Channel # of Sales (Quantity > 100)

Online
Direct
Retail

How many distributors sold the following products with respective quantity in the table below?

Product Quantity Sold

Pure Soft Detergent - 100ml >=50
Pure Soft Detergent - 200ml >=100
Pure Soft Detergent - 250ml >=150
# of Sales
Detafast Stain Remover - 100ml >=50
Detafast Stain Remover - 200ml >=100
Detafast Stain Remover - 800ml >=150
Super Soft - 250ml >=50
Super Soft - 500ml >=100
Super Soft - 1 Litre >=150
Super Soft Bulk - 2 Litres >=50
Problem 3A- Instructor Lead

Find out the total revenue for each of the products mentioned in the table below

Product
Pure Soft Detergent - 100ml
Pure Soft Detergent - 200ml
Pure Soft Detergent - 250ml
Pure Soft Detergent - 500ml
Detafast Stain Remover - 100ml
Detafast Stain Remover - 200ml
Detafast Stain Remover - 800ml
Super Soft - 250ml
Super Soft - 500ml
Super Soft - 1 Litre
Super Soft Bulk - 2 Litres

Find out the total revenue where the quantity of product sold is less than 100

Total Revenue (Quantity < 100) [Using SUMIFS]

Problem 3B- Student Lead

Find out the total revenue in the month of August (8)

Total Revenue in August (Using SUMIFS)

Total Revenue(Using SUMIFS)
Find the total revenue of products for which the total revenue is greater than $2000, less than $2000, equals $2000 and does not equal $2000

Sum (Using SUMIFS)

Greater than $2000 Less than $2000 Equals $2000 Does not Equal $2000
Problem 4A - Instructor Lead

Calculate the total revenue for the following sales channel where the number of units sold is greater than 100.

Sales Channel Total Revenue (Units sold > 100)

Online
Direct
Retail

Calculate the total revenue by sales channel for all products.

Product Online
Pure Soft Detergent - 100ml
Pure Soft Detergent - 200ml
Pure Soft Detergent - 250ml
Pure Soft Detergent - 500ml
Detafast Stain Remover - 100ml
Detafast Stain Remover - 200ml
Detafast Stain Remover - 800ml
Super Soft - 250ml
Super Soft - 500ml
Super Soft - 1 Litre
Super Soft Bulk - 2 Litres

Problem 4B - Student Lead

Calculate the total revenue for the following product and country pairs.

Product Country
Direct Retail

Total Revenue
Pure Soft Detergent - 100ml Indonesia
Pure Soft Detergent - 200ml Cape Verde
Pure Soft Detergent - 250ml Malaysia
Detafast Stain Remover - 100ml Denmark
Detafast Stain Remover - 200ml India
Detafast Stain Remover - 800ml Brazil
Super Soft - 250ml Cuba
Super Soft - 500ml India
Super Soft - 1 Litre Iceland
Super Soft Bulk - 2 Litres Canada
Problem 6A - Instructor Lead

Identify if the product sales below have met both the target price and target revenue goals.

Target Unit Price 4

Target Revenue 605

Region Product QTY

North America Super Soft - 1 Litre 100
South Asia Detafast Stain Remover - 200ml 75
Pacific Islands Super Soft Bulk - 2 Litres 13
Africa Detafast Stain Remover - 800ml 200
Eastern Europe Pure Soft Detergent - 250ml 11

Problem 6B- Student Lead

Identify the Region Name for those products that have exceeded the Target Unit Price and Target Quantity.

Target Unit Price 10

Target Quantity 50

Region Product QTY

North America Super Soft - 1 Litre 100
South Asia Detafast Stain Remover - 200ml 75
Pacific Islands Super Soft Bulk - 2 Litres 13
Africa Detafast Stain Remover - 800ml 200
Eastern Europe Pure Soft Detergent - 250ml 11
Unit Price Revenue Analysis
4.5 450
13 975
25 325
0.75 150
55 605

Unit Price Revenue Analysis

4.5 450
13 975
25 325
0.75 150
55 605
Problem 7A - Instructor Lead

Identify the Distributors that do not sell products through the online channel.

Sales Channel Distributor Industry Revenue Country

Retail The Distribution Group Consumer Stapes 1000 Indonesia
Online Distributors R Us Commodities 2500 Canada
Online Discount Distribution Leisure 750 France
Direct Freight Away Industrial Materials 1152 China
Retail Infinity Trucking Consumer Stapes 345 Australia

Problem 7B - Student Lead

Return the Distributor Name if they sell online or sell over $1,000 of product.

Sales Channel Distributor Industry Revenue Country

Retail The Distribution Group Consumer Stapes 1000 Indonesia
Online Distributors R Us Commodities 2500 Canada
Online Discount Distribution Leisure 750 France
Direct Freight Away Industrial Materials 1152 China
Retail Infinity Trucking Consumer Stapes 345 Australia
Analysis

Analysis
Distributor ID
Distributor Name
Country Product Code Product Sales Channel Date Sold Month Sold Quantity Unit Price Revenue
23345 Devin AbbottFrance SUPA105 Super Soft Bulk Online
- 2 Litres 41150 8 208 14.5 3016
23278 Aphrodite Brennan
Malawi SUPA105 Super Soft Bulk Direct
- 2 Litres 41145 8 197 14.5 2856.5
23303 Guinevere KeyColombia SUPA105 Super Soft Bulk Retail
- 2 Litres 41138 8 176 14.5 2552
23353 Zahir Fields Canada SUPA105 Super Soft Bulk Direct
- 2 Litres 41070 6 168 14.5 2436
23289 Deacon CraigMongolia SUPA105 Super Soft Bulk Retail
- 2 Litres 41123 8 166 14.5 2407
23378 Brynne Mcgowan
Finland SUPA105 Super Soft Bulk Online
- 2 Litres 41078 6 157 14.5 2276.5
23283 Lani Sweet Vanuatu SUPA105 Super Soft Bulk Online
- 2 Litres 41084 6 142 14.5 2059
23324 Noble Warner Burkina FasoSUPA104 Super Soft - 1RetailLitre 41134 8 193 9.99 1928.07
23264 Levi DouglasTanzania, United
DETA800
RepublicDetafast
of Stain
Online
Remover - 800ml 41139 8 205 9 1845
23291 Jelani Odonnell
Albania DETA800 Detafast Stain Retail
Remover - 800ml 41139 8 199 9 1791
23305 Jared Sandoval
Botswana DETA800 Detafast Stain Online
Remover - 800ml 41147 8 188 9 1692
23350 Hiroko Acevedo
Burundi DETA800 Detafast Stain Online
Remover - 800ml 41085 6 188 9 1692
23300 Rhona ClarkeZimbabwe SUPA104 Super Soft - 1Online Litre 40915 1 167 9.99 1668.33
23348 Tad Mack Iceland SUPA104 Super Soft - 1RetailLitre 41146 8 163 9.99 1628.37
23290 Rama Goodwin Tunisia DETA800 Detafast Stain Online
Remover - 800ml 41132 8 170 9 1530
23328 Keaton Wolfe French Southern
SUPA105
Territories
Super Soft Bulk
Retail
- 2 Litres 40923 1 102 14.5 1479
23294 Samuel AyalaBrazil DETA800 Detafast Stain Retail
Remover - 800ml 41082 6 160 9 1440
23371 Doris Williams
Trinidad andSUPA103
Tobago Super Soft - 500ml
Online 41136 8 204 6.99 1425.96
23288 Ingrid Bush Montserrat SUPA104 Super Soft - 1Direct Litre 41074 6 141 9.99 1408.59
23347 Nell MaddoxAzerbaijan DETA800 Detafast Stain Online
Remover - 800ml 41088 6 147 9 1323
23361 Benedict ByrdMauritania SUPA103 Super Soft - 500ml Online 40915 1 184 6.99 1286.16
23275 Ethan GregoryTuvalu DETA800 Detafast Stain Retail
Remover - 800ml 40912 1 141 9 1269
23297 Ursula Mcconnell
Hungary DETA800 Detafast Stain Online
Remover - 800ml 41133 8 135 9 1215
23327 Fletcher Jimenez
Chad DETA200 Detafast Stain Retail
Remover - 200ml 40939 1 176 6.5 1144
23325 Isadora Mcclure
Indonesia DETA100 Detafast Stain Retail
Remover - 100ml 41082 6 184 6 1104
23292 Liberty Mcbride
Fiji SUPA105 Super Soft Bulk Online
- 2 Litres 40911 1 73 14.5 1058.5
23335 Noble GilbertUnited StatesDETA800 Detafast Stain Online
Remover - 800ml 41134 8 116 9 1044
23314 Maxine GentryPanama SUPA104 Super Soft - 1RetailLitre 41131 8 95 9.99 949.05
23329 Melinda Cobb Uruguay PURA250 Pure Soft Detergent
Retail - 250ml 40931 1 203 4.5 913.5
23332 Yael Carter Malaysia PURA250 Pure Soft Detergent Direct - 250ml 40950 2 203 4.5 913.5
23317 Kay Buckley Malta PURA250 Pure Soft Detergent
Direct - 250ml 40956 2 196 4.5 882
23271 Athena Fitzpatrick
Reunion SUPA103 Super Soft - 500mlRetail 40966 2 125 6.99 873.75
23287 Joy Vazquez Korea PURA250 Pure Soft Detergent
Retail - 250ml 41077 6 189 4.5 850.5
23349 Amery FrazierGeorgia DETA200 Detafast Stain Retail
Remover - 200ml 41112 7 126 6.5 819
23309 BuckminsterSierra
Hopkins
LeonePURA200 Pure Soft Detergent Online - 200ml 41083 6 201 3.99 801.99
23338 George BestWestern Sahara PURA250 Pure Soft DetergentRetail - 250ml 41133 8 178 4.5 801
23301 Maxwell Parker
Falkland Islands
SUPA103
(Malvinas)
Super Soft - 500ml
Retail 41109 7 108 6.99 754.92
23320 Lance Little Croatia DETA100 Detafast Stain Direct
Remover - 100ml
41075 6 125 6 750
23365 Gwendolyn Walton
Cuba SUPA102 Super Soft - 250ml Retail 41099 7 165 4.5 742.5
23302 Isaac Wolf Panama PURA500 Pure Soft DetergentOnline - 500ml 41117 7 105 6.5 682.5
23266 Celeste PughGabon PURA200 Pure Soft DetergentOnline - 200ml 41132 8 170 3.99 678.3
23307 Oprah Ellis Dominican Republic
DETA100 Detafast Stain Retail
Remover - 100ml
41094 7 113 6 678
23368 Emerson BeardNiue SUPA102 Super Soft - 250ml Retail 41146 8 150 4.5 675
23286 Renee PadillaYemen DETA800 Detafast Stain Online
Remover - 800ml
41129 8 69 9 621
23373 Maite Henson Bangladesh DETA200 Detafast Stain Online
Remover - 200ml
41114 7 95 6.5 617.5
23380 Ivory Chang Tonga PURA500 Pure Soft DetergentRetail - 500ml 41112 7 95 6.5 617.5
23284 Clark WeaverPalau PURA250 Pure Soft DetergentRetail - 250ml 41077 6 135 4.5 607.5
23306 Ima Cummings Philippines DETA200 Detafast Stain Online
Remover - 200ml
41068 6 93 6.5 604.5
23281 Adria KaufmanBouvet IslandSUPA102 Super Soft - 250ml Retail 41103 7 134 4.5 603
23351 Nyssa QuinnCocos (Keeling)PURA200
Islands Pure Soft Detergent
Online - 200ml 41124 8 151 3.99 602.49
23282 Amir Alexander
Liberia DETA100 Detafast Stain Retail
Remover - 100ml
41142 8 100 6 600
23376 Imogene Bradshaw
Niger SUPA103 Super Soft - 500ml Direct 41113 7 85 6.99 594.15
23354 Gwendolyn Mccarty
Madagascar SUPA103 Super Soft - 500ml Online 41124 8 84 6.99 587.16
23337 Bell Prince Guinea SUPA103 Super Soft - 500ml Retail 41097 7 82 6.99 573.18
23326 Katelyn Joseph
Slovenia SUPA102 Super Soft - 250ml Retail 41142 8 126 4.5 567
23316 Robert JuarezSvalbard andPURA200
Jan Mayen Pure Soft Detergent
Retail - 200ml 41061 6 137 3.99 546.63
23362 Jerry Alvarado
Korea, Republic
PURA100
of Pure Soft Detergent
Online - 100ml 41139 8 179 3 537
23296 India GilbertDenmark SUPA105 Super Soft Bulk Retail
- 2 Litres 41068 6 37 14.5 536.5
23352 Iliana PorterPoland DETA100 Detafast Stain Online
Remover - 100ml
41097 7 89 6 534
23304 Deanna Santana
Solomon Islands
PURA200 Pure Soft DetergentRetail - 200ml 41061 6 131 3.99 522.69
23369 Ivor Mclaughlin
United StatesPURA500
Minor Outlying
Pure Islands
Soft Detergent
Retail - 500ml 41092 7 77 6.5 500.5
23268 Latifah WallGuadeloupe DETA100 Detafast Stain Online
Remover - 100ml
41102 7 82 6 492
23315 Anika TillmanBurkina FasoPURA250 Pure Soft Detergent Retail - 250ml 41102 7 109 4.5 490.5
23342 Paul Duke Puerto Rico PURA200 Pure Soft Detergent Online - 200ml 41088 6 122 3.99 486.78
23333 Sawyer StokesMalta PURA250 Pure Soft DetergentOnline - 250ml 41126 8 106 4.5 477
23263 Xerxes SmithPanama DETA200 Detafast Stain Online
Remover - 200ml
41096 7 73 6.5 474.5
23270 Wanda GarzaKyrgyzstan SUPA103 Super Soft - 500ml Retail 41067 6 67 6.99 468.33
23272 Anjolie HicksTurks and Caicos
DETA200
Islands Detafast Stain
Direct
Remover - 200ml
41121 7 71 6.5 461.5
23274 Asher WeberMacedonia PURA100 Pure Soft Detergent Retail - 100ml 41143 8 153 3 459
23364 Mercedes Humphrey
Turkey DETA800 Detafast StainOnline
Remover - 800ml
41093 7 47 9 423
23276 Hayes RollinsNepal PURA500 Pure Soft Detergent
Online - 500ml 41122 8 65 6.5 422.5
23343 Josiah Yates Bangladesh SUPA104 Super Soft - 1Online
Litre 41144 8 42 9.99 419.58
23344 Winifred CantuKazakhstan DETA200 Detafast StainOnline
Remover - 200ml
41265 12 64 6.5 416
23299 Germaine Kidd Niger PURA200 Pure Soft Detergent
Retail - 200ml 41087 6 104 3.99 414.96
23310 Kenyon JoyceCanada SUPA104 Super Soft - 1Online
Litre 41077 6 41 9.99 409.59
23358 Joel Rivers Australia SUPA104 Super Soft - 1Retail
Litre 41071 6 41 9.99 409.59
23323 Colby KnappPakistan PURA100 Pure Soft Detergent
Online - 100ml 41272 12 135 3 405
23267 Vance Campos Syrian Arab Republic
PURA100 Pure Soft Detergent
Online - 100ml 41101 7 129 3 387
23340 Lael Gould El Salvador SUPA102 Super Soft - 250ml
Online 41095 7 85 4.5 382.5
23269 Jane HernandezMacedonia PURA100 Pure Soft Detergent
Online - 100ml 41063 6 116 3 348
23308 Dara Cunningham
Saint HelenaPURA100 Pure Soft Detergent
Retail - 100ml 41099 7 112 3 336
23356 Colette Sargent
Norfolk Island
PURA200 Pure Soft Detergent
Online - 200ml 41081 6 80 3.99 319.2
23318 Shea Cortez India DETA200 Detafast StainOnline
Remover - 200ml
41099 7 48 6.5 312
23357 Cyrus Whitley Denmark DETA100 Detafast StainRetail
Remover - 100ml
41107 7 50 6 300
23377 Eleanor HopperMadagascar PURA500 Pure Soft Detergent
Online - 500ml 41075 6 43 6.5 279.5
23311 Forrest Macdonald
New CaledoniaSUPA105 Super Soft Bulk
Retail
- 2 Litres 41072 6 18 14.5 261
23379 Desirae Perkins
Chile PURA200 Pure Soft Detergent
Online - 200ml 41270 12 65 3.99 259.35
23360 Barrett Mckinney
Syrian Arab Republic
SUPA103 Super Soft - 500ml
Online 41073 6 37 6.99 258.63
23339 Basil Vang Moldova DETA100 Detafast StainOnline
Remover - 100ml
41101 7 41 6 246
23341 Noel Key Gambia PURA100 Pure Soft Detergent
Retail - 100ml 41026 4 77 3 231
23374 Ebony Mercer Cape Verde PURA200 Pure Soft Detergent
Online - 200ml 41257 12 57 3.99 227.43
23273 Isaac CooperNetherlands SUPA104
Antilles Super Soft - 1Online
Litre 41256 12 22 9.99 219.78
23280 James Spencer Burkina FasoSUPA103 Super Soft - 500ml
Online 41002 4 30 6.99 209.7
23370 Clark Orr Indonesia PURA100 Pure Soft Detergent
Retail - 100ml 41028 4 63 3 189
23372 Phillip Perkins
Nigeria PURA500 Pure Soft Detergent
Online - 500ml 41255 12 22 6.5 143
23265 Uriel BentonSouth Africa SUPA104 Super Soft - 1Retail
Litre 41248 12 14 9.99 139.86
23346 Aretha Patton Bouvet IslandSUPA104 Super Soft - 1Online
Litre 41119 7 13 9.99 129.87
23312 Thomas Barnes Mayotte PURA200 Pure Soft Detergent
Online - 200ml 41096 7 28 3.99 111.72
23355 Victoria SolisPalau PURA250 Pure Soft Detergent
Online - 250ml 41026 4 16 4.5 72
23322 Arsenio Knowles
Malaysia PURA100 Pure Soft Detergent
Retail - 100ml 41009 4 20 3 60
23298 Ryder Conner Virgin Islands,
PURA250
British Pure Soft Detergent
Direct - 250ml 41118 7 12 4.5 54
23367 Roary DixonSaudi ArabiaPURA250 Pure Soft Detergent
Retail - 250ml 41023 4 10 4.5 45
23334 Silas Battle Niue PURA100 Pure Soft Detergent
Online - 100ml 41260 12 14 3 42
23285 Leonard Cardenas
Madagascar SUPA102 Super Soft - 250ml
Retail 41114 7 9 4.5 40.5
23375 Brittany Burris
Palau SUPA103 Super Soft - 500ml
Retail 41029 4 5 6.99 34.95
23336 Petra Mckenzie
Morocco SUPA102 Super Soft - 250ml
Retail 41091 7 7 4.5 31.5
23279 Angela WiseMoldova PURA100 Pure Soft Detergent
Online - 100ml 41020 4 10 3 30
Problem 1a - Instructor Lead

You were given the small subset of data from your client:

Distributor ID Distributor Name Country

23265
23315
23326
23367

Your team has been given a list of transactions that require additional follow up.
Identify the product code and product below using vlookup:

Distributor ID Product Code Product

23265
23315
23367

Problem 1b - Instructor Lead

Using the Sales Data Set tab, lets pull the Quantity, unit price and revenue for each Distributor ID

Distributor ID Quantity Unit Price Revenue

23265
23378
23288
23347

Problem 1c - Student Exersise

Identify the Sales Channel, Quantity and Revenue amounts associated with the Distributor IDs below.

Distributor ID Sales Channel Quantity Revenue

 23353
23289
23378
23283
23324
23303

Problem 3a - Instructor Lead

Nest V-lookup with Iferror and pull the Country, Name and Revenue associated with the Distributor ID.

Distributor ID Country Name Revenue

23265
23315
30000
40000
23367
23326
35000

Your team has been given a list of transactions that require additional follow up.
Identify the associated distributors below using vlookup:

Distributor ID Name Revenue

23265
40000
23367

Total Revenue

Problem 3b - Student Lead

Use the Sales Data Set to pull the Unit Price for each Distributor ID.
 Distributor ID Unit Price
23265
23315
30000
23377
23311
23371.5

Average Unit Price