Techniques

Excel vs. Machine Learning

Some background:

• My career began in internal audit.
• As an ICAEW Chartered Accountant, I developed a strong foundation in financial analysis and process improvement.
• However, I soon realized that classical methods were insufficient to address the complexities and scale of modern business challenges.
• My pivot to data science and AI has enabled me to leverage advanced analytics techniques to solve complex problems.

In this post, I compare traditional Excel-based methods with modern machine learning approaches.

Scenario 1: Inventory Ledger – Detecting Stock Anomalies

A manufacturing company notices discrepancies between recorded and actual stock. The goal is to identify unusual stock movement patterns.

A. Classical Excel Techniques:

Technique: Conditional Formatting and Threshold Filtering

1. Use Excel functions like IF, AVERAGE, and STDEV to calculate thresholds.
2. Highlight outliers using conditional formatting.

Steps in Excel:

• Calculate the mean (=AVERAGE(range)) and standard deviation (=STDEV(range)) of inventory usage for each product.
• Flag values that deviate beyond ±2 standard deviations.

Example Data:

Limitations:

• Only identifies anomalies based on fixed thresholds (±2 SD).
• Ignores multivariate patterns (e.g., seasonality or correlations).

B. Modern ML Techniques:

Technique: Isolation Forest for Anomaly Detection

Python Code:

import pandas as pd
from sklearn.ensemble import IsolationForest

# Sample Inventory Data
inventory_data = pd.DataFrame({
    'product_id': [1001, 1002, 1003, 1004],
    'month_1_usage': [500, 300, 100, 700],
    'month_2_usage': [510, 305, 110, 720],
    'month_3_usage': [2000, 290, 105, 730]
})

# Model Training
X = inventory_data[['month_1_usage', 'month_2_usage', 'month_3_usage']]
model = IsolationForest(random_state=42)
inventory_data['anomaly_score'] = model.fit_predict(X)

# Flagging Anomalies
inventory_data['is_anomaly'] = inventory_data['anomaly_score'] == -1
print(inventory_data)

Output:

Benefits of ML:

• Captures multivariate relationships between usage patterns across months.
• Adapts to non-linear data distributions.

Scenario 2: Accounting Ledger – Detecting Suspicious Transactions

An e-commerce company suspects fraudulent transactions in its general ledger.

A. Classical Excel Techniques:

Technique: Rule-Based Filters

1. Use logical Excel formulas (IF, AND, OR) to filter based on known risk patterns.
2. Example Rules:
3. Transactions > $10,000.
4. Transactions on weekends or public holidays.
5. Payments split into multiple smaller amounts.

Steps in Excel:

• Apply filters for conditions like large values (>10000) or weekend dates (=TEXT(date, "ddd")).

Example Data:

Limitations:

• Requires pre-defined rules, missing unknown fraud patterns.
• Inflexible for complex conditions.

B. Modern ML Techniques:

Technique: Clustering Transactions with K-Means

Python Code:

from sklearn.cluster import KMeans
import numpy as np

# Sample Transaction Data
transaction_data = pd.DataFrame({
    'transaction_id': [2001, 2002, 2003, 2004],
    'amount': [15000, 5000, 100, 700],
    'weekday': [2, 0, 3, 5],  # Mon=0, ..., Sun=6
    'hour_of_day': [14, 20, 11, 22]
})

# Clustering
X = transaction_data[['amount', 'weekday', 'hour_of_day']]
kmeans = KMeans(n_clusters=2, random_state=42)
transaction_data['cluster'] = kmeans.fit_predict(X)

# Flag Anomalous Cluster
transaction_data['is_anomaly'] = transaction_data['cluster'] == transaction_data['cluster'].value_counts().idxmin()
print(transaction_data)

Output:

Benefits of ML:

• Automatically groups similar transactions without manual rule-setting.
• Identifies outliers based on clustering of similar patterns.

Scenario 3: Entity Resolution – Matching Vendor Names

• You are tasked with reconciling a dataset where transaction descriptions are inconsistent due to typos, abbreviations, and variations in format (e.g., "Vendor A" appears as "Vndr A", "Vendor_A", "VendorA").
• Accurate reconciliation requires grouping similar descriptions to identify discrepancies or summarize totals by vendor.

A. Classical Excel Techniques:

Technique: Manual Filtering and String Matching in Excel

This technique relies on manual string matching and tools like Excel's VLOOKUP or IF formulas.

Example Workflow:

1. Manually Inspect Data: Identify inconsistencies visually.
2. Exact Matching: Use IF or COUNTIF to match strings exactly.

excel =IF(A1="Vendor A", "Match", "No Match")

1. Partial Matching: Use SEARCH or FIND for substring matching.

excel =IF(ISNUMBER(SEARCH("Vendor", A1)), "Potential Match", "No Match")

Limitations:

• Misses non-obvious variations (e.g., "Vndr_A" will not match "Vendor A").
• Time-consuming for large datasets.
• Prone to human error and lacks scalability.

B. Modern ML Techniques:

Technique: Using Fuzzy String Matching and Pre-processing

Python libraries like fuzzywuzzy or rapidfuzz can be used to group similar entries, automating the matching process while handling noise.

Python Code:

from rapidfuzz import process, fuzz
import pandas as pd

# Sample Data
data = pd.DataFrame({
    'transaction_id': [1, 2, 3, 4, 5],
    'vendor': ['Vendor A', 'Vndr A', 'Vendor_A', 'Vendor B', 'Vendoor C'],
    'amount': [100, 200, 150, 300, 250]
})

# Define Reference List of Vendors
reference_vendors = ['Vendor A', 'Vendor B', 'Vendor C']

# Fuzzy Matching
data['matched_vendor'] = data['vendor'].apply(
    lambda x: process.extractOne(x, reference_vendors, scorer=fuzz.ratio)[0]
)

print(data)

Output:

Advantages of this Approach:

1. Scalability: Automates reconciliation for thousands of rows in seconds.
2. Accuracy: Handles complex variations, typos, and abbreviations reliably.
3. Customization: Adjust similarity thresholds and match logic dynamically.
4. Integration: Pre-processed fuzzy data can feed into machine learning models for anomaly detection, clustering, or forecasting.

Use Case Example: Combining Techniques:

Scenario: A retailer processes invoices from hundreds of suppliers. Many supplier names are misspelled or abbreviated, making it hard to reconcile payments.

Key Takeaways

1. Excel Techniques:
2. Rule-based and statistical approaches are effective for structured problems with clear thresholds.
3. Limited scalability and accuracy for complex or multivariate data.

4. Without fuzzy matching, manual reconciliation is time-consuming and error-prone.

5. ML Techniques:

6. Handle multivariate and nonlinear relationships, finding hidden patterns.
7. Adaptable to large datasets and dynamic patterns.
8. Require basic programming knowledge but provide significant insights over traditional methods.
9. Fuzzy matching automates data pre-processing, enhancing accuracy and scalability.