3rd excel exam s2021 |

Uncategorized

After graduating from college, you and three of your peers founded the software company TechStore Unlimited (TSU). TSU provides an online market place that fosters business to business (B2B), business to consumer (B2C), and consumer to consumer sales (C2C). As one of the company’s principal owners, you have decided to compile a report that details all aspects of the business, including: employee payroll, facility management, sales data, and product inventory. To complete the task you will duplicate existing formatting, import data from an Access database, utilize various conditional logic functions, complete an amortization table, visualize data with PivotTables and Power Maps, connect and transform several external data sources, and lastly you will inspect the workbook for issues.

Start Excel. Open 3rd Excel Exam SU1 2021.xlsx. Grader has automatically added your last name to the beginning of the filename.

Create a Data Validation list in cell J7 based on the employee IDs located in the range A6:A25. Add the Input Message Select Employee ID and use the Stop Style Error Alert.
Hint: Data Validation is located on the Data tab in the Data Tools group.

In cell K7, enter an INDEX functions with two nested MATCH functions. The first MATCH function will look up the row number of the employee number in cell J7 in the range A6:A25. The second MATCH function will look up the column number of the value in cell K6 in the range B5:H5. Note K6 contains a validation list that can be used to select various lookup categories.

Hint: The arguments of the INDEX are array (B6:H25), Row Number,Column Number. The purpose of the two MATCH functions is to look up the ROW number and COLUMN number. I suggest you do the first MATCH function to ensure it works and then nest it inside of the INDEX function. Then nest the second MATCH function in the INDEX function. To test the INDEX function, use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6. Did it look up the correct salary for the specified employee number?

Click cell K11 and type FT. Click cell A28 and type Full Time Employees.

Use the Format Painter to apply the formatting from the cell A3 to the range A28:B28.

Hint: The Format Painter is located on the
Home tab in the Clipboard group.

Use Advanced Filtering to restrict the data to only display FT employees based on the criteria in the range K10:K11. Place the results in cell A29.

Hint: Advanced Filtering is located on the Data tab in the Sort & Filter group.

Enter a database function (DCOUNTA) in cell K18 to determine the total number of FT employees. To complete the function, use the range A5:H25 as the database argument, cell E5 for the field, and the range K10:K11 for the criteria.

Hint: To access DATABASE functions, click insert function, and select Database from the function category menu.

Enter a database function in cell K19 (DSUM) to determine the total value of FT employee salaries. To complete the function, use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

Hint: To access DATABASE functions, click insert function, and select Database from the function category menu.

Enter a database function (DAVERAGE) in cell K20 to determine the average FT employee salary. To complete the function, use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

Hint: To access DATABASE functions, click insert function, and select Database from the function category menu.

Enter a database function in cell K21(DMAX) to determine the highest FT salary. To complete the function, use the range A5:H25 as the database argument, cell H5 for the field, and the range K10:K11 for the criteria.

Hint: To access DATABASE functions, click insert function, and select Database from the function category menu.

Format the range K19:K21 with Currency Number Format.

Hint: Currency Number Format is located on the
Home tab in the Number Group.

Use PowerQuery to connect and open the Orders table in the eApp_Cap_Orders.accdb database. Use the Query editor to format column A with Date number format and load the table. Rename the worksheet Orders.
Hint: Data can be imported using Get & Transform data tools located on the Data tab in the Get & Transform data group.

Adapt the previous step to connect and load the Warehouse table.

Hint: Data can be imported using Get & Transform data tools located on the Data tab in the Get & Transform data group.

Connect to, but don’t load the Inventory table from the eApp_Cap_Orders.accdb database.

Create the following relationships.

  Relationship 1
  Table Name
  Inventory

Column (Foreign)
Warehouse

Table
Warehouse

Column (Primary)
Warehouse

Relationship 2

Table
Orders

Column (Foreign)

  Item_Number
  Table
  Inventory

Column (Primary)

Item_Number

Hint: Relationships are located on the Data tab in the Data Tools group.

