You manage an online retail company that s

THE products to countries in Europe, and you are

SITUATION workbook to track key business metrics

TASKS:
On the Orders worksheet, use a formula to fill the Product Name column using the
Products worksheet
In cell L2 on the Orders worksheet, use a formula to calculate the number of orders
selected in cell K2 with a Revenue greater than or equal to $100
On the Countries worksheet, insert a Filled Map using the information in cells A1:B1
In cell B5 of the Warehouse Extension worksheet, use a formula that calculates the
amount, assuming the payment is due at the beginning of each month
On the Summary Chart worksheet, add a Line Pivot Chart and change the Pivot Cha
Style 5 and Layout 4
 l company that ships specialty
rope, and you are preparing a
ness metrics

column using the information in the

number of orders from the Country

ation in cells A1:B15

that calculates the monthly payment
onth
ange the Pivot Chart formatting to
Order # Order Date Country Client ID Product ID Product Name
540546 2019-01-09 Portugal 12766 22740
541115 2019-01-13 Italy 12578 21260
541115 2019-01-13 Italy 12578 22190
541224 2019-01-14 Germany 12474 21025
541269 2019-01-17 Germany 12626 22174
541509 2019-01-18 United Kingdom 13263 22694
541631 2019-01-20 France 12637 22174
541711 2019-01-21 Netherlands 14646 22653
542080 2019-01-25 Germany 13815 20713
542080 2019-01-25 Germany 13815 22740
542080 2019-01-25 Germany 13815 22741
542371 2019-01-27 Germany 12468 62018
542428 2019-01-28 Sweden 17404 21260
542535 2019-01-28 France 12735 21888
542612 2019-01-30 United Kingdom 17841 20713
542648 2019-01-31 Germany 12476 21116
542887 2019-02-01 Austria 12373 20713
543731 2019-02-11 United Kingdom 17677 20713
543733 2019-02-11 Belgium 12395 22740
543737 2019-02-11 Germany 12477 21116
543831 2019-02-14 United Kingdom 15769 20713
544355 2019-02-18 France 12714 22741
544355 2019-02-18 France 12714 22740
544399 2019-02-18 Italy 12594 20713
544480 2019-02-21 Netherlands 14646 20713
544480 2019-02-21 Netherlands 14646 22653
544657 2019-02-22 United Kingdom 14895 22174
544672 2019-02-22 Netherlands 14646 22653
544672 2019-02-22 Netherlands 14646 20713
544811 2019-02-23 Germany 12471 22741
545664 2019-03-04 Italy 12584 21260
545937 2019-03-08 Portugal 12758 22740
545988 2019-03-08 Germany 12662 20713
546780 2019-03-17 Denmark 12435 20713
546843 2019-03-17 Germany 12472 20713
546843 2019-03-17 Germany 12472 22740
546920 2019-03-18 Germany 12471 22741
547194 2019-03-21 France 12637 20713
547517 2019-03-23 Belgium 12395 22740
547685 2019-03-24 Belgium 12408 20713
547897 2019-03-28 Portugal 12792 21888
548711 2019-04-03 United Kingdom 18116 22694
548745 2019-04-04 Germany 12471 22741
550188 2019-04-14 Switzerland 12457 21888
550665 2019-04-20 Germany 12530 22740
550827 2019-04-21 France 12670 22740
552337 2019-05-09 Germany 12621 21116
552978 2019-05-12 Germany 12590 22740
553037 2019-05-13 Germany 12471 22741
553377 2019-05-16 United Kingdom 14888 21888
553540 2019-05-17 United Kingdom 17511 21888
553832 2019-05-19 United Kingdom 12957 21025
554356 2019-05-24 France 12670 22740
555095 2019-05-31 Spain 12540 21116
555162 2019-06-01 Germany 12473 22740
555162 2019-06-01 Germany 12473 22741
555284 2019-06-02 United Kingdom 14298 62018
555572 2019-06-05 Belgium 12449 21888
555637 2019-06-06 France 12535 21116
556258 2019-06-09 France 12694 21888
557007 2019-06-16 Spain 12484 22197
557466 2019-06-20 Germany 13815 22740
557509 2019-06-20 United Kingdom 15389 62018
557525 2019-06-21 Netherlands 12759 21260
557789 2019-06-22 Belgium 12379 22740
557885 2019-06-23 Belgium 12465 22740
558262 2019-06-28 Netherlands 14646 20713
558628 2019-06-30 Germany 12626 20713
559036 2019-07-05 France 12637 22174
559366 2019-07-08 United Kingdom 13102 22197
559418 2019-07-08 France 12681 22197
559550 2019-07-10 United Kingdom 17757 22197
559665 2019-07-11 Spain 12556 22197
559862 2019-07-13 Portugal 12782 22740
559907 2019-07-13 Portugal 12766 22740
560211 2019-07-15 Germany 12621 20713
560590 2019-07-19 Germany 12560 22740
560694 2019-07-20 Portugal 12757 20713
560901 2019-07-21 Germany 12476 21116
561066 2019-07-24 United Kingdom 16710 22197
561093 2019-07-25 Spain 12540 21116
561093 2019-07-25 Spain 12540 22740
561902 2019-07-31 Sweden 17404 21260
562605 2019-08-08 Germany 12530 20713
562789 2019-08-09 Netherlands 14646 20713
563749 2019-08-19 Austria 12360 20713
563756 2019-08-19 Switzerland 12418 21888
563808 2019-08-19 Germany 12626 20713
563950 2019-08-22 Germany 12471 22741
564140 2019-08-23 Germany 12621 21116
564328 2019-08-24 Germany 12662 20713
564360 2019-08-24 Germany 12471 22741
564438 2019-08-25 United Kingdom 16628 20713
564479 2019-08-25 France 12682 22197
564539 2019-08-25 Sweden 12715 20713
564734 2019-08-28 Spain 12484 22197
564965 2019-08-31 United Kingdom 17677 22197
565333 2019-09-02 Finland 12375 20713
565386 2019-09-02 United Kingdom 17997 22174
565416 2019-09-04 Germany 12710 22197
565430 2019-09-04 Germany 14335 22174
565519 2019-09-05 Spain 12502 22741
565765 2019-09-06 Germany 12526 20713
565854 2019-09-07 France 12490 22197
565865 2019-09-07 France 12637 22174
565930 2019-09-08 France 12685 22174
565967 2019-09-08 Netherlands 14646 20713
565967 2019-09-08 Netherlands 14646 22653
566076 2019-09-09 Belgium 12449 21888
566163 2019-09-09 France 12637 22174
566195 2019-09-09 Norway 12433 22197
566567 2019-09-13 United Kingdom 16161 20713
566721 2019-09-14 United Kingdom 12921 22653
567185 2019-09-18 United Kingdom 16370 20713
567280 2019-09-19 Netherlands 14646 20713
567526 2019-09-21 Denmark 12435 20713
567552 2019-09-21 France 12583 22197
567795 2019-09-22 Netherlands 14646 20713
567915 2019-09-22 France 12579 62018
567924 2019-09-22 Germany 12471 22741
567928 2019-09-22 Belgium 12380 20713
567938 2019-09-22 Finland 12704 22694
568040 2019-09-23 France 12681 22197
568179 2019-09-25 Spain 12545 22197
568650 2019-09-28 Switzerland 13505 22653
568650 2019-09-28 Switzerland 13505 22174
568953 2019-09-29 France 12728 22197
568953 2019-09-29 France 12728 22741
569332 2019-10-03 France 12637 20713
569486 2019-10-04 United Kingdom 15339 22694
569562 2019-10-05 Germany 12720 22197
569640 2019-10-05 Germany 12471 22741
569653 2019-10-05 Switzerland 12451 20713
569844 2019-10-06 Germany 12626 20713
569848 2019-10-06 United Kingdom 16316 22197
569860 2019-10-06 Germany 13812 22197
569866 2019-10-06 Portugal 12757 20713
569866 2019-10-06 Portugal 12757 22741
570249 2019-10-10 United Kingdom 17509 21888
570653 2019-10-11 United Kingdom 14710 22197
570672 2019-10-11 France 12536 21888
570833 2019-10-12 United Kingdom 14834 22197
570851 2019-10-12 France 12583 22197
570964 2019-10-13 Portugal 12766 21888
571227 2019-10-14 Germany 12477 21116
571280 2019-10-16 United Kingdom 18122 22197
571328 2019-10-17 Germany 12473 22741
571670 2019-10-18 Italy 12611 22197
571904 2019-10-19 Germany 12522 21116
572058 2019-10-20 United Kingdom 18252 23417
572065 2019-10-20 Spain 12556 22197
572327 2019-10-24 United Kingdom 15277 21888
572887 2019-10-26 Belgium 12362 22174
573333 2019-10-30 Sweden 12483 21888
573343 2019-10-30 United Kingdom 13566 21888
573656 2019-10-31 Belgium 12417 22741
573814 2019-11-01 United Kingdom 13268 23417
573889 2019-11-01 United Kingdom 13571 22197
574093 2019-11-03 France 12437 22197
574329 2019-11-04 Belgium 12362 22197
574501 2019-11-04 France 12577 22174
574506 2019-11-04 France 12577 22197
574506 2019-11-04 France 12577 22197
574550 2019-11-04 Spain 12484 22197
574575 2019-11-04 United Kingdom 14971 22694
574709 2019-11-06 United Kingdom 17768 22197
574714 2019-11-06 United Kingdom 15427 22197
574740 2019-11-06 Switzerland 12357 22190
574740 2019-11-06 Switzerland 12357 21116
575067 2019-11-08 France 12583 22197
575331 2019-11-09 United Kingdom 13209 22197
575507 2019-11-10 United Kingdom 17197 21888
575514 2019-11-10 Spain 12541 22197
575880 2019-11-11 France 12726 21888
575886 2019-11-11 Germany 12517 20713
576215 2019-11-14 United Kingdom 17652 22197
576255 2019-11-14 United Kingdom 15993 22197
576629 2019-11-16 Germany 12621 62018
577033 2019-11-17 United Kingdom 17797 22694
577046 2019-11-17 Belgium 12449 22174
577121 2019-11-17 France 12681 22197
577152 2019-11-18 France 14277 22741
577314 2019-11-18 Norway 12444 22694
577316 2019-11-18 Italy 12578 21888
577476 2019-11-20 Spain 12540 21116
577476 2019-11-20 Spain 12540 22741
577938 2019-11-22 United Kingdom 15525 22197
578108 2019-11-23 Italy 14912 22197
578147 2019-11-23 United Kingdom 12748 22197
578781 2019-11-25 United Kingdom 15872 62018
578949 2019-11-27 United Kingdom 14954 21260
579135 2019-11-28 United Kingdom 18096 22197
579503 2019-11-29 Spain 17097 20713
579692 2019-11-30 Norway 12433 22197
580265 2019-12-02 Finland 12587 20713
580998 2019-12-06 United Kingdom 16987 22694
581246 2019-12-08 United Kingdom 15453 22694
581253 2019-12-08 United Kingdom 16891 22694
581412 2019-12-08 United Kingdom 14415 20713
581476 2019-12-09 Norway 12433 22197
Quantity Unit Price Revenue Country Orders
48 $0.85 $40.80 United Kingdom
6 $3.25 $19.50
12 $2.10 $25.20
10 $1.25 $12.50
12 $1.65 $19.80
1 $2.10 $2.10
12 $1.65 $19.80
10 $1.95 $19.50
10 $1.95 $19.50
48 $0.85 $40.80
48 $0.85 $40.80
6 $1.95 $11.70
48 $3.25 $156.00
4 $3.75 $15.00
1 $1.95 $1.95
3 $4.95 $14.85
10 $1.95 $19.50
100 $1.65 $165.00
48 $0.85 $40.80
3 $4.95 $14.85
100 $1.65 $165.00
48 $0.85 $40.80
96 $0.85 $81.60
10 $1.95 $19.50
100 $1.65 $165.00
200 $1.65 $330.00
12 $1.65 $19.80
20 $1.95 $39.00
100 $1.65 $165.00
48 $0.85 $40.80
6 $3.25 $19.50
48 $0.85 $40.80
10 $1.95 $19.50
20 $1.95 $39.00
10 $1.95 $19.50
48 $0.85 $40.80
48 $0.85 $40.80
2 $1.95 $3.90
48 $0.85 $40.80
10 $1.95 $19.50
4 $3.75 $15.00
1 $2.10 $2.10
48 $0.85 $40.80
4 $3.75 $15.00
 48 $0.85 $40.80
