CRA External Data and Tables

Description

complete the file below

Unformatted Attachment Preview

External Data and Tables Comprehensive Review Activity – Part 1
Files to Download from Blackboard:
Text Files: Import Export Balance.txt; Trade in Goods.prn
Database File: Customer Database.accdb
Tasks:
1.
Delete the empty ‘Import Export 1’ worksheet in this activity file.
Use the File –> Open option to import the ‘Import Export Balance.txt’ tab delimited file into a NEW Excel w
Do not import the file header information (the first 3 rows in the file), indicate that the data has headers, a
Copy the worksheet from the new file into this workbook and rename the new worksheet ‘Import Export 1
Close the new workbook without saving.
2.
Delete the empty ‘Import Export 2’ worksheet in this activity file.
Use the ‘Get and Transform Data’ option, to import the ‘Import Export Balance.txt’ tab delimited file into TH
Do not import the file header information (the first 3 rows in the file), and indicate that the data has heade
Skip importing the ‘Duty’ field, delete any blank columns that display to the right side of the imported data
Place the data on a new worksheet, and rename the new worksheet ‘Import Export 2’
Open the Import Export Balance.txt file – open by (1) locating the file location using File Explorer;
(
Change the value of ‘Balance’ for ‘Agricultural, Forestry & Fishery Products’ to from 1,387 to 1,500, save an
Refresh the data connection (Data –> Refresh All) and ensure that the value for ‘Agricultural, Forestry & Fis
3.
Delete the empty ‘Trade in Goods’ worksheet in this activity file
Using a Legacy Wizard, import the ‘Trade in Goods.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 2 rows in the file), indicate that the data has headers, a
Ensure that the fields are delimited correctly – check all rows of the imported data to ensure the data is div
Correct field headers:
Balance Q1 2023
Place the data on a new worksheet, and rename the new worksheet ‘Trade in Goods’
4.
Using either the ‘Get and Transform Data’ or a Legacy Wizard, import data from ‘Customer Database.accdb
Place the imported data on this worksheet starting in the range B35.
NOTES:
Several data files are necessary for completion of this activity. These data files are available in the acti
When accessing the data file on Blackboard, right-click and save data files, do NOT click to open.
ted file into a NEW Excel workbook. (HINT: change the file type to ‘All Files’ in the Open dialog box)
hat the data has headers, and skip importing the ‘Balance’ field.
worksheet ‘Import Export 1′.
xt’ tab delimited file into THIS workbook.
te that the data has headers.
side of the imported data, and delete any rows with null values.
ng File Explorer;
(2) right-clicking on the file name and selecting the option to open in Note Pad.
om 1,387 to 1,500, save and then close the text file.
Agricultural, Forestry & Fishery Products’ has updated in cell B2 of the ‘Import Export 2’ worksheet.
THIS workbook.
hat the data has headers, and skip importing the ‘Year-to-Date’ fields for each of the three years.
a to ensure the data is divided into fields correctly.
Balance Q3 2023
Balance YTD 2023
Balance Q12024 Balance Q3 2024
Balance YTD 2024
(NOTE: some of the field names may be tru
Customer Database.accdb’ Microsoft access database file (as a Table) , CustomerT table into THIS workbook.
data files are available in the activity portal on Blackboard.
files, do NOT click to open.
n Note Pad.
BalanceQ12025 Balance Q3 2025
ome of the field names may be truncated)
THIS workbook.
Balance YTD 2025
Legacy Wizards can be a
File –> Options –> Data
93992
External Data and Tables Comprehensive Review Activity – Part 2
Files to Download from Blackboard:
Text Files: FINC 3330 WH Salary Data.prn
Tasks:
## Delete the empty ‘Salary 1’ worksheet from this activity file.
Using the Legacy Wizard, import the ‘FINC 3330 WH Salary Data.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 2 rows in the file) and indicate that the data has headers. Import all fields.
Ensure that the fields are delimited correctly. The field headings are as follows:
Employee Name, Employee Status, Salary, Basis, Position
Place the data on a new worksheet, and rename the new worksheet ‘Salary 1’
NOTE: if you use the ‘Get and Transform’ option to import this data, it is necessary to convert the imported data from a table to a data range before moving to Step 2.
## Delete the ‘Salary 2’ worksheet from this activity file.
Copy the ‘Salary 1’ worksheet; rename the new worksheet ‘Salary 2’.
## On the ‘Salary 1’ worksheet, complete the following tasks:
* Freeze panes so that both the column headings (Row 1) and Employee Names (Column A) remain on the screen as you scroll to the right and down.
* Use the Custom Sort Dialog box to sort the data region by ‘Employee Status’ (A to Z) and within ‘Employee Status’ by ‘Salary’ (largest to smallest)
* Filter the records to display only those employees that hold a position of either an ‘ANALYST’ or a ‘ASSISTANT DIRECTOR’
and have a ‘Salary’ greater than $45,000 (apply using a ‘Numbers’ filter instead of picking values from the ‘Salary’ list)
HINT: for sorting and filtering, use options available on the HOME ribbon in the EDITING group
## On the ‘Salary 2’ worksheet, complete the following tasks:
* Convert the data region to an Excel table.
You might receive a warning (shown to the right) … click Yes … this occurs because Excel detects the connection to an external data source
* Remove (duplicate) records that match on all fields
* Insert a column to the right of the ‘Salary’ field … to the right of column C, name the field ‘Avg Salary by Position’
* In this new column, insert the AFVERAGEIF or AVERAGEIFS function with STRUCTURAL references that averages ‘Salary’ for each ‘Position’
* Use the Format dialog box to format this new field as currency with a ‘$’ sign, comma separator, and zero decimal places of precision.
93992
External Data and Tables Comprehensive Review Activity – Part 3
Files to Download from Blackboard:
Text Files: FINC 3330 WH Salary Data2.prn
NOTE: This is a file that contains salary data for White House employees
Tasks:
## Delete the empty ‘Salary 3’ worksheet
Using the Legacy Wizard, import the ‘FINC 3330 WH Salary Data2.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 3 rows in the file), and indicate that the data has headers.
Ensure that the fields are delimited correctly. The field headings are as follows:
Employee_Name, Employee_Status, Salary, Pay_Basis, Position
Skip importing the ‘Pay_Basis’ field.
Place the data on a new worksheet, and rename the new worksheet ‘Salary 3’
NOTE: if you use the ‘Get and Transform’ option to import this data, it is necessary to convert the imported data from a table to a data range before creating the pivot table.
## Using the salary data, create a pivot table with the following specifications:
* Place the pivot table on the ‘Salary 3’ worksheet starting in the range H1 (ensure that you leave sufficient room between your data range and the pivot table)
* The pivot table should average ‘Salary’ (this is a summary field) by ‘Employee status’ (this is a row field); the user should be able to filter the table by ‘Position’ (this is a filter field)
* Format the summation field as currency with no decimal places of precision and apply the custom name ‘Average Salary by Status’
Use the pivot table to answer the following questions – place your response in the corresponding cell in column H:
a) What Employee_Status has the largest average salary?
b) Apply a filter to the ‘Position’ field to determine the average salary of the positions ‘Analyst’ and ‘Coordinator’ (examined together)
Remove the pivot table filter used to answer part b.
##
Modify the pivot table by adding a summary field to count the number of individuals at each position; name this field ‘Position Count’
Use the pivot table to answer the following questions:
a) How many individuals occupy positions in the White House?
b) Apply a filter to the ‘Position field to determine the number of executives who hold positions of Director or Deputy Director.
Remove the pivot table filter used to answer part b.
##
Modify the pivot table by adding a show-as field that shows the percentage each Employee_Status comprises of the total number of
White House workers. Add the Custom Name ‘Percent of Workers’.
HINT: Add the ‘Position’ field to the ‘Values’ area. On the pivot table (not the Show Fields dialog box) right-click on the new field and select the ‘Show Value As’ option.
Use the pivot table to answer the following questions:
a) What percent of White House workers are attributable to part-time employees?
##
Modify the pivot table by adding a show-as field that ranks (smallest to largest) the salary by Employee_Status.
Custom Name the field ‘Salary Rank’.
HINT: Add the ‘Employee_Status’ field to the ‘Values’ area. On the pivot table (not the Show Fields dialog box) right-click on the new field and select the ‘Show Value As’ option.
Use the pivot table to answer the following questions:
b) What Employee_Status has the lowest (smallest) salary rank?
##
Based on the ‘Salary_Table’ pivot table, create the pivot pie chart shown to the right.
The chart should have the following properties
Average Salary by Status
Chart Title:
Remove legend
Add Data labels to display information as shown in the image
Add a slicer to the pivot chart (based on Position)
Use the pivot table and slicer to answer the following questions:
a) What is the ‘Employee’ average salary for the position ‘Policy Advisor’?
b) Bring the ‘Percent of Workers’ tab to the beginning (right click on tab).
What percent of ‘Assistant to the Executive Clerk’ hold an ‘Executive’ position?
– It will be necessary to addNOTES:
data labels to the chart when viewing the ‘Percent of Workers’ tab
– Assistant to the Executive Clerk is different than ‘Assistant Executive Clerk’
– Do not remove the slicer after completing part b.
93992
93992
93992
93992
93992
93992
93992
“Exports, Imports, and Balance of Goods by Selected NAICS-Based Product Code”
Not Seasonally Adjusted
In millions of dollars.
NAICS-Based Product Balance Exports Imports Duty
“Agricultural, Forestry & Fishery Products” “1,387.0” “40,026.5” “38,639.6” 141.5
“Agricultural Products, Total” “11,126.7” “35,240.5” “24,113.8” 63.3
Livestock And Livestock Products “-2,870.3” “1,069.5” “3,939.8” 2.1
“Forestry Products, Nspf” -325.1 “1,230.2” “1,555.3” 2.9
“Fish, Fresh Or Chilled; And Other Marine Products” “-6,544.3” “2,486.3” “9,030.6” 73.2
“Mining, Total” “-14,532.7” “64,264.0” “78,796.7” 78.4
Oil And Gas “-22,270.2” “52,757.5” “75,027.6” 74.0
Minerals And Ores “7,737.5” “11,506.6” “3,769.1” 4.3
Food And Kindred Products “-2,822.0” “37,334.0” “40,156.0” 691.4
Beverages And Tobacco “-11,087.3” “4,301.0” “15,388.3” 64.1
Textile And Fabrics 205.4 “5,077.8” “4,872.4” 331.4
Textile Mill Products “-12,076.2” “1,536.2” “13,612.4” 959.0
Apparel And Accessories “-50,248.8” “1,793.4” “52,042.2” “6,983.3”
Leather And Allied Products “-21,483.6” “1,691.3” “23,174.9” “2,757.4”
Wood Products “-6,901.8” “3,950.5” “10,852.2” 300.0
Paper Products 744.0 “13,515.0” “12,771.0” 227.7
“Printing, Publishing & Similar Products” -881.9 “2,537.2” “3,419.1” 42.6
Petroleum And Coal Products “9,804.5” “53,428.2” “43,623.7” 100.9
Chemicals “-38,897.4” “116,417.9” “155,315.4” “1,527.4”
Plastic And Rubber Products “-16,789.5” “18,117.1” “34,906.7” “1,617.5”
Nonmetallic Mineral Products “-7,766.9” “6,399.3” “14,166.2” 773.7
Primary Metal Products “-23,368.3” “27,018.4” “50,386.6” “3,081.3”
Fabricated Metal Products “-20,564.2” “24,394.2” “44,958.4” “2,383.8”
“Machinery, Except Electrical” “-42,409.0” “70,212.6” “112,621.5” “3,182.6”
Computers and Electronic Products “-152,695.7” “65,289.9” “217,985.7” “2,521.7”
“Electrical Equipment, Appliances and Components” “-43,772.2” “26,746.5” “70,518.6” “3,932.7”
Transportation Equipment “-97,387.9” “147,376.1” “244,764.0” “3,816.0”
Furniture and Fixtures “-22,278.6” “2,728.7” “25,007.4” “1,568.4”
Miscellaneous Manufactured Commodities “-45,983.8” “26,985.2” “72,969.0” 790.5
Special Classification Provisions “-27,313.5” “48,582.5” “75,896.0” 37.8
Scrap & Waste “7,513.2” “11,308.4” “3,795.2” 2.1
Used Or Second-Hand Merchandise (1) “2,593.2” “9,658.1” “7,064.9” 10.6
Goods Returned Or Reimported “-51,752.7” 24.5 “51,777.1” 5.0
“Special Classification Provision, Nspf” “14,332.8” “27,591.6” “13,258.8” 20.0
NAICS-Based Product
Agricultural, Forestry & Fishery Products
Agricultural Products, Total
Livestock And Livestock Products
Forestry Products, Nspf
Fish, Fresh Or Chilled; And Other Marine Products
Mining, Total
Oil And Gas
Minerals And Ores
Food And Kindred Products
Beverages And Tobacco
Textile And Fabrics
Textile Mill Products
Apparel And Accessories
Leather And Allied Products
Wood Products
Paper Products
Printing, Publishing & Similar Products
Petroleum And Coal Products
Chemicals
Plastic And Rubber Products
Nonmetallic Mineral Products
Primary Metal Products
Fabricated Metal Products
Machinery, Except Electrical
Computers and Electronic Products
Electrical Equipment, Appliances and Components
Transportation Equipment
Furniture and Fixtures
Miscellaneous Manufactured Commodities
Special Classification Provisions
Scrap & Waste
Used Or Second-Hand Merchandise (1)
Goods Returned Or Reimported
Special Classification Provision, Nspf
Balance
Exports
Imports
Duty
1387 40026.5
38639.6 141.5
11126.7 35240.5
24113.8
63.3
-2870.3
1069.5
3939.8
2.1
-325.1
1230.2
1555.3
2.9
-6544.3
2486.3
9030.6
73.2
-14532.7
64264
78796.7
78.4
-22270.2 52757.5
75027.6
74
7737.5 11506.6
3769.1
4.3
-2822
37334
40156 691.4
-11087.3
4301
15388.3
64.1
205.4
5077.8
4872.4 331.4
-12076.2
1536.2
13612.4
959
-50248.8
1793.4
52042.2 6983.3
-21483.6
1691.3
23174.9 2757.4
-6901.8
3950.5
10852.2
300
744
13515
12771 227.7
-881.9
2537.2
3419.1
42.6
9804.5 53428.2
43623.7 100.9
-38897.4 116417.9 155315.4 1527.4
-16789.5 18117.1
34906.7 1617.5
-7766.9
6399.3
14166.2 773.7
-23368.3 27018.4
50386.6 3081.3
-20564.2 24394.2
44958.4 2383.8
-42409 70212.6 112621.5 3182.6
-152695.7 65289.9 217985.7 2521.7
-43772.2 26746.5
70518.6 3932.7
-97387.9 147376.1
244764
3816
-22278.6
2728.7
25007.4 1568.4
-45983.8 26985.2
72969 790.5
-27313.5 48582.5
75896
37.8
7513.2 11308.4
3795.2
2.1
2593.2
9658.1
7064.9
10.6
-51752.7
24.5
51777.1
5
14332.8 27591.6
13258.8
20
Column1
_1
_2
_3
_4
_5
_6
_7
_8
_9
_10
_11
_12
_13
_14
_15
_16
CustomerID
FirstName
1 Richard
2 Joe
3 Suzan
4 Alan
5 Anna
6 Ronald
7 Donald
8 Benny
9 Peter
10 Barbra
11 Joe
LastName
Rost
Smith
Jones
Watson
Picore
Simms
Barker
Sperduti
Smith
Peters
Jones
CompanyName
Access Learning Zone
XYZ Corp
123 Packing
Forestry Services
Pharmacom
LMNOP Wholesale
GamerSkum Inc.
Euro Real Estate Inc.
XYZ Corp
XYZ Corp
Willians Inc
Address
PO Box 101
101 Main St
101 Main St
202a South Park Ave
1020 Middle Street #2
14 Homer Ave
1010 Yonge St
Via Appia 85
Box 86
5 Oak Drive
123 Elk Ave
City
State
Amherst
NY
Buffalo
NY
Long Beach CA
Toledo
OH
Houston
TX
Edmonton AB
Montrose IA
Rome
IL
Huntsville TX
Burlington IA
Buffalo
NY
PostalCode
14226
14220
90802
43614
77007
11569
52627
00122
77341
52601
14222
Phone
NumEmployees
DiscountRate
7165557777
2
0.5
7165553434
500
0.1
5624365146
25
0.1
4193895477
16
0
7132364479
3500
0
7807465468
30
0
4164415555
900
0
7167267262
10
0.2
5645597777
1
0.5
8292827288
1
0
5495465466
1
0
CustomerSince
CreditLimit
1/1/2012 0:00
2000
3/1/1990 0:00
500
1/2/1998 0:00
1500
2/1/1994 0:00
1000
9/8/2002 0:00
1700
4/3/1992 0:00
50
3/1/1998 0:00
250
4/6/1999 0:00
1600
4/4/2000 0:00
500
5/1/2001 0:00
50
6/1/2004 0:00
500
External Data and Tables Comprehensive Review Activity – Part 1
Files to Download from Blackboard:
Text Files: Import Export Balance.txt; Trade in Goods.prn
Database File: Customer Database.accdb
Tasks:
1.
Delete the empty ‘Import Export 1’ worksheet in this activity file.
Use the File –> Open option to import the ‘Import Export Balance.txt’ tab delimited file into a NE
Do not import the file header information (the first 3 rows in the file), indicate that the data has
Copy the worksheet from the new file into this workbook and rename the new worksheet ‘Impo
Close the new workbook without saving.
2.
Delete the empty ‘Import Export 2’ worksheet in this activity file.
Use the ‘Get and Transform Data’ option, to import the ‘Import Export Balance.txt’ tab delimited
Do not import the file header information (the first 3 rows in the file), and indicate that the data
Skip importing the ‘Duty’ field, delete any blank columns that display to the right side of the imp
Place the data on a new worksheet, and rename the new worksheet ‘Import Export 2’
Open the Import Export Balance.txt file – open by (1) locating the file location using File Explorer
Change the value of ‘Balance’ for ‘Agricultural, Forestry & Fishery Products’ to from 1,387 to 1,50
Refresh the data connection (Data –> Refresh All) and ensure that the value for ‘Agricultural, Fo
3.
Delete the empty ‘Trade in Goods’ worksheet in this activity file
Using a Legacy Wizard, import the ‘Trade in Goods.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 2 rows in the file), indicate that the data has
Ensure that the fields are delimited correctly – check all rows of the imported data to ensure the
Correct field headers:
Place the data on a new worksheet, and rename the new worksheet ‘Trade in Goods’
4.
Using either the ‘Get and Transform Data’ or a Legacy Wizard, import data from ‘Customer Data
Place the imported data on this worksheet starting in the range B35.
NOTES:
Several data files are necessary for completion of this a
When accessing the data file on Blackboard, right-click
rt Balance.txt’ tab delimited file into a NEW Excel workbook. (HINT: change the file type to ‘All Files’ in the Open dialog box)
ws in the file), indicate that the data has headers, and skip importing the ‘Balance’ field.
ok and rename the new worksheet ‘Import Export 1’.
‘Import Export Balance.txt’ tab delimited file into THIS workbook.
ws in the file), and indicate that the data has headers.
s that display to the right side of the imported data, and delete any rows with null values.
w worksheet ‘Import Export 2′
cating the file location using File Explorer;
(2) right-clicking on the file name and selecting the option to open in Note
& Fishery Products’ to from 1,387 to 1,500, save and then close the text file.
ensure that the value for ‘Agricultural, Forestry & Fishery Products’ has updated in cell B2 of the ‘Import Export 2’ worksheet.
fixed-width text file into THIS workbook.
ws in the file), indicate that the data has headers, and skip importing the ‘Year-to-Date’ fields for each of the three years.
rows of the imported data to ensure the data is divided into fields correctly.
Balance Q1 2023
Balance Q3 2023
Balance YTD 2023
Balance Q12024
Wizard, import data from ‘Customer Database.accdb’ Microsoft access database file (as a Table) , CustomerT table into THIS wo
sary for completion of this activity. These data files are available in the activity portal on Blackboard.
le on Blackboard, right-click and save data files, do NOT click to open.
s’ in the Open dialog box)
the option to open in Note Pad.
mport Export 2′ worksheet.
each of the three years.
Balance Q3 2024
BalanceBalanceQ12025Balance
YTD 2024
Q3 2025
Balance YTD 2025
(NOTE: some of the field names may be truncated)
ustomerT table into THIS workbook.
Legacy Wizards can be added to the ‘Data’ ribbon using the following option se
File –> Options –> Data –> Place check marks in front of the desired wizards
93992
External Data and Tables Comprehensive Review Activity – Part 2
Files to Download from Blackboard:
Text Files: FINC 3330 WH Salary Data.prn
Tasks:
## Delete the empty ‘Salary 1’ worksheet from this activity file.
Using the Legacy Wizard, import the ‘FINC 3330 WH Salary Data.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 2 rows in the file) and indicate that the data has headers. Import all fields.
Ensure that the fields are delimited correctly. The field headings are as follows:
Employee Name, Employee Status, Salary, Basis, Position
Place the data on a new worksheet, and rename the new worksheet ‘Salary 1’
NOTE: if you use the ‘Get and Transform’ option to import this data, it is necessary to convert the imported data from a table to a data range before moving to Step 2.
## Delete the ‘Salary 2’ worksheet from this activity file.
Copy the ‘Salary 1’ worksheet; rename the new worksheet ‘Salary 2’.
## On the ‘Salary 1’ worksheet, complete the following tasks:
* Freeze panes so that both the column headings (Row 1) and Employee Names (Column A) remain on the screen as you scroll to the right and down.
* Use the Custom Sort Dialog box to sort the data region by ‘Employee Status’ (A to Z) and within ‘Employee Status’ by ‘Salary’ (largest to smallest)
* Filter the records to display only those employees that hold a position of either an ‘ANALYST’ or a ‘ASSISTANT DIRECTOR’
and have a ‘Salary’ greater than $45,000 (apply using a ‘Numbers’ filter instead of picking values from the ‘Salary’ list)
HINT: for sorting and filtering, use options available on the HOME ribbon in the EDITING group
## On the ‘Salary 2’ worksheet, complete the following tasks:
* Convert the data region to an Excel table.
You might receive a warning (shown to the right) … click Yes … this occurs because Excel detects the connection to an external data source
* Remove (duplicate) records that match on all fields
* Insert a column to the right of the ‘Salary’ field … to the right of column C, name the field ‘Avg Salary by Position’
* In this new column, insert the AFVERAGEIF or AVERAGEIFS function with STRUCTURAL references that averages ‘Salary’ for each ‘Position’
* Use the Format dialog box to format this new field as currency with a ‘$’ sign, comma separator, and zero decimal places of precision.
93992
External Data and Tables Comprehensive Review Activity – Part 3
Files to Download from Blackboard:
Text Files: FINC 3330 WH Salary Data2.prn
NOTE: This is a file that contains salary data for White House employees
Tasks:
## Delete the empty ‘Salary 3’ worksheet
Using the Legacy Wizard, import the ‘FINC 3330 WH Salary Data2.prn’ fixed-width text file into THIS workbook.
Do not import the file header information (the first 3 rows in the file), and indicate that the data has headers.
Ensure that the fields are delimited correctly. The field headings are as follows:
Employee_Name, Employee_Status, Salary, Pay_Basis, Position
Skip importing the ‘Pay_Basis’ field.
Place the data on a new worksheet, and rename the new worksheet ‘Salary 3’
NOTE: if you use the ‘Get and Transform’ option to import this data, it is necessary to convert the imported data from a table to a data range before creating the pivot table.
## Using the salary data, create a pivot table with the following specifications:
* Place the pivot table on the ‘Salary 3’ worksheet starting in the range H1 (ensure that you leave sufficient room between your data range and the pivot table)
* The pivot table should average ‘Salary’ (this is a summary field) by ‘Employee status’ (this is a row field); the user should be able to filter the table by ‘Position’ (this is a filter field)
* Format the summation field as currency with no decimal places of precision and apply the custom name ‘Average Salary by Status’
Use the pivot table to answer the following questions – place your response in the corresponding cell in column H:
a) What Employee_Status has the largest average salary?
b) Apply a filter to the ‘Position’ field to determine the average salary of the positions ‘Analyst’ and ‘Coordinator’ (examined together)
Remove the pivot table filter used to answer part b.
##
Modify the pivot table by adding a summary field to count the number of individuals at each position; name this field ‘Position Count’
Use the pivot table to answer the following questions:
a) How many individuals occupy positions in the White House?
b) Apply a filter to the ‘Position field to determine the number of executives who hold positions of Director or Deputy Director.
Remove the pivot table filter used to answer part b.
##
Modify the pivot table by adding a show-as field that shows the percentage each Employee_Status comprises of the total number of
White House workers. Add the Custom Name ‘Percent of Workers’.
HINT: Add the ‘Position’ field to the ‘Values’ area. On the pivot table (not the Show Fields dialog box) right-click on the new field and select the ‘Show Value As’ option.
Use the pivot table to answer the following questions:
a) What percent of White House workers are attributable to part-time employees?
##
Modify the pivot table by adding a show-as field that ranks (smallest to largest) the salary by Employee_Status.
Custom Name the field ‘Salary Rank’.
HINT: Add the ‘Employee_Status’ field to the ‘Values’ area. On the pivot table (not the Show Fields dialog box) right-click on the new field and select the ‘Show Value As’ option.
Use the pivot table to answer the following questions:
b) What Employee_Status has the lowest (smallest) salary rank?
##
Based on the ‘Salary_Table’ pivot table, create the pivot pie chart shown to the right.
The chart should have the following properties
Average Salary by Status
Chart Title:
Remove legend
Add Data labels to display information as shown in the image
Add a slicer to the pivot chart (based on Position)
Use the pivot table and slicer to answer the following questions:
a) What is the ‘Employee’ average salary for the position ‘Policy Advisor’?
b) Bring the ‘Percent of Workers’ tab to the beginning (right click on tab).
What percent of ‘Assistant to the Executive Clerk’ hold an ‘Executive’ position?
– It will be necessary to addNOTES:
data labels to the chart when viewing the ‘Percent of Workers’ tab
– Assistant to the Executive Clerk is different than ‘Assistant Executive Clerk’
– Do not remove the slicer after completing part b.
93992
93992
U.S. Trade in
Goods by Selecte d Countries and Areas:
2024
In millions of
dollars.
Country
Balance Q1 2023 Balance Q3 2023
Balance YTD 2023 Balance Q12024
Canada
-3,486
-2,871
-13,040
23,367
Mexico
-7,976
-9,852
-58,314
22,123
Austria
102
-1,011
-3,674
1,201
Belgium
828
952
8,391
2,613
Czech Republic
-272
-215
-1,465
219
Finland
-367
-439
-3,066
226
France
-2,386
-1,603
-12,619
2,552
Germany
-6,973
-4,800
-38,864
4,430
Hungary
-329
-276
-1,720
146
Ireland
-4,514
-4,268
-29,954
728
Italy
-3,697
-2,567
-19,901
1,784
Netherlands
1,355
1,983
13,710
3,731
Poland
-344
-29
-1,448
429
Spain
-481
-128
-1,159
1,172
Sweden
-735
-724
-4,821
319
United Kingdom
-281
562
2,841
5,329
Other
-2,028
-1,449
-10,225
979
Norway
-173
-276
-1,721
316
Russia
-1,564
-1,462
-8,796
402
Switzerland
-2,588
-1,794
-14,584
1,217
Other Europe
446
363
1,335
1,841
Euro Area
-17,145
-12,524
-92,235
19,025
Australia
1,210
1,192
7,826
2,204
China
-32,775
-29,968
-199,819
8,734
Hong Kong
1,995
2,537
15,656
2,294
Indonesia
-986
-789
-6,827
538
Japan
-6,331
-5,711
-42,373
6,576
Korea, South
-1,831
-1,132
-12,745
4,683
Malaysia
-2,223
-2,037
-14,478
1,106
Philippines
-483
-355
-2,375
682
Singapore
-331
33
1,855
2,376
Taiwan
-2,255
-1,699
-12,924
2,483
Other
-21
-4
214
395
Argentina
225
450
1,986
745
Brazil
506
1,273
5,779
3,588
Chile
529
455
2,354
1,501
Colombia
-172
-16
-324
1,128
Other
1,721
2,892
18,400
6,011
Nigeria
-246
-167
-689
248
Saudi Arabia
-140
-307
-1,082
933
Venezuela
Other
Algeria
Egypt
South Africa
Other
India
Thailand
Other
50
236
-28
102
-136
-234
-2,421
-1,875
-5,202
67
199
-213
250
-180
-129
-899
-1,559
-4,380
-978
2,425
-1,312
1,572
-1,190
-1,609
-13,546
-11,256
-38,241
70
2,326
151
461
481
1,128
2,532
983
4,359
Balance Q3 2024 Balance YTD 2024 BalanceQ12025Balance Q3 2025 Balance YTD 2025
24,884
171,956
26,853 27,755
184,996
20,655
151,397
30,098 30,507
209,711
346
4,109
1,099
1,356
7,783
2,739
20,732
1,785
1,786
12,341
232
1,694
491
447
3,158
150
1,086
592
589
4,152
3,118
21,985
4,938
4,722
34,603
4,780
34,807
11,404
9,581
73,671
155
1,118
475
431
2,838
767
5,155
5,242
5,036
35,109
2,077
13,720
5,481
4,643
33,622
4,431
29,820
2,376
2,448
16,110
616
3,509
773
646
4,958
1,271
8,579
1,652
1,399
9,738
318
2,398
1,053
1,042
7,219
5,377
39,447
5,610
4,815
36,607
1,043
7,698
3,007
2,492
17,923
482
2,459
489
758
4,180
553
3,667
1,966
2,015
12,464
1,684
10,651
3,805
3,478
25,235
1,592
10,513
1,395
1,230
9,177
20,347
144,618
36,170 32,871
236,852
2,063
14,195
994
871
6,369
9,035
60,734
41,509 39,002
260,553
2,848
18,203
299
311
2,547
738
4,631
1,524
1,527
11,458
6,210
43,420
12,907 11,921
85,793
5,092
32,965
6,514
6,224
45,710
1,061
7,308
3,330
3,097
21,786
700
4,990
1,165
1,055
7,365
2,647
18,029
2,707
2,614
16,174
2,562
17,830
4,737
4,262
30,753
402
2,907
416
406
2,693
808
5,031
520
358
3,046
3,896
24,380
3,082
2,624
18,601
1,214
8,915
972
759
6,562
1,263
8,678
1,300
1,279
9,002
6,686
47,114
4,291
3,794
28,714
286
1,816
494
453
2,505
980
7,805
1,073
1,287
8,887
93
2,679
123
582
446
1,575
3,243
1,036
3,620
846
18,377
642
3,464
3,163
8,579
20,883
7,571
25,236
21
2,089
179
359
617
1,361
4,953
2,858
9,560
26
2,480
336
332
625
1,705
4,142
2,596
8,000
1,825
15,952
1,954
1,892
4,354
10,188
34,429
18,827
63,478
93992
This file cotains data
File creation October,
Employee Name
Abrams, Adam W.
Adams, Ian H.
Agnew, David P.
Albino, James
Aldy, Jr., Joseph E.
Alley, Hilary J.
Amorsingh, Lucius L.
Anderson, Amanda D.
Anderson, Charles D.
Andrias, Kate E.
Anello, Russell M.
Asen, Jonathan D.
Attili, Candice L.
Axelrod, David M.
Ayling, Lindsay A.
Baggetto, Maude L.
Baldassaro, Frederick
Barnes, Melody C.
Baskerville, Jr., Ches
Bassin, Ian M.
Bates, Andrew J.
Bauer, Nicholas T.
Bauer, Robert F.
Becnel, Bradley D.
Bedingfield, Katherine
Beinart, Diana H.
Belive, Lauren E.
Beliveau, Emmett S.
Bernard, Sarah C.
Beverly, Alaina C.
Bhowmik, Rachana
Binns, Mary U.
Birdsall, Kristen S.
Bisi, Rachel I.
Blake, Michael A.
Block, Michael R.
Blount, Patricia H.
Bond, Brian K.
Bookey-Baker, Natalie
Branch, Katherine Y.
de scribing White House Emplo
20 19 by Ma ry Funck
Employee Status Salary
Employee 66300Per
Employee 45000Per
Employee 93840Per
Employee 91800Per
Employee 130500Per
Employee 42000Per
Employee 56092Per
Employee 60000Per
Employee 51000Per
Employee 130500Per
Employee 66300Per
Employee 42000Per
Employee 58511Per
Employee 172200Per
Employee 42000Per
Employee 42000Per
J. Detailee 122744Per
Employee 172200Per
ter Employee 47532Per
Employee 99000Per
Employee 42000Per
Employee 55000Per
Employee 172200Per
Employee 55000Per
J. Employee 90000Per
Employee 130500Per
Employee 45000Per
Employee 130000Per
Employee 70000Per
Employee 71400Per
Employee 100000Per
Employee 70126Per
Employee 45900Per
Employee 42000Per
Employee 61200Per
Employee 42000Per
Employee 44402Per
Employee 93840Per
F. Employee 45000Per
Employee 61200Per
Brennan, John O.
Bressler, Steven Y.
Brewer, Anne P.
Brooke, Mary J.
Brown, Elizabeth M.
Browner, Carol M.
Brundage, Amy J.
Brush, Michael P.
Buffett, Howard W.
Burman, Kendall C.
Burnough, Erinn J.
Burton, William A.
Campbell, Frances L.
Campbell, Grant E.
Campbell, Nnaji-Semayi
Campos, David R.
Campoverdi, Alejandra
Camuͱez, Michael C.
Canery, Beverly D.
Carden, Meredith M.
Cardwell, Rebecca A.
Carrier, Ethan A.
Carter, Rhonda M.
Castillo, Laura M.
Chaifetz, Samantha L.
Chambers, Caroline M.
Chen, Margaret C.
Cherlin, Reid H.
Chhabra, Anand
Chiodo, Theodore A.
Cizner, Jennifer C.
Clarke, Kay F.
Claude, Lilia H.
Cobbina, Kwesi A.
Cohen, Elizabeth S
Cokley, Rebecca A.
Colvin, Nicholas M.
Conrad, Shasti K.
Corrigan, Tara C.
Coven, Martha B.
Crowley, Jeffrey S.
Crutchfield, Danielle
Cuba, Juan C.
Employee 172200Per
Detailee 148510Per
Employee 45000Per
Employee 89033Per
Employee 172200Per
Employee 172200Per
Employee 78000Per
Employee 56100Per
Detailee 51630Per
Employee 130500Per
Employee 66300Per
Employee 113000Per
Employee 100904Per
Employee 45900Per
K. Employee 42000Per
Employee 42000Per
M. Employee 60000Per
Employee 140000Per
Employee 58511Per
Employee 42000Per
Employee 43656Per
Detailee 94969Per
Employee 57000Per
Employee 42000Per
Detailee 123758Per
Employee 120000Per
Employee 0Per
Employee 78000Per
Employee 42000Per
Employee 60000Per
Employee 85680Per
Employee 64439Per
Employee 58511Per
Employee 42000Per
Employee 45900Per
Employee 72000Per
Employee 63240Per
Employee 42000Per
Employee 45000Per
Employee 120000Per
Employee 102000Per
M. Employee 130000Per
Employee 42000Per
Cuellar, Martin E.
Cunningham, Jason F.
Cunningham, Vinson T.
Cusack, David J.
Cushman, Chase M.
Cutte