Use PowerPivot to create a blank   PivotTable on a new worksheet. Add the following fields to the PivotTable.
  Rows
  Warehouse: Location
  Warehouse: Warehouse
  Inventory:
  Item_Number
  Values
  Inventory:
  Current_InventoryInventory:
  Total_Value

Insert a Slicer based on Warehouse. Place the upper left corner of the Slicer inside the borders of cell F3.

Hint: Slicers are located on the Insert tab in the Filters group.

Create a 3D PowerMap that displays the location of all warehouses based on the City geographic type. Rename the worksheet Inventory.
Hint: 3D Maps is located on the Insert tab in the Tours group.

Save and close the 3rd Excel Exam SU1 2021. Go to the Test Presentation Window. Upload the exam and submit it for grading

Continue to order Get a quote

July 1, 2023

61. Which of the following statements regarding intermolecular forces is incorrect?

A)Hydrogen bonding is the strongest of the intermolecular forces.

B)London dispersion forces increase with increasing molecular size.

C)Dipole-dipole forces are present in all atoms and molecules.

D)In order for a pure substance to participate in hydrogen bonding, it must have hydrogen bonded to F, O, or N.

E)Intermolecular forces are much weaker than bonding forces.

62. Which of the following statements regarding intermolecular forces is incorrect?

A)Hydrogen bonding is the strongest of the intermolecular forces for substances of similar molar mass.

B)London dispersion forces are present in all atoms and molecules.

C)Dipole-dipole forces increase with increasing molecular size.

D)Bonding forces are much stronger than intermolecular forces.

E)Hydrogen bonding is the reason for the abnormally high boiling point of water.

63. Which choice correctly lists the intermolecular forces present in CH3NH2?

A)London forces only

B)dipole-dipole forces only

C)London forces, dipole-dipole forces, and hydrogen bonding

D)dipole-dipole forces and hydrogen bonding

E)hydrogen bonding only

64. Which choice correctly lists the intermolecular forces present in CH4?

A)London forces only

B)London forces and dipole-dipole forces

C)London forces, dipole-dipole forces, and hydrogen bonding

D)dipole-dipole forces and hydrogen bonding

E)London forces and hydrogen bonding

65. Which choice correctly lists the intermolecular forces present in CH3Br?

A)London forces only

B)London forces and dipole-dipole forces

C)London forces, dipole-dipole forces, and hydrogen bonding

D)dipole-dipole forces and hydrogen bonding

E)London forces and hydrogen bonding

66. Arrange the following substances in order of increasing boiling point: CH3OH, CH4, CH3CH2OH, HOCH2CH2OH

A)CH3OH < CH4 < CH3CH2OH < HOCH2CH2OH B)CH4 < CH3OH < CH3CH2OH < HOCH2CH2OH C)CH4 < CH3OH < HOCH2CH2OH < CH3CH2OH D)CH3OH < CH4 < HOCH2CH2OH < CH3CH2OH E)HOCH2CH2OH < CH3CH2OH < CH3OH < CH4 67. Arrange the following substances in order of increasing boiling point: N2, H2, NH3, PH3 A)H2 < N2 < PH3 < NH3D)N2 < PH3 < NH3 < H2 B)N2 < H2 < PH3 < NH3E)NH3 < PH3 < N2 < H2 C)H2 < N2 < NH3 < PH3 68. Select the pair of substances which has the one with the higher equilibrium vapor pressure at a given temperature listed first. A)H2O, H2SD)NH3, PH3 B)CH3CH2OH, CH3OHE)Br2, Cl2 C)CH3COCH3, CH3CH2CH2OH 69. Select the pair of substances which has the one with the higher equilibrium vapor pressure at a given temperature listed first. A)H2O, HClD)AsH3, PH3 B)CH3OH, CH4E)Cl2, Br2 C)CH3CH2CH2OH, CH3COCH3 70. Which of the following statements regarding the liquid state is incorrect? A)Liquids are about 1000 times denser than gases. B)Liquids are normally about 90-95% as dense as their corresponding solid. C)The viscosity of a liquid is a measure of its resistance to flow. D)Molecules with high intermolecular forces will have high surface tension. E)Surface tension increases as temperature increases.