48 $0.85 $40.80
3 $4.95 $14.85
48 $0.85 $40.80
48 $0.85 $40.80
6 $3.75 $22.50
4 $3.75 $15.00
10 $1.25 $12.50
48 $0.85 $40.80
3 $4.95 $14.85
48 $0.85 $40.80
48 $0.85 $40.80
48 $1.25 $60.00
4 $3.75 $15.00
3 $4.95 $14.85
4 $3.75 $15.00
5 $0.85 $4.25
48 $0.85 $40.80
400 $1.25 $500.00
6 $3.25 $19.50
48 $0.85 $40.80
48 $0.85 $40.80
200 $1.79 $358.00
10 $2.08 $20.80
12 $1.65 $19.80
24 $0.85 $20.40
12 $0.85 $10.20
10 $0.85 $8.50
4 $0.85 $3.40
48 $0.85 $40.80
96 $0.85 $81.60
10 $2.08 $20.80
48 $0.85 $40.80
10 $2.08 $20.80
3 $4.95 $14.85
12 $0.85 $10.20
6 $4.95 $29.70
48 $0.85 $40.80
48 $3.25 $156.00
10 $2.08 $20.80
100 $1.79 $179.00
10 $2.08 $20.80
4 $3.75 $15.00
20 $2.08 $41.60
96 $0.85 $81.60
 3 $4.95 $14.85
10 $2.08 $20.80
96 $0.85 $81.60
20 $2.08 $41.60
12 $0.85 $10.20
10 $2.08 $20.80
18 $0.85 $15.30
12 $0.85 $10.20
10 $2.08 $20.80
6 $1.65 $9.90
12 $0.85 $10.20
12 $1.65 $19.80
48 $0.85 $40.80
10 $2.08 $20.80
36 $0.85 $30.60
12 $1.65 $19.80
12 $1.65 $19.80
10 $2.08 $20.80
20 $1.95 $39.00
4 $3.75 $15.00
12 $1.65 $19.80
100 $0.72 $72.00
10 $2.08 $20.80
10 $1.95 $19.50
4 $2.08 $8.32
100 $1.79 $179.00
100 $1.79 $179.00
24 $0.85 $20.40
100 $1.79 $179.00
6 $1.95 $11.70
48 $0.85 $40.80
10 $2.08 $20.80
6 $2.10 $12.60
12 $0.85 $10.20
12 $0.85 $10.20
10 $1.95 $19.50
12 $1.65 $19.80
12 $0.85 $10.20
48 $0.85 $40.80
3 $2.08 $6.24
2 $2.10 $4.20
12 $0.85 $10.20
96 $0.85 $81.60
10 $2.08 $20.80
10 $2.08 $20.80
24 $0.85 $20.40
12 $0.85 $10.20
10 $2.08 $20.80
12 $0.85 $10.20
4 $3.75 $15.00
6 $0.85 $5.10
4 $3.75 $15.00
24 $0.85 $20.40
12 $0.85 $10.20
4 $3.75 $15.00
6 $4.95 $29.70
6 $0.85 $5.10
48 $0.85 $40.80
12 $0.85 $10.20
3 $4.95 $14.85
1 $1.65 $1.65
3 $0.85 $2.55
8 $3.75 $30.00
12 $1.65 $19.80
4 $3.75 $15.00
2 $3.75 $7.50
48 $0.85 $40.80
4 $1.65 $6.60
15 $0.85 $12.75
12 $0.85 $10.20
36 $0.85 $30.60
3 $1.65 $4.95
2 $0.85 $1.70
4 $0.85 $3.40
12 $0.85 $10.20
2 $2.10 $4.20
22 $0.85 $18.70
4 $0.85 $3.40
12 $2.10 $25.20
16 $4.25 $68.00
24 $0.85 $20.40
12 $0.85 $10.20
4 $3.75 $15.00
24 $0.85 $20.40
4 $3.75 $15.00
3 $2.08 $6.24
24 $0.85 $20.40
10 $0.85 $8.50
6 $1.95 $11.70
4 $2.10 $8.40
 12 $1.65 $19.80
12 $0.85 $10.20
48 $0.85 $40.80
6 $2.10 $12.60
4 $3.75 $15.00
6 $4.95 $29.70
48 $0.85 $40.80
2 $0.85 $1.70
100 $0.72 $72.00
4 $0.85 $3.40
1 $1.95 $1.95
1 $3.25 $3.25
20 $0.85 $17.00
1 $2.08 $2.08
100 $0.72 $72.00
10 $2.08 $20.80
2 $2.10 $4.20
1 $2.10 $2.10
4 $2.10 $8.40
5 $2.08 $10.40
100 $0.72 $72.00
Product ID Product Name
20713 Jumbo Bag Owls
21025 Space Frog
21116 Owl Doorstop
21260 First Aid Tin
21888 Bingo Set
22174 Photo Cube
22190 Local Cafe Mug
22197 Popcorn Holder
22653 Button Box
22694 Wicker Star
22740 Polkadot Pen
22741 Funky Diva Pen
23417 Bathroom Hook
62018 Sombrero
 Country Orders
Austria 2
Belgium 12
Denmark 2
Finland 3
France 32
Germany 44
Italy 7
Netherlands 12
Norway 4
Portugal 9
Spain 14
Sweden 4
Switzerland 7
United Kingdom 48
 Mortgage Payments
Loan Balance $250,000
Annual Interest Rate 4.5%
Term Length (yrs) 30
Monthly Payment
Months Sum of Revenue
Jan $459.80
Feb $1,306.65
Mar $299.10
Apr $139.50
May $202.10
Jun $1,211.40
Jul $518.65
Aug $574.15
Sep $1,161.72
Oct $504.09
Nov $678.42
Dec $117.90
Total Result $7,173.48
 You are the Sales Director of a beer manufa
THE that supplies the beer for all Major League B
SITUATION and you are preparing a workbook to help m

TASKS:
On the Team Selector worksheet, add a data validation list to cell C6 using range A2
Prices worksheet as the source; select the Philadelphia Phillies
On the Beer Prices worksheet, group columns B:C
On the Price per Ounce worksheet, use conditional formatting to apply a Red - Whit
to range B2:F31
On the Price per Ounce worksheet, add a formula in cell G2 that displays “None” if t
in 2018 was equal to the Price per Ounce in 2013, displays “Increase” if the Price pe
greater than the Price per Ounce in 2016 or greater than the average Price per Ounc
Otherwise, display “Decrease”.
f a beer manufacturing company
l Major League Baseball stadiums,
rkbook to help manage pricing

C6 using range A2:A31 from the Beer

apply a Red - White - Green color scale

displays “None” if the Price per Ounce

ase” if the Price per Ounce in 2018 is
age Price per Ounce from 2013-2016.
 MLB Beer Prices
Team

Price -
Size -

Price per Ounce -

 Team Nickname City Price Size
Arizona Diamondbacks Diamondbacks Arizona $4.00 14
Atlanta Braves Braves Atlanta $5.00 12
Baltimore Orioles Orioles Baltimore $4.00 12
Boston Red Sox Red Sox Boston $8.00 12
Chicago Cubs Cubs Chicago $9.00 20
Chicago White Sox White Sox Chicago $7.00 16
Cincinnati Reds Reds Cincinnati $6.25 14
Cleveland Indians Indians Cleveland $5.00 12
Colorado Rockies Rockies Colorado $3.00 12
Detroit Tigers Tigers Detroit $5.00 12
Houston Astros Astros Houston $6.00 14
Kansas City Royals Royals Kansas City $4.00 12
Los Angeles Angels Angels Anaheim $4.50 12
Los Angeles Dodgers Dodgers Los Angeles $6.25 16
Miami Marlins Marlins Miami $6.00 12
Milwaukee Brewers Brewers Milwaukee $7.00 16
Minnesota Twins Twins Minnesota $8.50 20
New York Mets Mets New York $10.50 20
New York Yankees Yankees New York $6.00 12
Oakland Athletics Athletics Oakland $5.50 12
Philadelphia Phillies Phillies Philadelphia $6.00 12
Pittsburgh Pirates Pirates Pittsburgh $6.00 16
San Diego Padres Padres San Diego $5.00 12
San Francisco Giants Giants San Francisco $8.25 14
Seattle Mariners Mariners Seattle $5.00 12
St. Louis Cardinals Cardinals St. Louis $5.00 12
Tampa Bay Rays Rays Tampa $5.00 12
Texas Rangers Rangers Arlington $6.00 16
Toronto Blue Jays Blue Jays Toronto $5.67 14
Washington Nationals Nationals Washington $7.00 14
 Team 2013 2014 2015 2016 2018 Change
