2. Devon wants to purchase a Cat and a Spider but he is not sure which supplier is the best. Assuming that the quality of pets supplied by all the suppliers is the same, Devon wants to know how suppliers compare in terms of other parameters such as the markup and delivery time. Complete the following tasks to help Devon finalize his purchase: a. Design a query that includes relevant fields for the problem (read the whole question to determine which fields are needed) along with two customized columns named Delivery Time and Markup. Name this query as qryCompareSuppliers. These customized columns can be input by the following way: NewVariableName: Formula Hence, the two new columns are defined in this way: Delivery Time: ReceiveDate - OrderDate Markup: ListPrice - Cost b. In your Excel file, import the query data and convert it into a range. Create a pivot table to find the Average Delivery time and Average Markup for each supplier. c. Filter the data in your pivot table from the previous step and only focus on Cat. Copy your result into a new worksheet and name it as Cat Supplier. Now, use the rank.avg function to rank the suppliers in ascending order based on Average Delivery time. Save the results in a column. The suppliers with the lowest time gets Rank 1. Similarly rank the suppliers based on Average Markup and save the results in a different column. The supplier with the lowest cost gets rank 1. Create a column Final Rank which is the sum of the two ranks for each supplier. (Hint: use absolute cell reference in rank.avg function.) Repeat all the steps for Spider Data and put on a separate worksheet. Answer the corresponding questions 1. Which supplier(s) (name) should Devon choose for his Cat purchase? What is the final rank? 2. Which supplier(s) (name) should Devon choose for his Spider purchase? What is the final rank?

Follow the steps in the question showing how to write this query. Complete subquestions A,B,C. And answer the last two sub-questions.