Module 2: Tables, Sorting & Filtering
Module 2 Overview
Business Analytics: Methods, Models, and Decisions, Chapter 2
Focus Areas:
Datasets and databases
Setting up range names for data
Table-based calculations and sorting
Pareto Analysis
Filtering with purchase orders data
Learning Outcomes
Ability to create an Excel data Table
Understanding various forms of business datasets
Data manipulation in Tables (filtering and sorting)
Techniques for exploring large datasets
Comprehensive understanding of Pareto Analysis
Advanced data filtering methods
Data Sets and Databases
Definition
Data Set: A collection of data (e.g., marketing survey responses, historical stock prices).
Database: A structured collection of related files containing records on entities (people, places, things).
Organized as a two-dimensional table (columns = fields/attributes, rows = records).
Example: Purchase Order Database
Each row represents a record; each column represents a field (e.g., Alum Sheeting, Order No., Item No.).
Using Range Names for a Database
Define range names for data:
Each row = month
Each column = product
Example: Range name for January.
Use named ranges for calculations, e.g., annual sales of Product A.
Case Study: Big Data in Water Monitoring
Town of Cary installed wireless meters for better water usage tracking.
Increased data collection from 12 monthly readings to 8,760 annual readings per customer.
Analyzed half-a-billion data points using SAS software.
Benefits:
Early leak detection for customers.
Cost savings estimated at over $10 million.
Proactive water usage tracking for customers.
Enhanced future infrastructure planning based on accurate data.
Data Queries: Tables in Excel
Creating an Excel Table
Steps to create:
Select data range including headers.
Go to Insert > Table, ensure "My Table Has Headers" is checked.
Alter table names via Table Design > Properties.
Example: Credit Risk Data Table
Data table allows automatic updates for calculations as new records are added.
Data Queries: Sorting Data
Sorting procedure:
Select a cell (non-header) in the target column.
Use Data tab buttons to sort.
Click Sort for advanced sorting options.
Example: Sorting Purchase Orders Database
Example sorting data by supplier.
Pareto Analysis
Concept
Based on Vilfredo Pareto's observation: 80% of effects come from 20% of causes.
Emphasizes that a small number of items often contribute to a large portion of results (sales, etc.).
Application
Involves sorting data and calculating cumulative proportions (e.g., sales).
Example: Bicycle Inventory Database
Compute inventory value of each product and determine cumulative percentage of total inventory value.
Data Queries: Filtering Data
Filtering Overview
Filtering selects records that meet certain criteria.
Two methods:
AutoFilter for simple criteria
Advanced Filter for complex criteria.
Example: Filtering Purchase Order Records
Filtering by Item Description
Select a cell, click Filter, and choose specific item criteria.
Filtering by Item Cost
Identify records with item cost $200 or more using Custom AutoFilter.
Caution with Filtering
Filtering hides records but does not extract them. Ensure to copy filtered data to use only visible records for calculations.
Case Study: Allders International
Manages duty-free shops and uses a data warehouse to handle point-of-sale data efficiently.
Improved analytics over paper-based processes allowed quick and effective inventory management decisions.
Pareto analysis revealed that 20% of products accounted for 80% of profits, guiding inventory optimization.
Advanced Filter
Features
Allows explicit criteria definition for data filtering.
Copy database headers and specify criteria underneath.
Example: Advanced Filter Usage
For specific order quantity and dates conditions, choose Advanced option under Filter in the Data tab. Display records meeting specified criteria.