Arizona Diamondbacks $0.29 $0.29 $0.29 $0.29 $0.29
Atlanta Braves $0.45 $0.45 $0.45 $0.48 $0.42
Baltimore Orioles $0.38 $0.42 $0.42 $0.42 $0.33
Boston Red Sox $0.60 $0.65 $0.65 $0.65 $0.67
Chicago Cubs $0.45 $0.47 $0.47 $0.47 $0.45
Chicago White Sox $0.41 $0.41 $0.41 $0.41 $0.44
Cincinnati Reds $0.46 $0.46 $0.46 $0.38 $0.45
Cleveland Indians $0.33 $0.33 $0.33 $0.33 $0.42
Colorado Rockies $0.38 $0.38 $0.38 $0.25 $0.25
Detroit Tigers $0.42 $0.42 $0.42 $0.42 $0.42
Houston Astros $0.36 $0.36 $0.36 $0.36 $0.43
Kansas City Royals $0.41 $0.41 $0.41 $0.41 $0.33
Los Angeles Angels $0.28 $0.28 $0.28 $0.28 $0.38
Los Angeles Dodgers $0.39 $0.34 $0.34 $0.31 $0.39
Miami Marlins $0.40 $0.50 $0.50 $0.50 $0.50
Milwaukee Brewers $0.38 $0.38 $0.38 $0.38 $0.44
Minnesota Twins $0.38 $0.38 $0.38 $0.38 $0.43
New York Mets $0.48 $0.48 $0.48 $0.48 $0.53
New York Yankees $0.50 $0.50 $0.50 $0.50 $0.50
Oakland Athletics $0.36 $0.42 $0.42 $0.42 $0.46
Philadelphia Phillies $0.37 $0.37 $0.37 $0.37 $0.50
Pittsburgh Pirates $0.34 $0.34 $0.34 $0.34 $0.38
San Diego Padres $0.36 $0.36 $0.36 $0.36 $0.42
San Francisco Giants $0.42 $0.50 $0.50 $0.54 $0.59
Seattle Mariners $0.46 $0.50 $0.50 $0.38 $0.42
St. Louis Cardinals $0.56 $0.56 $0.56 $0.42 $0.42
Tampa Bay Rays $0.42 $0.42 $0.42 $0.42 $0.42
Texas Rangers $0.31 $0.31 $0.31 $0.38 $0.38
Toronto Blue Jays $0.52 $0.49 $0.49 $0.49 $0.41
Washington Nationals $0.52 $0.41 $0.41 $0.41 $0.50
 You work for an international non-profit org
THE are preparing an Excel workbook to analyze
SITUATION indicators by country

TASKS:
On the Indicators worksheet, remove duplicates from the Indicators table by only co
in the Country column
Add Spanish (Mexico) as an authoring language and modify the Proofing options to
verb forms only
Disable all macros except digitally signed macros in this workbook
For the Pivot Table on the Region Summary worksheet, sort the Region descending
Population
On the Region Summary worksheet, add a calculated field called “Population Densit
Table that is equal to Population divided by Area
 al non-profit organization and you
kbook to analyze development

ors table by only considering the values

roofing options to allow Spanish Tuteo

k
Region descending by Sum of

“Population Density” to the Pivot

 Country Region Population Area
Afghanistan South Asia 38,041,754 652,230
Albania Europe & Central Asia 2,854,191 28,748
Algeria Middle East & North Africa 43,053,054 2,381,741
Andorra Europe & Central Asia 77,142 468
Angola Sub-Saharan Africa 31,825,295 1,246,700
Antigua and Barbuda Latin America & Caribbean 97,118 443
Argentina Latin America & Caribbean 44,938,712 2,780,400
Armenia Europe & Central Asia 2,957,731 29,743
Australia East Asia & Pacific 25,364,307 7,741,220
Austria Europe & Central Asia 8,877,067 83,871
Azerbaijan Europe & Central Asia 10,023,318 86,600
Bahrain Middle East & North Africa 1,641,172 765
Bangladesh South Asia 163,046,161 148,460
Belarus Europe & Central Asia 9,466,856 207,600
Belgium Europe & Central Asia 11,484,055 30,528
Belize Latin America & Caribbean 390,353 22,966
Benin Sub-Saharan Africa 11,801,151 112,622
Bhutan South Asia 763,092 38,394
Bolivia Latin America & Caribbean 11,513,100 1,098,581
Bosnia and Herzegovina Europe & Central Asia 3,301,000 51,197
Botswana Sub-Saharan Africa 2,303,697 581,730
Brazil Latin America & Caribbean 212,559,417 8,515,770
Brunei Darussalam East Asia & Pacific 433,285 5,765
Bulgaria Europe & Central Asia 6,975,761 110,879
Burkina Faso Sub-Saharan Africa 20,321,378 274,200
Burundi Sub-Saharan Africa 11,530,580 27,830
Cabo Verde Sub-Saharan Africa 549,935 4,033
Cambodia East Asia & Pacific 16,486,542 181,035
Cameroon Sub-Saharan Africa 25,876,380 475,440
Canada North America 37,589,262 9,984,670
Central African Republic Sub-Saharan Africa 4,745,185 622,984
Chad Sub-Saharan Africa 15,946,876 1,284,000
Chile Latin America & Caribbean 18,952,038 756,096
China East Asia & Pacific 1,397,715,000 9,596,960
Colombia Latin America & Caribbean 50,339,443 1,138,910
Comoros Sub-Saharan Africa 850,886 2,235
Congo, Dem. Rep. Sub-Saharan Africa 86,790,567 2,344,858
Congo, Rep. Sub-Saharan Africa 5,380,508 342,000
Costa Rica Latin America & Caribbean 5,047,561 51,100
Cote d'Ivoire Sub-Saharan Africa 25,716,544 322,463
Croatia Europe & Central Asia 4,067,500 56,594
Cyprus Europe & Central Asia 1,198,575 9,251
Czech Republic Europe & Central Asia 10,669,709 78,867
Denmark Europe & Central Asia 5,818,553 43,094
Djibouti Middle East & North Africa 973,560 23,200
Dominica Latin America & Caribbean 71,808 751
Dominican Republic Latin America & Caribbean 10,738,958 48,670
Ecuador Latin America & Caribbean 17,373,662 283,561
Egypt, Arab Rep. Middle East & North Africa 100,388,073 1,001,450
El Salvador Latin America & Caribbean 6,453,553 21,041
Equatorial Guinea Sub-Saharan Africa 1,355,986 28,051
Estonia Europe & Central Asia 1,326,590 45,228
Eswatini Sub-Saharan Africa 1,093,238 17,364
Ethiopia Sub-Saharan Africa 112,078,730 1,104,300
Fiji East Asia & Pacific 889,953 18,274
Finland Europe & Central Asia 5,520,314 338,145
France Europe & Central Asia 67,059,887 643,801
Gabon Sub-Saharan Africa 2,172,579 267,667
Gambia, The Sub-Saharan Africa 2,347,706 11,300
Georgia Europe & Central Asia 3,720,382 69,700
Germany Europe & Central Asia 83,132,799 357,022
Ghana Sub-Saharan Africa 30,417,856 238,533
Greece Europe & Central Asia 10,716,322 131,957
Grenada Latin America & Caribbean 112,003 349
Guatemala Latin America & Caribbean 16,604,026 108,889
Guinea Sub-Saharan Africa 12,771,246 245,857
Guinea-Bissau Sub-Saharan Africa 1,920,922 36,125
Guyana Latin America & Caribbean 782,766 214,969
Haiti Latin America & Caribbean 11,263,077 27,750
Honduras Latin America & Caribbean 9,746,117 112,090
Hong Kong SAR, China East Asia & Pacific 7,507,400 1,108
Hungary Europe & Central Asia 9,769,949 93,028
Iceland Europe & Central Asia 361,313 103,000
India South Asia 1,366,417,754 3,287,263
Indonesia East Asia & Pacific 270,625,568 1,904,569
Iraq Middle East & North Africa 39,309,783 438,317
Ireland Europe & Central Asia 4,941,444 70,273
Israel Middle East & North Africa 9,053,300 20,770
Italy Europe & Central Asia 60,297,396 301,340
Jamaica Latin America & Caribbean 2,948,279 10,991
Japan East Asia & Pacific 126,264,931 377,944
Jordan Middle East & North Africa 10,101,694 89,342
Kazakhstan Europe & Central Asia 18,513,930 2,724,900
Kenya Sub-Saharan Africa 52,573,973 580,367
Kiribati East Asia & Pacific 117,606 811
Korea, Rep. East Asia & Pacific 51,709,098 99,720
Kuwait Middle East & North Africa 4,207,083 17,818
Kyrgyz Republic Europe & Central Asia 6,456,900 199,951
Latvia Europe & Central Asia 1,912,789 64,589
Lebanon Middle East & North Africa 6,855,713 10,400
Lesotho Sub-Saharan Africa 2,125,268 30,355
Liberia Sub-Saharan Africa 4,937,374 111,369
Libya Middle East & North Africa 6,777,452 1,759,540
Lithuania Europe & Central Asia 2,786,844 65,300
Luxembourg Europe & Central Asia 619,896 2,586
Macao SAR, China East Asia & Pacific 696,100 28
Madagascar Sub-Saharan Africa 26,969,307 587,041
Malawi Sub-Saharan Africa 18,628,747 118,484
Malaysia East Asia & Pacific 31,949,777 329,847
Maldives South Asia 530,953 298
Mali Sub-Saharan Africa 19,658,031 1,240,192
Malta Middle East & North Africa 502,653 316
Marshall Islands East Asia & Pacific 58,791 181
Mauritania Sub-Saharan Africa 4,525,696 1,030,700
Mauritius Sub-Saharan Africa 1,265,711 2,040
Mexico Latin America & Caribbean 127,575,529 1,964,375
Moldova Europe & Central Asia 2,657,637 33,851
Mongolia East Asia & Pacific 3,225,167 1,564,116
Montenegro Europe & Central Asia 622,137 13,812
Morocco Middle East & North Africa 36,471,769 446,550
Mozambique Sub-Saharan Africa 30,366,036 799,380
Myanmar East Asia & Pacific 54,045,420 676,578
Namibia Sub-Saharan Africa 2,494,530 824,292
Nepal South Asia 28,608,710 147,181
Netherlands Europe & Central Asia 17,332,850 41,543
New Zealand East Asia & Pacific 4,699,755 268,838
Nicaragua Latin America & Caribbean 6,545,502 130,370
Niger Sub-Saharan Africa 23,310,715 1,267,000
Nigeria Sub-Saharan Africa 200,963,599 923,768
North Macedonia Europe & Central Asia 2,107,158 25,713
Norway Europe & Central Asia 5,347,896 323,802
Oman Middle East & North Africa 4,974,986 309,500
Pakistan South Asia 216,565,318 796,095
Palau East Asia & Pacific 18,008 459
Panama Latin America & Caribbean 4,246,439 75,420
Papua New Guinea East Asia & Pacific 8,776,109 462,840
Paraguay Latin America & Caribbean 7,044,636 406,752
Peru Latin America & Caribbean 32,510,453 1,285,216
Philippines East Asia & Pacific 108,116,615 300,000
Poland Europe & Central Asia 37,970,874 312,685
Portugal Europe & Central Asia 10,269,417 92,212
Puerto Rico Latin America & Caribbean 3,193,694 13,791
Qatar Middle East & North Africa 2,832,067 11,586
Romania Europe & Central Asia 19,356,544 238,391
Russian Federation Europe & Central Asia 144,373,535 17,098,240
Rwanda Sub-Saharan Africa 12,626,950 26,338
Samoa East Asia & Pacific 202,506 2,831
Sao Tome and Principe Sub-Saharan Africa 215,056 964
Saudi Arabia Middle East & North Africa 34,268,528 2,149,690
Senegal Sub-Saharan Africa 16,296,364 196,722
Serbia Europe & Central Asia 6,944,975 77,474
Seychelles Sub-Saharan Africa 97,625 455
Sierra Leone Sub-Saharan Africa 7,813,215 71,740
Singapore East Asia & Pacific 5,703,569 716
Slovak Republic Europe & Central Asia 5,454,073 49,035
Slovenia Europe & Central Asia 2,087,946 20,273
Solomon Islands East Asia & Pacific 669,823 28,896
Somalia Sub-Saharan Africa 15,442,905 637,657
South Africa Sub-Saharan Africa 58,558,270 1,219,090
Spain Europe & Central Asia 47,076,781 505,370
Sri Lanka South Asia 21,803,000 65,610
St. Kitts and Nevis Latin America & Caribbean 52,823 261
St. Lucia Latin America & Caribbean 182,790 616
St. Vincent and the Grenadines Latin America & Caribbean 110,589 389
Suriname Latin America & Caribbean 581,372 163,820
Sweden Europe & Central Asia 10,285,453 450,295
Switzerland Europe & Central Asia 8,574,832 41,277
Tajikistan Europe & Central Asia 9,321,018 144,100
Tanzania Sub-Saharan Africa 58,005,463 947,300
Thailand East Asia & Pacific 69,625,582 513,120
Timor-Leste East Asia & Pacific 3,500,000 14,874
Togo Sub-Saharan Africa 8,082,366 56,785
Tonga East Asia & Pacific 104,494 747
Trinidad and Tobago Latin America & Caribbean 1,394,973 5,128
Tunisia Middle East & North Africa 11,694,719 163,610
Turkey Europe & Central Asia 83,429,615 783,562
Turkmenistan Europe & Central Asia 5,942,089 488,100
Turks and Caicos Islands Latin America & Caribbean 42,953 948
Tuvalu East Asia & Pacific 11,646 26
Uganda Sub-Saharan Africa 44,269,594 241,038
Ukraine Europe & Central Asia 44,385,155 603,550
United Arab Emirates Middle East & North Africa 9,770,529 83,600
United Kingdom Europe & Central Asia 66,834,405 243,610
United States North America 328,239,523 9,833,517
Uruguay Latin America & Caribbean 3,461,734 176,215
Uzbekistan Europe & Central Asia 33,580,650 447,400
Vanuatu East Asia & Pacific 299,882 12,189
Vietnam East Asia & Pacific 96,462,106 331,210
Yemen, Rep. Middle East & North Africa 29,161,922 527,968
Zambia Sub-Saharan Africa 17,861,030 752,618
Zimbabwe Sub-Saharan Africa 14,645,468 390,757
Australia East Asia & Pacific 22,283,288 7,741,220
Austria Europe & Central Asia 8,047,112 83,871
Belgium Europe & Central Asia 10,321,498 30,528
Canada North America 36,109,080 9,984,670
Chile Latin America & Caribbean 16,904,310 756,096
Czech Republic Europe & Central Asia 9,738,355 78,867
Denmark Europe & Central Asia 5,324,893 43,094
Estonia Europe & Central Asia 1,233,596 45,228
Finland Europe & Central Asia 5,515,838 338,145
France Europe & Central Asia 70,755,983 643,801
Germany Europe & Central Asia 76,022,658 357,022
Greece Europe & Central Asia 9,908,766 131,957
Hungary Europe & Central Asia 9,052,201 93,028
Iceland Europe & Central Asia 317,538 103,000
Ireland Europe & Central Asia 4,413,506 70,273
Israel Middle East & North Africa 7,526,699 20,770
Italy Europe & Central Asia 55,816,636 301,340
Japan East Asia & Pacific 118,296,548 377,944
Korea, Rep. East Asia & Pacific 47,379,585 99,720
Luxembourg Europe & Central Asia 571,158 2,586
Netherlands Europe & Central Asia 19,012,079 41,543
New Zealand East Asia & Pacific 4,405,091 268,838
Norway Europe & Central Asia 4,211,803 323,802
Poland Europe & Central Asia 34,902,400 312,685
Portugal Europe & Central Asia 9,331,652 92,212
Slovak Republic Europe & Central Asia 4,992,552 49,035
Slovenia Europe & Central Asia 1,871,845 20,273
Spain Europe & Central Asia 42,421,273 505,370
Sweden Europe & Central Asia 10,007,221 450,295
Switzerland Europe & Central Asia 7,945,641 41,277
United Kingdom Europe & Central Asia 59,865,720 243,610
United States North America 314,580,896 9,833,517
 Data
