2.P1 Practice: warehousing sales analysis

Analyze the sales data independently. Use the worksheet names, columns, and CSV format specified below. Save a working copy of the supplied workbook, preserve the received file, and save your work after each stage.

Case study: transactional data analysis

The sales workbook contains the history of a stationery wholesaler that sells office supplies, writing instruments, and paper products. The data is stored in two worksheets, Raw and Products. Each row in Raw records the number of units of one product ordered by a customer. Table 1 presents the first 10 rows of the Raw worksheet.

Table 1: First 10 rows of the “Raw” worksheet.
A B C D E F G H I J
1 Product ID Order ID Order line number Customer ID Selling units ordered Selling units shipped Selling unit of measure Order date time Vendor ID Weight (Pounds)
2 ACE07020 23927770 1 1051206 2 2 EA 2004-12-01 06:52 AVT 60.0
3 ESS21-150-4 23927778 1 1046550 3 3 EA 2004-12-01 07:30 ESS 17.0
4 GPL10103 23927796 1 552321 1 1 EA 2004-12-01 08:05 GPL 2.0
5 GPL10110 23927796 2 552321 1 1 EA 2004-12-01 08:05 GPL 5.0
6 RUB25010 23927798 1 552321 1 1 EA 2004-12-01 08:08 ROG 4.25
7 AVE72611 23927801 1 552321 1 1 PK 2004-12-01 08:09 AVE 23.4
8 HON5403AB12T 23927803 1 552321 1 0 EA 2004-12-01 08:10 H3N 65.0
9 SMD74236 23927825 1 552321 5 5 EA 2004-12-01 08:26 S2E 15.4
10 SMD74241 23927825 2 552321 5 5 EA 2004-12-01 08:26 S2E 15.4

Detailed product descriptions are available in the Products worksheet. Table 2 displays the first 10 rows of the Products worksheet.

Table 2: First 10 rows of the “Products” worksheet.
Product ID Vendor ID Item description Weight (Pounds)
0 AAG70-005-05 A2G APPT BK,WKLY,CLCS,3X6,BK 11.00
1 AAG70-064-05 A2G PLNR,DLX,MO,PCKT,BK 8.06
2 AAG70-130-05 A2G PLNR,CLSC,MTLY,8X10,BK 13.00
3 AAG70-203-05 A2G APPT BK,DLY,BK 13.00
4 AAG70-212-75 A2G APPT BK,8PER,DLY,8.5X11,BK 13.20
5 AAG70-655-05 A2G APPT,WK/MO,6.9X8.75,TEL,BK 5.77
6 AAG70-800-05-05 A2G APPT BK,DLY,CLSC,BK 16.50
7 AAG70-800-20 A2G APPT BK,DLY,CLSC,NY 16.50
8 AAG70-950-05-05 A2G APPT BK,PROF,WKLY,8X11 11.70
9 AAG80-905-10 AAG REFILL,F/G440,-550,70-064 11.25
NoteTransactional data

Transactional data records sales and stock changes. You can use these records to compare sales patterns and identify best-selling products.

The Raw worksheet contains the following key columns (among others):

  • Product ID: A unique identifier for each product.
  • Order ID: A unique identifier for each order (i.e., a group of purchased products).
  • Order line number: The sequential position of a product in an order. A single order may include multiple products. For example, if an order contains products P001, P002, and P003, their respective Order line number values will be 1, 2, and 3.
  • Customer ID: A unique identifier for each customer.
  • Selling units ordered: The quantity of a product ordered by a customer. For example, if a customer purchases 3 units of product P001, the Selling units ordered value will be 3.
  • Order date time: The timestamp when the order was placed.

The Products worksheet provides additional details about each product, including:

  • Product ID: A unique identifier for each product.
  • Vendor ID: A unique identifier for each vendor.
  • Item description: A detailed description of the product.
  • Weight (Pounds): The product’s weight in pounds.

Clean the data (worksheet Processed)

  1. Create a worksheet named Processed. Copy the data and headers from columns Product ID, Order ID, Order line number, Customer ID, Selling units ordered, and Order date time in Raw into Processed. Preserve Raw unchanged.
  2. Turn the data into an Excel Table.
  3. Create a column Order date containing only the date part of the column Order date time (e.g., 2004-12-01 00:33 becomes 2004-12-01). Change the number format of values in this column to a Date format.
  4. Create a column Order time containing only the time part of the column Order date time (e.g., 2004-12-01 00:33 becomes 00:33). Change the number format of values in this column to Time.
  5. Remove the column Order date time.
  6. Delete all entries where Order ID is 0. Make sure to remove the entire row.

Add product details

  1. In Worksheet Processed, add a column Item description and use the VLOOKUP or XLOOKUP functions to retrieve each product description from the worksheet Products.
  2. In Worksheet Products, create a column Shipping Category to classify products based on their weight (Weight (Pounds)). Use the following categories:
    • Parcel: Weight < 10
    • Bulk: 10 ≤ Weight < 50
    • Freight: Weight ≥ 50
TipFunction guide
  • XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]): Searches a range or an array, and returns an item corresponding to the first match found.
    • lookup_value: The value to search for.
    • lookup_array: The range or array to search.
    • return_array: The range or array to return a value from.
    • if_not_found: The value to return if no match is found.
    • match_mode: The type of match to perform.
    • search_mode: The type of search to perform.
  • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Searches for a value in the first column of a table array and returns a value in the same row from another column.
    • lookup_value: The value to search for.
    • table_array: The range of cells that contains the data. Note that the first column in this range must contain the lookup value.
    • col_index_num: The column number in the table from which to retrieve the value.
    • range_lookup: A logical value that specifies whether you want an exact or approximate match.
  • IF(logical_test, [value_if_true], [value_if_false]): Checks whether a condition is met and returns one value if TRUE and another if FALSE.
    • logical_test: The condition to check.
    • value_if_true: The value to return if the condition is TRUE.
    • value_if_false: The value to return if the condition is FALSE.

Format the data (worksheet Processed)

  1. Order columns as follows (from left to right):
    1. Order date
    2. Order time
    3. Order ID
    4. Order line number
    5. Product ID
    6. Item description
    7. Selling units ordered
    8. Customer ID
  2. Adjust column widths such that all headings are visible.
  3. Freeze the first three columns (Order date, Order time, and Order ID) and the top row.
  4. Sort the data hierarchically by:
    • 1st Level: Order ID (smallest to largest)
    • 2nd Level: Order line number (smallest to largest)
  5. Apply conditional formatting to highlight the cells in column Selling units ordered that contain a value greater than 10. Apply any background color of your choice (e.g., Light Red Fill with Dark Red Text).

Analyze sales (worksheet Pivot)

  1. Create a PivotTable based on the data from the Processed worksheet and place it in a new worksheet called Pivot.
  2. Display the sum of Selling units ordered per Product ID.
  3. Sort the table in descending order (i.e., from the highest to the lowest sum) to identify the best-selling items.
  4. Export the PivotTable to a .CSV named Excel_[Last Name]_[First Name]_[Student number].csv. Use this format:
    • Columns should be separated by commas.
    • The name of first column should be Product ID.
    • The name of the second column should be Total Selling Units.
    • The file cannot contain Grand Totals or Subtotals.