3rd excel exam su1 2021

 

3rd Excel Exam SU1 2021

 

#3rdExcelExamSU12021

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 

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Paper Helper has assembled a team of highly skilled writers with diverse experience in the online writing circles. Our aim is to become a one stop shop for all your Academic/ online writing. Check out below our amazing service!

Essays

Essay Writing Services

At Paper Helper, we prioritize on all aspects that creates a good grade such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines. The principal purpose of essay writing is to present the author's evaluation concerning a singular subject about which they have made. Since Professionalism is the mother of every success, try our team of experienced writers in helping you complete your essays and other assignments.

Admissions

Admission Papers

You have been trying to join that prestigious institution you long yearned for, but the hurdle of an admission essay has become a stumbling block. We have your back, with our proven team that has gained invaluable experience over time, your chance of joining that institution is now! Just let us work on that essay.How do you write an admission essay? How do you begin the essay? For answers, try Quality Custom Writers Now!

Editing

Editing and Proofreading

Regardless of whether you're pleased with your composing abilities, it's never an impractical notion to have a second eye go through your work. The best editing services leaves no mistake untouched. We recognize the stuff needed to polish up a writing; as a component of our editing and proofreading, we'll change and refine your write up to guarantee it's amazing, and blunder free. Our group of expert editors will examine your work, giving an impeccable touch of English while ensuring your punctuation and sentence structures are top-notch.

Coursework

Technical papers

We pride ourselves in having a team of clinical writers. The stringent and rigorous vetting process ensures that only the best persons for job. We hire qualified PhD and MA writers only. We equally offer our team of writers bonuses and incentives to motivate their working spirit in terms of delivering original, unique, and informative content. They are our resources drawn from diverse fields. Therefore your technical paper is in the right hands. Every paper is assessed and only the writers with the technical know-how in that field get to work on it.

Coursework

College Essay Writing

If all along you have been looking for a trustworthy college essay service provider that provides superb academic papers at reasonable prices, then be glad that you search has ended with us. We are your best choice! Get high-quality college essay writing from our magnificent team of knowledgeable and dedicated writers right now!

Coursework

Quality Assignment/Homework Help

We give the students premium quality assignments, without alarming them with plagiarism and referencing issues. We ensure that the assignments stick to the rules given by the tutors. We are specific about the deadlines you give us. We assure you that you will get your papers well in advance, knowing that you will review and return it if there are any changes, which should be incorporated.