Region Sum of Population Sum of Area
East Asia & Pacific 2,477,643,551 32,922,624
Europe & Central Asia 1,380,465,178 32,465,397
Latin America & Caribbean 623,779,788 20,172,724
Middle East & North Africa 359,564,756 9,456,933
North America 716,518,760 39,636,374
South Asia 1,835,776,742 5,135,531
Sub-Saharan Africa 1,049,530,538 21,646,744
Total Result 8,443,279,312 161,436,327
 You are the Head of Purchasing at a regiona
THE you are preparing an Excel workbook to trac
SITUATION inventory and orders

TASKS:
In cell F2 of the Inventory worksheet, write the year between parenthesis in the Na
Flash Fill to fill in the year for the rest of the wines in the inventory
On the Inventory worksheet, create a formula based conditional formatting rule to r
applies a bold font and light orange fill to rows where the Stock on Hand is less than
Reorder Quantity
On the Order Tracker worksheet, add a formula in cell B5 to calculate the arrival dat
Protect the workbook so users cannot add, edit, or delete worksheets using passwo
sing at a regional winery and
workbook to track your current

enthesis in the Name column and use

y
formatting rule to range A2:I101 that
n Hand is less than or equal to the

late the arrival date for the order

eets using password 12345
 Country Province Winery Variety
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Bordeaux Cheval Quancard Bordeaux Red
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Chardonnay
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Burgundy Louis Latour Pinot Noir
France Loire Valley Henri Bourgeois Pinot Noir
France Loire Valley Henri Bourgeois Pinot Noir
France Loire Valley Henri Bourgeois Pinot Noir
France Loire Valley Henri Bourgeois Pinot Noir
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
France Loire Valley Henri Bourgeois Sauvignon Blanc
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Cadence Bordeaux Red
US Washington Canoe Ridge Merlot
US Washington Canoe Ridge Merlot
 Name Year Stock On Hand Reorder Quantity Price
Cheval Quancard (2009) Chai de Bordes 26 15 $10
Cheval Quancard (2009) Monfort Bellevue 50 10 $22
Cheval Quancard (2010) Chai de Bordes 34 15 $12
Cheval Quancard (2010) Fleur de Roc 15 10 $20
Cheval Quancard (2010) Prestige de Bordes 28 15 $13
Cheval Quancard (2012) Chai de Bordes 36 15 $12
Cheval Quancard (2014) Chai de Bordes 42 15 $12
Cheval Quancard (2014) Fortin Plaisance 16 10 $20
Cheval Quancard (2014) Monfort Bellevue 25 15 $17
Cheval Quancard (2015) Chai de Bordes 31 15 $13
Cheval Quancard (2015) Fleur du Roc 22 10 $25
Louis Latour (2008) Batard-Montrachet 36 0 $255
Louis Latour (2008) Charmes Premier Cru 1 3 $69
Louis Latour (2008) Genievres 29 15 $16
Louis Latour (2009) Batard-Montrachet 50 0 $299
Louis Latour (2009) Charmes 45 3 $75
Louis Latour (2009) La Garenne 35 3 $70
Louis Latour (2009) Les Demoiselles 14 0 $320
Louis Latour (2010) Morgeot Premier Cru 30 3 $70
Louis Latour (2011) Caradeux Premier Cru 43 10 $45
Louis Latour (2011) Les Demoiselles 33 0 $380
Louis Latour (2011) Morgeot Premier Cru 45 3 $75
Louis Latour (2012) En Paradis 15 10 $24
Louis Latour (2012) Les Deux Moulins 47 10 $23
Louis Latour (2014) Batard-Montrachet 36 0 $430
Louis Latour (2014) En Paradis 35 10 $25
Louis Latour (2014) Le Montrachet 14 0 $630
Louis Latour (2015) Batard-Montrachet 15 0 $400
Louis Latour (2015) Les Demoiselles 24 0 $450
Louis Latour (2015) Morgeot Premier Cru 24 3 $90
Louis Latour (2015) Morgeot Premier Cru 3 3 $90
Louis Latour (2005) Clos de Vougeot 32 1 $240
Louis Latour (2005) Domaine Latour 15 3 $50
Louis Latour (2005) Morey-Saint-Denis 50 3 $60
Louis Latour (2008) Clos de Vougeot 43 1 $229
Louis Latour (2009) Charmes Chambertin 40 0 $359
Louis Latour (2009) En Chevret 31 3 $65
Louis Latour (2009) Les Chatelots 30 1 $135
Louis Latour (2009) Les Damodes 42 1 $115
Louis Latour (2009) Les Quatre Journaux 28 0 $425
Louis Latour (2010) Clos de Vougeot 12 0 $260
Louis Latour (2011) Clos de Vougeot 25 0 $280
Louis Latour (2011) Epenots Premier Cru 45 3 $95
Louis Latour (2011) Morgeot Premier Cru 44 3 $60
Louis Latour (2012) Nuits-St.-Georges 33 3 $80
Louis Latour (2014) Clos de Vougeot 34 0 $330
Louis Latour (2014) Epenots Premier Cru 15 1 $120
Louis Latour (2014) Les Quatre Journaux 7 0 $580
Louis Latour (2014) Morey-Saint-Denis 43 3 $80
Louis Latour (2014) Morey-Saint-Denis 7 3 $80
Louis Latour (2015) Domaine Latour 0 3 $75
Louis Latour (2015) Domaine Latour 36 3 $75
Louis Latour (2015) Les Quatre Journaux 0 0 $650
Louis Latour (2015) Morey-Saint-Denis 7 3 $79
Louis Latour (2015) Nuits-St.-Georges 5 3 $79
Henri Bourgeois (2012) La Bourgeoise 28 10 $45
Henri Bourgeois (2013) Les Baronnes 41 10 $27
Henri Bourgeois (2013) Les Baronnes 21 10 $27
Henri Bourgeois (2014) Les Baronnes 45 10 $27
Henri Bourgeois (2008) Le M.D. de Bourgeois 17 10 $36
Henri Bourgeois (2009) La Bourgeoise 10 10 $35
Henri Bourgeois (2009) Sancerre d'Antan 8 3 $59
Henri Bourgeois (2010) Jadis 35 3 $50
Henri Bourgeois (2011) Etienne Henri 1 3 $53
Henri Bourgeois (2011) Sancerre d'Antan 44 3 $50
Henri Bourgeois (2011) Sancerre Jadis 22 3 $50
Henri Bourgeois (2012) En Travertin 35 10 $25
Henri Bourgeois (2012) Jadis 19 3 $55
Henri Bourgeois (2012) La Bourgeoise 50 10 $40
Henri Bourgeois (2012) Les Baronnes 27 10 $23
Henri Bourgeois (2012) Sancerre d'Antan 37 3 $50
Henri Bourgeois (2013) Etienne Henri 3 3 $65
Henri Bourgeois (2013) La Bourgeoise 22 10 $40
Henri Bourgeois (2013) Les Baronnes 41 10 $25
Henri Bourgeois (2014) Etienne Henri 8 3 $65
Henri Bourgeois (2014) Haute Victoire 39 15 $18
Henri Bourgeois (2014) Jadis 30 3 $60
Henri Bourgeois (2014) La Bourgeoise 11 10 $40
Henri Bourgeois (2014) Les Baronnes 7 10 $23
Henri Bourgeois (2015) En Travertin 14 10 $25
Henri Bourgeois (2015) Haute Victoire 25 15 $18
Henri Bourgeois (2015) Les Baronnes 28 10 $25
Henri Bourgeois (2016) Les Baronnes 30 10 $25
Cadence (2011) Cara Mia 10 3 $60
Cadence (2011) Cara Mia 0 3 $60
Cadence (2011) Tapteil 41 10 $45
Cadence (2012) Cara Mia 5 3 $60
Cadence (2012) Coda 28 10 $25
Cadence (2012) Tapteil 17 10 $45
Cadence (2013) Cara Mia 30 3 $60
Cadence (2013) Ciel du Cheval 21 10 $45
Cadence (2013) Coda 47 10 $25
Cadence (2013) Tapteil 27 10 $45
Cadence (2014) Cara Mia 36 3 $60
Cadence (2014) Coda 24 10 $28
Cadence (2014) Tapteil 35 10 $45
Cadence (2014) Tapteil 1 10 $45
Cadence (2015) Coda 44 10 $28
Canoe Ridge (2013) The Expedition 16 15 $15
Canoe Ridge (2014) The Expedition 25 15 $15
 Order Tracker
