Business Analyst Excel Questions ( What would be the formulas )

Below is an explanation of the data in each sheet:

• Orders: January

  • Orders placed in January 2020. Order Number is a unique identifier of an instance of a customer placing an order on a date. Order Date is the date that the order was placed. Customer ID is the unique identifier for a customer. Product ID is the unique identifier for a product. Quantity Ordered is the number of units ordered for the product in a given row, on the order in that row.

• Orders: February

  • Same as Orders: February, but with data for February

• Orders: March

  • Same as Orders: March, but with data for March

• Customers

  • Customer details. Customer ID is the unique identifier for a customer.

• Products

  • Product details. Product ID is the unique identifier for a product.

• Shipment Status History

  • History of shipment statuses for orders. When the products in an order ship to a customer, a shipment status of SHIPPED is recorded. When a shipment is delivered to the customer, a shipment status of DELIVERED is recorded.

Exercise 1: Quantitative Questions

Using the data provided, please answer the following questions:

1. What is the total quantity of product CK-M-20015 ordered in January 2020?

2. How many orders were placed on Jan 16, 2020?

3. How many unique customers ordered Math Curriculum Kits (Product Category = Curriculum Kit and Product Subject = Math) in March 2020?

4. In 2020 Q1, what were the top-three Teacher Guides in terms of quantity ordered?

5. Based on all available order data, what were the top-three states to which products shipped, in terms of numbers of unique orders placed?

6. Days to Fulfill is defined as the number of days between the date an order is placed and the date an order is delivered. Based on all available order data, which Ship to State(s) have an average Days to Fulfill between 4 and 5?

7. Some orders have not yet been delivered. Given the data provided, which customer has been waiting the longest from the time that their order was placed?

8. What is the total quantity of product: Language Arts Student Workbook - Grade 4 (Set Of 25) that has been ordered, but not yet delivered, from January-March?