{"id":32831,"date":"2022-02-14T13:54:42","date_gmt":"2022-02-14T13:54:42","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/3rd-excel-exam-su1-2021\/"},"modified":"2022-02-14T13:54:42","modified_gmt":"2022-02-14T13:54:42","slug":"3rd-excel-exam-su1-2021","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/3rd-excel-exam-su1-2021\/","title":{"rendered":"3rd excel exam su1 2021"},"content":{"rendered":"
\n

\u00a0<\/p>\n

3rd Excel Exam SU1 2021<\/h2>\n

\u00a0<\/p>\n

#3rdExcelExamSU12021<\/p>\n

After graduating from college, you and \u00a0three of your peers founded the software company TechStore Unlimited \u00a0(TSU). TSU provides an online market place that fosters business to \u00a0business (B2B), business to consumer (B2C), and consumer to consumer \u00a0sales (C2C). As one of the company\u2019s principal owners, you have decided \u00a0to compile a report that details all aspects of the business, including: \u00a0employee payroll, facility management, sales data, and product \u00a0inventory. To complete the task you will duplicate existing formatting, \u00a0import data from an Access database, utilize various conditional logic \u00a0functions, complete an amortization table, visualize data with \u00a0PivotTables and Power Maps, connect and transform several external data \u00a0sources, and lastly you will inspect the workbook for issues.<\/p>\n

Start \u00a0\u00a0Excel. Open 3rd Excel Exam SU1 2021.xlsx<\/em>. Grader has automatically added \u00a0\u00a0your last name to the beginning of the filename.<\/p>\n

Create a Data Validation list in \u00a0\u00a0cell J7 based on the employee IDs located in the range A6:A25. Add the Input \u00a0\u00a0Message Select Employee ID<\/strong> and use the Stop Style Error Alert.
\u00a0\u00a0\u00a0Hint: Data Validation is located on the Data tab in the Data Tools group.
\u00a0<\/p>\n

In cell K7, enter an INDEX \u00a0\u00a0functions with two nested MATCH \u00a0functions. The first MATCH function will look up the row number of the \u00a0employee \u00a0\u00a0number in cell J7 in the range A6:A25. The second MATCH \u00a0function will look up the column number of the value \u00a0\u00a0in cell K6 in the \u00a0range B5:H5. Note \u00a0\u00a0K6 contains a validation list that can be used to \u00a0select various lookup \u00a0\u00a0categories.
\u00a0<\/p>\n

\u00a0\u00a0Hint: The arguments of the INDEX are array (B6:H25), Row \u00a0Number,Column \u00a0\u00a0Number. The purpose of the two MATCH \u00a0\u00a0functions is to \u00a0look up the ROW number and COLUMN number. I suggest you do the first \u00a0MATCH function \u00a0\u00a0to ensure it works and then nest it inside of the INDEX \u00a0function. Then nest the second MATCH function in the \u00a0\u00a0INDEX function. \u00a0To test the INDEX \u00a0\u00a0function, use the Data Validation list in cell J7 to \u00a0select Employee_ID 31461<\/em> and select Salary<\/em> in cell K6. Did it \u00a0\u00a0look up the correct salary for the specified employee number?
\u00a0<\/p>\n

Click cell K11 and type FT<\/strong>. Click cell A28 and type Full Time \u00a0\u00a0Employees<\/strong>.<\/p>\n

Use the Format Painter to apply \u00a0\u00a0the formatting from the cell A3 to the range A28:B28.
\u00a0<\/p>\n

\u00a0\u00a0Hint: The Format Painter is located on the
\u00a0\u00a0\u00a0Home tab in the Clipboard group.
\u00a0<\/p>\n

Use Advanced Filtering to \u00a0\u00a0restrict the data to only display FT \u00a0employees based on the criteria in the \u00a0\u00a0range K10:K11. Place the \u00a0results in cell A29.
\u00a0<\/p>\n

\u00a0\u00a0Hint: Advanced Filtering is located on the Data tab in the Sort & Filter \u00a0\u00a0group.
\u00a0<\/p>\n

Enter a database function (DCOUNTA) \u00a0\u00a0in cell K18 to determine the \u00a0total number of FT employees. To complete the \u00a0\u00a0function, use the range \u00a0A5:H25 as the database argument, cell E5 for the \u00a0\u00a0field, and the range \u00a0K10:K11 for the criteria.
\u00a0<\/p>\n

\u00a0\u00a0Hint: To access DATABASE functions, click insert function, and select \u00a0\u00a0Database from the function category menu.
\u00a0<\/p>\n

Enter a database function in \u00a0\u00a0cell K19 (DSUM) to determine the total \u00a0value of FT employee salaries. To \u00a0\u00a0complete the function, use the \u00a0range A5:H25 as the database argument, cell H5 \u00a0\u00a0for the field, and the \u00a0range K10:K11 for the criteria.
\u00a0<\/p>\n

\u00a0\u00a0Hint: To access DATABASE functions, click insert function, and select \u00a0\u00a0Database from the function category menu.
\u00a0<\/p>\n

Enter a database function \u00a0\u00a0(DAVERAGE) in cell K20 to determine the \u00a0average FT employee salary. To \u00a0\u00a0complete the function, use the range \u00a0A5:H25 as the database argument, cell H5 \u00a0\u00a0for the field, and the range \u00a0K10:K11 for the criteria.
\u00a0<\/p>\n

\u00a0\u00a0Hint: To access DATABASE functions, click insert function, and select \u00a0\u00a0Database from the function category menu.
\u00a0<\/p>\n

Enter a database function in \u00a0\u00a0cell K21(DMAX) to determine the \u00a0highest FT salary. To complete the function, \u00a0\u00a0use the range A5:H25 as \u00a0the database argument, cell H5 for the field, and the \u00a0\u00a0range K10:K11 \u00a0for the criteria.
\u00a0<\/p>\n

\u00a0\u00a0Hint: To access DATABASE functions, click insert function, and select \u00a0\u00a0Database from the function category menu.
\u00a0<\/p>\n

Format the range K19:K21 with \u00a0\u00a0Currency Number Format.
\u00a0<\/p>\n

\u00a0\u00a0Hint: Currency Number Format is located on the
\u00a0\u00a0\u00a0Home tab in the Number Group.
\u00a0<\/p>\n

Use PowerQuery to connect and \u00a0\u00a0open the Orders table in the eApp_Cap_Orders.accdb<\/em> \u00a0\u00a0database. Use the Query editor to format column A with Date number format and \u00a0\u00a0load the table. Rename the worksheet Orders<\/strong>.
\u00a0\u00a0\u00a0Hint: Data can be imported using Get & Transform data tools \u00a0located on \u00a0\u00a0the Data tab in the Get & Transform data group.
\u00a0<\/p>\n

Adapt the previous step to \u00a0\u00a0connect and load the Warehouse table.
\u00a0<\/p>\n

\u00a0\u00a0Hint: Data can be imported using Get & Transform data tools \u00a0located on \u00a0\u00a0the Data tab in the Get & Transform data group.
\u00a0<\/p>\n

Connect to, but don’t load the \u00a0\u00a0Inventory table from the eApp_Cap_Orders.accdb<\/em> \u00a0\u00a0database.\u00a0<\/p>\n

Create the following \u00a0\u00a0relationships.
\u00a0<\/p>\n

\u00a0\u00a0Relationship 1
\u00a0\u00a0\u00a0Table Name
\u00a0\u00a0\u00a0Inventory
\u00a0<\/p>\n

\u00a0\u00a0Column (Foreign)
\u00a0\u00a0\u00a0Warehouse
\u00a0<\/p>\n

\u00a0\u00a0Table
\u00a0\u00a0\u00a0Warehouse
\u00a0<\/p>\n

\u00a0\u00a0Column (Primary)
\u00a0\u00a0\u00a0Warehouse
\u00a0<\/p>\n

\u00a0\u00a0Relationship 2
\u00a0<\/p>\n

\u00a0\u00a0Table
\u00a0\u00a0\u00a0Orders
\u00a0<\/p>\n

\u00a0\u00a0Column (Foreign)
\u00a0<\/p>\n

\u00a0\u00a0Item_Number
\u00a0\u00a0\u00a0Table
\u00a0\u00a0\u00a0Inventory
\u00a0<\/p>\n

\u00a0\u00a0Column (Primary)
\u00a0<\/p>\n

\u00a0\u00a0Item_Number
\u00a0<\/p>\n

\u00a0\u00a0Hint: Relationships are located on the Data tab in the Data Tools group.
\u00a0<\/p>\n

Use PowerPivot to create a blank \u00a0\u00a0PivotTable on a new worksheet. Add the following fields to the PivotTable.
\u00a0\u00a0\u00a0Rows
\u00a0\u00a0\u00a0Warehouse: Location
\u00a0\u00a0\u00a0Warehouse: Warehouse
\u00a0\u00a0\u00a0Inventory:
\u00a0\u00a0\u00a0Item_Number
\u00a0\u00a0\u00a0Values
\u00a0\u00a0\u00a0Inventory:
\u00a0\u00a0\u00a0Current_InventoryInventory:
\u00a0\u00a0\u00a0Total_Value<\/p>\n

Insert a Slicer based on \u00a0\u00a0Warehouse. Place the upper left corner of the Slicer inside the borders of \u00a0\u00a0cell F3.
\u00a0<\/p>\n

\u00a0\u00a0Hint: Slicers are located on the Insert tab in the Filters group.
\u00a0<\/p>\n

Create a 3D PowerMap that \u00a0\u00a0displays the location of all warehouses based on the City geographic type. \u00a0\u00a0Rename the worksheet Inventory<\/strong>.
\u00a0\u00a0\u00a0Hint: 3D Maps is located on the Insert tab in the Tours group.
\u00a0<\/p>\n

Save and close the 3rd Excel \u00a0\u00a0Exam SU1 2021. Go to the Test \u00a0\u00a0Presentation Window. Upload the exam \u00a0\u00a0and submit it for grading\u00a0<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u00a0 3rd Excel Exam SU1 2021 \u00a0 #3rdExcelExamSU12021 After graduating from college, you and \u00a0three of your peers founded the software company TechStore Unlimited \u00a0(TSU). TSU provides an online market place that fosters business to \u00a0business (B2B), business to consumer (B2C), and consumer to consumer \u00a0sales (C2C). As one of the company\u2019s principal owners, you […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-32831","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/32831","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=32831"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/32831\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=32831"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=32831"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=32831"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}