Order ID: 555-123
Purchase Date: 2020-11-14
Shipment Time (business days): 12
Arrival Date:
 You’ve been hired as an analyst by a coffee r
THE and you are preparing a workbook to compa
SITUATION stores and product categories

TASKS:
On the Product Sales worksheet, insert a Subtotal that calculates the Sum on the Un
columns for each Product Group
On the Sales by Store worksheet, consolidate the quarterly sales figures for all 3 sto
table using a Sum
On the New Product worksheet, add a custom number format for cell B8 so that po
dollar sign ($), a thousand separator and no decimal points; for negative values use
wrap the number in parenthesis and give it a red font
Use an Excel forecasting tool in cell B6 on the New Product worksheet to calculate t
will result in a Profit of $3,000
alyst by a coffee roasting company
rkbook to compare sales across
ies

the Sum on the Units Sold and Sales

figures for all 3 stores in the NYC Totals

r cell B8 so that positive values have a

egative values use the same format but

sheet to calculate the Units Sold that

Product ID Product Group Product Category Product Type
1 Whole Bean/Teas Coffee beans Organic Beans
2 Whole Bean/Teas Coffee beans House blend Beans
3 Whole Bean/Teas Coffee beans Espresso Beans
4 Whole Bean/Teas Coffee beans Espresso Beans
5 Whole Bean/Teas Coffee beans Gourmet Beans
6 Whole Bean/Teas Coffee beans Gourmet Beans
7 Whole Bean/Teas Coffee beans Premium Beans
8 Whole Bean/Teas Coffee beans Premium Beans
9 Whole Bean/Teas Coffee beans Organic Beans
10 Whole Bean/Teas Coffee beans Green beans
11 Whole Bean/Teas Loose Tea Herbal tea
12 Whole Bean/Teas Loose Tea Herbal tea
13 Whole Bean/Teas Loose Tea Black tea
14 Whole Bean/Teas Loose Tea Black tea
15 Whole Bean/Teas Loose Tea Green tea
16 Whole Bean/Teas Loose Tea Chai tea
17 Whole Bean/Teas Loose Tea Chai tea
18 Whole Bean/Teas Loose Tea Chai tea
19 Whole Bean/Teas Packaged Chocolate Drinking Chocolate
20 Whole Bean/Teas Packaged Chocolate Organic Chocolate
21 Whole Bean/Teas Packaged Chocolate Drinking Chocolate
22 Beverages Coffee Drip coffee
23 Beverages Coffee Drip coffee
24 Beverages Coffee Drip coffee
25 Beverages Coffee Organic brewed coffee
26 Beverages Coffee Organic brewed coffee
27 Beverages Coffee Organic brewed coffee
28 Beverages Coffee Gourmet brewed coffee
29 Beverages Coffee Gourmet brewed coffee
30 Beverages Coffee Gourmet brewed coffee
31 Beverages Coffee Gourmet brewed coffee
32 Beverages Coffee Gourmet brewed coffee
33 Beverages Coffee Gourmet brewed coffee
34 Beverages Coffee Premium brewed coffee
35 Beverages Coffee Premium brewed coffee
36 Beverages Coffee Premium brewed coffee
37 Beverages Coffee Barista Espresso
38 Beverages Coffee Barista Espresso
39 Beverages Coffee Barista Espresso
40 Beverages Coffee Barista Espresso
41 Beverages Coffee Barista Espresso
42 Beverages Tea Brewed herbal tea
43 Beverages Tea Brewed herbal tea
44 Beverages Tea Brewed herbal tea
45 Beverages Tea Brewed herbal tea
46 Beverages Tea Brewed Green tea
47 Beverages Tea Brewed Green tea
48 Beverages Tea Brewed Black tea
49 Beverages Tea Brewed Black tea
50 Beverages Tea Brewed Black tea
51 Beverages Tea Brewed Black tea
52 Beverages Tea Brewed Chai tea
53 Beverages Tea Brewed Chai tea
54 Beverages Tea Brewed Chai tea
55 Beverages Tea Brewed Chai tea
56 Beverages Tea Brewed Chai tea
57 Beverages Tea Brewed Chai tea
58 Beverages Drinking Chocolate Hot chocolate
59 Beverages Drinking Chocolate Hot chocolate
60 Beverages Drinking Chocolate Hot chocolate
61 Beverages Drinking Chocolate Hot chocolate
63 Add-ons Flavours Regular syrup
64 Add-ons Flavours Regular syrup
65 Add-ons Flavours Sugar free syrup
69 Food Bakery Biscotti
70 Food Bakery Scone
71 Food Bakery Pastry
72 Food Bakery Scone
73 Food Bakery Pastry
74 Food Bakery Biscotti
75 Food Bakery Pastry
76 Food Bakery Biscotti
77 Food Bakery Scone
78 Food Bakery Scone
79 Food Bakery Scone
81 Merchandise Branded Clothing
82 Merchandise Branded Housewares
83 Merchandise Branded Housewares
84 Add-ons Flavours Regular syrup
87 Beverages Coffee Barista Espresso
 Product Name Units Sold Total Sales
Brazilian - Organic 1,289 $23,202
Our Old Time Diner Blend 1,186 $21,348
Espresso Roast 988 $14,573
Primo Espresso Roast 891 $18,221
Columbian Medium Roast 908 $13,620
Ethiopia 1,326 $27,846
Jamacian Coffee River 926 $18,289
Civet Cat 1,479 $66,555
Organic Decaf Blend 1,233 $27,743
Guatemalan Sustainably Grown 869 $8,690
Lemon Grass 981 $8,780
Peppermint 861 $7,706
English Breakfast 1,000 $8,950
Earl Grey 864 $7,733
Serenity Green Tea 908 $8,399
Traditional Blend Chai 938 $8,395
Morning Sunrise Chai 1,061 $10,080
Spicy Eye Opener Chai 768 $8,410
Dark chocolate 672 $4,301
Sustainably Grown Organic 1,271 $9,660
Chili Mayan 872 $11,624
Our Old Time Diner Blend Sm 26,768 $53,536
Our Old Time Diner Blend Rg 26,831 $67,078
Our Old Time Diner Blend Lg 24,888 $74,664
Brazilian Sm 26,671 $58,676
Brazilian Rg 26,852 $80,556
Brazilian Lg 26,001 $91,004
Columbian Medium Roast Sm 25,236 $50,472
Columbian Medium Roast Rg 27,514 $68,785
Columbian Medium Roast Lg 25,970 $77,910
Ethiopia Sm 27,061 $59,534
Ethiopia Rg 27,287 $81,861
Ethiopia Lg 26,146 $91,511
Jamaican Coffee River Sm 24,566 $60,187
Jamaican Coffee River Rg 24,877 $77,119
Jamaican Coffee River Lg 26,489 $99,334
Espresso shot 25,726 $77,178
Latte 27,754 $104,078
Latte Rg 27,141 $115,349
Cappuccino 26,355 $98,831
Cappuccino Lg 25,836 $109,803
Lemon Grass Rg 26,553 $66,383
Lemon Grass Lg 24,917 $74,751
Peppermint Rg 27,449 $68,623
Peppermint Lg 25,770 $77,310
Serenity Green Tea Rg 27,295 $68,238
Serenity Green Tea Lg 25,483 $76,449
English Breakfast Rg 25,337 $63,343
English Breakfast Lg 26,032 $78,096
Earl Grey Rg 28,388 $70,970
Earl Grey Lg 25,817 $77,451
Traditional Blend Chai Rg 27,258 $68,145
Traditional Blend Chai Lg 25,572 $76,716
Morning Sunrise Chai Rg 27,762 $69,405
Morning Sunrise Chai Lg 26,937 $107,748
Spicy Eye Opener Chai Rg 25,358 $64,663
Spicy Eye Opener Chai Lg 26,460 $82,026
Dark chocolate Rg 24,346 $85,211
Dark chocolate Lg 28,133 $126,599
Sustainably Grown Organic Rg 26,410 $99,038
Sustainably Grown Organic Lg 27,178 $129,096
Carmel syrup 15,458 $12,366
Hazelnut syrup 14,552 $11,642
Sugar Free Vanilla syrup 17,764 $14,211
Hazelnut Biscotti 12,290 $43,015
Cranberry Scone 12,549 $40,784
Chocolate Croissant 19,213 $72,049
Ginger Scone 15,845 $51,496
Almond Croissant 11,753 $44,074
Ginger Biscotti 11,007 $38,525
Croissant 11,837 $38,470
Chocolate Chip Biscotti 11,571 $40,499
Oatmeal Scone 11,065 $33,195
Scottish Cream Scone 11,837 $53,267
Jumbo Savory Scone 12,466 $46,748
I Need My Bean! T-shirt 1,429 $40,012
I Need My Bean! Diner mug 1,385 $16,620
I Need My Bean! Latte cup 1,883 $26,362
Chocolate syrup 16,051 $12,841
Ouro Brasileiro shot 19,967 $59,901
 Astoria Store NYC Totals
Product Group Q1 Q2 Q3 Q4 Product Group Q1
Beverages $108,111 $131,578 $126,159 $140,993
Food $15,700 $19,003 $18,173 $20,248
Whole Bean/Teas $8,927 $10,768 $10,171 $11,000
Merchandise $3,248 $3,829 $3,779 $4,371
Add-ons $1,075 $1,259 $1,221 $1,396
Grand Total $137,060 $166,437 $159,503 $178,008

Lower Manhattan Store

Product Group Q1 Q2 Q3 Q4
Beverages $104,200 $125,505 $121,091 $134,841
Food $16,616 $20,266 $19,442 $21,689
Whole Bean/Teas $9,853 $11,884 $11,037 $12,763
Merchandise $3,640 $4,718 $4,484 $4,986
Add-ons $2,272 $2,735 $2,593 $2,908
Grand Total $136,581 $165,108 $158,647 $177,187

Hell's Kitchen Store

Product Group Q1 Q2 Q3 Q4
Beverages $104,093 $126,824 $120,589 $136,553
Food $16,011 $19,371 $18,450 $20,885
Whole Bean/Teas $12,509 $16,338 $16,566 $17,577
Add-ons $1,630 $1,986 $1,873 $2,109
Merchandise $906 $1,322 $950 $1,470
Grand Total $135,150 $165,842 $158,428 $178,595
NYC Totals
Q2 Q3 Q4
Rocky Roast Premium Beans
Fixed Costs: $250
Variable Costs: $16.50
Price: $22.00
Units Sold:

Profit: -250
 You’ve been contracted to track traffic accid
THE City, and you are preparing a workbook to a
SITUATION trends and contributing factors

TASKS:
On the Van Collisions worksheet, record a macro named “TableHeader” with Ctrl+S
Shortcut key that selects range A1:F1, makes the font bold and size 14pt, and merge
selection; run the macro using the Shortcut on the Taxi Collisions worksheet
On the Monthly Trend worksheet, add a chart that shows the Collisions, as a cluste
and the Injury %, as a line chart on a secondary axis, by Month
On the Collision Causes worksheet, show values as % of Column Total for the Sum o
Injured
rack traffic accidents in New York
a workbook to analyze monthly
tors

eader” with Ctrl+Shift+T as the

ze 14pt, and merges & centers the
worksheet
lisions, as a clustered column chart,

Total for the Sum of # of Persons

Van Collisions in NYC
Date Time District Collision Cause
2020-01-02 18:15:00 Manhattan Passing Too Closely
2020-01-03 11:20:00 Brooklyn Passing Too Closely
2020-01-03 13:00:00 Bronx Unspecified
2020-01-04 04:55:00 Queens Brakes Defective
2020-01-04 11:07:00 Brooklyn Following Too Closely
2020-01-05 09:39:00 Brooklyn Driver Inattention/Distraction
2020-01-05 15:40:00 Brooklyn Passing Too Closely
2020-01-06 10:35:00 Brooklyn Driver Inattention/Distraction
2020-01-07 16:35:00 Brooklyn Driver Inexperience
2020-01-08 11:20:00 Brooklyn Driver Inattention/Distraction
2020-01-09 10:25:00 Brooklyn Passing or Lane Usage Improper
2020-01-09 10:30:00 Brooklyn Brakes Defective
2020-01-09 12:23:00 Brooklyn Unspecified
2020-01-11 05:40:00 Manhattan Passing Too Closely
2020-01-11 13:01:00 Manhattan Unsafe Lane Changing
2020-01-13 08:45:00 Queens Driver Inattention/Distraction
2020-01-13 13:00:00 Manhattan Driver Inattention/Distraction
2020-01-13 14:18:00 Brooklyn Backing Unsafely
2020-01-13 15:00:00 Manhattan Following Too Closely
2020-01-14 10:16:00 Queens Backing Unsafely
2020-01-14 14:14:00 Manhattan Unsafe Lane Changing
2020-01-15 12:20:00 Manhattan Following Too Closely
2020-01-15 12:20:00 Queens Driver Inattention/Distraction
2020-01-16 06:25:00 Manhattan Unsafe Speed
2020-01-16 11:30:00 Brooklyn View Obstructed/Limited
2020-01-16 15:00:00 Queens Driver Inattention/Distraction
2020-01-17 08:45:00 Manhattan Passing Too Closely
2020-01-17 09:10:00 Manhattan Driver Inattention/Distraction
2020-01-17 11:55:00 Manhattan Driver Inattention/Distraction
2020-01-21 07:20:00 Bronx Driver Inattention/Distraction
2020-01-21 10:15:00 Manhattan Unspecified
2020-01-21 13:00:00 Queens Passing Too Closely
2020-01-21 14:00:00 Brooklyn Passing Too Closely
2020-01-22 12:25:00 Manhattan Unspecified
2020-01-23 13:10:00 Bronx Other Vehicular
2020-01-23 14:40:00 Manhattan Passing Too Closely
2020-01-24 08:27:00 Brooklyn Unspecified
2020-01-25 15:45:00 Manhattan Reaction to Uninvolved Vehicle
2020-01-27 17:35:00 Bronx Driver Inattention/Distraction
2020-01-29 10:04:00 Manhattan Other Vehicular
2020-01-30 08:30:00 Manhattan Failure to Yield Right-of-Way
2020-01-30 10:16:00 Manhattan Following Too Closely
2020-02-02 15:15:00 Queens Unsafe Speed
2020-02-02 17:55:00 Brooklyn Driver Inattention/Distraction
2020-02-03 03:25:00 Bronx Passing or Lane Usage Improper
2020-02-03 08:45:00 Manhattan Unspecified
2020-02-03 15:57:00 Brooklyn Passing Too Closely
2020-02-03 21:17:00 Manhattan Unspecified
2020-02-06 10:13:00 Brooklyn Other Vehicular
2020-02-07 06:55:00 Manhattan Unspecified
2020-02-07 13:15:00 Brooklyn Driver Inattention/Distraction
2020-02-07 15:52:00 Manhattan Reaction to Uninvolved Vehicle
2020-02-07 16:40:00 Manhattan Following Too Closely
2020-02-09 10:07:00 Brooklyn Driver Inattention/Distraction
2020-02-10 10:53:00 Manhattan Other Vehicular
2020-02-10 15:40:00 Manhattan Unspecified
2020-02-10 19:56:00 Bronx Unspecified
2020-02-11 09:25:00 Brooklyn Driver Inattention/Distraction
2020-02-12 10:30:00 Manhattan Outside Car Distraction
2020-02-12 13:50:00 Brooklyn Driver Inattention/Distraction
2020-02-12 15:35:00 Manhattan Driver Inattention/Distraction
2020-02-13 17:28:00 Manhattan Following Too Closely
2020-02-14 09:20:00 Manhattan Passing Too Closely
2020-02-14 09:20:00 Manhattan Backing Unsafely
2020-02-14 16:05:00 Manhattan Other Vehicular
2020-02-14 17:05:00 Manhattan Other Vehicular
2020-02-17 11:00:00 Queens Driver Inattention/Distraction
2020-02-18 09:25:00 Queens Driver Inattention/Distraction
2020-02-18 18:20:00 Brooklyn Unspecified
2020-02-19 11:40:00 Brooklyn Unspecified
2020-02-19 13:05:00 Manhattan Driver Inattention/Distraction
2020-02-20 15:07:00 Brooklyn Driver Inattention/Distraction
2020-02-20 16:30:00 Brooklyn Passing or Lane Usage Improper
2020-02-23 12:00:00 Manhattan Passing Too Closely
2020-02-23 20:00:00 Manhattan Following Too Closely
2020-02-24 08:25:00 Queens Traffic Control Disregarded
2020-02-24 13:22:00 Manhattan Oversized Vehicle
2020-02-24 15:00:00 Manhattan Following Too Closely
2020-02-25 02:50:00 Brooklyn Passing Too Closely
2020-02-26 04:35:00 Bronx Pavement Slippery
2020-02-27 09:10:00 Brooklyn Passing Too Closely
2020-02-27 11:44:00 Manhattan Following Too Closely
2020-02-27 14:00:00 Brooklyn View Obstructed/Limited
2020-02-27 14:40:00 Brooklyn Unspecified
2020-02-27 15:29:00 Brooklyn Driver Inattention/Distraction
2020-02-29 09:59:00 Manhattan Unspecified
2020-03-01 14:17:00 Bronx Illnes
2020-03-02 08:42:00 Brooklyn Unspecified
2020-03-02 08:50:00 Manhattan Driver Inattention/Distraction
2020-03-02 11:50:00 Brooklyn Driver Inattention/Distraction
2020-03-03 11:00:00 Manhattan Backing Unsafely
2020-03-03 12:16:00 Brooklyn Driver Inattention/Distraction
2020-03-04 12:39:00 Queens Driver Inattention/Distraction
2020-03-04 19:00:00 Manhattan Oversized Vehicle
2020-03-05 16:50:00 Manhattan Driver Inattention/Distraction
2020-03-06 04:45:00 Manhattan Unspecified
2020-03-06 07:10:00 Manhattan Driver Inattention/Distraction
2020-03-06 13:21:00 Brooklyn Failure to Yield Right-of-Way
2020-03-06 16:50:00 Brooklyn Driver Inattention/Distraction
2020-03-07 11:59:00 Bronx Failure to Yield Right-of-Way
2020-03-08 03:50:00 Brooklyn Unspecified
2020-03-08 12:00:00 Manhattan Unspecified
2020-03-09 10:45:00 Brooklyn Driver Inattention/Distraction
2020-03-09 13:45:00 Queens Unsafe Lane Changing
2020-03-09 15:44:00 Brooklyn Alcohol Involvement
2020-03-10 06:08:00 Bronx Following Too Closely
2020-03-12 09:30:00 Manhattan Unspecified
2020-03-12 13:45:00 Manhattan Passing Too Closely
2020-03-12 22:45:00 Brooklyn Driver Inattention/Distraction
2020-03-14 05:15:00 Manhattan Other Vehicular
2020-03-14 08:06:00 Manhattan Following Too Closely
2020-03-18 10:20:00 Queens Driver Inattention/Distraction
2020-03-18 12:30:00 Brooklyn Backing Unsafely
2020-03-18 13:45:00 Queens Unspecified
2020-03-18 15:52:00 Queens Fatigued/Drowsy
2020-03-19 13:20:00 Manhattan Driver Inattention/Distraction
2020-03-19 17:59:00 Bronx Driver Inattention/Distraction
2020-03-20 20:00:00 Bronx Unspecified
2020-03-24 14:35:00 Queens Unspecified
2020-03-25 19:18:00 Brooklyn Driver Inattention/Distraction
2020-03-25 20:34:00 Bronx Following Too Closely
2020-03-26 11:30:00 Manhattan Passing Too Closely
2020-03-26 12:05:00 Queens Driver Inattention/Distraction
2020-04-01 13:37:00 Brooklyn Driver Inattention/Distraction
2020-04-03 10:22:00 Queens Reaction to Uninvolved Vehicle
2020-04-10 15:20:00 Brooklyn Unspecified
2020-04-23 15:30:00 Queens Unspecified
2020-04-24 20:37:00 Bronx Driver Inattention/Distraction
2020-04-29 12:00:00 Bronx Unsafe Speed
2020-04-30 04:15:00 Bronx Driver Inattention/Distraction
2020-04-30 05:50:00 Bronx Driver Inattention/Distraction
2020-05-02 11:40:00 Bronx Fatigued/Drowsy
2020-05-03 11:45:00 Manhattan Backing Unsafely
2020-05-03 13:15:00 Queens Backing Unsafely
2020-05-03 20:05:00 Brooklyn Backing Unsafely
2020-05-10 18:22:00 Queens Unspecified
2020-05-11 09:30:00 Queens Unspecified
2020-05-11 12:03:00 Manhattan Driver Inattention/Distraction
2020-05-12 09:10:00 Manhattan Driver Inattention/Distraction
2020-05-14 10:00:00 Brooklyn Driver Inattention/Distraction
2020-05-17 21:55:00 Brooklyn Driver Inattention/Distraction
2020-05-18 11:00:00 Brooklyn Other Vehicular
2020-05-20 03:07:00 Queens Aggressive Driving/Road Rage
2020-05-20 03:10:00 Brooklyn Unsafe Speed
2020-05-26 10:27:00 Brooklyn Reaction to Uninvolved Vehicle
2020-05-26 10:30:00 Bronx Reaction to Uninvolved Vehicle
2020-05-28 16:45:00 Brooklyn Unspecified
2020-06-02 11:00:00 Manhattan Driver Inattention/Distraction
2020-06-02 18:00:00 Brooklyn Unspecified
2020-06-03 15:00:00 Brooklyn Failure to Yield Right-of-Way
2020-06-04 12:00:00 Queens Backing Unsafely
2020-06-05 07:58:00 Queens Passing Too Closely
2020-06-05 13:25:00 Queens Passing Too Closely
2020-06-09 12:00:00 Queens Passing Too Closely
2020-06-09 14:39:00 Bronx Unsafe Lane Changing
2020-06-09 19:45:00 Brooklyn Driver Inattention/Distraction
2020-06-11 10:07:00 Bronx Driver Inattention/Distraction
2020-06-11 13:10:00 Brooklyn Passing or Lane Usage Improper
2020-06-12 09:27:00 Bronx Driverless/Runaway Vehicle
2020-06-12 12:58:00 Manhattan Driver Inattention/Distraction
2020-06-16 10:40:00 Queens Unspecified
2020-06-17 16:00:00 Brooklyn Driver Inattention/Distraction
2020-06-20 14:10:00 Queens Turning Improperly
2020-06-20 17:31:00 Brooklyn Unspecified
2020-06-22 12:25:00 Brooklyn Driver Inattention/Distraction
2020-06-23 13:34:00 Bronx Driver Inexperience
2020-06-23 17:28:00 Queens Following Too Closely
2020-06-23 18:09:00 Brooklyn Unspecified
2020-06-26 14:40:00 Brooklyn Reaction to Uninvolved Vehicle
2020-06-28 10:30:00 Brooklyn Fell Asleep
2020-06-29 14:51:00 Brooklyn Unspecified
2020-06-29 19:30:00 Queens Backing Unsafely
2020-06-30 16:15:00 Brooklyn Driver Inattention/Distraction
2020-06-30 17:50:00 Bronx Oversized Vehicle
2020-06-30 22:30:00 Bronx Unsafe Speed
2020-07-01 22:47:00 Bronx Alcohol Involvement
2020-07-02 13:25:00 Bronx Traffic Control Disregarded
2020-07-05 04:00:00 Brooklyn Unspecified
2020-07-06 08:29:00 Bronx Driver Inexperience
2020-07-09 10:47:00 Brooklyn Driver Inattention/Distraction
2020-07-10 10:00:00 Manhattan Following Too Closely
2020-07-10 17:20:00 Bronx Unspecified
2020-07-11 02:35:00 Queens Traffic Control Disregarded
2020-07-11 13:55:00 Brooklyn Unspecified
2020-07-13 04:48:00 Brooklyn Unsafe Speed
2020-07-14 11:30:00 Manhattan Turning Improperly
2020-07-14 17:10:00 Brooklyn Unspecified
2020-07-16 10:03:00 Manhattan Driver Inattention/Distraction
2020-07-17 00:20:00 Queens Driverless/Runaway Vehicle
2020-07-17 08:10:00 Brooklyn Driver Inattention/Distraction
2020-07-17 16:00:00 Manhattan Driver Inattention/Distraction
2020-07-17 19:05:00 Brooklyn Failure to Yield Right-of-Way
2020-07-18 13:45:00 Bronx Unspecified
2020-07-19 12:45:00 Staten Island Unspecified
2020-07-20 07:50:00 Brooklyn Unspecified
2020-07-21 08:20:00 Brooklyn Driver Inattention/Distraction
2020-07-21 18:27:00 Bronx Driver Inattention/Distraction
2020-07-21 22:00:00 Manhattan Passing or Lane Usage Improper
2020-07-22 16:55:00 Manhattan Driver Inattention/Distraction
 Vehicle # of Persons Injured
Bus 0
Sedan 0
Bike 1
Station Wagon 0
Van 0
Sedan 0
Station Wagon 0
Sedan 0
Trailer 0
Station Wagon 0
Station Wagon 0
Box Truck 0
Bus 0
Sedan 0
Pick-up Truck 0
Station Wagon 1
Box Truck 0
Sedan 0
Pick-up Truck 0
Box Truck 0
Bike 1
E-Bike 0
Sedan 0
Bus 0
Van 0
Station Wagon 0
Ambulance 0
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Station Wagon 0
Sedan 0
Station Wagon 0
Station Wagon 0
Station Wagon 0
Sedan 0
Station Wagon 0
Station Wagon 0
Sedan 0
Sedan 0
Sedan 0
Sedan 0
Bike 1
Dump 0
Bus 0
Trailer 0
Sedan 0
Van 1
Station Wagon 1
Van 0
Dump 0
Station Wagon 0
Sedan 0
Van 0
Taxi 0
Sedan 0
Station Wagon 0
Station Wagon 0
Station Wagon 0
Sedan 0
Station Wagon 0
Pick-up Truck 0
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Taxi 1
Bike 1
Taxi 0
Station Wagon 0
Bus 0
Station Wagon 0
Box Truck 0
Sedan 0
Bike 1
Van 0
Bus 0
Station Wagon 0
Sedan 0
Bus 0
Sedan 0
Trailer 0
Station Wagon 0
Dump 0
Station Wagon 0
Station Wagon 0
Station Wagon 0
Bus 0
Station Wagon 0
Station Wagon 0
Sedan 0
Sedan 0
Sedan 0
Taxi 0
Dump 0
Dump 0
Taxi 0
Station Wagon 0
Sedan 0
Station Wagon 0
Station Wagon 0
Station Wagon 0
Sedan 0
Sedan 0
Dump 0
Station Wagon 0
Box Truck 0
Sedan 1
Pick-up Truck 0
Taxi 0
Van 0
Bus 0
Sedan 0
Sedan 0
Dump 0
Box Truck 0
Sedan 0
Station Wagon 0
Pick-up Truck 0
Sedan 0
Station Wagon 0
Sedan 0
Sedan 0
Station Wagon 0
Sedan 2
Box Truck 0
Taxi 0
Sedan 0
Station Wagon 0
Van 0
Bike 1
Van 0
Sedan 0
Sedan 0
Sedan 0
Station Wagon 0
Concrete Mixer 0
Bike 0
Sedan 0
Station Wagon 0
Sedan 0
Sedan 0
Sedan 0
Station Wagon 0
Station Wagon 0
Van 0
Sedan 0
Bike 0
Sedan 0
Sedan 0
Sedan 0
Box Truck 0
Sedan 0
Station Wagon 0
Sedan 1
Sedan 0
Dump 0
Sedan 0
Sedan 1
Station Wagon 4
Pick-up Truck 1
Sedan 0
Sedan 0
Trailer 0
Sedan 0
Sedan 0
Taxi 1
Sedan 0
Station Wagon 0
Box Truck 0
Sedan 0
Sedan 0
Sedan 0
Station Wagon 0
Sedan 0
Sedan 1
Taxi 0
Station Wagon 1
Pick-up Truck 0
Taxi 0
Station Wagon 0
Sedan 1
Sedan 0
Sedan 0
Station Wagon 0
Box Truck 0
Van 1
Sedan 0
Sedan 0
Box Truck 0
Sedan 0
Sedan 0
Sedan 0
Station Wagon 2
Sedan 0
Sedan 0
Moped 1
Sedan 1
Taxi Collisions in NYC
Date Time District Collision Cause
2020-01-01 02:24:00 Bronx Driver Inattention/Distraction
2020-01-03 05:20:00 Manhattan Driver Inattention/Distraction
2020-01-04 04:42:00 Manhattan Driver Inattention/Distraction
2020-01-05 06:48:00 Bronx Traffic Control Disregarded
2020-01-05 08:00:00 Queens Passing Too Closely
2020-01-05 17:05:00 Manhattan Other Vehicular
2020-01-07 21:00:00 Brooklyn Passing or Lane Usage Improper
2020-01-08 18:00:00 Brooklyn Driver Inattention/Distraction
2020-01-08 22:39:00 Bronx Other Vehicular
2020-01-09 00:02:00 Manhattan Traffic Control Disregarded
2020-01-09 14:00:00 Manhattan Driver Inattention/Distraction
2020-01-10 13:10:00 Manhattan Unsafe Lane Changing
2020-01-10 20:25:00 Queens Driver Inattention/Distraction
2020-01-10 20:31:00 Manhattan Driver Inattention/Distraction
2020-01-10 23:36:00 Brooklyn Driver Inattention/Distraction
2020-01-11 11:20:00 Queens Driver Inattention/Distraction
2020-01-12 17:05:00 Manhattan Driver Inattention/Distraction
2020-01-12 19:00:00 Queens Driver Inattention/Distraction
2020-01-13 01:40:00 Queens Driver Inexperience
2020-01-14 07:20:00 Bronx Other Vehicular
2020-01-14 13:49:00 Queens Unsafe Lane Changing
2020-01-15 19:32:00 Manhattan Unsafe Lane Changing
2020-01-15 19:45:00 Queens Driver Inattention/Distraction
2020-01-16 11:15:00 Queens Other Vehicular
2020-01-16 12:35:00 Manhattan Driver Inattention/Distraction
2020-01-16 15:29:00 Bronx Unsafe Speed
2020-01-19 05:07:00 Bronx Other Vehicular
2020-01-19 15:30:00 Manhattan Driver Inattention/Distraction
2020-01-21 11:30:00 Manhattan Driver Inattention/Distraction
2020-01-22 04:30:00 Manhattan Other Vehicular
2020-01-22 18:25:00 Manhattan Driver Inattention/Distraction
2020-01-22 19:42:00 Manhattan Driver Inattention/Distraction
2020-01-23 08:22:00 Manhattan Driver Inattention/Distraction
2020-01-24 22:10:00 Manhattan Driver Inattention/Distraction
2020-01-24 23:55:00 Manhattan Passing or Lane Usage Improper
2020-01-25 16:39:00 Bronx Other Vehicular
2020-01-27 15:50:00 Manhattan Driver Inattention/Distraction
2020-01-28 08:10:00 Manhattan Failure to Yield Right-of-Way
2020-01-28 18:41:00 Manhattan Driver Inattention/Distraction
2020-01-29 19:35:00 Manhattan Driver Inattention/Distraction
2020-02-01 00:24:00 Brooklyn Driver Inattention/Distraction
2020-02-01 09:55:00 Manhattan Driver Inattention/Distraction
2020-02-02 02:00:00 Manhattan Passing or Lane Usage Improper
2020-02-02 14:30:00 Manhattan Failure to Yield Right-of-Way
2020-02-02 16:16:00 Manhattan Driver Inattention/Distraction
2020-02-03 23:33:00 Queens Driver Inattention/Distraction
2020-02-04 18:20:00 Manhattan Driver Inexperience
2020-02-05 09:30:00 Bronx Turning Improperly
2020-02-06 12:00:00 Manhattan Driver Inattention/Distraction
2020-02-06 18:06:00 Bronx Driver Inattention/Distraction
2020-02-06 20:55:00 Manhattan Driver Inattention/Distraction
2020-02-06 23:30:00 Manhattan Unsafe Lane Changing
2020-02-07 13:12:00 Manhattan Driver Inattention/Distraction
2020-02-07 19:45:00 Manhattan Other Vehicular
2020-02-08 17:20:00 Bronx Pedestrian/Bicyclist/Other Pedestrian Error/Confusion
2020-02-09 01:15:00 Manhattan Unsafe Lane Changing
2020-02-09 14:55:00 Brooklyn Driver Inattention/Distraction
2020-02-10 01:10:00 Manhattan Driver Inattention/Distraction
2020-02-10 09:00:00 Bronx Other Vehicular
2020-02-12 10:47:00 Manhattan Driver Inattention/Distraction
2020-02-12 21:16:00 Manhattan Driver Inattention/Distraction
2020-02-13 15:10:00 Manhattan Passing or Lane Usage Improper
2020-02-13 20:02:00 Manhattan Driver Inattention/Distraction
2020-02-14 11:53:00 Manhattan Following Too Closely
2020-02-14 15:45:00 Queens Driver Inattention/Distraction
2020-02-15 00:47:00 Manhattan Passing or Lane Usage Improper
2020-02-15 13:46:00 Brooklyn Unsafe Lane Changing
2020-02-19 00:00:00 Manhattan Passing or Lane Usage Improper
2020-02-19 12:30:00 Brooklyn Other Vehicular
2020-02-19 16:55:00 Queens Driver Inattention/Distraction
2020-02-22 21:29:00 Manhattan Other Vehicular
2020-02-24 11:41:00 Manhattan Following Too Closely
2020-02-25 11:39:00 Queens Driver Inattention/Distraction
2020-02-26 08:50:00 Manhattan Driver Inattention/Distraction
2020-02-26 10:30:00 Manhattan Unsafe Lane Changing
2020-02-27 10:15:00 Manhattan Driver Inattention/Distraction
2020-02-27 21:30:00 Manhattan Other Vehicular
2020-02-28 12:41:00 Bronx Driver Inattention/Distraction
2020-02-28 17:30:00 Manhattan Other Vehicular
2020-02-28 20:00:00 Queens Driver Inattention/Distraction
2020-02-29 02:10:00 Brooklyn Driver Inattention/Distraction
2020-02-29 15:53:00 Manhattan Driver Inattention/Distraction
2020-02-29 23:10:00 Manhattan Driver Inattention/Distraction
2020-03-01 01:54:00 Manhattan Driver Inattention/Distraction
2020-03-01 19:18:00 Bronx Unsafe Lane Changing
2020-03-01 20:10:00 Queens Passing or Lane Usage Improper
2020-03-01 22:00:00 Manhattan Driver Inattention/Distraction
2020-03-02 11:31:00 Manhattan Driver Inattention/Distraction
2020-03-02 12:00:00 Manhattan Passenger Distraction
2020-03-02 14:50:00 Manhattan Other Vehicular
2020-03-03 08:40:00 Brooklyn Reaction to Uninvolved Vehicle
2020-03-03 20:25:00 Manhattan Driver Inattention/Distraction
2020-03-04 00:05:00 Queens Passing Too Closely
2020-03-05 11:30:00 Manhattan Passing or Lane Usage Improper
2020-03-06 00:32:00 Manhattan Pedestrian/Bicyclist/Other Pedestrian Error/Confusion
2020-03-06 02:21:00 Manhattan Outside Car Distraction
2020-03-06 21:10:00 Queens Driver Inattention/Distraction
2020-03-06 21:35:00 Manhattan Backing Unsafely
2020-03-07 13:51:00 Manhattan Passing Too Closely
2020-03-08 03:34:00 Manhattan Other Vehicular
2020-03-08 05:22:00 Queens Passing or Lane Usage Improper
2020-03-08 20:00:00 Manhattan Reaction to Uninvolved Vehicle
2020-03-08 21:50:00 Manhattan Driver Inattention/Distraction
2020-03-09 00:29:00 Manhattan Driver Inattention/Distraction
2020-03-09 17:48:00 Bronx Unsafe Lane Changing
2020-03-10 18:46:00 Manhattan Passing or Lane Usage Improper
2020-03-11 12:44:00 Manhattan Unsafe Speed
2020-03-11 16:25:00 Manhattan Driver Inattention/Distraction
2020-03-11 17:00:00 Manhattan Other Vehicular
2020-03-11 22:20:00 Manhattan Driver Inattention/Distraction
2020-03-12 13:40:00 Bronx Driver Inattention/Distraction
2020-03-12 23:56:00 Manhattan Driver Inattention/Distraction
2020-03-13 17:18:00 Manhattan Driver Inattention/Distraction
2020-03-13 18:35:00 Manhattan Driver Inexperience
2020-03-14 10:40:00 Brooklyn Alcohol Involvement
2020-03-14 22:17:00 Bronx Driver Inattention/Distraction
2020-03-15 16:40:00 Queens Driver Inattention/Distraction
2020-03-16 00:44:00 Manhattan Driver Inattention/Distraction
2020-03-20 00:00:00 Manhattan Failure to Yield Right-of-Way
2020-03-20 08:50:00 Manhattan Driver Inattention/Distraction
2020-03-21 15:40:00 Manhattan Following Too Closely
2020-03-21 21:00:00 Bronx Driver Inattention/Distraction
2020-03-22 09:20:00 Bronx Other Vehicular
2020-03-27 17:37:00 Manhattan Driver Inattention/Distraction
2020-03-28 15:00:00 Brooklyn Driver Inattention/Distraction
2020-03-28 22:20:00 Manhattan Turning Improperly
2020-04-14 20:30:00 Brooklyn Driver Inattention/Distraction
2020-04-16 12:25:00 Bronx View Obstructed/Limited
2020-04-30 06:29:00 Manhattan Driver Inattention/Distraction
2020-05-02 14:47:00 Manhattan Driver Inattention/Distraction
2020-05-13 21:30:00 Bronx Other Vehicular
2020-05-13 22:38:00 Brooklyn Unsafe Lane Changing
2020-05-16 17:00:00 Bronx Pedestrian/Bicyclist/Other Pedestrian Error/Confusion
2020-05-18 15:41:00 Bronx Backing Unsafely
2020-05-20 03:43:00 Brooklyn Passing Too Closely
2020-05-22 23:56:00 Brooklyn Driver Inattention/Distraction
2020-05-23 17:30:00 Queens Reaction to Uninvolved Vehicle
2020-05-25 18:55:00 Bronx Other Vehicular
2020-05-31 00:45:00 Queens Other Vehicular
2020-05-31 02:19:00 Brooklyn Other Vehicular
2020-06-13 21:05:00 Manhattan Pedestrian/Bicyclist/Other Pedestrian Error/Confusion
2020-06-16 22:15:00 Manhattan Unsafe Lane Changing
2020-06-25 03:37:00 Manhattan Driver Inattention/Distraction
2020-06-25 21:47:00 Manhattan Driver Inattention/Distraction
2020-07-01 01:35:00 Manhattan Traffic Control Disregarded
2020-07-04 17:08:00 Bronx Driver Inattention/Distraction
2020-07-09 13:20:00 Brooklyn Following Too Closely
2020-07-09 22:08:00 Manhattan Driver Inattention/Distraction
2020-07-11 22:19:00 Manhattan Pedestrian/Bicyclist/Other Pedestrian Error/Confusion
 Vehicle # of Persons Injured
Taxi 3
Pick-up Truck 1
Sedan 0
Sedan 2
Taxi 0
Sedan 0
Sedan 0
Sedan 0
Sedan 0
Sedan 1
Bike 0
Van 0
Bike 1
Taxi 0
Sedan 0
Taxi 1
Sedan 0
Sedan 0
Taxi 1
Sedan 0
Sedan 0
E-Bike 1
Sedan 1
Sedan 0
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Bike 0
Sedan 0
Taxi 0
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Taxi 0
Bike 1
Pick-up Truck 0
Taxi 0
Box Truck 0
Taxi 1
Taxi 0
Taxi 0
Taxi 0
Sedan 0
Sedan 0
Bus 0
Taxi 1
Sedan 0
Taxi 0
Bike 1
Sedan 0
Sedan 0
Taxi 0
Bike 1
Taxi 0
Taxi 0
Taxi 0
Taxi 0
Taxi 0
Pick-up Truck 0
Taxi 0
Sedan 2
Taxi 0
Sedan 0
Taxi 0
Sedan 0
Bike 0
Sedan 0
Sedan 0
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Pick-up Truck 0
Sedan 0
Taxi 0
Sedan 0
Taxi 0
Sedan 0
Sedan 0
Bike 0
Taxi 0
Sedan 0
Pick-up Truck 0
Sedan 0
Taxi 0
Sedan 0
Van 0
Taxi 0
Sedan 0
Taxi 0
Bus 0
Bus 0
Taxi 0
Box Truck 0
Sedan 1
Sedan 0
Sedan 0
Taxi 0
Sedan 0
Taxi 0
Sedan 0
Sedan 1
Sedan 0
Sedan 0
Sedan 0
Sedan 0
Sedan 2
Bike 1
Sedan 0
Taxi 0
Taxi 0
Bike 1
Pick-up Truck 0
E-Bike 2
Sedan 0
Box Truck 0
Bike 1
Sedan 1
Bus 0
Sedan 0
Sedan 1
E-Bike 0
Sedan 0
Taxi 0
Bike 1
E-Scooter 1
Taxi 0
Bike 1
Sedan 3
Sedan 0
Bike 1
Sedan 0
Dump 0
Sedan 1
Sedan 0
Sedan 0
Sedan 0
Sedan 2
Bike 1
Sedan 2
Bike 1
Sedan 1
Sedan 2
E-Bike 1
Sedan 2
Sedan 5
Bike 1
Month Collisions Injury %
Jan 9,506 22.0%
Feb 8,986 22.2%
Mar 7,241 21.5%
Apr 2,598 24.6%
May 3,953 30.5%
Jun 5,012 35.6%
Jul 6,128 34.7%
Aug 5,716 35.0%
Collision Cause Sum of # of Persons Injured
Driver Inattention/Distraction 29
Other Vehicular 8
Traffic Control Disregarded 5
Pedestrian/Bicyclist/Other Pedestrian Error/Confusion 4
Unsafe Lane Changing 3
Driver Inexperience 2
Following Too Closely 2
View Obstructed/Limited 1
Failure to Yield Right-of-Way 1
Turning Improperly 1
Passing or Lane Usage Improper 0
Unsafe Speed 0
Reaction to Uninvolved Vehicle 0
Passing Too Closely 0
Alcohol Involvement 0
Passenger Distraction 0
Outside Car Distraction 0
Backing Unsafely 0
Total Result 56